[ZODB-Dev] Relstorage: Slow packing with Mysql when GC is turned on

[Anton Stonor]

One for the RelStorage issue tracker.

I've migrated a ~70Mb Data.fs to a Mysql backed RelStorage. Everything 
seems to work fine.

Packing can be slow, though. With GC turned off, packing took a minute 
or two. With GC turned on, it took many hours to complete.

While running the pack, the expensive query is this one according to 
"show processlist":

  UPDATE pack_object SET keep = TRUE WHERE keep = FALSE AND
  zoid IN ( SELECT DISTINCT to_zoid FROM object_ref JOIN temp_pack_visit
  USING (zoid) )

show processlist says: "Copying to tmp table"

This might indicate that the Mysql subselect implementation is slow in 
this case. And this ticket seem to confirm that:

http://bugs.mysql.com/bug.php?id=28257

I don't have a workaround, but will try to dig deeper.

Observed with RelStorage 1.1b1, Mysql 5.0.51a and 5.0.32


/Anton