MariaDB: Created_tmp_disk_tables high after start

Question

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

Answer 1

You may have other limiting factors involved. Here are some suggestions:

Additional Buffers

The per-connections buffers you may need to increase includes

• join_buffer_size
• sort_buffer_size

Queries will demand temp table based on join and sort operations. These may need to be increased.

Improve Queries

Queries that need to do joins and sorts can be reduced if queries had proper indexing. I would suggest that you profile all your queries using pt-query-digest and create whatever indexes that table need to reduce the incidence of temp table creation.

Create a RAM Disk

You should map a chunk of your RAM to be a dedicated RAM disk. Then configure tmpdir to point to it. See my post Is it bad to create many mysql temporary tables simultaneously? on how to set one up.