[Checkins] SVN: relstorage/branches/1.1/ Changed a query for following object references (used during packing)

[Shane Hathaway]

Log message for revision 87064:
  Changed a query for following object references (used during packing)
    to work around a MySQL performance bug.  Thanks to Anton Stonor for
    discovering this.
  

Changed:
  U   relstorage/branches/1.1/CHANGELOG.txt
  U   relstorage/branches/1.1/relstorage/adapters/common.py
  U   relstorage/branches/1.1/relstorage/adapters/mysql.py

-=-
Modified: relstorage/branches/1.1/CHANGELOG.txt
===================================================================
--- relstorage/branches/1.1/CHANGELOG.txt	2008-05-30 16:26:51 UTC (rev 87063)
+++ relstorage/branches/1.1/CHANGELOG.txt	2008-05-31 13:58:18 UTC (rev 87064)
@@ -7,7 +7,11 @@
 - In the PostgreSQL adapter, made the pack lock fall back to table locking
   rather than advisory locks for PostgreSQL 8.1.
 
+- Changed a query for following object references (used during packing)
+  to work around a MySQL performance bug.  Thanks to Anton Stonor for
+  discovering this.
 
+
 RelStorage 1.1b1
 
 - Fixed the use of setup.py without setuptools.  Thanks to Chris Withers.

Modified: relstorage/branches/1.1/relstorage/adapters/common.py
===================================================================
--- relstorage/branches/1.1/relstorage/adapters/common.py	2008-05-30 16:26:51 UTC (rev 87063)
+++ relstorage/branches/1.1/relstorage/adapters/common.py	2008-05-31 13:58:18 UTC (rev 87064)
@@ -98,6 +98,16 @@
             WHERE tid = %(tid)s
             LIMIT 1
             """,
+
+        'prepack_follow_child_refs': """
+            UPDATE pack_object SET keep = %(TRUE)s
+            WHERE keep = %(FALSE)s
+                AND zoid IN (
+                    SELECT DISTINCT to_zoid
+                    FROM object_ref
+                        JOIN temp_pack_visit USING (zoid)
+                )
+            """,
     }
 
 
@@ -490,6 +500,10 @@
     def _pre_pack_with_gc(self, conn, cursor, pack_tid, get_references):
         """Determine what to pack, with garbage collection.
         """
+        stmt = self._scripts['create_temp_pack_visit']
+        if stmt:
+            self._run_script(cursor, stmt)
+
         log.info("pre_pack: following references after the pack point")
         # Fill object_ref with references from object states
         # in transactions that will not be packed.
@@ -513,33 +527,27 @@
 
         -- Keep objects that have been revised since pack_tid.
         UPDATE pack_object SET keep = %(TRUE)s
-        WHERE keep = %(FALSE)s
-            AND zoid IN (
-                SELECT zoid
-                FROM current_object
-                WHERE tid > %(pack_tid)s
-            );
+        WHERE zoid IN (
+            SELECT zoid
+            FROM current_object
+            WHERE tid > %(pack_tid)s
+        );
 
         -- Keep objects that are still referenced by object states in
         -- transactions that will not be packed.
         UPDATE pack_object SET keep = %(TRUE)s
-        WHERE keep = %(FALSE)s
-            AND zoid IN (
-                SELECT to_zoid
-                FROM object_ref
-                WHERE tid > %(pack_tid)s
-            );
+        WHERE zoid IN (
+            SELECT to_zoid
+            FROM object_ref
+            WHERE tid > %(pack_tid)s
+        );
         """
         self._run_script(cursor, stmt, {'pack_tid': pack_tid})
 
-        stmt = self._scripts['create_temp_pack_visit']
-        if stmt:
-            self._run_script(cursor, stmt)
-
         # Each of the packable objects to be kept might
         # refer to other objects.  If some of those references
-        # include objects currently set to be removed, keep
-        # those objects as well.  Do this
+        # include objects currently set to be removed, mark
+        # the referenced objects to be kept as well.  Do this
         # repeatedly until all references have been satisfied.
         pass_num = 1
         while True:
@@ -588,16 +596,8 @@
 
             # Visit the children of all parent objects that were
             # just visited.
-            stmt = """
-            UPDATE pack_object SET keep = %(TRUE)s
-            WHERE keep = %(FALSE)s
-                AND zoid IN (
-                    SELECT DISTINCT to_zoid
-                    FROM object_ref
-                        JOIN temp_pack_visit USING (zoid)
-                )
-            """
-            self._run_script_stmt(cursor, stmt)
+            stmt = self._scripts['prepack_follow_child_refs']
+            self._run_script(cursor, stmt)
             found_count = cursor.rowcount
 
             log.debug("pre_pack: found %d more referenced object(s) in "

Modified: relstorage/branches/1.1/relstorage/adapters/mysql.py
===================================================================
--- relstorage/branches/1.1/relstorage/adapters/mysql.py	2008-05-30 16:26:51 UTC (rev 87063)
+++ relstorage/branches/1.1/relstorage/adapters/mysql.py	2008-05-31 13:58:18 UTC (rev 87064)
@@ -63,6 +63,22 @@
 class MySQLAdapter(Adapter):
     """MySQL adapter for RelStorage."""
 
+    _scripts = Adapter._scripts.copy()
+    # work around a MySQL performance bug
+    # See: http://mail.zope.org/pipermail/zodb-dev/2008-May/011880.html
+    #      http://bugs.mysql.com/bug.php?id=28257
+    _scripts['prepack_follow_child_refs'] = """
+    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)
+            ) AS child_zoids
+        )
+    """
+
     def __init__(self, **params):
         self._params = params.copy()
         self._params['use_unicode'] = True