Take the 2-minute tour ×

I have been struggling to fix this issue but I couldn't so far. I have database with 41 tables using MyISAM engine. The overall size of the database is 96.7MB right now.

The problem is that "MySQL runtime information" shows very alarming stats. These are:

Handler_read_rnd = 1,495K

Handler_read_rnd_next = 51M

Created_tmp_disk_tables = 31K

Opened_tables = 217K

I have set following values:

key_buffer_size = 768 MB

query_cache_size = 40 MB

table_cache = 800

tmp_table_size = 64 MB

sort_buffer_size = 20 MB

read_rnd_buffer_size = 6 MB

My server is running Linux with 6GB RAM. I restarted the db server so MySQL server has been running for 2 days, 10 hours, 34 minutes and 14 seconds.

Any suggestion to improve these performance issues?

One more thing to ask: table_cache = 800 <- 800 is what, KB or MB ?

share|improve this question
add comment

1 Answer

Are you certain that your my.cnf is being used? Use show global variables and verify that your settings are actually being used.

As far as the 800, that's the number of table handles that will be cached.

Given your settings and results, I have to suspect that your settings aren't being honored.

BTW my personal advice after many years of building and tuning big MySQL systems (plural) is to use InnoDB. One of the advantages is no need to turn such arcane and simple knobs to get reasonable out of the box behavior and performance.

share|improve this answer
+1 for InnoDB suggestion. –  N.B. Oct 26 '11 at 14:28
Thanks Jeremiah. Yes, my.cnf file is being used. The parameters are defined in that file. So if I have 41 tables in total, value of 80 should be enough for table_cache, right ? –  D3 K Oct 26 '11 at 14:59
add comment

Your Answer


