MySQL 5 optimizing

To kick it off, it's all true. Starting with your best possible queries with the best possible database scheme (structure), etc. Tuning a database is not easy, you have a lot of variables to take into account, and they vary over time! Yes you read me right, you don't stop tuning, it never ends. True DBA's can tell you all about it, I can't (yet) but I'll share what I've found.

One of the first things you should know is that tuning a mysqld is going to take time. You can't just reload your server look at the performance (emulated or live) change your /etc/my.cnf and repeat. The best thing you can do is take a starting configuration, let the server run a few hours and then look at the performance. There are some commands that can speed things up a bit (for MyISAM), but in my experience thats not the perfect situation.

To get you started you can: LOAD INDEX INTO CACHE table1, table2

This will load your MyISAM index tables into cache so the result of your caching parameters (in your my.cnf) should show sooner.
To read more about load index, check this link: http://dev.mysql.com/doc/refman/5.0/en/load-index.html

So let's get to the key factors of MySQL (5) tuning.
The most important variables you can tune are located in your my.cnf (default located in: /etc/my.cnf )

• key_buffer (or key_buffer_size, those are aliases)
• innodb_buffer_pool_size
• table_cache
• query_cache_*

Key_buffer_size

This setting applies only to MyISAM storage engines, and is used to cache your indexes. (= good)
The typical setting is to use about 20% to 30% of total memory on hybrid servers (server running a web server and MySQL)

Innodb_buffer_pool_size

This setting is the 'key_buffer_size' for InnoDB storage engines. And used to cache your indexes. Typical setting are about 40% to 60% of your system memory on hybrid machines.

Table_cache

This setting isn't a performance booster, but it prevents your server to be negatively influenced by it.
Setting this variable too low can seriously hurt your optimizing process. A typical way of determining the setting is to look at your queries, if you JOIN on 3 or 4 tables. you should set it on: 4 times your max_connections so the formula would be:
table_cache = max_connections * 4

max_connections is talked about later in this article.

Query_cache

Query caching is a god's gift. In a few cases it's not efficient, but in most cases it's going to boost your performance a lot.
At moment of writing query_cache_ has 3 settings, namely: query_cache_type, query_cache_size and query_cache_limit.

Query_cache_type

query_cache_type has 3 settings, 0, 1 and 2. 0 means it's off. 1 means it's always on. 2 means it's 'on demand'.
I recommend setting this to 1 unless you know what you are doing

Query_cache_size

The amount of memory you want to allocate for caching queries.

Query_cache_limit

The amount of memory a query result can be, if your query result exceeds this amount it's never cached!

Applying cache parameters on a per query base

In some cases you might not want to cache queries per default (clustering) or you just don't have access to your my.cnf (shared hosting) in these cases you can set a per query cache 'request' where you simply tell your mysqld to please cache all your read queries. to do this put the keyword 'SQL_CACHE' in your query. e.g.: "SELECT SQL_CACHE * FROM `news` LIMIT 0, 20;"

What next?

When you set these settings it's time to run a benchmark. Look at your memory and cpu usage and monitor your performance.
After you let it run a few hours it's time to look at some other critical components.
To make it easy for me and you Matthew Montgomery wrote a script that helped me a lot tuning my MySQL servers, you can get it here: http://www.day32.com/MySQL/ download the primer, run it and look at the recommendations.