|
|
There are two primary start-up parameters that will affect your system the most: key_buffer_size and table_cache. If you get only two server parameters correctly tuned, make sure they're these two!
The value of key_buffer_size is the size of the buffer used with indexes. The larger the buffer, the faster the SQL command will finish and a result will be returned. Try to find the fine line between finely tuned and over-optimized; you may have a key_buffer_size of 256MB on a system with 512MB of RAM, but any more than 256MB could cause degraded server performance.
A simple way to check the actual performance of the buffer is to examine four additional variables: key_read_requests, key_reads, key_write_requests, and key_writes.
Use the sql: show status like "key_%";
| Variable_name | Value |
|---|---|
| Key_blocks_used | 250317 |
| Key_read_requests | 14446786 |
| Key_reads | 1604492 |
| Key_write_requests | 3346082 |
| Key_writes | 34704 |
If you divide the value of key_read by the value of key_reads_requests, the result should be less than 0.01. Also, if you divide the value of key_write by the value of key_writes_requests, the result should be less than 1. Using the values above, we have results of 0.1110622 and 0.0103715 respectively, well within the acceptable parameters.
To try to get these numbers even smaller, more tuning could occur by increasing the value of key_buffer_size, but these numbers would be fine to leave as they are.
The other important server parameter is table_cache, which is the number of open tables for all threads. The default is 64, but you may need to adjust this number. Look for a variable called open_tables in the output. If this number is large, the value of table_cache should be increased.
Use: show variables like "table_%" ;
and show status like "tables%" ;
Each time MySQL accesses a table, it places it in the cache. If your system accesses many tables, it is faster to have these in the cache. A good way to see whether your system needs to increase this is to examine the value of open_tables at peak times (one of the extended status values, above). If you find it stays at the same value as your table_cache value, and then the number of opened_tables starts increasing, you should increase the table_cache if you have enough memory.
If you are using MySQL 4.x, a few new variables have been introduced that will help your performance tremendously:
| query_cache_limit = 1M |
| query_cache_size = 32M |
| query_cache_type = 1 |
You may want to drop query_cache_size to 16M if you have only 1GB of ram, and maybe even lower if you have less ram.
mkdir /path/to/where/you/want/the/data chown mysql:mysql /path/to/where/you/want/the/data
edit /etc/my.cnf add in the [mysqld] section:
datadir = /path/to/where/you/want/the/dataKill all mysql processes and restart it
Check with:
mysql> show variables like "data%" ;
Veterinary Solutions Technical support: support@vetsolutions.co.uk
Telephone: 0131 556 0555 Fax: 0131 556 3525