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

Posted in: Technical Track

Welcome, readers, to the 92nd Log Buffer, the weekly review of database blogs.

Brian “Krow” Aker started an interesting blog-thread with his post, The Death of Read Replication, the crux of which is that object caches, such as memcached, make the DBMS itself a little less central, particularly in “Web 2.0” scenarios. “What does this mean? Less database servers. Bringing down your load means you push off the load to another tier. . . . Why do I need to go through MySQL at all… unless I just want it as a backup or for ad-hoc reporting?”

Ronald Bradford responds with an overview of the MySQL-plus-replication scene. Farhan Mashraqi concurred with Brian’s post, while Arjen also agrees, adding, “I’m not sure the new memory based MySQL storage engines coming out are so relevant, they might be fixing the wrong thing in the wrong place.”

Ronald (who, by the way, is on-deck for a his third Log Buffer on the 25th) also surveys both the storage-engine stuff to be had at the MySQL Conference, and the prevalence lately of talk about Kickfire in MySQL blogs, something also mentioned by Peter Zaitsev on the MySQL Performance Blog.

Peter has another question on his mind: should you have your swap file enabled while running MySQL? He wants to hear your approach to this matter, having himself experienced variable results. Lots of responses already.

Here on the Pythian Group Blog, Paul Moen posted about a situation in which SHOW SLAVE STATUS lies.

Moving into Oracle stuff, our Alex Gorbachev also pointed out something that doesn’t quite work: the ASMCMD cp command in ASM 11g. He sure gives it a try, but finally concludes: “I couldn’t make the cp command work even a single time.” Except maybe on datafiles.

A new blogger made his first appearance here this week: Riyaj Shamsudeen, with his item on multi-column correlation and extended stats in Oracle 11g.

Laurent Schneider is looking at the right-or-wrongness of date-format. He writes, “What is worst? To use DD-MON-RR or to use DD-MON-YY?”. Probably neither, as the comments point out. Chen Shapira says, “FXYYYY rules indeed.”

And it’s from an idea of Chen’s that the Obfuscated SQL Code Contest arose. Eddie Awad played Master of Ceremonies, and here announces the winners or the contest (everyone!), writing, “The SQL code that was submitted was not only obfuscated but also beautiful and artful.”

Laurent offers greater detail about his obfuscated submission. Also a trippy picture of Chen.

Tom Kyte writes that he has been awaiting inspiration, and he apparently found it in a post to asktom (which could reasonably be described as obfuscated) from a junior DBA wanting to write applications for mobile banking. From that he goes to another regarding the expectation of “5 9s” service sans the budget for it. Tom writes, “I am not generally in favor of ‘certification’, but I seriously think we have a real problem here. If just anyone is allowed to put themselves up as a developer – everyone will. . . . Doctors and Lawyers and many other professionals have to meet some basic minimums in that area – perhaps the people that write the stuff that manages . . . virtually every aspect of our lives in many cases – should have to do the same.”

Tom is one of the heroes singled-out for tribute Richard Foote’s thoughtful piece, The Top 5 Most Influencial [sic] DBAs In My Oracle Career, the fifth of whom remains a mystery for the time being. A worthwhile discussion ensues.

David Aldridge, the Oracle Sponge, has a question about indexing options for change data capture. I’ll try to summarize it here. He writes, “I have a large and busy OLTP table, 100GB or so, against which there is a need to capture changes. Until an asynchronous CDC solution is in place we have to rely on two columns: create_date and update_date (null until the first update), both being of DATE type of course. . . . Is it possible to mitigate the index contention problem with a suitably high initrans values? . . . Basically, ‘what would you do’?”

On the SQL Server side, we have several technical posts this week. On In Recovery…, Paul Randall offers up a new script that shows how much of the database has changed since the last full backup. Writes Paul, “How can I tell what percentage of a database has changed since the last full backup, so I can choose between a differential or full backup? No such code exists as far as I know – until now!”

Andrew Calvett offers a server-side tracing starter kit for SSAS 2005. He introduces it: “To date i had always used the profiler gui to do the SSAS tracing but today i found myself needing to initiate and manage a trace with scripts. . . . So, what did i want to achieve? A script that would create a trace on the server and log to a specified directory similar to the way you can with a SQL Server trace[;] list all running traces on an analysis server[;] destroy a named running trace.”

On SqlServer-qa.net, Satya SK Jayanty covers the fundamentals of transactions and checkpointing in SSIS.

Ben Miller reports on the return of a bug in SQL 2005 and Windows 2003 when clustering. “I found that if I was given a Windows 2003 R2 x64 Server with SP2+ on it, then the cluster went well no matter if the machine name was lower case or not, but the key to this entire bug is that if you go into Cluster Manager and add the nodes to the Cluster, and you see a lowercase name in the list of cluster nodes, SQL will ALWAYS fail.”

Denis Gobo shares a link to a video interview with two of the creators of SQL Server Data Services, who, he writes, “. . . dig into how to design applications for SSDS and cover a number of the interesting aspects of working with SSDS in terms of features, design, and security.”

The Plastic SCM Blog compares the performance of SQL Server, MySQL, and Firebird, looking for the fastest way to insert 100K records. A number of different approaches are taken. The conclusion: “And the winner is… MySql takes only 9 seconds to insert the 100K records… but only using the multi-value insert operation.”

In other words, for things to work their best, it helps to know what you’re doing. Cary Millsap reports on the general upshot of this knowing-what-you’re-doing thing: a messed-up app, like the one he grappled with at Heathrow Airport. 2,000 bus rides! Sounds like a nightmare I’ve had.

On zillablog, Robert Treat has a post about disaster recovery at 1000 GB’s, with PostgreSQL. He begins, “I had mentioned to a few people our TB+ disaster recovery scheme at the PG-East conference last week . . . We’ve been able to do a rough run through, so I wanted to report some results. First, a quick recap of why most of the common backup solutions suck for our needs,” where “common backup solutions” are pg_dump, pitr, and slony. Robert writes that his approach shows promise, and some interest is sparked.

On select * from depesz;, Hubert Lubaciewski offers a few tips on some handy, and perhaps little-known keyboard shortcuts in psql.

Finally, Zack Urlocker of The OpenForce.com writes, “What could be more of an incentive to attend the MySQL Conference & Expo in Santa Clara next week than to know that you could get a pair of genuine MySQL boxer shorts?” Warning: contains photo of DBAs in their boxers! NSFW!

That’s all for now. As I mentioned before, Ronald Bradford is up in a couple weeks, and I think we can also look forward to Log Buffers by Colin Charles and Paul Ricketts.

Till next time!



Want to talk with an expert? Schedule a call with our team to get the conversation started.

About the Author

Dave Edwards is the Communications Specialist for the Pythian Group.

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *