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 only takes a minute to sign up.
Sign up to join this community
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.
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
Jan 04, 2013 : Is there a MySQL engine or trick to avoid writing so many temp tables to disk?May 10, 2012 : Why is MySQL is creating so many temporary tables on disk?Going back to your question: Since all your data is InnoDB, you can make key_buffer_size 8M
