Uploaded image for project: 'ZABBIX BUGS AND ISSUES'
  1. ZABBIX BUGS AND ISSUES
  2. ZBX-24819

Slow SQL's on template full clone

XMLWordPrintable

    • Sprint candidates
    • 2

      Steps to reproduce:

      1. 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

            asestakovs Aleksejs Sestakovs
            edgar.akhmetshin Edgar Akhmetshin
            Team B
            Votes:
            0 Vote for this issue
            Watchers:
            7 Start watching this issue

              Created:
              Updated:
              Resolved: