Log Buffer #164: A Carnival of the Vanities for DBAs
Oct 2, 2009 / By David Edwards
Welcome to the 164th edition of Log Buffer, the weekly review of database blogs.
We have a delicious assortment of technical posts from the SQL Server world this week.
Piotr Rodak writes, “While I always knew and imagined that ON DELETE CASCADE may be useful, I wondered, what scenarios would be suitable for ON UPDATE CASCADE. I still don’t have this answer, but I came across some interesting behavior which kept me occupied for quite a bit more time that I had intended to.”
Davide Mauri wonders if he has found a SET IDENTITY_INSERT little bug. He writes, “ . . . When working on an empty table that has an identity column, if you insert a value forcing the identity value, the next time SQL Server will generate the identity value, it will generate it missing one number. . . . Not a huge problem as you can see, but [it] can cause little troubles if your forgot about it and you expect to have predictable identity values.”
Martin Widlake noted that Big Discs are Bad, his response to an older article by Paul Vallée. Martin writes, “Bottom line. Disc Drives are now slower in proportion to the disc acerage than they used to be.”
On the AMIS Technology Blog, Lucas Jellema continues his introduction of new style hierarchical querying using recursive subquery factoring. He writes, “Oracle Database 11g Release 2 introduces the successor to the good old Connect By based hierarchical querying, called Recursive Subquery Factoring. . . . This article will show some additional examples of using this recursive subquery factoring syntax.”
Mark Callaghan of MySQL@Facebook got something started with his note, Be nice to the query cache. “I might want to use [the query cache] in the future so I enabled it during a run of sysbench readonly on an 8-core server. . . . Performance with the query cache enabled is bad for MySQL 5.0.44, good for MySQL 5.0.84 and then bad again for MySQL 5.1.38. By bad I mean that throughput does not increase with concurrency as it does when the query cache is disabled.”
Sheeri K. Cabral responded with Is the query cache useful? “[Mark's] tests are skewed — not on purpose, I am sure, and Mark admits he has not used the query cache before — but they are skewed all the same.”
Ronald Bradford piped up with an item on using the query cache effectively. The essence, writes Ronald, is: “Maximize your strengths, minimize your weaknesses.” And Kostja Osipov asked query cache = useless?. And Hide-and-SQL shared its thoughts on the usefulness of the query cache.
And now, from Sun comes a piece on how to backup MySQL in a second with ZFS. “The pain-point [in back-ups] comes from the fact that while backuping the database is not available to respond to client requests anymore. . . . But ZFS snapshosts bring a new straightforward approach that avoids the pain and the complexity of a master/slave replication.”
On the MySQL Performance Blog, Baron Schwartz has a quick comparison of MyISAM, Infobright, and MonetDB, and lots of discussion.
Baron also asked, what data types does your innovative storage engine NOT support? “For example, Infobright’s documentation shows a list of every data type supported. What’s missing? Hmm, I don’t see BLOB, BIT, ENUM, SET . . . I don’t mean to pick on Infobright. I’ve recently looked at another third-party storage engine and they did exactly the same thing.”
That’s all for now. Please join us again next week for #165.