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

[Shane Hathaway]

Anton Stonor wrote:
> 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.

The bug comments suggest wrapping the subquery in "select * from (...)" 
to materialize the subquery only once.  I've attached a patch that does 
that.  Let me know whether it helps or hurts.  Note that this isn't the 
final form of the patch since the other supported databases don't need 
this hack, but if it helps, I'll apply it to the MySQL adapter only.

Shane

-------------- next part --------------
A non-text attachment was scrubbed...
Name: mysql-rs-hack.diff
Type: text/x-patch
Size: 567 bytes
Desc: not available
Url : http://mail.zope.org/pipermail/zodb-dev/attachments/20080526/22681f14/mysql-rs-hack.bin