-
Patch request
-
Resolution: Commercial support required
-
Major
-
None
-
None
-
None
-
None
Steps to reproduce:
- I am seeing zabbix_server.log reporting lots of slow query and all related to trends_uint.
- I am sharing SQL example.
mysql> show variables like '%buffer%';
---------------------------------------------------+
| Variable_name | Value |
---------------------------------------------------+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_chunk_size | 134217728 |
| innodb_buffer_pool_dump_at_shutdown | ON |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_dump_pct | 25 |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 32 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | ON |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 38654705664 |
| innodb_change_buffer_max_size | 25 |
| innodb_change_buffering | all |
| innodb_log_buffer_size | 16777216 |
| innodb_sort_buffer_size | 1048576 |
| join_buffer_size | 1048576 |
| key_buffer_size | 8388608 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 131072 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 262144 |
| sql_buffer_result | OFF |
---------------------------------------------------+
24 rows in set (0.00 sec)
mysql> \q
Bye
[root@tkt-zabbix-mysql-02 mysql]# free -m
total used free shared buff/cache available
Mem: 48138 41744 339 204 6054 5692
Swap: 2047 494 1553
[root@tkt-zabbix-mysql-02 mysql]#
15899:20191101:113619.481 slow query: 50.847825 sec, "insert into trends_uint (itemid,clock,num,value_min,value_avg,value_max) values (2880054,1572598800,6,0,0,0),(2880047,1572598800,6,0,0,0),(2876501,1572598800,6,0,0,0),(93479,1572598800,6,0,0,0),(2879863,1572598800,6,0,0,0),(4123755,1572598800,6,0,0,0),(4123754,1572598800,6,0,0,0),(4123750,1572598800,6,0,0,0),(4183804,1572598800,6,0,0,0),(2880050,1572598800,6,0,0,0),(2880049,1572598800,6,0,0,0),(2880048,1572598800,6,0,0,0),(2550806,1572598800,4,847,2198,3553),(2506201,1572598800,3,356184064,356184064,356184064),(2581801,1572598800,3,36960227328,36962571605,36965003264),(2090015,1572598800,1,21464350720,21464350720,21464350720),(3221637,1572598800,4,1572599401,1572600751,1572602101),(2687712,1572598800,4,0,0,0),(159581,1572598800,4,1572599401,1572600751,1572602101),(2550578,1572598800,4,552,1902,3252),(3118874,1572598800,4,1570461381,1570461381,1570461381),(3118860,1572598800,4,1,1,1),(3118862,1572598800,4,616,1966,3317),(3118863,1572598800,4,4,4,4),(3118864,1572598800,4,0,0,0),(3118865,1572598800,4,110,110,110),(3118891,1572598800,4,1572599401,1572600751,1572602101),(3118892,1572598800,4,127,127,127),(3118897,1572598800,4,2138020,2139370,2140720),(3392590,1572598800,4,0,0,0),(970201,1572598800,3,302346240,302346240,302346240),(2498677,1572598800,4,1,1,1),(2498704,1572598800,4,1572599399,1572600750,1572602101),(2544565,1572598800,4,83,83,83),(2545578,1572598800,4,0,0,0),(2546517,1572598800,4,0,0,0),(80202,1572598800,4,58342425,58343775,58345125),(2593240,1572598800,4,1572599401,1572600751,1572602101),(28066,1572598800,4,36988841,36990191,36991541),(3203401,1572598800,3,401050533888,401050533888,401050533888),(3132601,1572598800,3,359682048,359682048,359682048),(2895827,1572598800,1,3097418076,3097418076,3097418076),(2796314,1572598800,4,0,0,0),(3159001,1572598800,3,20320059392,20324563626,20329066496),(2687707,1572598800,4,1,1,1),(85676,1572598800,4,1572599401,1572600751,1572602102),(3237902,1572598800,4,1,1,1),(2550573,1572598800,4,870,2222,3581),(2506202,1572598800,3,1090301247488,1090529325056,1090745012224),(2581802,1572598800,3,304836608,304836608,304836608);
Result:
Zabbix performance issue.
Expected:
See screenshot....
See attached patch file...