Teadmistebaas

For a basic level optimization of MySQL server you can use the MySLQ tuner script that is easily available :

Usage :

1.Download the script from the following website “http://mysqltuner.com/”

wget https://github.com/major/MySQLTuner-perl/zipball/master

2. Now execute the following commands.

unzip master
cd major-MySQLTuner*
chmod +x mysqltuner.pl
perl mysqltuner.pl

The script will check the status of MySQL and update you with the variables that you need to tweak to optimize MySQL. For advanced level of optimization you must fine tune your server according to the applications and resourse utilization. Below are few of important system variables that need to be tweaked in normal case.

table_cache : Each time MySQL accesses a table, it places the table in the cache. If the wesite access many tables frequenty it will be faster if these tables are placed in the cache.

You can check whether your system needs to have the table_cache value increased by checking the open_tables and opened_tables status variables during peak time.

mysql> SHOW STATUS LIKE “open%tables%”;
open_tables = 64
opened_tables = 5426787

open_tables is the number of tables opened in cache. Whereas opened_tables is the total number of tables open. Since MySQL supports multi-threading, several queries might be executed on the same table at the same time. So each of these queries will open a table.

Default vaue is 64. If you have enogh RAM left in your server, you can increase the table_cache so that it will reduce the number of opened_tables by moving those tables to cache.

query_cache_size : If you have a MySQL query that is being executed again and again by your website. MySQL can cache the results of this query so that there is no need to run these queries each 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.

key_buffer_size : key_buffer_size is the size of buffer used by all the indexes. Ideally it should be set to at least a quarter of the memory available or more. 

The optimum solution is to keep the ratio as follows >

Key_reads : Key_read_requests should be 1 : 100 and Key_writes / Key_write_requests should always be less than 1. 

If the Key_reads value is high compared to Key_read_requests you need to increase key_buffer_size. 

You can get the value of these variables using the following command :

mysql> SHOW GLOBAL STATUS where Variable_name like “Key_%”;
+————————+———–+
| Variable_name | Value |
+————————+———–+
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 48394 |
| Key_blocks_used | 8078 |
| Key_read_requests | 973911676 |
| Key_reads | 54135 |
| Key_write_requests | 824911 |
| Key_writes | 739554 |
+————————+———–+ 

sort_buffer_size : This value mproves large and complex sorts. Increase this value for faster ORDER BY or GROUP BY operations. The default value for sort_buffer_size is 2MB.  

Sufficient size of sort_buffer_size allows the sort opertations to be done in memory cache rather than in temp files in hardisks so it is much faster.

thread_cache : If your server has large trafiic on MySQL server, then the server will create a lot of new threads at a very high rate. This may eat up a lot of CPU time. 

When a connection disconnects the threads are put in the cache and when a new threadit is taken from this cache, and only when the cache is empty is a new thread created. 

If Threads_created is large, you may want to increase the thread_cache_size variable. The cache hit rate can be calculated with Threads_created/Connections. 

mysql> SHOW GLOBAL STATUS where Variable_name like “Connections”;
mysql> SHOW GLOBAL STATUS where Variable_name like “Threads_created”;

read_rnd_buffer_size : read_rnd_buffer_size is used after a sort for reading the rows in the sorted order. If your application has a lot of queries with ORDER BY, increasing this can improve the performance. This is buffer is also at a per client basis. The default value for read_rnd_buffer_size is 128K. General rule is to allot 1MB of read_rnd_buffer_size for every 1GB memory in server.

tmp_table_size : Sometimes for executing a statement, a temporary table needs to be created. This variable determines the maximum size for a temporary table in memory. 

Always try to avoid temporary table creation by optimizing your query. But if it is unavoidable, make sure that the table is created in the memory. If the memory is not sufficient, a MyISAM table will be created in the disk. 

If a large number of tables are created in the disk you need to increase your tmp_table_size. You can also check the status variables Created_tmp_disk_tables and Created_tmp_tables.

mysql> SHOW GLOBAL STATUS where Variable_name like “Created_tmp_disk_tables”;
mysql> SHOW GLOBAL STATUS where Variable_name like “Created_tmp_tables”;

Created_tmp_disk_tables : Number of temporary tables created on disk while executing a statement

Created_tmp_tables : Number of in-memory tables created.