RelStorage 1.1.1 Released

For those just tuning in: RelStorage is an adapter for ZODB (the database behind Zope) that stores data in a relational database such as MySQL, PostgreSQL, or Oracle.  It has advantages over FileStorage and ZEO, the default storage methods.  Read more in the ZODB wiki.

Download this release from PyPI.  As I mentioned in another post, this release works around MySQL performance bugs to make it possible to pack large databases in a reasonable amount of time.

If you are upgrading from previous versions, a simple schema migration is required.  See the migration instructions.

Packing With MySQL

I’ve been working on packing a 6.5GB production database based on RelStorage and MySQL 5.0.  It contains 6.4 million objects, meaning “select count(1) from current_object” returns 6426879.  I would call this a moderately sized database.  When I first approached it, it had not been packed for at least 3 months.  At first, I could not pack it, because RelStorage was using statements that MySQL mis-optimizes.  I will discuss how I fixed the problem.

The first time I tried to pack this database, the pre_pack phase took several hours before I stopped it by killing the Python process.  I studied the problem using “show processlist” and the MySQL docs online.  I found a statement in RelStorage that MySQL mis-optimizes.  I tried again and it still took too long; this time I discovered that a workaround I had applied earlier for a mis-optimized query did not actually help this instance of MySQL.  I decided to make the workaround more robust by using an explicit temporary table.

Then, on Saturday, I started a pack again.  The pre_pack phase completed in less than an hour this time and the final phase began.  The final phase gets from the pre_pack phase a complete list of objects and states to delete; all it really has to do is delete them.  I let it run all weekend.  Today (Monday), using “show processlist” again, I measured 17 seconds per transaction, with 26,000 transactions to go.  With a 50% duty cycle, this would have taken about 10 days!  (The 50% duty cycle is a configurable parameter that causes RelStorage to periodically sleep during packing, allowing other clients to write to the database without significant interruption.)

I read up on the delete statement.  It turns out that delete with a subquery is often very expensive in MySQL.  I converted the delete statements to use a table join instead of a subquery, and the time per transaction dropped from 17 seconds to about 3 milliseconds.  This time the pack finished in minutes.

Does this mean I should optimize every statement this way?  Certainly not.  The SQL syntax I had to use to optimize MySQL is not portable, so I had to sacrifice portability, readability, and maintainability, in order to gain acceptable performance.  I will only make that sacrifice when necessary.

I am very happy that I did not try to optimize this code until I was in a position where I could properly measure the performance with millions of real objects.  If I had optimized prematurely, I would have focused on micro-optimizations that might have only doubled or tripled the performance.  By waiting until the time was ripe, I multiplied the pack performance by a factor of at least 5,000, with only a few days of work and only minor code changes.  Next time someone asks why premature optimization is so evil, I will try to remember this experience.  Most of the best software developers I know skip micro-optimizations and focus on readability instead, helping to ensure that much larger optimizations will be possible later.

Note that during my optimization work, I never once needed to interrupt the main site nor restart MySQL.  I only restarted my own Python program repeatedly.  I doubt anyone else knew there was a pack going on.

RelStorage 1.1.1 Coming Soon

After a lot of testing and performance profiling, on Friday I released RelStorage 1.1.  On Saturday, I was planning to blog about the good news and the bad news, but I was unhappy enough with the bad news that I decided to turn as much of the bad news as I could into good news.  So I fixed the performance regressions and will release RelStorage 1.1.1 next week.

The performance charts have changed since RelStorage 1.0.

Chart 1: Speed tests with 100 objects per transaction.

Chart 2: Speed tests with 10,000 objects per transaction.

I’m still using 32 bit Python 2.4 and the same hardware (AMD 5200+), but this time, I ran the tests using ZODB 3.8.1.

The good news:

  • Compared with RelStorage 1.0, reads from MySQL and Oracle are definitely faster.  In some tests, the Oracle adapter now reads as quickly as MySQL.  To get the Oracle optimization, you need cx_Oracle 5, which was just released this week.  Thanks again to Helge Tesdal and Anthony Tuininga for making this happen.
  • Loading 100 objects or less per transaction via MySQL is now so fast that my speed test can no longer measure it.  The time per transaction is 2 ms whether I’m running one ZODB client or 15 concurrently.  (The bad news is now I probably ought to refine the speed test.)

The bad news in version 1.1 that became good news in version 1.1.1:

  • The Oracle adapter took a hit in write speed in 1.1, but a simple optimization (using setinputsizes()) fixed that and writes to Oracle are now slightly faster than they were in RelStorage 1.0.
  • MySQL performance bugs continue to be a problem for packing.  I attempted to pack a 5 GB customer database, with garbage collection enabled, using RelStorage 1.1, but MySQL mis-optimized some queries and wanted to spend multiple days on an operation that should only take a few seconds.  I replaced two queries involving subqueries with temporary table manipulations.  Version 1.1.1 has the workarounds.

The bad news that I haven’t resolved:

  • Writing a lot of objects to MySQL is now apparently a little slower.  With 15 concurrent writers, it used to take 2.2 seconds for each of them to write 10,000 objects, but now it takes 2.9 seconds.  This puts MySQL in last place for write speed under pressure.  That could be a concern, but I don’t think it should hold up the release.

