Xaprb

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 High Performance MySQL.

The rest of the book is much more focused on PostgreSQL. There are chapters on memory use, server configuration, maintenance (with a good survey of how PostgreSQL handles things like MVCC), benchmarking, indexing, query optimization, statistics, monitoring and trending, pooling, caching, replication, partitioning, proxies, and finally an extensive laundry list of common problems and how to solve them.

It was a pleasure to read — the quality and clarity of the writing is very good. Greg is an excellent writer and obviously put a lot of work into this book.

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.

The real crown jewels in Percona Server are not the performance improvements. They are the features such as making the replication thread write the queries it executes to the slow query log just like normal threads. There are many such improvements. It doesn’t sound like much — but it makes a world of difference.

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 sleep(.0001);
+--------------+
| sleep(.0001) |
+--------------+
|            0 |
+--------------+
1 row in set (0.000000 sec)

Alas, elsewhere in the code I now see that times() is used for timing, rather than a higher-resolution mechanism such as gettimeofday(). Bummer — I thought the sed trick could be such a neat hack.