MySQL optimization

MySQL OPTIMIZATION

MySQL is a popular choice of database for use in web applications. Fully optimizing MySQL takes both time and effort since every application has different requirements.

The MySQL database server performance depends on the number of factors. The Optimized Query is one of the factors for the MySQL robust performance.

The MySQL performance depends on the below factors.

1) Hardware (RAM, DISK, CPU etc)
2) Operating System (i.e. Linux OS will give the more performance compare to Windows OS )
3) Application
4) Optimization of MySQL Server & Queries

This article is meant to be an easy and relatively safe way to enhance mysql performance.

Below are notes on some of the important variables

Key Buffer

The key buffer holds the indexes of tables in memory and a bigger key buffer results in faster row lookups. Adjust according to your own needs. Bigger is better, but prevent swapping at all costs. The larger the buffer, the faster the SQL command will finish and a result will be returned.

A good rule of thumb seems to be to use 1/4 of system memory.

key_buffer = 128M

Query Cache

The query_cache_size, as the name suggests, is the total size of memory available to query caching.

The value query_cache_limit is the maximum number of kilobytes one query may be in order to be cached.
Setting this value too high might prevent a lot of smaller queries to be cached.

Setting it too low will result in bigger queries to never be cached, and the smaller queries not being able to completely fill the cache size, which would be a waste of resources. Adjust according to your own needs and memory available:

query_cache_size = 128MB
query_cache_limit = 4MB

Table Cache

An important variable if your application accesses many tables.

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.

table_cache = 512

Sort Buffers

Sort_buffer_size (the variable previously known as sort_buffer), used for grouping and sorting and is a per-thread buffer. If the buffer can not hold the data to be sorted, a sort is performed on disk.

Watch out for making this too large as the buffer is allocated for every thread that needs sorting and with many sorts it can easily consume all your memory.

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.

sort_buffer_size = 32M
myisam_sort_buffer_size = 32M

Connection Timeout

This is a little tweak that determines the closing of sleeping connections. The default is one hour and is often too long for practical purposes. I often set this at one minute instead (60).

Setting the max_connections too high will result in the MySQL server crashing with an “Out of memory” error. General value is 400 -500 depending on your ram and other process that are occupying the ram.

wait_timeout = 60

Temporary Tables

Temporary tables are used for sorting and grouping.

The buffer is created on demand so watch out for setting this too high here as well. If the buffer cannot accomodate the data, a temp file is used on disk instead.

tmp_table_size = 64MB

eg: SELECT NAME, MAX(TIME) as TIME FROM table GROUP BY time ORDER BY time DESC

Thread cache

If you have a busy server that’s getting a lot of quick connections, set your thread cache high.

when a new connection is requested MySQL first looks into the thread cache to see if there is any ideal thread, if it finds one it uses that one for the connection, if it doesn’t find any it has to create the thread.

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.

Obviously MySQL response time much faster if its just using threads from the cache and not creating them.

The number of threads in the cache is dictated by the thread_cache_size variable.

thread_cache_size = 150

max_connection

Max connections is the maximum connections that can be made to the Mysql server and max_user connection is the maximum a user can make to the Mysql server.

max_connections = 500
max_user_connections=50

Binary Logging

The binary log contains “events” that describe database changes such as table creation operations or changes to table data.

The binary log serves as a written record of all events that modify database structure or content (data) from the moment the server was started.

which keeps track of all updates to the database. It is used for database restoration and replication.

1) Certain data recovery operations require use of the binary log. After a backup has been restored, the events in the binary log that were recorded after the backup was made are re-executed. These events bring databases up to date from the point of the backup.

2) For replication, the binary log is used on master replication servers as a record of the statements to be sent to slave servers. The master server sends the events contained in its binary log to its slaves, which execute those events to make the same data changes that were made on the master

Each slave that connects to the master requests a copy of the binary log. That is, it pulls the data from the master, rather than the master pushing the data to the slave. The slave also executes the events from the binary log that it receives. This has the effect of repeating the original changes just as they were made on the master.

mysqlbinlog –database=db1 /data/binlog/mysql-bin.000047 > recover.sql

slow_query

Sometime, a single SQL query may be the cause of all the server’s problems. MySQL has built-in functionality to capture slow query log or identify queries that are not optimal and take a long time to finish.

To enable slow query log, simply add the following line to MySQL configuration file (my.cnf or my.ini), and then restart the MySQL server:

# log-slow-queries=/var/log/mysql-slow.log

Long_query

You can specify how long a quey needs to run for before it is logged with the “long_query_time” setting. By default this is 10 seconds.

The default value of long_query_time is 10.

# long_query_time = 5