MySQL Forums
Forum List  »  Performance

MyISAM, key cache, and open tables
Posted by: Brian O'Neill
Date: April 27, 2005 02:27PM

Appoligies for posting this a 2nd time, I first posted it in the MyISAM forum but noticed there is very little traffic there and it seems just as approriate for this forum.

I have recently switched from using InnoDB to MyISAM (issues with InnoDB file size and select speed). I'm running on a server with a large amount of memory (8G). When using InnoDB I had it's key cache set very high and it filled it and I had a very high hitrate on my key buffer. Now, with MyISAM I'm down to around 35% hitrate on the same data.

My thought on the problem is it has to do with the number of open tables. I currently have about 14,000 tables and my table_cache value is set to 500. If I increase this value my hitrate increases, but, the time it takes to do my inserts goes up 50-75% which is worse then my slow selects. Right now I've found that about 500 tables in the table_cache is my sweet spot. I normally have about 70-80 threads running at a time.

From what I can gather mysql has to have the index file for a MyISAM table open for it to reference it in the key buffer. This seems very odd to me. Can anyone confirm this? I know it's not an issue with my key_buffer size as it's set to 4G and it's currently using only 86M.

I should also add I'm running 4.1.10a and all 14,000 tables are being opening atleast once in a 60 second period. In the last 24 hours that my server has been running it has opened 37M tables.

Here's a quick snapshot of mtop:
load average: 9.54, 9.49, 9.50 mysqld 4.1.10a-standard-log up 1 day(s), 5:22 hrs
65 threads: 37 running, 3 cached. Queries/slow: 56.9M/49 Cache Hit: 33.58%
Opened tables: 37.0M RRN: 711.2M TLW: 18.7K SFJ: 0 SMP: 0 QPS: 1K MEM: 6GB

Any thoughts?

Thanks,
Brian

Options: ReplyQuote


Subject
Views
Written By
Posted
MyISAM, key cache, and open tables
2981
April 27, 2005 02:27PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.