Welcome to the 147th edition of Log Buffer, the weekly review of database blogs.
Let’s start this week, with blogs from the SQL Server world, where a number of excellent technical posts appear. Alexander Kuznetsov surprises his readers with this assertion: without ORDER BY, there is no default sort order. “Sounds trivial? Right, but different flavors of this myth still persist. . . . Because apparently many visitors agreed with this myth, I decided to post a repro script which demonstrates that this is simply not true.”
In their post on Read/Write deadlock, the rusano.com blog likewise was answering a question, how does a simple SELECT deadlock with an UPDATE? “Surprisingly, they can deadlock even on well tuned systems that does not do spurious table scans. The answer is very simple: when the read and the write use two distinct access paths to reach the same key and they use them in reverse order.” Examples and fixes follow.
Linchi Shea looks for an answer to this mystery—why the tempdb log keeps growing. Linchi says, “Whether it’s a user database or tempdb, when its transaction log is filled up or forced to autogrow, large long-running transactions should not be the only potential culprit for you to track down.”
The SQL Programmability & API Development Team Blog was also concerned by the matter of unexpected growth, in their case seeking to provide an answer to the question, Why did the size of my indexes expand when I rebuilt my indexes?
On a related matter, Paul S. Randal gave an example of a nasty cluster key on In recovery… Paul’s readers offers examples from their own experience of disastrous primary keys and clustered indexes.
Michelle Ufford, the SQL Fool, covers overhead in non-unique clustered indexes, giving a walk-through of scenarios in which the DBA will encounter this.
In the Oracle ‘sphere, Hemant K Chitale examines 90-10 Index Block Splits and their possible causes.
Jonathan Lewis has the perfect thing to follow your Log Buffer read—a lunchtime quiz on the Oracle Scratchpad. There are lots of answers already posted, so no peaking below the fold.
Pythian’s André Araujo responds to a different test with his very elegant NoCoug SQL Challenge Entry.
Girlgeek Claudia Zeiler made problems for herself: “Playing around with my toy database I asked myself, ‘What happens if DUAL has more than 1 row?’ I found out.” Marco Gralike offers some relevant links in his comment. Read and learn.
“I’ll Gladly Pay You Tuesday for a Hamburger Today,” quotes Kerry Osborne, who was in a Wimpy frame of mind at the time. The analogy to J. Wellington Wimpy’s burger-mooching , Kerry writes, applies to, ” . . . paying the price for our bad/missing statistics.”
Here’s Laurent Schneider with a tip on how to reuse a connection in the shell.
MySQL bloggers are still very taken up with the future of their beloved DBMS in the wake of its acquisition by Oracle Corporation.
Curt Monash of DBMS2 observed, “MySQL forking heats up, but not yet to the benefit of non-GPLed storage engine vendors”.
Paul McCullagh of PrimeBase XT writes, At last we have a MySQL Foundation, its called The Open Database Alliance. “What is really most important,” Paul writes, “is that we in the community now have an entity that is going to tie our side of things together . . . For the community it is critical that things do not split up any further and that instead our efforts are bundled. I believe the Alliance can do this for us.”
Or is it already too late? Neil McAllister of Developer World wonders if MySQL is forked beyond repair.
Not everyone had their eyes on the DBMS chess game. Vadim of the MySQL Performance Blog examined what time 18446744073709550.000 means. The answer, Vadim and his readers show, comes from a very low-level indeed.
Johan Andersson’s Cluster and HA Blog offers a thorough HOWTO on write scaling using Cluster to non-cluster replication. “There are various setups and solutions,” Johan begins, “to solve the problem of having redundant mysql masters and scaling writes on them. MySQL Cluster can be an alternative. Here is why and how.”
“When working with InnoDB,” writes Schlomi Noach, “you have two ways for managing the tablespace storage. . . . I will discuss the advantages and disadvantages of the two options, and will strive to convince that innodb_file_per_table is preferable.” The item is, Reasons to use innodb_file_per_table.
PostgreSQL. Just across the Rideau Canal from where I sit, PGCon 2009 is happening. Magnus Hagander gives his review of PGCon’s first day.
Andrew Dunstan attended Robert Treat’s talk on new features in 8.4, and concluded that small features can still win big.
IBM‘s EMEA Information on Demand 2009 Conference takes place in June in Berlin. Vincent Burney notes that the IBM IOD conferences and sessions now have Twitter tags and speaker blogs, and he provides useful links to these and other IOD resources.
Leons Petrazickis of the DB2 Express-C Team Blog wonders, “What are you working on with DB2?” “I just created an informal round-robin forum thread to see what everyone is working on with DB2 Express-C. It’s an exercise in community participation, if you will.”
That’s all for this edition. Let us hear about your favourite DB blogs from the week gone by—please leave a comment.
Till next time!