------------------------------------------------
/etc/my.cnf
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-locking
skip-innodb
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
max_connections=900
interactive_timeout=100
wait_timeout=100
connect_timeout=10
thread_cache_size=128
#key_buffer=16M
key_buffer=200M
join_buffer=1M
max_allowed_packet=16M
table_cache=1536
sort_buffer_size=1M
read_buffer_size=1M
read_rnd_buffer_size=1M
max_connect_errors=10
# Try number of CPU's*2 for thread_concurrency
thread_concurrency=4
myisam_sort_buffer_size=64M
#log-bin
server-id=1
Query caching was added as of MySQL version 4, the following three directives will greatly enhance mysql server performance.
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
Query caching is a server wide variable, so set these generous. I have found the above levels are generally best if you server has at least 512 ram. If you run a server just for DBs with a lot of ram, you can up these quite a bit, like 2m limit and a 64+M cache size.
The key buffer is a variable that is shared amongst all MySQL clients on the server. A large setting is recomended, particularly helpful with tables that have unique keys. (Most do)
key_buffer=150M
The next set of buffers are at a per client level. It is important to play around with these and get them just right for your machine. With the setting below, every active mysql client will have close to 3 MB's in buffers. So 100 clients = almost 300 MB. Giving too much to these buffers will be worse than giving too little. Nothing kills a server quite like memory swapping will.
sort_buffer_size=1M
read_buffer_size=1M
read_rnd_buffer_size=768K
The following directive should be set to 2X the number of processors in your machine for best performance.
thread_concurrency=2
Heres a few example configurations for servers running MySQL and web for common memory sizes. These are not perfect, but good starting points.
Server with 512MB RAM:
thread_cache_size=50
key_buffer=40M
table_cache=384
sort_buffer_size=768K
read_buffer_size=512K
read_rnd_buffer_size=512K
thread_concurrency=2
For servers with 1 GB ram:
thread_cache_size=80
key_buffer=150M
table_cache=512
sort_buffer_size=1M
read_buffer_size=1M
read_rnd_buffer_size=768K
thread_concurrency=2
########################################################
For optimizing mysql, first we need to know the values of mysql variables and status.
The following are some commands used for this purpose:
# mysqladmin processlist extended-status
or
mysql> show status;
mysql> show variables;
To get more specific answer, the commands can be enhanced a little more like as follows:
mysql> show status like '%Open%_tables';
mysql> show variables like 'table_cache';
1. The most important variables in mysql are table_cache and key_buffer_size
a) Run the above two commands and check Open_tables and Opened_tables
If Opened_tables is big, then your table_cache variable is probably
too small.
So increase the table_cache variable. Open /etc/my.cnf and change/add table_cache=newvalue
b) Run the following commands to check key_buffer_size, key_read_requests and key_reads
mysql> show variables like '%key_buffer_size%';
mysql> show status like '%key_read%';
If key_reads / key_read_requests is < 0.01, key_buffer_size is enough. Otherwise key_buffer_size should be increased.
Also run the following command to check key_write_requests and key_writes
mysql> show status like '%key_write%';
If key_writes / key_write_requests is not less than 1 (near 0.5 seems to be fine), increase key_buffer_size.
Check the total size of all .MYI files. If it is larger than key_buffer_size change key_buffer_size to total size of MYI files.
2. Wait_timeout, max_connection, thread_cache
If you want to allow more connections, reduce wait_timeout to 15 seconds and increase max_connection as you want.
Check the number of idle connections. If it is too high reduce the wait_timeout and use Thread_cache
How many threads we should keep in a cache for reuse. When a client disconnects, the client's threads are put in the cache if there aren't more than thread_cache_size threads from before. All new threads are first taken from the cache, and only when the cache is empty is a new thread created. This variable can be increased to improve performance if you have a lot of new connections. (Normally this doesn't give a notable performance improvement if you have a good thread implementation.) By examing the difference between the Connections and Threads_created you can see how efficient the current thread cache is for you.
If Threads_created is big, you may want to increase the
thread_cache_size variable. The cache hit rate can be calculated with
Threads_created/Connections.
Default thread_cache_size may be 0 if so increase it to 8.
You may try this formula : table_cache = opened table / max_used_connection
No comments:
Post a Comment