AdMiN-nOObS

Optimize my.cnf

http://www.linuxweblog.com/tune-my.cnf

Had to do some fine tuning of MySQL 4.1.9 and here is what my.cnf file looks like for a 2GHz machine with 1GB of memory.

[mysqld]
socket=/path/to/mysql.sock
datadir=/var/lib/mysql
skip-locking
skip-innodb
# MySQL 4.x has query caching available.
# Enable it for vast improvement and it may be all you need to tweak.
query_cache_type=1
query_cache_limit=1M
query_cache_size=32M
# max_connections=500
# Reduced to 200 as memory will not be enough for 500 connections.
# memory=key_buffer+(sort_buffer_size+read_buffer_size)*max_connections
# which is now: 64 + (1 + 1) * 200 = 464 MB
# max_connections = approx. MaxClients setting in httpd.conf file
# Default set to 100.
#max_connections=200
#interactive_timeout=180
interactive_timeout=100
#wait_timeout=180
#wait_timeout=100
# Reduced wait_timeout to prevent idle clients holding connections.
#wait_timeout=30
wait_timeout=15
connect_timeout=10
# max_connect_errors is set to 10 by default
#max_connect_errors=10
#table_cache=256
#table_cache=1024
# Checked opened tables and adjusted accordingly after running for a while.
table_cache=512
#tmp_table_size=32M by default
#thread_cache=128
# Reduced it to 32 to prevent memory hogging. Also, see notes below.
thread_cache=32
# key_buffer=258M
# Reduced it by checking current size of *.MYI files, see notes below.
key_buffer=128M
# Commented out the buffer sizes and keeping the default.
# sort_buffer_size=2M by default.
#sort_buffer_size=1M
# read_buffer_size=128K by default.
#read_buffer_size=1M
# 1Mb of read_rnd_buffer_size for 1GB RAM -- see notes below.
# read_rnd_buffer_size=256K by default.
#read_rnd_buffer_size=1M
# myisam_sort_buffer_size used for ALTER, OPTIMIZE, REPAIR TABLE commands.
# myisam_sort_buffer_size=8M by default.
#myisam_sort_buffer_size=64M
# thread_concurrency = 2 * (no. of CPU)
thread_concurrency=2
# log slow queries is a must. Many queries that take more than 2 seconds. 
# If so, then your tables need enhancement.
log_slow_queries=/var/log/mysqld.slow.log
long_query_time=2

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open_files_limit=8192

