Server:mysql
From Linux How-To Repository
Contents |
Formulas for my.cnf
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = total_memory
key_buffer_size should be 1/4 - 1/3 of the total_memory
Fine Tuning MySQL
Source: Anthony Ferrara at http://www.joomlaperformance.com/articles/server_related/tweaking_mysql_server_23_16.html
Before anything backup /etc/mysql/my.cnf
Now figure out how much RAM you have to use. Type this:
free -m
Look for the “Mem:” line and the “free” column). That shows you how much RAM you have free. Note this down.
Next, you’ll want to run the following command:
top -d 1 -n 2 -u mysql
Replace mysql with the user MySQL is running under. Now, look at the column “VIRT” to see how much memory MySQL is currently using. Add that to the free memory from before to get what you have to work with. Check on these values from time to time to ensure nothing is going into swap.
Have a look at your my.cnf file:
sudo gedit /etc/mysql/my.cnt
It should have something like this:
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [client] socket=/var/lib/mysql/mysql.sock
Delete any variables with numbers next to them. But don’t delete anything about connections, timeouts, or open_files_limit. Add the following directives to the my.cnf under the [mysqld] heading (substituting the % of memory from before where you see a %). Now, don't be a complete dickhead like I was and try to use percentage figures.
Query_cache_limit=2M Query_cache_size=25% Query_cache_type=1 Thread_cache_size=128 Key_buffer = 25% Join_buffer = 4M Table_cache=25% Sort_buffer=4M Read_rnd_buffer_size=1% Tmp_table_size=10% (or 32M, whichever is greater)
Only 86% of free memory is used here plus about 10 megs. Now, restart MySQL to get a baseline and let it run for a few days. Check the running memory usage and free memory again after it has been running on the new setup.
Now open up your phpMyAdmin installation in your browser. On the front page, click “Show MySQL runtime information”. Go down to the blocks of information with 3 columns (Variable, Value, and Description). All the bad variable values will be in red. These are what you have to tweak.
Tweaking with PhpMyAdmin
Ignore the following variables that deal with query efficiency: slow_queries, all the handler_ variables, and the select_ variables.
Scroll down to the section about “Key Cache”.
Key_reads must be much smaller, by about 1000 times, than Key_read_requests.
If it isn’t, that means that you need to increase the key buffer. Start by increasing the key_buffer by about 25% and see how that works. You can tell if your key buffer is too big by comparing the key_blocks_unused with the key_blocks_used. Don’t make it too big as you need to leave free space and you risk swapping.
Now compare Open_tables and Opened_tables variable. If they are close, you’re fine.
If opened_tables is bigger than open_tables (i.e. 10 times bigger), adjust the table_cache variable.
Remember we want to go up in increments, so only increase the size by 25%.
After this, let’s look at Query Cache.
Check that Qcache_free_memory is about at least a few megs.
You also want the Qcache_lowmem_prunes to be 0 if possible.
If it’s not 0, increase the query_cache_size.
The last thing you want to check is created_tmp_files.
If this is not small, increase tmp_table_size.
Now, restart the server. Let it run for a few days while you check everything. When it is at the point that you don't need to adjust any variables, then you're done.
Command Line MySQL Checking
Source: http://dev.mysql.com/doc/refman/5.0/en/mysqlcheck.html
Use the following to check the status of databases:
mysqlcheck -u root -p -A -e
The source link has many other parameters that can be used to check/repair databases.
Use the following to get an extended status report:
mysqladmin -u root -p extended-status
Other Resources
IBM: http://www.ibm.com/developerworks/linux/library/l-tune-lamp-3.html
