MySQL Forums
Forum List  »  Performance

Re: Need Perfomance / Tuning Help
Posted by: Jay Pipes
Date: November 22, 2005 08:21PM

Well, the first thing I would suggest is enabling the query cache by giving it some size. Currently, you have it enabled, but it's size (query_cache_size server variable) is 0, therefore no queries are actually cached.

Secondly, you're using a my.cnf variant for small memory systems. Given the fact that you are using a 4-CPU SMP system, I would expect you to have quite a bit of RAM (>1GB). If so, you should adjust your key_buffer_size variable to around 50% of your total RAM to start out and see if that makes a difference in performance (it definitely should). It's tough to say how effective increasing this variable will be, however, since your status variables show an uptime of only 6946 seconds, or about 2 hours. So, even though the key_blocks_unused status variable is not 0, we don't know whether the key_buffer will be exhausted sooner rather than later...

You don't have max_connections variable set, so it should be at the default of 100. You might want to monitor your SHOW FULL PROCESSLIST output to see how many concurrent connections you are achieving and adjust this setting to a larger number, if required (you'll see the infamous too many connections error if you need to adjust this setting).

Somewhat related to this last point is your table_cache variable setting. You currently have the setting at 5M, though I'm not quite sure why (in fact, I'm not sure the M is a legitimate shortcut for this variable, though it could be). The table_cache variable controls the number of *file descriptors* that MySQL will cache for *all* running connections on the box. A file descriptor is opened for each distinct table used in a joined resultset retrieved during a query (because the .FRM file is opened to determine, among other things, the columns contained in the table). Therefore, you should set table_cache to max_connections * N, where N is the maximum number of joins used in your common queries. Because you've set this to 5M, I'm wondering whether you just have misunderstood the purpose of the variable, or you have done the above calculation and determined that is an appropriate setting. If you have, OK, but beware that on *nix systems, you may have to adjust the limit of open file descriptors the mysql process can actually keep open in order for this setting to be effective. You should research how to adjust the file descriptor limit for Linux if this is necessary (do a search on the topic in these forums; I know I've answered the question before, as have others...)

Your temp table creation is a bit high (~24K temp tables created in 2 hours) but none of those were created on disk because of a lack of sort_buffer_size or tmp_table_size, so I don't think changing any of those settings would help considerably. Instead, I'd imagine that your queries are simply doing a lot of sorting (perhaps forum or community-based software) and that's where the temp tables come from. You will probably remedy much of that through the use of the query cache.

I hope this answers at least some of your questions.


Jay Pipes
Community Relations Manager, North America, MySQL Inc.

Got Cluster?

Options: ReplyQuote

Written By
November 21, 2005 05:38AM
November 21, 2005 06:31AM
November 21, 2005 07:26AM
Re: Need Perfomance / Tuning Help
November 22, 2005 08:21PM
November 23, 2005 07:19AM
November 23, 2005 08:54AM

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.