[Erp5-report] r29672 - /erp5/trunk/products/CMFActivity/skins/activity/

[nobody at svn.erp5.org]

Author: vincent
Date: Thu Oct 15 14:33:35 2009
New Revision: 29672

URL: http://svn.erp5.org?rev=29672&view=rev
Log:
Improve "ORDER BY" condition:
- remove uid and date sort, they causes pathologic cases in
  "site reindexation" payloads:
  Module indexation creates per-module batches of activities. All activities
  in a given batch will have identical date values, so indexation will happen
  on only one module at a time, which creates a bottleneck on setups where
  modules are scattered to multiple ZEOs (precisely to paralellise work on
  different modules).
- random sort added for multinode setups (see dtml comment, this is serious)

Modified:
    erp5/trunk/products/CMFActivity/skins/activity/SQLDict_reserveMessageList.zsql
    erp5/trunk/products/CMFActivity/skins/activity/SQLQueue_reserveMessageList.zsql

Modified: erp5/trunk/products/CMFActivity/skins/activity/SQLDict_reserveMessageList.zsql
URL: http://svn.erp5.org/erp5/trunk/products/CMFActivity/skins/activity/SQLDict_reserveMessageList.zsql?rev=29672&r1=29671&r2=29672&view=diff
==============================================================================
--- erp5/trunk/products/CMFActivity/skins/activity/SQLDict_reserveMessageList.zsql [utf8] (original)
+++ erp5/trunk/products/CMFActivity/skins/activity/SQLDict_reserveMessageList.zsql [utf8] Thu Oct 15 14:33:35 2009
@@ -21,7 +21,16 @@
   AND date <= <dtml-sqlvar to_date type="datetime">
   <dtml-if expr="group_method_id is not None"> AND group_method_id = <dtml-sqlvar group_method_id type="string"> </dtml-if>
 ORDER BY
-  priority, date, uid
+<dtml-comment>
+  Explanation of the order by:
+  - priority must be respected (it is a feature)
+  - when multiple nodes simultaneously try to fetch activities, they should not
+    be given the same set of lines as it would cause all minus one to wait for
+    a write lock (and be ultimately aborted), effectively serializing their
+    action (so breaking paralellism).
+    So we must force MySQL to update lines in a random order.
+</dtml-comment>
+  priority, RAND()
 LIMIT <dtml-sqlvar count type="int">
 <dtml-var sql_delimiter>
 COMMIT

Modified: erp5/trunk/products/CMFActivity/skins/activity/SQLQueue_reserveMessageList.zsql
URL: http://svn.erp5.org/erp5/trunk/products/CMFActivity/skins/activity/SQLQueue_reserveMessageList.zsql?rev=29672&r1=29671&r2=29672&view=diff
==============================================================================
--- erp5/trunk/products/CMFActivity/skins/activity/SQLQueue_reserveMessageList.zsql [utf8] (original)
+++ erp5/trunk/products/CMFActivity/skins/activity/SQLQueue_reserveMessageList.zsql [utf8] Thu Oct 15 14:33:35 2009
@@ -19,7 +19,16 @@
   processing_node=0
   AND date <= <dtml-sqlvar to_date type="datetime">
 ORDER BY
-  priority, date, uid
+<dtml-comment>
+  Explanation of the order by:
+  - priority must be respected (it is a feature)
+  - when multiple nodes simultaneously try to fetch activities, they should not
+    be given the same set of lines as it would cause all minus one to wait for
+    a write lock (and be ultimately aborted), effectively serializing their
+    action (so breaking paralellism).
+    So we must force MySQL to update lines in a random order.
+</dtml-comment>
+  priority, RAND()
 LIMIT <dtml-sqlvar count type="int">
 <dtml-var sql_delimiter>
 COMMIT