-
Problem report
-
Resolution: Fixed
-
Critical
-
6.0.31, 6.4.16, 7.0.0
-
Zabbix 6.0.31/PostgreSQL 14.12/Timescale 2.10.2/>32Cores/>256GB RAM/SSD storage array
-
Sprint candidates
-
2
Steps to reproduce:
- Try to make a full clone of a template while having the following size:
System information Parameter Value Details Zabbix server is running Yes 192.168.250.58:10051 Number of hosts (enabled/disabled) 16771 16771 / 0 Number of templates 393 Number of items (enabled/disabled/not supported) 40078592 12896493 / 26733341 / 448758 Number of triggers (enabled/disabled [problem/ok]) 792857 771152 / 21705 [49443 / 721709] Number of users (online) 123 13 Required server performance, new values per second 22293.26 High availability cluster Enabled Fail-over delay: 2 minutes
Result:
Debug (Frontend):
******************** Script profiler ******************** Total time: 98.830161 Total SQL time: 98.394164 SQL count: 215 (selects: 175 | executes: 54) Peak memory usage: 52M Memory limit: 4G
SQL (23.106825): SELECT itemid,uuid FROM items WHERE flags=2 AND (uuid IN ('fa2c84dc551447efb87a243012941e24','5d254d8e9d9f4c75811155fe59101f75','73eec1088b7447d18df05f131f2af139','462c78a8e621424d82d7cdd6d9c90805')) LIMIT 1 templates.php:399 → CApiWrapper->__call() → CFrontendApiWrapper->callMethod() → CApiWrapper->callMethod() → CFrontendApiWrapper->callClientMethod() → CLocalApiClient->callMethod() → CDiscoveryRule->copy() → CDiscoveryRule->copyDiscoveryRule() → CDiscoveryRule->copyItemPrototypes() → CItemPrototype->create() → CItemGeneral->checkInput() → CItemGeneral::checkUuidDuplicates() → DB::select() → DBselect() in include/classes/db/DB.php:1062 SQL (24.176015): SELECT itemid,uuid FROM items WHERE flags=1 AND uuid='056a4931c39e4e0b8bdfbaf50312a385' LIMIT 1 templates.php:399 → CApiWrapper->__call() → CFrontendApiWrapper->callMethod() → CApiWrapper->callMethod() → CFrontendApiWrapper->callClientMethod() → CLocalApiClient->callMethod() → CDiscoveryRule->copy() → CDiscoveryRule->copyDiscoveryRule() → CDiscoveryRule->create() → CDiscoveryRule->checkInput() → CItemGeneral->checkInput() → CItemGeneral::checkUuidDuplicates() → DB::select() → DBselect() in include/classes/db/DB.php:1062 SQL (23.37888): SELECT itemid,uuid FROM items WHERE flags=2 AND (uuid IN ('822fa5268a094fc9983fe0331fe752db','09c5bfedc177486890ab4257bac76f0d','10a94ffa5fce4874848f6c6530d92458','acb3b21b0b1344ab855a575042f3a5c1')) LIMIT 1 templates.php:399 → CApiWrapper->__call() → CFrontendApiWrapper->callMethod() → CApiWrapper->callMethod() → CFrontendApiWrapper->callClientMethod() → CLocalApiClient->callMethod() → CDiscoveryRule->copy() → CDiscoveryRule->copyDiscoveryRule() → CDiscoveryRule->copyItemPrototypes() → CItemPrototype->create() → CItemGeneral->checkInput() → CItemGeneral::checkUuidDuplicates() → DB::select() → DBselect() in include/classes/db/DB.php:1062
Slow SQL:
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON) SELECT itemid,uuid FROM items WHERE flags=1 AND uuid='056a4931c39e4e0b8bdfbaf50312a385' LIMIT 1; [ { "Plan": { "Node Type": "Limit", "Parallel Aware": false, "Async Capable": false, "Startup Cost": 10000000000.00, "Total Cost": 10000657853.75, "Plan Rows": 1, "Plan Width": 9, "Actual Startup Time": 25641.521, "Actual Total Time": 25641.523, "Actual Rows": 0, "Actual Loops": 1, "Output": ["itemid", "uuid"], "Shared Hit Blocks": 2547977, "Shared Read Blocks": 112132, "Shared Dirtied Blocks": 0, "Shared Written Blocks": 0, "Local Hit Blocks": 0, "Local Read Blocks": 0, "Local Dirtied Blocks": 0, "Local Written Blocks": 0, "Temp Read Blocks": 0, "Temp Written Blocks": 0, "Plans": [ { "Node Type": "Seq Scan", "Parent Relationship": "Outer", "Parallel Aware": false, "Async Capable": false, "Relation Name": "items", "Schema": "public", "Alias": "items", "Startup Cost": 10000000000.00, "Total Cost": 10003289268.76, "Plan Rows": 5, "Plan Width": 9, "Actual Startup Time": 25641.515, "Actual Total Time": 25641.516, "Actual Rows": 0, "Actual Loops": 1, "Output": ["itemid", "uuid"], "Filter": "((items.flags = 1) AND ((items.uuid)::text = '056a4931c39e4e0b8bdfbaf50312a385'::text))", "Rows Removed by Filter": 41368672, "Shared Hit Blocks": 2547977, "Shared Read Blocks": 112132, "Shared Dirtied Blocks": 0, "Shared Written Blocks": 0, "Local Hit Blocks": 0, "Local Read Blocks": 0, "Local Dirtied Blocks": 0, "Local Written Blocks": 0, "Temp Read Blocks": 0, "Temp Written Blocks": 0 } ] }, "Query Identifier": -1574290887641449573, "Planning": { "Shared Hit Blocks": 170, "Shared Read Blocks": 0, "Shared Dirtied Blocks": 0, "Shared Written Blocks": 0, "Local Hit Blocks": 0, "Local Read Blocks": 0, "Local Dirtied Blocks": 0, "Local Written Blocks": 0, "Temp Read Blocks": 0, "Temp Written Blocks": 0 }, "Planning Time": 1.726, "Triggers": [ ], "Execution Time": 25641.730 } ]
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON) SELECT itemid,uuid FROM items WHERE flags=2 AND (uuid IN ('822fa5268a094fc9983fe0331fe752db','09c5bfedc177486890ab4257bac76f0d','10a94ffa5fce4874848f6c6530d92458','acb3b21b0b1344ab855a575042f3a5c1')) LIMIT 1; [ { "Plan": { "Node Type": "Limit", "Parallel Aware": false, "Async Capable": false, "Startup Cost": 10000000000.00, "Total Cost": 10000026311.08, "Plan Rows": 1, "Plan Width": 9, "Actual Startup Time": 25207.867, "Actual Total Time": 25207.868, "Actual Rows": 0, "Actual Loops": 1, "Output": ["itemid", "uuid"], "Shared Hit Blocks": 2659408, "Shared Read Blocks": 701, "Shared Dirtied Blocks": 0, "Shared Written Blocks": 0, "Local Hit Blocks": 0, "Local Read Blocks": 0, "Local Dirtied Blocks": 0, "Local Written Blocks": 0, "Temp Read Blocks": 0, "Temp Written Blocks": 0, "Plans": [ { "Node Type": "Seq Scan", "Parent Relationship": "Outer", "Parallel Aware": false, "Async Capable": false, "Relation Name": "items", "Schema": "public", "Alias": "items", "Startup Cost": 10000000000.00, "Total Cost": 10003394128.72, "Plan Rows": 129, "Plan Width": 9, "Actual Startup Time": 25207.862, "Actual Total Time": 25207.863, "Actual Rows": 0, "Actual Loops": 1, "Output": ["itemid", "uuid"], "Filter": "((items.flags = 2) AND ((items.uuid)::text = ANY ('{822fa5268a094fc9983fe0331fe752db,09c5bfedc177486890ab4257bac76f0d,10a94ffa5fce4874848f6c6530d92458,acb3b21b0b1344ab855a575042f3a5c1}':: text[])))", "Rows Removed by Filter": 41368673, "Shared Hit Blocks": 2659408, "Shared Read Blocks": 701, "Shared Dirtied Blocks": 0, "Shared Written Blocks": 0, "Local Hit Blocks": 0, "Local Read Blocks": 0, "Local Dirtied Blocks": 0, "Local Written Blocks": 0, "Temp Read Blocks": 0, "Temp Written Blocks": 0 } ] }, "Query Identifier": 1135621909428237434, "Planning": { "Shared Hit Blocks": 55, "Shared Read Blocks": 0, "Shared Dirtied Blocks": 0, "Shared Written Blocks": 0, "Local Hit Blocks": 0, "Local Read Blocks": 0, "Local Dirtied Blocks": 0, "Local Written Blocks": 0, "Temp Read Blocks": 0, "Temp Written Blocks": 0 }, "Planning Time": 0.318, "Triggers": [ ], "Execution Time": 25207.898 } ]
Expected:
Fast SQL. Fast template cloning. This is not OK
