Mysql – MariaDB: Created_tmp_disk_tables high after start

mariadbMySQL

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

Best Answer

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.

SUGGESTION #1

You may want to refactor the query as follows

select
    t.id, t.subject,
    count(distinct p.id) as postCount
from thread t left join
(
    select id,thread_id from post where type=0 and 
    posted_on >= DATE_SUB(CURDATE(), INTERVAL 3 DAY)
    order by thread_id
) p on t.id = p.thread_id
group by p.thread_id
order by max(p.posted_on) desc
limit 0, 25;

SUGGESTION #2

Create a compound index for the subquery

ALTER TABLE post
    DROP INDEX type,
    ADD INDEX type_posted_on_ndx (type,posted_on)
;

GIVE IT A TRY !!!

ALTERNATE QUERY

select
    t.id, t.subject,
    count(distinct p.id) as postCount
from thread t left join
(
    select id,thread_id from post where type=0 and 
    posted_on >= DATE_SUB(CURDATE(), INTERVAL 3 DAY)
    order by thread_id
    limit 0,25
) p on t.id = p.thread_id
group by p.thread_id
order by max(p.posted_on) desc;

Mysql – How to lower “Created_tmp_disk_tables” – temporary tables count created on DISK

What do you mean by a "26GB Heap"?

tmp_table_size = 256M is dangerously high. If multiple connections decide to need tmp tables, you could run out of RAM. Swapping is worse for performance than lowering various settings.

Tmp tables are necessary in many situations. Don't fear them. But do check on them.

DISTINCT, GROUP BY, ORDER BY and UNION often require a tmp table. If the tmp table fits in min(tmp_table_size, max_heap_table_size), then the tmp table may be in RAM using Engine=MEMORY. If bigger than that, then the tmp table is Engine=MyISAM and is slower. There are other reasons for using MyISAM, most notably is selecting a TEXT field. More details.

Another common "error" is to blindly use VARCHAR(255) and utf8. When using MEMORY, that becomes a 765-byte CHAR, which hastens the conversion to MyISAM.

The numbers you gave...

Created_tmp_disk_tables is nearly as large as Created_tmp_tables -- This implies that either tmp_table_size is not big enough (which I doubt), or MEMORY cannot be used (which I suspect). Above 20% is a red flag in my analysis.
Created_tmp_tables / Uptime -- above 20/second is a red flag
Created_tmp_disk_tables / Uptime -- above 1/second is a red flag
Created_tmp_disk_tables / Questions -- above 4% is a red flag

Set long_query_time = 1 and turn on the SlowLog, preferably to FILE. Wait a day, then use pt-query-digest on the slowlog to find the "worst" queries. If you can't figure out how to improve them, ask us.

I'll disagree with Rolando on two items:

RAM disk is a bad idea -- it is better to give the extra RAM to other caches rather than trying to outsmart MySQL.
OPTIMIZE TABLE is rarely useful, and not very relevant for the question.