Uploaded image for project: 'ZABBIX FEATURE REQUESTS'
  1. ZABBIX FEATURE REQUESTS
  2. ZBXNEXT-5437

zabbix server and API some Slow SQL problem

    XMLWordPrintable

Details

    • Change Request
    • Status: Closed
    • Critical
    • Resolution: Change request declined
    • None
    • None
    • API (A), Server (S)
    • None
    • MySQL5.6.42 zabbix4.0.10

    Description

      Our Zabbix monitoring system monitors tens of thousands of instances.I found zabbix server and API have a lot some Slow SQL problem:

      1. # Files: slow.log
      2. Overall: 7.18k total, 44 unique, 0.96 QPS, 11.61x concurrency __________
      3. Time range: 2019-09-16 14:13:33 to 16:18:13
      4. Attribute total min max avg 95% stddev median
      5. ============ ======= ======= ======= ======= ======= ======= =======
      6. Exec time 86866s 1s 88s 12s 52s 17s 5s
      7. Lock time 2s 0 25ms 286us 247us 2ms 108us
      8. Rows sent 470.76M 0 2.69M 67.16k 233.54k 284.31k 202.40
      9. Rows examine 45.89G 0 45.20M 6.55M 42.20M 14.10M 619.64k
      10. Rows affecte 26.39k 0 6.98k 3.76 0 119.34 0
      11. Bytes sent 44.16G 11 361.69M 6.30M 25.91M 32.93M 16.75k
      12. Query size 17.18M 8 256.04k 2.45k 833.10 20.25k 313.99
      1. Profile
      2. Rank Query ID Response time Calls R/Call V/M Item
      3. ==== ================== ================ ===== ======= ===== ===========
      4. 1 0x60E03AE83477FF30 40164.4536 46.2% 771 52.0940 0.27 SELECT problem event_suppress
      5. 2 0x2E08FA2520A6A2A3 13940.3708 16.0% 2783 5.0091 1.49 SELECT problem
      6. 3 0x30A8075DC29B081F 9488.4300 10.9% 157 60.4359 0.49 SELECT problem functions items hosts_groups rights event_suppress
      7. 4 0x941A8505A5620E30 5684.9990 6.5% 740 7.6824 0.54 SELECT problem functions items hosts_groups rights
      8. 5 0x4BDD0222A771767D 5430.9763 6.3% 119 45.6385 1.04 SELECT triggers functions items hosts_groups rights
      9. 6 0xC70739615684F475 2951.0098 3.4% 389 7.5861 0.54 SELECT problem functions items hosts_groups rights
      10. 7 0x4C42CADC4E4A80C4 1773.1870 2.0% 740 2.3962 0.31 SELECT problem functions items hosts_groups rights
      11. 8 0x69AC02DDE3D7AB97 1523.9639 1.8% 609 2.5024 0.13 SELECT items hosts
      12. 9 0x8E14B30A7031C07E 1361.7154 1.6% 62 21.9632 0.14 SELECT items hosts
      13. 10 0x0B0E5D9850A66736 1302.3942 1.5% 61 21.3507 0.20 SELECT hosts items functions triggers
      14. 11 0x6A68081108273397 1259.0357 1.4% 62 20.3070 0.27 SELECT item_preproc items hosts
      15. 12 0xAC0C2090F0E24E80 833.9245 1.0% 62 13.4504 0.07 SELECT hosts items functions triggers
      16. 13 0x19C8068B5C1997CD 371.6809 0.4% 262 1.4186 0.17 ROLLBACK
      17. 14 0xBAC20BE59A63DBC0 176.7546 0.2% 25 7.0702 0.02 SELECT hosts_groups
      18. 15 0x05E1FABFE95833A9 115.3319 0.1% 61 1.8907 0.44 SELECT trigger_depends triggers hosts items functions
      19. 16 0x5E4437C866788DAD 98.3025 0.1% 72 1.3653 0.10 SELECT problem
      20. 17 0xBC5490DD236E7849 65.0786 0.1% 33 1.9721 0.15 INSERT history
      21. 18 0x27A5848D2C10B561 56.6377 0.1% 40 1.4159 0.15 INSERT event_recovery
      22. 20 0x57EFEF3601B9E050 33.0257 0.0% 15 2.2017 0.09 SELECT hosts interface hosts_groups rights
      23. 21 0x7977A8CB9A7C408D 31.6753 0.0% 16 1.9797 0.07 SELECT hosts hosts_groups rights
      24. 22 0xEF254BD50950CCE2 27.9562 0.0% 14 1.9969 0.11 SELECT hosts interface hosts_groups rights
      25. 23 0xCE503BC5064530AB 17.7837 0.0% 10 1.7784 0.42 SELECT history_uint
      26. 24 0x46C2873C23DD7221 12.8535 0.0% 11 1.1685 0.00 UPDATE problem
      27. MISC 0xMISC 144.2947 0.2% 64 2.2546 0.0 <21 ITEMS>
      1. Query 1: 0.10 QPS, 5.38x concurrency, ID 0x60E03AE83477FF30 at byte 4282560039
      2. This item is included in the report because it matches --limit.
      3. Scores: V/M = 0.27
      4. Time range: 2019-09-16 14:13:45 to 16:18:13
      5. Attribute pct total min max avg 95% stddev median
      6. ============ === ======= ======= ======= ======= ======= ======= =======
      7. Count 10 771
      8. Exec time 46 40164s 45s 78s 52s 57s 4s 49s
      9. Lock time 4 91ms 64us 195us 117us 152us 26us 113us
      10. Rows sent 0 181 0 3 0.23 0.99 0.53 0
      11. Rows examine 70 32.29G 42.86M 42.90M 42.89M 42.20M 0.50 42.20M
      12. Rows affecte 0 0 0 0 0 0 0 0
      13. Bytes sent 0 313.38k 399 617 416.22 487.09 42.44 381.65
      14. Query size 1 238.68k 317 317 317 317 0 317
      15. String:
      16. Databases zabbix
      17. Hosts
      18. Last errno 0
      19. Users zabbix
      20. Query_time distribution
      21. 1us
      22. 10us
      23. 100us
      24. 1ms
      25. 10ms
      26. 100ms
      27. 1s
      28. 10s+ ################################################################
      29. Tables
      30. SHOW TABLE STATUS FROM `zabbix` LIKE 'problem'\G
      31. SHOW CREATE TABLE `zabbix`.`problem`\G
      32. SHOW TABLE STATUS FROM `zabbix` LIKE 'event_suppress'\G
      33. SHOW CREATE TABLE `zabbix`.`event_suppress`\G
      34. EXPLAIN /!50100 PARTITIONS/
        SELECT p.eventid,p.r_eventid,p.objectid,p.severity,p.clock,p.r_clock,p.name FROM problem p WHERE p.source='0' AND p.object='0' AND p.severity IN ('4','5') AND NOT EXISTS (SELECT NULL FROM event_suppress es WHERE es.eventid=p.eventid) AND p.r_eventid IS NULL AND p.clock>='1568619293' ORDER BY p.eventid DESC LIMIT 15\G
      1. Query 2: 0.37 QPS, 1.86x concurrency, ID 0x2E08FA2520A6A2A3 at byte 4274966048
      2. This item is included in the report because it matches --limit.
      3. Scores: V/M = 1.49
      4. Time range: 2019-09-16 14:13:38 to 16:18:13
      5. Attribute pct total min max avg 95% stddev median
      6. ============ === ======= ======= ======= ======= ======= ======= =======
      7. Count 38 2783
      8. Exec time 16 13940s 1s 21s 5s 10s 3s 4s
      9. Lock time 7 157ms 29us 120us 56us 80us 13us 52us
      10. Rows sent 2 10.90M 0 15.25k 4.01k 13.78k 5.68k 0.99
      11. Rows examine 0 10.90M 0 15.25k 4.01k 13.78k 5.68k 0.99
      12. Rows affecte 0 0 0 0 0 0 0 0
      13. Bytes sent 0 266.85M 203 378.81k 98.19k 345.04k 139.48k 223.14
      14. Query size 1 324.17k 118 120 119.28 118.34 1 118.34
      15. String:
      16. Databases zabbix
      17. Hosts
      18. Last errno 0
      19. Users zabbix
      20. Query_time distribution
      21. 1us
      22. 10us
      23. 100us
      24. 1ms
      25. 10ms
      26. 100ms
      27. 1s ################################################################
      28. 10s+ ####
      29. Tables
      30. SHOW TABLE STATUS FROM `zabbix` LIKE 'problem'\G
      31. SHOW CREATE TABLE `zabbix`.`problem`\G
      32. EXPLAIN /!50100 PARTITIONS/
        select eventid,object,objectid from problem where r_eventid is null and source=3 and ( (object=4 and objectid=10344359))\G
      1. Query 3: 0.03 QPS, 1.86x concurrency, ID 0x30A8075DC29B081F at byte 4282572573
      2. This item is included in the report because it matches --limit.
      3. Scores: V/M = 0.49
      4. Time range: 2019-09-16 14:13:36 to 15:38:34
      5. Attribute pct total min max avg 95% stddev median
      6. ============ === ======= ======= ======= ======= ======= ======= =======
      7. Count 2 157
      8. Exec time 10 9488s 53s 88s 60s 69s 5s 57s
      9. Lock time 1 36ms 133us 368us 230us 287us 46us 236us
      10. Rows sent 0 0 0 0 0 0 0 0
      11. Rows examine 15 6.93G 45.17M 45.20M 45.19M 44.31M 0 44.31M
      12. Rows affecte 0 0 0 0 0 0 0 0
      13. Bytes sent 0 61.17k 399 399 399 399 0 399
      14. Query size 0 96.13k 627 627 627 627 0 627
      15. String:
      16. Databases zabbix
      17. Hosts
      18. Last errno 0
      19. Users zabbix
      20. Query_time distribution
      21. 1us
      22. 10us
      23. 100us
      24. 1ms
      25. 10ms
      26. 100ms
      27. 1s
      28. 10s+ ################################################################
      29. Tables
      30. SHOW TABLE STATUS FROM `zabbix` LIKE 'problem'\G
      31. SHOW CREATE TABLE `zabbix`.`problem`\G
      32. SHOW TABLE STATUS FROM `zabbix` LIKE 'functions'\G
      33. SHOW CREATE TABLE `zabbix`.`functions`\G
      34. SHOW TABLE STATUS FROM `zabbix` LIKE 'items'\G
      35. SHOW CREATE TABLE `zabbix`.`items`\G
      36. SHOW TABLE STATUS FROM `zabbix` LIKE 'hosts_groups'\G
      37. SHOW CREATE TABLE `zabbix`.`hosts_groups`\G
      38. SHOW TABLE STATUS FROM `zabbix` LIKE 'rights'\G
      39. SHOW CREATE TABLE `zabbix`.`rights`\G
      40. SHOW TABLE STATUS FROM `zabbix` LIKE 'event_suppress'\G
      41. SHOW CREATE TABLE `zabbix`.`event_suppress`\G
      42. EXPLAIN /!50100 PARTITIONS/
        SELECT p.eventid,p.r_eventid,p.objectid,p.severity,p.clock,p.r_clock,p.name FROM problem p WHERE p.source='0' AND p.object='0' AND NOT EXISTS (SELECT NULL FROM functions f,items i,hosts_groups hgg LEFT JOIN rights r ON r.id=hgg.groupid AND r.groupid IN ('133','134','203','228') WHERE p.objectid=f.triggerid AND f.itemid=i.itemid AND i.hostid=hgg.hostid GROUP BY i.hostid HAVING MAX(permission)<2 OR MIN(permission) IS NULL OR MIN(permission)=0) AND p.severity='5' AND NOT EXISTS (SELECT NULL FROM event_suppress es WHERE es.eventid=p.eventid) AND p.r_eventid IS NULL AND p.clock>='1568619323' ORDER BY p.eventid DESC LIMIT 15\G
      1. Query 4: 0.10 QPS, 0.76x concurrency, ID 0x941A8505A5620E30 at byte 4282549616
      2. This item is included in the report because it matches --limit.
      3. Scores: V/M = 0.54
      4. Time range: 2019-09-16 14:13:33 to 16:17:51
      5. Attribute pct total min max avg 95% stddev median
      6. ============ === ======= ======= ======= ======= ======= ======= =======
      7. Count 10 740
      8. Exec time 6 5685s 4s 24s 8s 11s 2s 7s
      9. Lock time 6 143ms 106us 334us 193us 247us 40us 185us
      10. Rows sent 0 1.06M 1.47k 1.47k 1.47k 1.47k 0 1.47k
      11. Rows examine 3 1.74G 2.40M 2.41M 2.41M 2.37M 0.03 2.37M
      12. Rows affecte 0 0 0 0 0 0 0 0
      13. Bytes sent 0 86.35M 119.43k 119.53k 119.49k 117.95k 0.00 117.95k
      14. Query size 2 377.89k 517 523 522.92 511.45 0 511.45
      15. String:
      16. Databases zabbix
      17. Hosts
      18. Last errno 0
      19. Users zabbix
      20. Query_time distribution
      21. 1us
      22. 10us
      23. 100us
      24. 1ms
      25. 10ms
      26. 100ms
      27. 1s ################################################################
      28. 10s+ ######
      29. Tables
      30. SHOW TABLE STATUS FROM `zabbix` LIKE 'problem'\G
      31. SHOW CREATE TABLE `zabbix`.`problem`\G
      32. SHOW TABLE STATUS FROM `zabbix` LIKE 'functions'\G
      33. SHOW CREATE TABLE `zabbix`.`functions`\G
      34. SHOW TABLE STATUS FROM `zabbix` LIKE 'items'\G
      35. SHOW CREATE TABLE `zabbix`.`items`\G
      36. SHOW TABLE STATUS FROM `zabbix` LIKE 'hosts_groups'\G
      37. SHOW CREATE TABLE `zabbix`.`hosts_groups`\G
      38. SHOW TABLE STATUS FROM `zabbix` LIKE 'rights'\G
      39. SHOW CREATE TABLE `zabbix`.`rights`\G
      40. EXPLAIN /!50100 PARTITIONS/
        SELECT p.eventid,p.objectid,p.clock,p.ns,p.name,p.severity FROM problem p WHERE p.source='0' AND p.object='0' AND NOT EXISTS (SELECT NULL FROM functions f,items i,hosts_groups hgg LEFT JOIN rights r ON r.id=hgg.groupid AND r.groupid IN ('133','134','203','228') WHERE p.objectid=f.triggerid AND f.itemid=i.itemid AND i.hostid=hgg.hostid GROUP BY i.hostid HAVING MAX(permission)<2 OR MIN(permission) IS NULL OR MIN(permission)=0) AND p.severity BETWEEN '0' AND '5' AND p.r_eventid IS NULL ORDER BY p.eventid DESC LIMIT 1501\G
      1. Query 5: 0.02 QPS, 0.73x concurrency, ID 0x4BDD0222A771767D at byte 4282578003
      2. This item is included in the report because it matches --limit.
      3. Scores: V/M = 1.04
      4. Time range: 2019-09-16 14:14:05 to 16:17:34
      5. Attribute pct total min max avg 95% stddev median
      6. ============ === ======= ======= ======= ======= ======= ======= =======
      7. Count 1 119
      8. Exec time 6 5431s 35s 81s 46s 57s 7s 42s
      9. Lock time 0 19ms 92us 263us 156us 214us 39us 144us
      10. Rows sent 0 143 0 6 1.20 3.89 1.19 0.99
      11. Rows examine 4 1.91G 16.43M 16.43M 16.43M 15.90M 0 15.90M
      12. Rows affecte 0 0 0 0 0 0 0 0
      13. Bytes sent 0 11.20k 82 154 96.36 124.25 13.62 92.72
      14. Query size 0 48.11k 414 414 414 414 0 414
      15. String:
      16. Databases zabbix
      17. Hosts
      18. Last errno 0
      19. Users zabbix
      20. Query_time distribution
      21. 1us
      22. 10us
      23. 100us
      24. 1ms
      25. 10ms
      26. 100ms
      27. 1s
      28. 10s+ ################################################################
      29. Tables
      30. SHOW TABLE STATUS FROM `zabbix` LIKE 'triggers'\G
      31. SHOW CREATE TABLE `zabbix`.`triggers`\G
      32. SHOW TABLE STATUS FROM `zabbix` LIKE 'functions'\G
      33. SHOW CREATE TABLE `zabbix`.`functions`\G
      34. SHOW TABLE STATUS FROM `zabbix` LIKE 'items'\G
      35. SHOW CREATE TABLE `zabbix`.`items`\G
      36. SHOW TABLE STATUS FROM `zabbix` LIKE 'hosts_groups'\G
      37. SHOW CREATE TABLE `zabbix`.`hosts_groups`\G
      38. SHOW TABLE STATUS FROM `zabbix` LIKE 'rights'\G
      39. SHOW CREATE TABLE `zabbix`.`rights`\G
      40. EXPLAIN /!50100 PARTITIONS/
        SELECT t.triggerid FROM triggers t WHERE NOT EXISTS (SELECT NULL FROM functions f,items i,hosts_groups hgg LEFT JOIN rights r ON r.id=hgg.groupid AND r.groupid IN ('133','134','203','228') WHERE t.triggerid=f.triggerid AND f.itemid=i.itemid AND i.hostid=hgg.hostid GROUP BY i.hostid HAVING MAX(permission)<'2' OR MIN(permission) IS NULL OR MIN(permission)=0) AND t.lastchange>'1568619308' AND t.flags IN ('0','4')\G
      1. Query 6: 0.05 QPS, 0.40x concurrency, ID 0xC70739615684F475 at byte 4275053284
      2. This item is included in the report because it matches --limit.
      3. Scores: V/M = 0.54
      4. Time range: 2019-09-16 14:13:42 to 16:17:58
      5. Attribute pct total min max avg 95% stddev median
      6. ============ === ======= ======= ======= ======= ======= ======= =======
      7. Count 5 389
      8. Exec time 3 2951s 4s 26s 8s 10s 2s 7s
      9. Lock time 4 85ms 135us 326us 218us 287us 43us 214us
      10. Rows sent 0 79.07k 203 219 208.14 212.52 4.27 202.40
      11. Rows examine 1 935.43M 2.40M 2.41M 2.40M 2.37M 0.03 2.37M
      12. Rows affecte 0 0 0 0 0 0 0 0
      13. Bytes sent 0 6.46M 16.58k 17.89k 17.00k 16.75k 404.50 16.75k
      14. Query size 1 260.98k 687 687 687 687 0 687
      15. String:
      16. Databases zabbix
      17. Hosts
      18. Last errno 0
      19. Users zabbix
      20. Query_time distribution
      21. 1us
      22. 10us
      23. 100us
      24. 1ms
      25. 10ms
      26. 100ms
      27. 1s ################################################################
      28. 10s+ ######
      29. Tables
      30. SHOW TABLE STATUS FROM `zabbix` LIKE 'problem'\G
      31. SHOW CREATE TABLE `zabbix`.`problem`\G
      32. SHOW TABLE STATUS FROM `zabbix` LIKE 'functions'\G
      33. SHOW CREATE TABLE `zabbix`.`functions`\G
      34. SHOW TABLE STATUS FROM `zabbix` LIKE 'items'\G
      35. SHOW CREATE TABLE `zabbix`.`items`\G
      36. SHOW TABLE STATUS FROM `zabbix` LIKE 'hosts_groups'\G
      37. SHOW CREATE TABLE `zabbix`.`hosts_groups`\G
      38. SHOW TABLE STATUS FROM `zabbix` LIKE 'rights'\G
      39. SHOW CREATE TABLE `zabbix`.`rights`\G
      40. EXPLAIN /!50100 PARTITIONS/
        SELECT DISTINCT p.eventid,p.objectid,p.clock,p.ns,p.name,p.severity FROM problem p ,functions f,items i,hosts_groups hg WHERE p.source='0' AND p.object='0' AND NOT EXISTS (SELECT NULL FROM functions f,items i,hosts_groups hgg LEFT JOIN rights r ON r.id=hgg.groupid AND r.groupid IN ('133','134','203','228') WHERE p.objectid=f.triggerid AND f.itemid=i.itemid AND i.hostid=hgg.hostid GROUP BY i.hostid HAVING MAX(permission)<2 OR MIN(permission) IS NULL OR MIN(permission)=0) AND p.objectid=f.triggerid AND f.itemid=i.itemid AND i.hostid=hg.hostid AND hg.groupid IN ('657','1042','1358','2746') AND p.severity BETWEEN '0' AND '5' AND p.r_eventid IS NULL ORDER BY p.eventid DESC LIMIT 1501\G
      1. Query 7: 0.10 QPS, 0.24x concurrency, ID 0x4C42CADC4E4A80C4 at byte 4273955953
      2. This item is included in the report because it matches --limit.
      3. Scores: V/M = 0.31
      4. Time range: 2019-09-16 14:13:37 to 16:17:54
      5. Attribute pct total min max avg 95% stddev median
      6. ============ === ======= ======= ======= ======= ======= ======= =======
      7. Count 10 740
      8. Exec time 2 1773s 1s 11s 2s 4s 858ms 2s
      9. Lock time 6 139ms 100us 342us 188us 247us 41us 185us
      10. Rows sent 0 441.70k 605 624 611.21 592.07 5.14 592.07
      11. Rows examine 1 541.01M 726.50k 754.20k 748.63k 753.18k 13.85k 753.18k
      12. Rows affecte 0 0 0 0 0 0 0 0
      13. Bytes sent 0 35.07M 48.04k 49.59k 48.54k 49.01k 725.50 46.68k
      14. Query size 2 397.40k 544 550 549.92 537.02 0 537.02
      15. String:
      16. Databases zabbix
      17. Hosts
      18. Last errno 0
      19. Users zabbix
      20. Query_time distribution
      21. 1us
      22. 10us
      23. 100us
      24. 1ms
      25. 10ms
      26. 100ms
      27. 1s ################################################################
      28. 10s+ #
      29. Tables
      30. SHOW TABLE STATUS FROM `zabbix` LIKE 'problem'\G
      31. SHOW CREATE TABLE `zabbix`.`problem`\G
      32. SHOW TABLE STATUS FROM `zabbix` LIKE 'functions'\G
      33. SHOW CREATE TABLE `zabbix`.`functions`\G
      34. SHOW TABLE STATUS FROM `zabbix` LIKE 'items'\G
      35. SHOW CREATE TABLE `zabbix`.`items`\G
      36. SHOW TABLE STATUS FROM `zabbix` LIKE 'hosts_groups'\G
      37. SHOW CREATE TABLE `zabbix`.`hosts_groups`\G
      38. SHOW TABLE STATUS FROM `zabbix` LIKE 'rights'\G
      39. SHOW CREATE TABLE `zabbix`.`rights`\G
      40. EXPLAIN /!50100 PARTITIONS/
        SELECT p.eventid,p.objectid,p.clock,p.ns,p.name,p.severity FROM problem p WHERE p.source='0' AND p.object='0' AND NOT EXISTS (SELECT NULL FROM functions f,items i,hosts_groups hgg LEFT JOIN rights r ON r.id=hgg.groupid AND r.groupid IN ('133','134','203','228') WHERE p.objectid=f.triggerid AND f.itemid=i.itemid AND i.hostid=hgg.hostid GROUP BY i.hostid HAVING MAX(permission)<2 OR MIN(permission) IS NULL OR MIN(permission)=0) AND p.severity BETWEEN '0' AND '5' AND p.r_eventid IS NULL AND p.eventid<='169574939' ORDER BY p.eventid DESC LIMIT 1501\G

      Attachments

        Activity

          People

            Unassigned Unassigned
            wangwei wei
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: