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