Zabbix Item Collection Slow After Restarting Service

[StealthJoe]

I got an error on my Server dashboard saying that the value cache is running in low memory mode so I changed my ValueCacheSize to a larger number (1G - 2G) in my configuration file and restarted the service. That went fine but after restarting the service Zabbix has been very slow to show new item data. It's lagging behind by hours at this point but is slowly catching up. I've had this happen other times when restarting the service.

Has anyone seen this before? How can I fix it?

This is a pretty large installation - 1800 hosts (mostly active), 170k items, 650 nvps. It's running CentOS 7 and MariaDB 5.5. The server and database are on the same VM. I have setup partitioning on the database as well. Performance has been great since then, this is the first hiccup.

I am seeing slow queries in my server log but not sure why it's only an issue after the restart.

[LenR]

What version of Zabbix? Do you have any proxies? Check the history syncer stats during the problem. There have been similar problems, for example https://support.zabbix.com/browse/ZBX-13343

[StealthJoe]

What version of Zabbix? Do you have any proxies? Check the history syncer stats during the problem. There have been similar problems, for example https://support.zabbix.com/browse/ZBX-13343

Version is 4.2.1 and no I don't have any proxies. The history syncer is at 100% during the problem.

[LenR]

Can you look at the mysql tuning recommendations in something like myphpadmin or one of the tuner scripts?

[StealthJoe]

Can you look at the mysql tuning recommendations in something like myphpadmin or one of the tuner scripts?

I just installed phpmyadmin and there are some useful tips in here that I will take a look at soon. Does anything obvious stand out?

The query cache is not enabled.	The query cache is known to greatly improve performance if configured correctly. Enable it by setting query_cache_size to a 2 digit MiB value and setting query_cache_type to 'ON'. Note: If you are using memcached, ignore this recommendation.
Too many sorts are causing temporary tables.	Consider increasing sort_buffer_size and/or read_rnd_buffer_size, depending on your system memory limits
There are lots of rows being sorted.	While there is nothing wrong with a high amount of row sorting, you might want to make sure that the queries which require a lot of sorting use indexed columns in the ORDER BY clause, as this will result in much faster sorting
There are too many joins without indexes.	This means that joins are doing full table scans. Adding indexes for the columns being used in the join conditions will greatly speed up table joins
The rate of reading the first index entry is high.	This usually indicates frequent full index scans. Full index scans are faster than table scans but require lots of CPU cycles in big tables, if those tables that have or had high volumes of UPDATEs and DELETEs, running 'OPTIMIZE TABLE' might reduce the amount of and/or speed up full index scans. Other than that full index scans can only be reduced by rewriting queries.
The rate of reading data from a fixed position is high.	This indicates that many queries need to sort results and/or do a full table scan, including join queries that do not use indexes. Add indexes where applicable.
The rate of reading the next table row is high.	This indicates that many queries are doing full table scans. Add indexes where applicable.
Many temporary tables are being written to disk instead of being kept in memory.	Increasing max_heap_table_size and tmp_table_size might help. However some temporary tables are always being written to disk, independent of the value of these variables. To eliminate these you will have to rewrite your queries to avoid those conditions (Within a temporary table: Presence of a BLOB or TEXT column or presence of a column bigger than 512 bytes) as mentioned in the MySQL Documentation
MyISAM key buffer (index cache) % used is low.	You may need to decrease the size of key_buffer_size, re-examine your tables to see if indexes have been removed, or examine queries and expectations about what indexes are being used.
Thread cache is disabled, resulting in more overhead from new connections to MySQL.	Enable the thread cache by setting thread_cache_size > 0.
Too many clients are aborted.	Clients are usually aborted when they did not close their connection to MySQL properly. This can be due to network issues or code not closing a database handler properly. Check your network and code.
Too many clients are aborted.	Clients are usually aborted when they did not close their connection to MySQL properly. This can be due to network issues or code not closing a database handler properly. Check your network and code.
The InnoDB log file size is inadequately large.	It is usually sufficient to set innodb_log_file_size to 25% of the size of innodb_buffer_pool_size. A very big innodb_log_file_size slows down the recovery time after a database crash considerably. See also this blog entry

[LenR]

I wonder if it's https://support.zabbix.com/browse/ZBX-16955. 4.2.x is out of support and I don't think this fix ever went into 4.2.

From the mysql side, you are looking for disk write delay. I made our innodb buffer pool larger. In phpmyadmin, all status variables, filter for innodb (If that's what mariadb uses). Compare data reads to data writes, look for any other alarming stats.