2

I have a database about 750GB in size. It's all innodb.

Larger analytical queries often need to group by several columns or use distinct so it's common that MySQL will have to create tmp tables. The tmp tables fit into memory.

My cache hit ratio (Key_reads / Key_read_requests) is 0.

When MySQL creates these tmp tables, I'm guessing it doesn't it create pseudo indexes to be used by key_buffer_size.

2

No, not at all.

Every time, I see a temp table materialize on disk, I see something like this:

-rw-rw---- 1 mysql mysql    0 May  8 17:44 #sql_22d5_2.MYD
-rw-rw---- 1 mysql mysql 1024 May  8 17:44 #sql_22d5_2.MYI

.MYI is always the default size.

On disk, they must be using the MEMORY storage engine. Again, there are no indexes created in conjunction with the temp table.

If you are concerned about many temp tables, use a RAM disk (See my post Is it bad to create many mysql temporary tables simultaneously?)

Your goal should be avoid making temp tables, regardless of mapping tmp tables to disk or RAM.

See my other posts on this

Going back to your question: Since all your data is InnoDB, you can make key_buffer_size 8M

  • Yea, I'm not really worried about disk tmp tables. Generally, the queries fit into memory and I'm already using SSD with tempfs. I'm specifically concerned with tuning myisam variables for tmp tables. – Justin May 8 '13 at 21:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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