today i looked in the status of my MySQL server which is based on MariaDB and i noticed that Created_tmp_disk_tables is very high (over 168k). I think this is the reason for my high I/O usage. I try to optimize this by increasing tmp_table_size and max_heap_table_size and some other config parameters.
Now i restarted the server and took a look at the status variables:
Created_tmp_disk_tables 12 Created_tmp_tables 82
The server is running for 20 seconds, but why it has created 12 tmp tables on the hdd? I set max_heap_table_size and tmp_table_size to 256MB so it's not possible that this is full after 20 seconds.
After 5 Minutes the Status looks like this:
Created_tmp_disk_tables 40 Created tmp tables 3300
The difference is not as big as it was before increasing the RAM. But i can't understand why tmp tables are created on the hdd when enough RAM is avaliable. I think this is not good because it slow down the querys and create high I/O usage. My server also have enough RAM, so that i want to avoid this and make MariaDB to create all tmp tables in the RAM.
I use MariaDB on Debian 7.3