How to optimize Zabbix 1.6 Mysql database (too many temporary tables created on disk)

We have a zabbix server whose database is under quite heavy load. Recently, I noticed there were something like 200 temporary tables created per second (Created_tmp_tables), and 60% of them were written on disk!! (Created_tmp_disk_tables).

I first used a quickfix, namely mounting a ramdisk tmpfs somewhere in /tmp, and make MySql write disk temporary tables in it. Fine, the server was fast again.

Recently, I found a better solution: to really solve the problem. Basically, looking at temporary tables in /tmp, I found they were all very very small (1K). According to http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html the basic reason why so tiny temporary tables could finish on the disk is: some TEXT or BLOB columns. Right, I found them inside zabbix database, using a full log of all MySQL queries.

Basically :

alter table items modify params VARCHAR(255);

alter table triggers modify comments VARCHAR(255);

just solved the problem. No more temporary tables written on disk.

By the way, make a backup first: this will truncate comments of your alerts, whenever they are longer than 255 characters.