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.

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.

share|improve this question
add comment

1 Answer 1

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

share|improve this answer
    
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
add comment

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.