[Zabbix-users] Poor performances with MySQL 5.7

[Nicolas C. <za...@nr...>]

Hello,

I'm using Zabbix 2.4.7 with MySQL Community Server 5.6.27 on a Debian 
Jessie virtual machine, we currently have 740 NVPS (roughly 60 millions 
rows per day).

We use MySQL partitioning 
(https://www.zabbix.org/wiki/Docs/howto/mysql_partition) : hourly for 
"history" and "history_uint" and daily for the others tables.

We tried upgrading MySQL to 5.7.9 and we experienced very poor 
performances with the database :

  - intensive reads on the database, even when nobody was on the 
frontend (didn't happen with MySQL 5.6)

  - Dashboard very slow when the filter is disabled (a few minutes to 
display the page)

  - Peaks of "history syncer processes", 100% during several minutes. 
With MySQL 5.6 the average is 1% busy with the default value 
StartDBSyncers=4

  - Drops in "free history write cache". With MySQL 5.6 and 
HistoryCacheSize=128M we have 99.94% free history write cache on average.

All this issues happened on a Saturday with no particular activity on 
the storage and the server. We switched back to 5.6 and everything was 
back to normal.

Before upgrading on the production server, we tested on a Zabbix 
installation with less NVPS and MySQL 5.7 was working fine.

For information, we tweaked your MySQL configuration with the percona 
guide :

max_connections = 500

sync_binlog = 0
query_cache_size = 0
query_cache_type = 0

innodb_flush_method = O_DIRECT
innodb_buffer_pool_instances = 8
innodb_file_per_table = 1
innodb_buffer_pool_size = 8G
innodb_flush_log_at_trx_commit = 0
innodb_io_capacity = 800

Is anyone using MySQL 5.7 as database for Zabbix ? Any idea why it works 
normally on a test server with 40NVPS but not on a production server 
with 740NVPS ?

Thanks,

Nicolas