How to Fix the MySQL Write Speed

Last time I ran the RelStorage performance tests, the write speed to a MySQL database appeared to be slow and getting slower.  I suspected, however, that all I needed to do was tune the database.  Today I changed some InnoDB configuration parameters from the defaults.  The simple changes solved the MySQL performance problem completely.

The new 10K chart, using RelStorage 1.1.3 on Debian Sid with Python 2.4 and the same hardware as before:

I added the following lines to my.cnf to get this speed:

innodb_data_file_path = ibdata1:10M:autoextend
innodb_buffer_pool_size=256M
innodb_additional_mem_pool_size=20M
innodb_log_file_size=64M
innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=1
innodb_file_per_table

This is similar to the configuration suggested by the InnoDB documentation for a 512 MB database server.  Even if you have a 16 GB server, I would suggest starting with the settings for a 512 MB server, then watch what happens to the RAM and CPU on the database server when you connect all of your client machines simultaneously.  You want to leave at least half the RAM available for disk cache and usage spikes.

Not all of these changes are related to speed.  The innodb_file_per_table option just seems like a good idea because it makes tables visible on the filesystem, which should improve manageability.  I think it might improve cache locality as well.

With these changes to my.cnf, ZEO, PostgreSQL, and MySQL all perform about the same for writes, with MySQL having a slight lead.  I suspect all three are hitting hardware and kernel limits.  I think the differences would be more pronounced on higher-end storage hardware.

A big caveat: It’s risky to change InnoDB settings unless you’re familiar with all the effects.  Some changes break compatibility with existing table data.  Get to know the InnoDB documentation very well before you change these settings, and make backups using mysqldump, as always.

Meanwhile, Oracle XE continues to write slowly and ZEO read performance is so bad that it’s off the chart.  I bet ZEO read performance could be improved with some simple optimizations somewhere, but I don’t have an incentive to fix that. 🙂  Perhaps it has been fixed in ZODB 3.9.