Tag: MySQL

RelStorage 1.3.0b1, Now With Blob Support

I have just released two versions of RelStorage. Version 1.3.0b1 adds full support for ZODB blobs stored on the filesystem. Version 1.2.0 is currently the better choice if you’re upgrading a production system and don’t need blob support.

People have been asking for blob support for months. I am glad to finally get it done, with a little help from a customer. With blob support, now we can easily store large artifacts on the filesystem, while keeping all metadata in the database.

To celebrate the new release, I have created a sample buildout.cfg that builds Plone with RelStorage, PostgreSQL, and blob support. (Thanks goes to Hanno Schlichting, who released a compatible version of plone.recipe.zope2instance only moments after I requested it.) Here it is:

[buildout]
parts = plone zope2 instance zopepy
find-links =
    http://dist.plone.org
    http://download.zope.org/ppix/
    http://download.zope.org/distribution/
    http://effbot.org/downloads
    http://packages.willowrise.org
eggs =
    elementtree
    PILwoTk
    RelStorage
    psycopg2
versions = versions

[versions]
ZODB3 = 3.8.3-polling
RelStorage = 1.3.0b1

[plone]
recipe = plone.recipe.plone

[zope2]
recipe = plone.recipe.zope2install
url = ${plone:zope2-url}

[instance]
recipe = plone.recipe.zope2instance
zope2-location = ${zope2:location}
user = admin:admin
products = ${plone:products}
eggs =
    ${buildout:eggs}
    ${plone:eggs}
    plone.app.blob
zcml = plone.app.blob
rel-storage =
    type postgresql
    dsn dbname='plone' user='plone' host='localhost' password='plone'
    blob-dir var/blobs

[zopepy]
recipe = zc.recipe.egg
eggs = ${instance:eggs}
interpreter = zopepy
extra-paths = ${instance:zope2-location}/lib/python
scripts = zopepy zodbconvert

P.S. I have been told that a very prominent Plone developer recently configured RelStorage with master/slave replication on MySQL, and that it works smoothly. I expect him to announce his success soon!

RelStorage 1.2.0b2 Released

This release works with unpatched versions of ZODB 3.9!  A big thank-you to Jim Fulton for including support for RelStorage in ZODB.  This release also continues to support patched versions of ZODB 3.7 and 3.8.

I have been doing a lot of testing, and I have found MySQL 5.1.34 to be a lot more stable than earlier releases of MySQL 5.1, so I am now declaring MySQL 5.1.34 and above supportable, meaning that if you ask questions about it, I am no longer going to request that you revert to MySQL 5.0. 🙂

Finally, I recently expanded my private RelStorage Buildbot to include a Windows XP slave.  After solving a couple of minor test glitches, the test results are now all consistently green on 4 platforms:

  • Debian Etch, 32 bit (Python 2.4.4, MySQL 5.0.32, PostgreSQL 8.1.17, Oracle 10g XE)
  • Debian Lenny, 32 bit (Python 2.5.2, MySQL 5.0.51a, PostgreSQL 8.3.7, Oracle 10g XE)
  • Debian Lenny, 64 bit (same as above but no Oracle)
  • Windows XP, 32 bit (Python 2.6.2, MySQL 5.1.34, PostgreSQL 8.3.7)

I’m thinking about adding another Linux slave that runs MySQL 5.1 and Python 2.6.

Anyway, enjoy the release!

P.S. You may be wondering why I released 1.2.0b2 instead of 1.2.0b1.  A little slip ruined the web page on PyPI, so I fixed the slip and skipped to the next version number.

How to Install Plone with RelStorage and MySQL

These step by step instructions describe how to install Plone on Ubuntu with RelStorage connected to MySQL as the main database. Familiarity with Linux systems administration is expected. Update: These instructions were revised in August 2009 for Plone 3.2.3 and RelStorage 1.2.0.

Continue reading How to Install Plone with RelStorage and MySQL

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.

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.

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.

RelStorage: New MySQL Adapter

I was interested in adding MySQL 5.0+ support to RelStorage, so I went ahead and did it. The code is in Subversion now.

The tests I’ve run suggest the MySQL adapter is already a lot faster than ZEO and both of the other adapters. The MySQL adapter has consistently finished the tests 40% faster than the PostgreSQL adapter. I haven’t tested PostgreSQL 8.3 yet; perhaps that will make a difference. I don’t know yet how the reliability of each adapter compares.

The port took all day. (My wife and I both have a cold, making it unwise to go to work today.) I didn’t think it would take that long, but I had to slow down to figure out the strange relationship between locks and transactions in MySQL. The LOCK TABLE statement is full of surprises! I ended up using GET_LOCK and RELEASE_LOCK and row-level locks instead.

So after editing queries to fit MySQL’s rules and fixing miscellaneous details, RelStorage now has a third database adapter. I’m excited to see what happens next. For one thing, MySQL support could have a major positive effect on Plone.

By the way, MySQLdb version 1.2.2 is required. Version 1.2.1 has a bug involving BLOBs; RelStorage depends heavily on BLOBs.