Zabbix upgrade issue with slow mysql select queries from 3.0.2 to 3.4

[jonxor]

My instance of zabbix 3.0.2 was set up and monitoring about 1300 hosts (about 400 NVPS), mirroring my old 2.2 setup. I had a DBA help me partition the history and trend tables based on clock value, so I could drop partitions based on time instead of needing to use the housekeeper.

I use Percona Mysql as the database backend on Ubuntu 16.04. Mysql, Zabbix collector and PHP frontend are all on the same system, bare metal. 2x6-core Intel Xeon CPU, 256GB RAM, Database is on 10x1TB SSD's in RAID 10 on MDADM.
I ran the upgrade from 3.0.2 to 3.4.
After the upgrade completed, I noticed select queries on history_uint were consistently extremely slow:

mysql> select clock,ns,value from history_uint where itemid=175831 and clock>1505845680 and clock<=1505845980 order by clock desc limit 1;
Empty set (1 min 9.01 sec)

mysql> select clock,ns,value from history_uint where itemid=175831 and clock>1505845680 and clock<=1505845980;
Empty set (0.00 sec)

Running one of these queries would cause one CPU thread to spike to 100% usage. Launching the zabbix-server service (which would run dozens of these queries) caused all CPU threads to spike to 100% on the database server (CPU usage was noted by the mysqld processes). While the system was operating this way, no new data appeared on the frontend in graphs or "latest data".

We were able to resolve this by updating Mysql versions from 5.7.18-15 to 5.7.19-17. I do not know what the root cause was, as I had already corrected the issue by upgrading versions.

As far as I could tell, the partitioning had no effect on the upgrade, and no bad interactions during the upgrade process. Hopefully, future generations can benefit from this.