The latest from the MySQL community

I’ve seen this a handful of times: someone has trouble with their database performance, and they have heard that XtraDB is much faster than InnoDB. They build a custom-compiled server with XtraDB.

This is unfortunately missing the point a bit. If you have a server that is the same as normal MySQL, but you’ve replaced InnoDB by XtraDB, what do you have? Depending on the version of MySQL you’re using, you have somewhere between, say, 1.5x and 15x performance improvement, at best. Compared to what you could be getting, that is not much, because you’re missing the most important improvement in Percona Server: the ability to measure the server’s activity. In other words, with a faster server that you still can’t measure and diagnose easily, you have just painted yourself into a faster corner. Your application’s workload is likely to grow 1.5x in no time; you have barely put off needing to diagnose why it is slow.

…

What is wrong with the following?

mysql> select 'hello world';
+-------------+
| hello world |
+-------------+
| hello world | 
+-------------+
1 row in set (0.00 sec)

Centisecond resolution for query times belongs to the last century. I want at least millisecond resolution — microsecond is better. Fortunately, this is as simple as changing a printf format specifier in the mysql client program.

Edit: I thought that maybe I could fix this by changing the printf format specifier with sed, but it looks like I was wrong:

$ sed -e 's/%\.2f sec/%.6f sec/' bin/mysql > bin/mysql-precision

Now when I enter commands, I actually do see 6 digits after the decimal point, but it looks like I still get only 2 digits of precision:

mysql> select sleep(.009);
+-------------+
| sleep(.009) |
+-------------+
|           0 |
+-------------+
1 row in set (0.010000 sec)
mysql> select …

I’ve just committed some changes to diskstats, an I/O analysis tool in Aspersa that’s actually been in the Subversion repository for a long time, but in a barely usable fashion and with no documentation. Now it’s usable and documented.

It is basically a reimplementation of iostat in awk. Why on earth would I reinvent that wheel? Because I spend a lot of time gathering and analyzing raw data from /proc/diskstats, which is vital to really understanding what the storage subsystem is doing. The iostat tool hides important details. Seeing that detail has immediately solved many a disk performance problem and proven SAN vendors wrong, for instance. (I used to do this the old-fashioned way.) Disk performance, of course, is one of the most important …

A couple of extremely informative recent blog posts have gone to either Planet MySQL or Planet PostgreSQL, but not both, and I think everyone on both aggregators who cares about database internals should be interested in them. Here they are:

• Robert Haas: MySQL vs. PostgreSQL, Part 2: VACUUM vs. Purge
• Ewen Fortune: How InnoDB handles REDO logging

Happy cross-pollination.

Related posts:

1. Awesome Postgres/MySQL cross-pollination
2. …

InnoDB’s checkpoint algorithm is not well documented. It is too complex to explain in even a long blog post, because to understand checkpoints, you need to understand a lot of other things that InnoDB does. I hope that explaining how InnoDB does checkpoints in high-level terms, with simplifications, will be helpful. A lot of the simplifications are because I do not want to explain the complexities of how the simple rules can be tweaked for optimization purposes, while not violating the ACID guarantees they enforce.

A bit of background: Gray and Reuter’s classic text on transaction processing introduced two types of checkpoints beginning on page 605. There is a sharp checkpoint, and there is a fuzzy checkpoint.

A sharp checkpoint is accomplished by flushing all modified pages for committed transactions to disk, and …

I’ll be presenting several sessions at the O’Reilly MySQL Conference & Expo 2011, which is April 11-14 in Santa Clara, California. I recommend this conference to anyone interested in open-source databases including MySQL, PostgreSQL, CouchDB, MongoDB, and others. There is very good coverage of a diverse list of open-source databases.

My sessions are as follows:

• Building on Strengths, Learning from Differences: a keynote address on the open-source database ecosystem, how we got here, and what we can do to make the most of the future.
• Forecasting MySQL Performance and Scalability: mathematical models for forecasting performance and …

WHAT TIME IS IT?

This post started with a simple question: “Does the function NOW() get executed every time a row is examined?” According to the manual,  “Functions that return the current date or time each are evaluated only once per query …. multiple references to a function such as NOW() … produce the same result. …. (However,) as of MySQL 5.0.12, SYSDATE() returns the time (the row is) executes. “

• CURDATE() returns the current date.
• CURTIME() returns the current time.
• UTC_DATE() returns the current UTC date.
• UTC_TIME() returns the current UTC time.
• NOW() return the current date and time.
• UTC_TIMESTAMP() returns the current UTC date and time.
• SYSDATE() returns the date and …

I’ve released version 1.1.8 of the Better Cacti Templates project. This release includes a bunch of bug fixes and several important new graphs. There are graphs for the new response-time statistics exposed in Percona Server, and a new set of graphs for MongoDB.

There are upgrade instructions on the project wiki for this and all releases. There is also a comprehensive tutorial on …

I read a recent blog post about the coming extinction of MySQL bloggers with concern. The post plotted the blogging activity of Planet MySQL bloggers and fit a polynomial curve to it:

Blogging Activity

The curve isn’t extended out, but it’s a polynomial, and we know what happens: it’s going to intersect the X-axis. I didn’t do the math, but if you want to, you can predict, to the day, when blogging as we know it will cease.

I couldn’t help thinking: what else can we fit a curve to? The stock market? — no, that’s heavy stuff. How about the hours of daylight this year? There can’t be any harm in that, can there? So I went to an online hours-of-daylight calculator and got the sunrise and sunset times for the first 60 days of the year. I plotted the result in gnuplot and fit a polynomial to it. The X-axis is the …

I wish I could be at this: Percona Live San Francisco. It is on February 16th. Cost is minimal, and content is maximal. The topic is on what’s new in MySQL these days beyond 5.1: the new 5.5 release, HandlerSocket, MariaDB, and of course Percona Server.

This is a follow-up to the enormously successful Percona Performance Conference in 2009.

Related posts:

1. Sessions of interest at the Percona Performance Conference
2. Schedule for MySQL-and-beyond conference is live
3. …