MySQL Optimization

Find max connection using the formula:-  memory = keybuffer + (readbuffer + sort buffer ) max connections

Open /etc/my.cnf file in your favorite editor (eg: vi, pico etc)

max_connections=400
max_user_connections=30
key_buffer=256M (128MB for every 1GB of RAM)
myisam_sort_buffer_size=64M
join_buffer_size=1M
read_buffer_size=1M (1MB for every 1GB of RAM)
sort_buffer_size=1M (1MB for every 1GB of RAM)
table_cache=1500
thread_concurrency=2 (Number of CPUs x 2)
thread_cache_size=128M
wait_timeout=10
connect_timeout=5
max_allowed_packet=16M
max_connect_errors=1082.165.248.54190.212.44.109
query_cache_limit=1M
query_cache_size=32M (32MB for every 1GB of RAM)
query_cache_type=1

and restart mysql service by /etc/init.d/mysqld restart

Below are notes on some of the important variables in the my.cnf file ,  that is to be changed inorder to tweak mysql performance

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.

8. query_cache_size
********************

Query caching has been introduced from MySQL 4 onwards. If your application executes a particular query again and again, MySQL can cache the result set, thereby avoiding the overhead of running through the data over and over and thereby increase the execution time.

You can enable query caching by setting the server variable query_cache_type=1 and setting the cache size in the variable query_cache_size. If either of the above is set to 0, query caching will not be enabled.

There are three status for query caching;

1. Disabled – query_cache_type = 0

2. Enabled – query_cache_type = 1

3. On Demand – query_cache_type = 2

10 comments on “MySQL Optimization
  1. Kimmono says:

    Love this stuff! Would everything apply to MySQL 5?

    Like

  2. Anees says:

    Yea.. You can apply this to mysql 5.

    Like

  3. LiewCF says:

    thank you. this is very informative article. Helped me a lot!

    Like

  4. Buddy says:

    thank u very much..these document helped me alot to complete my assignments…. :)

    Like

  5. Protimus says:

    This option does not works with mysql 5.5:

    max_connect_errors=1082.165.248.54190.212.44.109

    Like

  6. Nik says:

    Hi,

    Not sure who is writting this blog but the suggestions are very generic and reflect some amateurism.

    Every server has to be optimised according to the specs at the time of deployment, and every time you change the hardware and sometimes depending on the software you run.

    Like

  7. Linewalker says:

    Nik, he did said “Below are notes on some of the important variables in the my.cnf file , that is to be changed inorder to tweak mysql performance”

    Pls read before you trolling. His config is an example of what he is using based on his machine specs.

    Like

  8. joao240 says:

    Hi, great post!!! I have a dedicated server with 8GB RAM, what values for that config do you recommend? It will run a WordPress site with Woocommerce, then, it’s an e-commerce. Thanks.

    Like

  9. winson says:

    Hello,That’s Great post. Thank you.

    I run my WP under a 4GB RAM Server (4 CPU).

    And the Status: 70,000~80,000 IP, 20000000~30000000 pageviews peday

    But my WP will be very slow to loading without Cached Plugin when there’re over 450 Visitors online.

    But I couldn’t use the Cache Plugin. Because My Firewall couldn’t block the IP after Cached.

    And I ensure that the problem is the MYSQL. Because I did some tests when the Visitors over 450 Online. Because I found that it’s very difficult to access PHPMYADMIN.

    Would you mind to tell me how to configure this situations for me?

    Here is my setup after I read your post

    max_connections=10000
    max_user_connections=900
    key_buffer=512M
    myisam_sort_buffer_size=128M
    join_buffer_size=2M
    read_buffer_size=4M (1MB for every 1GB of RAM)
    sort_buffer_size=4M
    table_cache=1500
    thread_concurrency=4
    thread_cache_size=512M
    wait_timeout=200
    connect_timeout=100
    max_allowed_packet=16M
    max_connect_errors=1082.165.248.54190.212.44.109
    query_cache_limit=2M
    query_cache_size=128M
    query_cache_type=1

    Like

  10. Sanoj says:

    Wow its ow some
    ………….its absolutely a new information for me

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog Stats
  • 119,806 hits
Follow

Get every new post delivered to your Inbox.

Join 209 other followers

%d bloggers like this: