Products.CMFActivity: Fix poor performance with many family-bound activities
When there are many simultaneously-pending activities attached to any processing node family, the node>=0 subquery becomes dominant (taking hundreds of time longer than the other subqueries). As a consequence, this starves processing nodes of activities and increases the CPU needs of the mariadb process hosting the activity tables. So, move this subquery out of the regular codepath, and only run it if no other subquery found any activity: - there is no activity preferentially targeting the current node - there is no activity bound to any of the current node's families - there is no activity without any node preference at all Also, simplify the content of that subquery: the effective priority can only be 3 * priority + 1 when this query is run, and node=0 rows can be excluded (they should not exist in the current database view). Also, factorise the logic producing "node=processing_node" and "node IN node_set" subqueries, for simplicity. In turn, this makes all family-dependent subqueries use a simple equality test, ensuring a stable query plan independently from the number of families the current node is member of. Also, use "UNION ALL" always, as now: - all subqueries have stritly distinct result sets - as per mariadb documentation, "UNION [DISTINCT] applies to all UNIONs on the left", so the original comment about where ALL is used was incorrect in assuming it was improving the effective query performance Also, line-split SQL queries as visible in the python source to be more readable, without effect on the produced SQL. Also, line-split a few non-trivial python expression to make their internal structure immediately apparent. Another effect of this change this change is to reduce activity theft (activities to be preferentially executed by one node being executed by another), potentially improving object cache hit-rate and hence decreasing I/O pressure on the ZODB.
Status | Job ID | Name | Coverage | ||||||
---|---|---|---|---|---|---|---|---|---|
External | |||||||||
passed |
#515563
external
|
ERP5.CodingStyleTest-Master |
00:50:03
|
||||||
passed |
#515564
external
|
ERP5.PerformanceTest-Master |
00:26:28
|
||||||
failed |
#515594
external
|
ERP5.UnitTest-Master |
01:25:53
|
||||||
failed |
#515514
external
|
ERP5.UnitTest-Master.Medusa |
02:44:20
|
||||||
passed |
#515573
external
|
SlapOS.Eggs.UnitTest-Master.Python2 |
00:19:14
|
||||||
passed |
#515586
external
|
SlapOS.Eggs.UnitTest-Master.Python3 |
00:22:29
|
||||||
passed |
#515358
external
retried
|
ERP5.CodingStyleTest-Master |
00:53:03
|
||||||
passed |
#515284
external
retried
|
ERP5.CodingStyleTest-Master |
00:48:04
|
||||||
passed |
#515485
external
retried
|
ERP5.CodingStyleTest-Master |
01:15:57
|
||||||
passed |
#515359
external
retried
|
ERP5.PerformanceTest-Master |
00:26:34
|
||||||
failed |
#515506
external
retried
|
ERP5.PerformanceTest-Master |
00:27:06
|
||||||
passed |
#515319
external
retried
|
ERP5.PerformanceTest-Master |
00:26:32
|
||||||
failed |
#515552
external
retried
|
ERP5.UnitTest-Master |
02:29:16
|
||||||
failed |
#515285
external
retried
|
ERP5.UnitTest-Master |
01:44:18
|
||||||
failed |
#515370
external
retried
|
ERP5.UnitTest-Master |
04:49:02
|
||||||
failed |
#515282
external
retried
|
ERP5.UnitTest-Master.Medusa |
01:47:09
|
||||||
failed |
#515378
external
retried
|
ERP5.UnitTest-Master.Medusa |
02:56:29
|
||||||
passed |
#515306
external
retried
|
SlapOS.Eggs.UnitTest-Master.Python2 |
00:11:58
|
||||||
passed |
#515344
external
retried
|
SlapOS.Eggs.UnitTest-Master.Python2 |
00:13:58
|
||||||
passed |
#515482
external
retried
|
SlapOS.Eggs.UnitTest-Master.Python2 |
00:14:50
|
||||||
passed |
#515496
external
retried
|
SlapOS.Eggs.UnitTest-Master.Python3 |
00:15:12
|
||||||
passed |
#515293
external
retried
|
SlapOS.Eggs.UnitTest-Master.Python3 |
00:13:26
|
||||||
passed |
#515330
external
retried
|
SlapOS.Eggs.UnitTest-Master.Python3 |
00:13:30
|
||||||