The call for papers for the MySQL conference’s Ignite sessions is open. These are a lot of fun: 5 minutes, 20 slides, period. Last year I spoke way too fast — if I do it again this year, I’ll try not to cram so much into so little time. Either that, or I’ll deliberately talk like Busta Rhymes.

Related posts:

1. Submit your proposals for MySQL conference 2010
2. I’m a MySQL Conference and Expo advocate again
3. …

I often need to watch a server that’s very minimally configured, e.g. has no Perl DBI libraries installed, and I shouldn’t install anything. The following snippet is a quick way to do that:

watch 'mysqladmin proc | grep -v Sleep | cut -b0-130'

Replace 130 by the width of your terminal, naturally.

(Of course, innotop is much more featureful than mytop, but mytop is the essential functionality we’re going for here!)

Related posts:

1. An easy way to run many tasks in parallel
2. How to read Linux’s /proc/diskstats easily
3. …

I’m not sure how many of this blog’s readers are likely to be aware of VoltDB. It is one of the systems that I think could be poised to dispel the notion that SQL (or the relational model) is somehow inherently unscalable. Here’s a blog post explaining how VoltDB does transaction ordering and replication.

Related posts:

1. Why MySQL replication is better than mysqlbinlog for recovery
2. What are your favorite MySQL replication filtering rules?
3. …

PostgreSQL 9.0 High Performance

PostgreSQL 9.0 High Performance. By Gregory Smith, Packt 2010. About 420 pages. (Here’s a link to the publisher’s page for this book.)

I enjoyed this book a lot and recommend it to everyone who uses PostgreSQL or MySQL. MySQL users should benefit from understanding PostgreSQL. Beyond that, I learned a lot from this book that I can apply directly to MySQL. In particular, the book begins with a few chapters on hardware performance, benchmarking, and configuration. This material is database-agnostic and very well done. There is about 70 pages of it — it goes into a lot of details. It is more detailed than the similar material in my own book …

Today, I had a task of modifying a slave to exclude (or if you want to say specifically include) tables. The situation is that this slave need only contain tables used for reporting. The added benefit being that with fewer tables to replicate there will be less replication traffic, less disk I/O and less disk space used hence better performance for queries that digest statistical data.

In the past, I would manually edit files, but today I decided to turn over a new leaf and be lazy — and let the database do the work for me! I’m posting this article because I feel obliged to share with you my laziness.

The client provided me a list of tables in a spreadsheet which I cut-n-pasted and used the magical regex powers of vim to give me a quoted, comma-separated list of tables. I love information_schema, because as opposed to ‘SHOW’ commands which may be easy to remember, don’t give me the ability to produce an output which I …

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 …