MySQL Query Profiling
Apparently MySQL quietly (logical fallacy: I assume it was quiet because I didn’t hear anything about it) slipped a query profiler patch in MySQL 5.0 (as early as 5.0.37) that will give you time statistics about each step of a query’s lifetime. From the aforementioned:
mysql> show profile for query 1; +--------------------+------------+ | Status | Duration | +--------------------+------------+ | (initialization) | 0.00006300 | | Opening tables | 0.00001400 | | System lock | 0.00000600 | | Table lock | 0.00001000 | | init | 0.00002200 | | optimizing | 0.00001100 | | statistics | 0.00009300 | | preparing | 0.00001700 | | executing | 0.00000700 | | Sending data | 0.00016800 | | end | 0.00000700 | | query end | 0.00000500 | | freeing items | 0.00001200 | | closing tables | 0.00000800 | | logging slow query | 0.00000400 | +--------------------+------------+ 15 rows in set (0.00 sec)
This looks immensely useful! And while the MySQL documentation is mute on the matter, according to comments on the blog of the original author, the times reported aren’t specific to a thread. In other words, you’ll only get reliable numbers when there’s only one thread running.
Note: this seems to have been left out of the version of MySQL available in Gentoo portage. We’re not sure why, but we have some good minds (i.e., not mine) trying to figure out where it went. Consequently, I haven’t actually used it yet.
MySQL has some weird versioning going on, I think. I built a ebuild for 5.0.45, and it includes the profiling.
Are you running Gentoo on your machine?
Depends on where you’re talking about. 😉 My work machines run Gentoo, as do all of our production web servers and my personal server. The box I just reclaimed at home (where I was playing with WordPress) is running Xubuntu, the XFCE derivative of Ubuntu.
I forgot that your LT box ran Gentoo too.