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

  • 1) Are you using Linux, 2) How much RAM on your DB Server ??? Feb 6, 2014 at 0:15
  • I use Debian 7.3 and mysqltuner tell me that 2.9GB of 16GB are assigned to MariaDB
    – Lion
    Feb 6, 2014 at 0:25

1 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

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.

  • Thanks, i'll let my server running and then look at Sort_merge_passes to find out if increasing is necessary. A RAM Disc sounds good. But after ~30min i've 163 tables which are created in 6 tmp files from a total amount of 36.2k tmp tables. I know 30min are to short to find this out. But if this value isn't increasing rapidly, do you think a RAM disc for them is still a good idea when only a few querys are affected?
    – Lion
    Feb 6, 2014 at 0:48
  • A RAM disk for tmpdir has two drawbacks: (1) it takes RAM away from other caches, thereby slowing down everything, and (2) if you need more space than was allocated, things crash instead of simply slow down.
    – Rick James
    Dec 28, 2016 at 19:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.