As did last week's Craig Mullins of dbazine.com I wrote a short post about the LogBuffer recently when I found it in my Blog's referrer list and was promptly asked if I would like to compile one myself.
So here am I am, welcoming all of you to the the 9th issue of the Log Buffer, a Carnival of the Vanities for the DBA community. Once again you will find a plethora of links to all sorts of information on the one thing that keeps many of us both fed and sometimes close to blank despair.
Myself being some sort of a mixture between a software developer and database admin I have had a fair amount of time over the last years to get experience especially with MySQL. I did some work with the MS SQL server, too, so I am happy to have at least one item to cover for this faction:
Joseph Sack made sort of a mental note about SQL Server 2005 Instant File Initialization on his SQL Server blog. If you have been unnerved by waiting for your SQL Server to physically fill up its data files before allowing you to use them, go have a look. This can save a lot of time and may be especially interesting when expanding an existing installation.
While this is sadly the only item I have for the Microsoft fans, the MySQL people seem to be a lot chattier.
Peter Zaitsev is one of them. Formerly a MySQL AB employee he started his own consulting firm and was recently joined by his former - and new - colleague Vadim Tkachenko. During their time at MySQL AB they were already very active on their MySQL Performance Blog, but leaving the company does not seem to have affected their ambitions in any way.
There were three very interesting articles published during the last week alone, probably the most noteworthy, even if not completely new or even limited to databases, being Peter's warning that Even Minor Upgrades Are Not Always Safe. I am sure many of us (*raises hand*) have been through the trouble of upgrading to a bug-fix release of (insert favorite software package here) and only after having transferred it into production been confronted with its nasty little side effects. So from personal experience I can only support Peter's view on the matter.
Roland Bouman of MySQL AB published a detailed article on Refactoring MySQL Cursors, both on the O'Reilly Databases blog as well as his own blogspot blog. While the post itself centers on MySQL's new (since version 5) support of cursors, it surely holds valuable general information that can be applied to any other system as well. I have to admit that when reading it I felt reminded of several situation where I found myself or other people doing exactly what Roland describes. So if you have ever written a cursor, maybe you should have a look at this and after it decide again whether your choice was wise.
Jay Pipes has two interesting items, even more so as they point in somewhat different directions. The first one is his mentioning of an Interview with Rohit Nadhani from Webyog concerning the recent open-sourcing of their popular SQLyog MySQL GUI frontend which is now available in both a community and professional edition.
If you have some ressources to spare and are still looking for something useful to do with them, be sure to have a look at Jay's second article entitled Build Farm Scripts Available on MySQL Forge which is an announcement of the progress of the very interesting MySQL Build Farm initiative. They aim to provide a unified way of building MySQL on a variety of platforms and concentrate the results in order to improve quality.
Another post from Peter Zaitsev is about the Slow Query Log Analysis Tools. These include a patch to the MySQL server which increases the slow-query log threshold resolution from seconds to milliseconds. While most people will probably not want to use a patched server in production it might still be worth a look for benchmarking and performance tuning during development.
Also on the MySQL Performance Blog, Vladim Tkachenko provides a helpful hint towards the useful GROUP_CONCAT extension to GROUP BY. It can help prevent looping on the application side by combining values of a group into a single field value. Example snippets for PHP are provided, but can easily be adapted to suit other needs as well.
While we are at it, Mark Rittman is not only writing about GROUP BY...ROLLUP, but also about his plans for Comparing Oracle 10g Aggregation Techniques. He is going to present his findings about initial aggregation speed, ease of use and speed as well as space requirements at the UK Oracle User Group Conference in Birmingham this November. Since Mark says he is going to keep us informed about his progress, a regular look at his blog is definitely advisable.
This week Pete Finnigan gives a link to a Nice idea on audition using trace events in his Oracle security forums. The basic thought is to use Oracle error messages and attach events to them to improve Oracle security auditing, possibly finding some intruder poking around.
Although written using some Oracle wording, Dominic Giles post On the subject of I/O is really an excellent and mostly database-agnostic article. It covers the common problem of non-technical folks (and even some of the technical ones, too) underestimating the extreme importance of a well-balanced system design regarding the I/O subsystem, memory and CPU power.
Written by another Oracle expert - Chris Foot - but also interesting for all DBAs out there, regardless of the products they use, is The Non-Technical Art of Being a Successful DBA - Naming Conventions. It contains recommendations and best-practice advice to help prevent what Chris calls Moonlight Script Hunting
, and outlines how to keep even large installations with lots of different databases running in a well-structured way.
Staying with Oracle for one more item: Eddi Awad has published some examples for the Cool Undocumented SQL Function: REVERSE which seems to have been available since Oracle 8i, if not officially documented. While its practical use may not be immediately evident, reading this article might get you some fresh ideas.
Two interesing posts come from Xapbr. The first started as he looking for some recommendations in a MySQL IRC channel and ended up as A review of the Glom graphical database frontend for PostgreSQL, somewhat similar to Microsoft Access or Filemaker Pro, but with a "real" database behind it.
Xapbr's second post is a notice that Version 0.1.149 of innotop has been released. While previous versions of this great tool for keeping an eye on the low level status of MySQL's InnoDB storage engine had some stability problems, those have been addressed now. I have already tried it and so far, I havent't encountered any of the problems I used to have.
More on PostgreSQL can be found among Greg's Postgres stuff who provides some tools that might come in handy at times for Finding Multi-Column Keys using the INFORMATION_SCHEMA. Apart from being outright useful they might also be seen as a starting point for more things to be done with database metadata.
Re-inventing Greg's method to prevent re-inventing nicely shows PostgreSQL's ability to use different sorts of languages to implement custom functionality. Robert Treat demonstrates how to implement an email validity check in pl/php using the Pear::Validate module, following the same feature, however implemented earlier by Greg in pl/perl with EMail::Valid.
To finish this weeks edition I'd like to point you to Sean McCown's What's a Tiny Mistake? on the Database Underground blog. While I primarily find it to be a nice obituary for "The Crocodile Hunter" Steve Irwin, Sean also draws a parallel to the IT business by giving out a warning to software developers and database folks reminding them of that small changes can have disastrous effects.