[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[mysqlhotcopy]
interactive-timeout 

[client]
socket=/path/to/mysql.sock

Below are notes on some of the important variables, I took down while tuning the config file.

  1. query_cache_size:
    • MySQL 4 provides one feature that can prove very handy – a query cache. In a situation where the database has to repeatedly run the same queries on the same data set, returning the same results each time, MySQL can cache the result set, avoiding the overhead of running through the data over and over and is extremely helpful on busy servers.
  2. key_buffer_size:
    • 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. The rule-of-thumb is to set the key_buffer_size to at least a quarter, but no more than half, of the total amount of memory on the server. Ideally, it will be large enough to contain all the indexes (the total size of all .MYI files on the server).
    • 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.
    • 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.
  3. table_cache:
    • The default is 64. Each time MySQL accesses a table, it places it in the cache. If the system accesses many tables, it is faster to have these in the cache. MySQL, being multi-threaded, may be running many queries on the table at one time, and each of these will open a table. Examine the value of open_tables at peak times. If you find it stays at the same value as your table_cache value, and then the number of opened_tables starts rapidly increasing, you should increase the table_cache if you have enough memory.
  4. sort_buffer:
    • The sort_buffer is very useful for speeding up myisamchk operations (which is why it is set much higher for that purpose in the default configuration files), but it can also be useful everyday when performing large numbers of sorts.
  5. read_rnd_buffer_size:
    • The read_rnd_buffer_size is used after a sort, when reading rows in sorted order. If you use many queries with ORDER BY, upping this can improve performance. Remember that, unlike key_buffer_size and table_cache, this buffer is allocated for each thread. This variable was renamed from record_rnd_buffer in MySQL 4.0.3. It defaults to the same size as the read_buffer_size. A rule-of-thumb is to allocate 1KB for each 1MB of memory on the server, for example 1MB on a machine with 1GB memory.
  6. thread_cache:
    • If you have a busy server that’s getting a lot of quick connections, set your thread cache high enough that the Threads_created value in SHOW STATUS stops increasing. This should take some of the load off of the CPU.
  7. tmp_table_size:
    • « Created_tmp_disk_tables » are the number of implicit temporary tables on disk created while executing statements and « created_tmp_tables » are memory-based. Obviously it is bad if you have to go to disk instead of memory all the time.

http://www.askwebhosting.com/article/174/Optimising_MySql.html

Optimising mysql

There is never “best parameters”, the best parameters is those fits your needs, box hardware, mysql usage…
So, I’ll not give the best parameters but rather how to define these ones.
Make some tests, and you’ll quickly find your own parameters.

There a lot of available parameters but only few one are very important to tweak your mysql box.

The most important variables are:

  • – max_connections
  • – wait_timeout
  • – thread_cache_size
  • – table_cache
  • – key_buffer_size
  • – query_cache_size
  • – tmp_table_size

    How to display mySql variables:

    1) login on mysql
    2) from mysql type: show variables;

    From command line type: mysqladmin variables

    How to display Mysql Status and processes

    from Mysql : show status;
    from command line mysqladmin –i10 processlist extended-status

    OPTIMISING MYSQL

    To obtain the stat of your mysql server since it has been loaded from command line:
    type: mysqladmin processlist extended-status.

    1 – Table_cache and Key_buffer_size

    * If Opened_tables is big, then your table_cache variable is probably
    too small.
    table_cache 64
    Open_tables 64
    Opened_tables 544468

    This is the first serious problem. « The table_cache is the number of open
    tables for all threads. MySQL, being multi-threaded, may be running many
    queries on the table at one time, and each of these will open a table. »
    Therefore, even though we only have a few tables, we will need many more
    open_tables.

    The Opened_tables value is high and shows the number of
    cache misses. Getting the table_cache size correct is one of the two best
    things you can do to improve performance.

    * If Key_reads is big, then your key_buffer_size variable is probably
    too small. The cache hit rate can be calculated with
    Key_reads/Key_read_requests.
    key_buffer_size 16M
    Key_read_requests 2973620399
    Key_reads 8490571
    (cache hit rate = 0.0028)

    “The key_buffer_size affects the size of the index buffers and the speed
    of index handling, particularly reading. » The MySQL manual (and other sources) say that
    « Key_reads/Key_read_request ratio should normally be < 0.01. » This is the
    other most important thing to get correct. Here the value seems to be correct (< 0.01)

    Also check key_write_requests and key_writes.
    The key_writes/key_writes_request should normally be < 1 (near 0.5 seems to be fine)

    2 – Wait_timeout, max_connexion, thread_cache

    A little explanation :
    Generaly you have a lot of mysql process that are sleeping because wait_timeout are not set low. So I make sure that the wait_timeout is set to a very low value: 15 seconds (for me) . That means MySQL would close any connection that was idle for more than 15 seconds.

    The problem is you also have to increment your max_connexion (mine is set to 300) to be sure there is not a lot of idle clients holding connections and blocking out new clients from connecting and getting real work done.
    The pbm is that the box has to create new threads (MySQL is a multi-threaded server) at a very high rate. That may sucks up a measurable amount of CPU time.

    So the solution is to use the Thread_cache (from mysql doc) :
    “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.
    thread_cache_size 0
    Threads_created 150022
    Connections 150023

    This is the second problem that should be fixed. A cache size of zero is the default for my-medium.cnf but the recommended size in my-large.cnf is 8.

    you may try this formula : table_cache = opened table / max_used_connection

    3 – tmp_table_size and Handler_read_rnd / Handler_read_rnd_next

    * If Created_tmp_disk_tables is big, you may want to increase the
    tmp_table_size variable to get the temporary tables memory-based instead
    of disk based.

    tmp_table_size 32M
    Created_tmp_disk_tables 3227
    Created_tmp_tables 159832
    Created_tmp_files 4444

    Created_tmp_disk_tables are the « number of implicit temporary tables on
    disk created while executing statements » and Created_tmp_tables are
    memory-based. Obviously it is bad if you have to go to disk instead of
    memory. About 2% of temp tables go to disk, which doesn’t seem too bad
    but increasing the tmp_table_size probably couldn’t hurt either.

    * If Handler_read_rnd is big, then you probably have a lot of queries
    that require MySQL to scan whole tables or you have joins that don’t use
    keys properly.
    Handler_read_rnd 27712353
    Handler_read_rnd_next 283536234

    These values are high, that we could probably stand to improve
    the indexes and queries.

    4 – Memory Used by Mysql

    Used MySQL memory = key_buffer + max_connections * (join_buffer + record_buffer + sort_buffer + thread_stack + tmp_table_size)

    Notice the max_connexion and the multiplier.
    connexion increase = memory usage increase too.

    Notice key_buffer
    for a given memory :more you add mem to key buffer, less connexion is less is key buffer, more connexion is

    If you change one of these settings for a high value, you system may swap.
    If you system swap, try lot decrease these values

    5 – About table_cache

    Increasing the size of the table cache may really help you.
    But you must be careful not to make the value too large. All operating systems have a limit on the number « open file pointer » (sorry in french it is called pointer, maybe descriptors is the good translation) a single process may have.
    If MySQL tries to open a lot of files, the OS may refuse it and MySQL will generate error message in the error log.

    Maybe there is others tweaks to perform, but I know well only these ones. I did setup using these ones on differents mysql box, and generally it did help us to increase performance without have to change hardware (our boxes have 2GB ram)

 

 

 

 

 

 

 

Publié

dans

par

admin

Étiquettes :