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

Index: common.py
===================================================================
--- common.py	(revision 86976)
+++ common.py	(working copy)
@@ -592,9 +592,11 @@
             UPDATE pack_object SET keep = %(TRUE)s
             WHERE keep = %(FALSE)s
                 AND zoid IN (
+                  SELECT * FROM (
                     SELECT DISTINCT to_zoid
                     FROM object_ref
                         JOIN temp_pack_visit USING (zoid)
+                  )
                 )
             """
             self._run_script_stmt(cursor, stmt)
_______________________________________________
For more information about ZODB, see the ZODB Wiki:
http://www.zope.org/Wikis/ZODB/

ZODB-Dev mailing list  -  ZODB-Dev@zope.org
http://mail.zope.org/mailman/listinfo/zodb-dev

Reply via email to