Re: MySQL Performance Tuning on my server

[Rick James]

* 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.