The latest from the MySQL community

In a couple of recent consulting cases, I needed a tool to analyze how a log of queries accesses indexes and tables in the database, specifically, to find out which indexes are not used. I initially hacked together something similar to Daniel Nichter’s mysqlidxchk, but using the framework provided by Maatkit, which gave me a pretty good start right out of the box. This was useful in the very tight time constraints I was under, but was not a complete solution. Alas, I could not use anything like Percona’s enhancements for finding unused indexes.

So, in response to another consultant’s customer request (and sponsorship — thank you!) I spent more time actually writing a real tool in the Maatkit style, with full tests and all the rest of the usual goodies. The resulting …

The May release of Maatkit included a new feature in mk-query-digest. This allows you to process queries in many pieces, write out intermediate results, and then combine the pieces in a separate step. Maybe it’s not exactly map-reduce, but it makes a good headline.

The purpose is to enable query analysis across an arbitrarily large group of servers. Process queries on all of them, ship the results to a central place, and then combine them together. Pre-processing the results has some nice benefits, such as reduced bandwidth requirements, speeding up processing by doing it in parallel, and reducing the workload on the central aggregator. One Percona customer with many MySQL instances is trying this out.

The --save-results option on mk-query-digest saves the digested results to a file, stopping just before the final stages of the query event pipeline. There is a tool in Subversion trunk, tentatively called …

Just a quick notice to let everyone know that there is a new version of Kontrollkit available. There are some required bug fixes to the formerly new python backup script and some Solaris compatible changes to the various my.cnf files. You can download the new version here: http://kontrollsoft.com/software-downloads, or here: http://code.google.com/p/kontrollkit/

It's been an extremely busy year thus far with all the great work we're doing at NorthScale as well as the release of CaptTofu 2.0 (My son Kiran!). With the new UI on rt.cpan.org, in my spare time, I went through and closed some bugs, hence a new release of DBD::mysql, 4.014. In this release:

* BUG 30033 Fixed handling of comments to allow comments that contain characters
  that might otherwise cause placeholder detection to not work properly
* BUG 53844, Fix for memory leak in stats. (Gregory Burmistrov)
* BUG 49719, Fix for handling of NULLs in prepared statements (Gert Pache)
* BUG 55627, Fix for testing failure due to strict mode (Yves)
* BUG 51784, Fix for mysqladmin on Windows in Makefile (Zeeshan Muhammad)
* BUG 41630, Typo in Makefile

There are other bugs in rt.cpan.org, hence a pending release in the next week or two. I like an empty bug list! …

Quick note to let our users know that there was an XML tag closure error on the “alert_22″ subroutine in the “bin/kontroll-reporter-cli.pl” script. This does not affect the webapp portion of Kontrollbase – only reports generated via the command line reporter script. It is not a fatal error but will cause the XML file to […]

Just a quick notice to let everyone know that there is a new version of Kontrollkit available. There are two new scripts included as well as some good updates to the my.cnf files. You can download the new version here: http://kontrollsoft.com/software-downloads kt-mysql-systemcheck – generates a report for point-in-time system status that is useful for troubleshooting MySQL [...]

Maatkit does more than just MySQL. I’ve just committed a new version of mk-query-digest, a powerful log analysis tool, with support for Posgtres logs, in both syslog and stderr format. I’m hoping that people will give this a spin in the real world. I have lots of test cases, but that’s never enough; I’m looking for people to crunch their logs and let me know if anything breaks.

A brief tutorial:

# Get it
$ wget http://www.maatkit.org/trunk/mk-query-digest

# Run it
$ perl mk-query-digest --type pglog /path/to/log/file

# Learn about it (search for the string "pglog")
$ perldoc mk-query-digest

I’m going to close comments on this blog post so I don’t get bug reports in the comments. If you have feedback, please post it to the Maatkit mailing list, or …

The time has come for major performance improvements to the reporter, stats-gather, alerter, and client scripts. This means that I will be rewriting the scripts in Python. A couple of reasons for this; to cut down on the number of modules that are required for the installation process (which also makes distributing the client script [...]

Some background is needed. Crosstabs (also called data cubes or pivot tables, have been one of my favorite hacks for long time. In 2001 I wrote an article about a simple …

In the Autumn, I wrote about some experiments I did using KVM and virtual machines to build and test MariaDB binary packages on a number of different platforms. In the period since then I added some polish and refinements, and the system is now running well for some time. We build and test packages for Debian (4 and 5), Ubuntu (8.04 to 10.04), Centos 5, and generic Linux; amd64 and i386 architectures.

To better control the startup and shutdown of the virtual machines, I created a small wrapper script around KVM called runvm. This wrapper encapsulates the steps needed to boot up a virtual machine, run a series of commands inside it, and shut it down gracefully afterwards. Some special care is taken in the script to ensure that the …