Of the three databases RelStorage supports, MySQL tends to be both the fastest and the slowest.  MySQL is fast when RelStorage executes simple statements that the optimizer can hardly get wrong, but MySQL is glacial when its query optimizer makes a bad choice.  PostgreSQL performance seems more predictable.  PostgreSQL’s optimizer seems to handle a subquery just as well as a table join, and sometimes SQL syntax rules make it hard to avoid a subquery.  So, to support MySQL, I have to convert moderately complex statements into a series of statements that force the database to make reasonable decisions.  MySQL is smart, but absent-minded.

The ZEO tests no longer segfault in ZODB 3.8.1, which is very good news.  However, ZEO still appears to be the slowest at reading a lot of objects at once.  While it would be easy to blame the global interpreter lock, the second chart doesn’t agree with that assumption, since it shows that ZEO reads are slow regardless of concurrency level.  ZEO clients write to a cache after every read and perhaps that is causing the overhead.

I will recommend that all users of RelStorage upgrade to this version.  The packing improvements alone make it worth the effort.

RelStorage 1.1: All Green

I said earlier that before I release RelStorage 1.1, I wanted to set up thorough automated testing of RelStorage with different combinations of ZODB versions, RelStorage versions, Python versions, and platform types.  I originally intended to use hand crafted scripts to run the tests, but this past week I learned Buildbot’s model through experience working on a customer’s Buildbot configuration, so now I’m comfortable with it and I decided to use Buildbot after all.

Here are the results.

I could just say all the tests are passing, but it only feels real when there is a table with many green cells and no red cells.  This system tests a lot of software combinations.  There are three platforms: 32 bit Debian Etch, 32 bit Debian Lenny, and 64 bit Debian Lenny.  On each platform, I’m testing the RelStorage trunk and 1.1 branch combined with ZODB 3.7, ZODB 3.8, and the ZODB shane-poll-invalidations branch, except that the 64 bit platform with Python 2.5 does not test the ZODB 3.7 branch, since ZODB 3.7 is not compatible with 64 bit Python 2.5.  Each 32 bit platform runs tests with MySQL, PostgreSQL, and Oracle.  The 64 bit platform runs only the MySQL and PostgreSQL tests.

That adds up to 44 tested combinations, all passing.  I can relax now!  My Buildbot is running on a private server, but I can expose a read-only web view if there’s interest.

HP dv9933cl

I recently bought a laptop for my new job.  I chose an HP dv9933cl, intending to install Ubuntu on it.  The verdict?  It’s great!  I bought it from CostCo rather than Newegg because I wanted to avoid shipping in case I needed to replace it, but so far I see no need to replace it.

It has a 17″ screen, 4GB RAM, 320GB HD, Intel Core 2 Duo, a LightScribe DVD writer, SD card slot, WiFi (802.11abgn), nVidia graphics, and all the regular ports you’d expect.  It comes with Windows Vista, which is OK for watching movies, but it’s not the right environment for getting work done. 🙂

Ubuntu 8.10 installed with hardly a hitch.  I installed all of the drivers I needed from a standard Ubuntu mirror.  The wi-fi didn’t work on the first boot, but apparently something sorted itself out and it began working (and hasn’t stopped working) on the second boot.  The wi-fi has better range than any laptop I’ve used before.  I can put the laptop to sleep just by closing the lid.  Compiz (for desktop 3D effects) works well.  The SD card reader works.  Even the unusual little infrared remote control that came with the laptop works.

I suspect most laptops in the HP Pavilion dv9000 line will have similar success with Linux.  I wish HP would publish something that says Linux works well on it.

z3c.sharding

I just uploaded to svn.zope.org a new package called z3c.sharding.  It is a BTree container that spreads its contents among multiple databases in an automatically balanced way.  It does all of its work with pure and simple ZODB mechanisms.  It should be useful for solving certain kinds of scaling problems.  Enjoy!  There are no releases yet, but go ahead and try it out; if it solves a real problem in its current state, I’ll make a release.

Always Run the RelStorage Tests

I would like to advise all users of RelStorage to run the RelStorage test suite on their staging and production servers before running any application that uses RelStorage.  There are a number of ways to misconfigure the database and the tests will reveal many kinds of issues.  The test suite is in very good shape and should pass every test, every time.

Here are a few MySQL-specific misconfigurations that the tests will reveal:

  • An incorrect database adapter.  For example, Debian Etch still ships MySQLdb 1.2.1, causing most of the tests to fail.
  • Insufficient packet size.  In my.cnf, you should increase max_allowed_packet from 16M to at least 32M.  This parameter limits the maximum size of an object stored by RelStorage.
  • Insufficient space in /tmp.  You need a lot more than 16M available, but linux-vserver limits /tmp to 16M by default.

To run the suite, do something like this:

cd /path/to/relstorage
export PYTHONPATH=`pwd`:/path/to/ZODB/src
python relstorage/tests/testmysql.py  # or testpostgresql.py or testoracle.py

Personally, every time I run these tests on a new database, I discover something misconfigured.  It is worth the time to run the tests.