Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
1) Are you using Linux, 2) How much RAM on your DB Server ??? –  RolandoMySQLDBA Feb 6 at 0:15
    
I use Debian 7.3 and mysqltuner tell me that 2.9GB of 16GB are assigned to MariaDB –  user3276634 Feb 6 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.

share|improve this answer
    
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? –  user3276634 Feb 6 at 0:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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