problem: [Z3005] query failed: [2006] MySQL server has gone away

[mike_z]

Hello everybody!


There are a lot of warnings:

44465:20160118:152009.626 [Z3005] query failed: [2006] MySQL server has gone away [select hostid,key_,state,filter,error,lifetime from items where itemid=44407]
 44603:20160118:152018.011 [Z3005] query failed: [2006] MySQL server has gone away [begin;]
 44592:20160118:152020.266 [Z3005] query failed: [2006] MySQL server has gone away [begin;]
 44525:20160118:152026.292 [Z3005] query failed: [2006] MySQL server has gone away [begin;]
 44597:20160118:152050.008 [Z3005] query failed: [2006] MySQL server has gone away [begin;]
 44537:20160118:152105.403 [Z3005] query failed: [2006] MySQL server has gone away [begin;]
 44522:20160118:152108.407 [Z3005] query failed: [2006] MySQL server has gone away [begin;]
 44489:20160118:152111.000 [Z3005] query failed: [2006] MySQL server has gone away [select hostid,key_,state,filter,error,lifetime from items where itemid=44461]
 44475:20160118:152252.896 [Z3005] query failed: [2006] MySQL server has gone away [select hostid,key_,state,filter,error,lifetime from items where itemid=44569]
 44472:20160118:152256.721 [Z3005] query failed: [2006] MySQL server has gone away [select hostid,key_,state,filter,error,lifetime from items where itemid=43975]
 44481:20160118:152346.262 [Z3005] query failed: [2006] MySQL server has gone away [select hostid,key_,state,filter,error,lifetime from items where itemid=44623]
 44483:20160118:152437.763 [Z3005] query failed: [2006] MySQL server has gone away [select hostid,key_,state,filter,error,lifetime from items where itemid=44677]
 44534:20160118:152526.002 [Z3005] query failed: [2006] MySQL server has gone away [begin;]
 44464:20160118:152758.843 [Z3005] query failed: [2006] MySQL server has gone away [select hostid,key_,state,filter,error,lifetime from items where itemid=23278]
 44604:20160118:152826.003 [Z3005] query failed: [2006] MySQL server has gone away [begin;]
 44536:20160118:152908.013 [Z3005] query failed: [2006] MySQL server has gone away [begin;]
 44480:20160118:152911.622 [Z3005] query failed: [2006] MySQL server has gone away [select hostid,key_,state,filter,error,lifetime from items where itemid=44947]
 44470:20160118:153006.329 [Z3005] query failed: [2006] MySQL server has gone away [select hostid,key_,state,filter,error,lifetime from items where itemid=45001]
44519:20160118:153020.007 [Z3005] query failed: [2006] MySQL server has gone away [begin;]
 44538:20160118:153220.012 [Z3005] query failed: [2006] MySQL server has gone away [begin;]
 44593:20160118:153226.012 [Z3005] query failed: [2006] MySQL server has gone away [begin;]
 44509:20160118:153356.462 [Z3005] query failed: [2006] MySQL server has gone away [select hostid,key_,state,filter,error,lifetime from items where itemid=45217]
 44585:20160118:153420.004 [Z3005] query failed: [2006] MySQL server has gone away [begin;]
 44594:20160118:153426.006 [Z3005] query failed: [2006] MySQL server has gone away [begin;]
 44485:20160118:153434.336 [Z3005] query failed: [2006] MySQL server has gone away [select hostid,key_,state,filter,error,lifetime from items where itemid=45271]
 44523:20160118:153435.007 [Z3005] query failed: [2006] MySQL server has gone away [begin;]
 44474:20160118:153528.959 [Z3005] query failed: [2006] MySQL server has gone away [select hostid,key_,state,filter,error,lifetime from items where itemid=45325]
 44530:20160118:153635.006 [Z3005] query failed: [2006] MySQL server has gone away [begin;]
 44649:20160118:153638.007 [Z3005] query failed: [2006] MySQL server has gone away [begin;]
 44502:20160118:153727.879 [Z3005] query failed: [2006] MySQL server has gone away [select hostid,key_,state,filter,error,lifetime from items where itemid=45433]
 44648:20160118:153750.010 [Z3005] query failed: [2006] MySQL server has gone away [begin;]
 44498:20160118:153816.841 [Z3005] query failed: [2006] MySQL server has gone away [select hostid,key_,state,filter,error,lifetime from items where itemid=44893]
 44651:20160118:153835.012 [Z3005] query failed: [2006] MySQL server has gone away [begin;]
 44520:20160118:153918.007 [Z3005] query failed: [2006] MySQL server has gone away [begin;]
 44507:20160118:154005.596 [Z3005] query failed: [2006] MySQL server has gone away [select hostid,key_,state,filter,error,lifetime from items where itemid=45595]

I've tried to increase max_allowed_packet and thread_cache_size, but it's not helping.

my.cnf

[mysqld]
log-error=/var/log/mariadb/mariadb_err.log
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
open_files_limit=4096
max_connections=800
log-slow-queries=/var/log/mariadb/slow_queries.log
long_query_time=10

max_allowed_packet = 128M
thread_cache_size = 8


innodb_flush_log_at_trx_commit = 0 
key_buffer_size = 8M 
sort_buffer_size = 1M 
join_buffer_size = 1M 
innodb_log_file_size = 48M 
innodb_old_blocks_time = 1000

innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_io_capacity = 10000

risks
symbolic-links=0

innodb_buffer_pool_size = 1024M

my zabbix db size ~3.3GB
wait_timeout | 28800
interactive_timeout | 28800


Any ideas?

[ingus.vilnis]

Hi,

Check how many Zabbix data gathering processes you have started and try lowering the amount if you are not utilizing them properly.
This post might help you with that: https://www.zabbix.com/forum/showthread.php?t=47781

However here you can learn more hot to tune your MySQL according to your hardware: https://www.percona.com/blog/2014/11...-mysql-zabbix/

The general problem I suppose is that you have started too many pollers/pingers/other Zabbix processes, they are idling and then MySQL closes connection for those after 8 hours. That is nothing sinister because the connection will be re-established if needed but the log message is annoying, I agree.

Best Regards,
Ingus