MySQL 5.5 to 5.6 zabbix DB backend upgrade surprise

More than a month ago I made a Zabbix MySQL DB backend upgrade from 5.5 to 5.6. I made this upgrade to open up possibilities of tracing SQL traffic over USDT (User Statically Defined Tracing) probes and correlate those observations with other OS layer data. I’ve not been expecting some significant improvements as so far all what I’ve read about MySQL 5.6 improvements was mainly about slave synchronization speedup.

In below last 2 months graphs is possible to spot significant improvements on used storage side.

Below is IO graph of the SSD device which is used as L2ARC and ZIL device where is possible to spot significant reduction of write IOs.

zabbix_db_zil_l2arc_ios

Even bigger reduction of write IOs is possible to observe on single spindle disk IO graph.

zabbix_db_spindle_io

Looks like with MySQL 5.5 we’ve been quite close to maximum IO bandwidth on single spindle. Upgrade to MySQL 5.6 caused huge reductions of write IOs which effectively is casing that it will be not necessary to upgrade hardware platform. In this case zabbix DB backend zpool uses 6 spindles working in RAID1+0 with one SSD used as zpool L2ARC+ZIL devices.

Before this upgrade I remember that on observing activity on DB backend using oneliner like “while true; do mysqladmin processlist | grep -v Sleep; sleep 1; do” usually was possible to see between 5 to 15 selects. After upgrade this list now no longer than 2-5 still running queries (mostly selects) so avg latency of all queries has been reduced. However I was not able easily observe SQL queries latency distribution as on 5.5 from Solaris 11.3 GA MySQL 5.5 was without USDT DTrace probes.

#mysql, #solaris, #zabbix

Advertisements