[ZBX-16854] Slow query logs for insert into trends_uint table Created: 2019 Nov 01  Updated: 2019 Nov 04  Resolved: 2019 Nov 04

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Patch request Priority: Major
Reporter: Cagri Assignee: Zabbix Support Team
Resolution: Commercial support required Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Attachments: File zabbix_server.log.gz    

 Description   

Steps to reproduce:

  1. I am seeing zabbix_server.log reporting lots of slow query and all related to trends_uint.
  2. 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...

 Comments   
Comment by Cagri [ 2019 Nov 04 ]

even if i increase the innodb_buffer_pool_size , problem reoccured.

 

Comment by Aigars Kadikis [ 2019 Nov 04 ]

Hello,

This is pure performance tuning topic. Kindly contact professional services.

Quite recently we released an official template for MySQL monitoring. A lot of predefined graphs + screens has been build in. At least Zabbix 4.2 required:
https://git.zabbix.com/projects/ZBX/repos/zabbix/browse/templates/db/mysql

It would be definitely good to have those graphs as startup capital for performance tuning.

Generated at Tue May 13 08:28:18 EEST 2025 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.