MySQL Forums
Forum List  »  Performance

Re: MySQL Performance Tuning on my server
Posted by: Rick James
Date: November 14, 2008 09:10PM

* Setting variables -- my.cnf is loaded at startup time. So changing the file does not take effect immediately. Or you could be changing the variables in the live server -- then it takes effect immediately, but is forgotten the next time you start mysql (or restart your machine). I don't know which of these apply to your tools.

* In MyISAM, key_buffer_size is the most important tunable. Set it to about 20% of available RAM (but not more than 4GB).

* If you change to InnoDB, it might (or might not) run faster. There, innodb_buffer_pool_size should be, say, 70% of available RAM.

* Extra cores don't help much.

* ORDER BY + LIMIT is a bad way to do pagination. Usually (I have seen lots of implementations of pagination), the query has to fetch all the rows, sort them, then deliver according to the limit. Tweaking the schema, the client code, and the indexes to make it so it needs to read only the desired number if rows is often difficult, but yields huge performance benefits.

* Show us your queries and table definitions.

Options: ReplyQuote


Subject
Views
Written By
Posted
4642
November 14, 2008 10:01AM
Re: MySQL Performance Tuning on my server
2218
November 14, 2008 09:10PM
2209
November 16, 2008 01:04PM
2357
November 16, 2008 11:00PM
1838
November 17, 2008 04:10AM


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.