Welcome to the 175th edition of Log Buffer, the weekly review of database blogs.
Oracle
Let’s begin with remoteDBAexperts blog, and Chris Foot’s prediction of the future of database tuning and database administration. It will be, ” . . . administrators interpreting and implementing the recommendations generated by the intelligent advisors and ADDM. . . . I also think that Oracle will eventually become self-tuning.”
Here in the present, DBAs (Oracle and otherwise) are still Striving for Optimal Performance as Christian Antognini is. Here’s his item on join elimination, which he introduces thus: “In some specific situations the query optimizer is able to completely avoid executing a join even if a SQL statement explicitly calls for it. Two are the cases currently covered by this optimization technique, which is called join elimination.”
Jeff Hunter of the So What Co-operative was at the optimizer too, and found an interesting optimizer result, and pursued it into Interesting Optimizer Result, Part II. “If the query included the package call in the WHERE clause, the query finished in over an hour. If the package call was not in the WHERE clause, the query finished in 5 minutes (but did not return the correct results). . . . Confident in my fondness for inline views, I ran the query fully expecting to get the results back in a few minutes. Except the query went on, and on, and on for a full 15 minutes before I killed it.”
Kellyn Pedersen discusses what to do when PGA size is not enough. Kellyn says, “I’ve come to realize that most folks don’t really understand the limitations in the allocation of PGA memory to hashing and sorting. They truly believe that if they set their PGA high enough, the database will just allocate as much memory as they have in the setting towards any hash or sort process, when in truth, there is a ‘threshold’ . . . ”
Marko Sutic writes, “Before database migration to new version it is always wise to test how will applications work on this new version. . . . But what if you have in production very big database and your test storage is limited – then partial restore of a database comes as handy solution.”
PostgreSQL
The End Point Blog looks at the flip-side of the restore, the backup: Postgres SQL Backup Gzip Shrinkage, aka DON’T PANIC!!!, exposing a little wrinkle in pg_dump.
Leo Hsu and Regina Obe of the Postgres OnLine Journal show just why they are looking forward to PostgreSQL 8.5.
Hubert Lubacziewski is likewise waiting for 8.5 – PL/pgSQL by default being one of the reasons. ” . . . Basically from 8.5 on PL/pgSQL will be enabled by default in all databases.”
Peter Eisentraut also is excited— . . . the next PostgreSQL release will be a great one for procedural languages.”
When the time comes to do that upgrade, The End Point Blog’s post, Postgres Upgrades – Ten Problems and Solutions will prove a useful resource.
Here is David Wheeler with an SQL hack: the something-est from each entity.
Andrew Dunstan has been looking at the MySQL/Monty/Oracle controversy, and feels what he calls, MySchadenfreude. He writes, “I don’t really take great joy in the turmoil in the MySQL world, but it’s very tempting every time that Richard Stallman or Monty Widenius open their mouths on the subject not to cheer on the opposition. . . . And I don’t appreciate misrepresentations and FUD concerning Postgres in Monty’s latest rant. . . . I don’t think Oracle will do MySQL much good, and I wouldn’t trust them as far as I could kick them. But that doesn’t mean we should just embrace any bad argument against the acquisition that comes along.”
MySQL
The good news, according to Mark Hinkle of the Socialized Software blog, is that MySQL and PostgresSQL jobs are on the Rise, Oracle job postings decline, according to statistics from indeed.com.
Charity Linden of the Second Life Blog, shares a Diary of a Paranoid MySQL Upgrade, which begins, “At 6 am on January 6th, our central database was upgraded from mysql 4.1 to 5.0. . . . This was not our first shot at an upgrade. We first tried to upgrade way back in November of 2007, but it turned out that 5.0 was just not fast enough . . . After two or three long, wretched days of cascading downtimes, degraded services, and intermittent data loss, we gave up and rolled back to 4.1, all thoroughly traumatized by the experience. . . . This is the story of our successful second attempt, and all the things we learned and checked and verified in order to make it successful.”
Masterzen’s Blog announces the launch of mysql-snmp 1.0, an SNMP monitoring for MySQL. “mysql-snmp is a mix between the excellent MySQL Cacti Templates and a Net-SNMP agent. The idea is that combining the power of the MySQL Cacti Templates and any SNMP based monitoring would unleash a powerful mysql monitoring system.”
Morgan Tocker reveals his MySQL Conference submission: workarounds. He writes, “Domas likes to do tricks with GDB. Sheeri likes symlinking log files to /dev/null when not in use. Peter likes to do what he calls a delayed join, and I like to use IN() lists. . . . What’s your favorite workaround? Are there any that you’ve seen people use in production that no longer work (or could be considered harmful)?”
Morgan’s colleague on the MySQL Performance Blog, Peter Zaitsev, writes, “One of the problems I have with Memcache is this cache is passive . . . This means application using Memcache has to has to special logic to handle misses from the cache, being careful updating the cache – you may have multiple data modifications happening at the same time. Finally you have to pay with increased latency constructing the items expired from the cache, while they could have been refreshed in the background. I think all of these problems could be solved with concept of active cache.”
SQL Server
The Rambling DBA, Jonathan Kehayias tells the story of his downgrading from SQL 2008 to 2005. He says, “Every couple of weeks on one of the forums someone will ask a question about how to restore a backup from SQL 2008 to SQL 2005. The answer to the question is always, you can’t restore a backup to a lower version of SQL Server. If you need to migrate backwards it is an entirely manual process.”
Paul S. Randall challenges some misconceptions around database snapshots and transaction rollbacks with, ” . . . a quick post to clarify an article I saw . . . this morning that seemed to state that transaction rollbacks push data into database snapshots. This is absolutely not true.”
Henk Van Der Valk examines an approach to optimizing SQL in-memory table scan processing speed. He says, “How fast can you can actually read data from a table that is already loaded in memory? . . . People often assume that, once it’s in memory, it’s as fast as it can get and won’t get any faster? But how fast is it really?”
Here’s another question: Would you optimize SQL for less performance? So asks Linchi Shea.
Jamie Thompson, the SSIS Junkie, bemoans what he deems the SQL developer gap: “I look around at our cousins in .Net land and I see their fancy WPF code editors, extensible languages . . . fluent this-that-and-the-other etc… and I can’t help but feeling a little hard done by. (Anyone that has used Resharper for .Net will know exactly what I mean.) Am I the only SQL guy that feels like a second class citizen in the Microsoft developer ecosystem?”
Buck Woody needs your help on a like matter, the database design process. He says, ” I know how I create databases, and I’ve watched a lot of other data professionals follow their own processes for that, but I want to know how YOU do it. . . . I currently use DBDesignerFork . . . which is not a perfect tool. But Microsoft doesn’t have a good one . . . ”
That’s all for this edition of Log Buffer. As always, you’re welcome to note your own favourite DB blogs from this week in the comments.
Till next time!
No comments