Log Buffer #164: A Carnival of the Vanities for DBAs

Oct 2, 2009 / By David Edwards

Tags: , , , ,

Welcome to the 164th edition of Log Buffer, the weekly review of database blogs.

SQL Server

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.”

On In Recovery…, Paul S. Randall pursued the answers to the question, how do checkpoints work and what gets logged?

Another question: will EMPTYFILE on primary ldf “doom” it somehow? This one is posed by Tibor Karaszi.

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.”

Pinal Dave offered Insert Values of Stored Procedure in Table – Use Table Valued Function.

The Less Than Dot blog shared their thorough look into 6 ways to import data into SQL Server.

Oracle

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.”

So, big disks are bad, but jumbo frames with Oracle RAC really does rock! So says Glenn Fawcett.

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.”

Luis Moreno Campos offered how to create a mount point in the new Oracle File System, ACFS.

MySQL

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.”

Switching our astrological orientation now, from the other side of the moon comes Phillip Tellis with a post on scaling writes in MySQL.

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.”

Domas Mituzas knows what kind of documentation we need. Here is his MySQL processlist phrase book.

That’s all for now. Please join us again next week for #165.

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>