Mysql Database Performance tuning

Mysql provides a configuration file located in /etc/my.cnf. From here you can set all of the memory, table, and connection limits as well as a host of other options.
You can get the default buffer sizes used by the mysqld server with this command:

shell> mysqld –help

This command produces a list of all mysqld options and configurable variables. The output includes the default values and looks something like this:

Possible variables for option –set-variable (-O) are:
back_log                 current value: 5
bdb_cache_size           current value: 1048540
binlog_cache_size        current value: 32768
connect_timeout          current value: 5
delayed_insert_timeout   current value: 300
delayed_insert_limit     current value: 100
delayed_queue_size       current value: 1000
flush_time               current value: 0
interactive_timeout      current value: 28800
join_buffer_size         current value: 131072
key_buffer_size          current value: 1048540
lower_case_table_names   current value: 0
long_query_time          current value: 10
max_allowed_packet       current value: 1048576
max_binlog_cache_size    current value: 4294967295
max_connections          current value: 100
max_connect_errors       current value: 10
max_delayed_threads      current value: 20
max_heap_table_size      current value: 16777216
max_join_size            current value: 4294967295
max_sort_length          current value: 1024
max_tmp_tables           current value: 32
max_write_lock_count     current value: 4294967295
myisam_sort_buffer_size  current value: 8388608
net_buffer_length        current value: 16384
net_retry_count          current value: 10
net_read_timeout         current value: 30
net_write_timeout        current value: 60
read_buffer_size         current value: 131072
record_rnd_buffer_size   current value: 131072
slow_launch_time         current value: 2
sort_buffer              current value: 2097116
table_cache              current value: 64
thread_concurrency       current value: 10
tmp_table_size           current value: 1048576
thread_stack             current value: 131072
wait_timeout             current value: 28800

Please note that –set-variable is deprecated since MySQL 4.0, just use –var=option on its own.
If there is a mysqld server currently running, you can see what values it actually is using for the variables by executing this command:

shell> mysqladmin variables

MySQL uses algorithms that are very scalable, so you can usually run with very little memory. If you, however, give MySQL more memory, you will normally also get better performance.
When tuning a MySQL server, the two most important variables to use are key_buffer_size and table_cache. You should first feel confident that you have these right before trying to change any of the other variables.
If you have much memory (>=256M) and many tables and want maximum performance with a moderate number of clients, you should use something like this:

shell> safe_mysqld -O key_buffer=64M -O table_cache=256 -O sort_buffer=4M -O read_buffer_size=1M &

If you have only 128M and only a few tables, but you still do a lot of sorting, you can use something like:

shell> safe_mysqld -O key_buffer=16M -O sort_buffer=1M

If you have little memory and lots of connections, use something like this:

shell> safe_mysqld -O key_buffer=512k -O sort_buffer=100k -O read_buffer_size=100k &

or even:

shell> safe_mysqld -O key_buffer=512k -O sort_buffer=16k -O table_cache=32 -O read_buffer_size=8k -O net_buffer_length=1K &

If you are doing a GROUP BY or ORDER BY on files that are much bigger than your available memory you should increase the value of record_rnd_buffer to speed up the reading of rows after the sorting is done.
When you have installed MySQL, the `support-files’ directory will contain some different `my.cnf’ example files, `my-huge.cnf’, `my-large.cnf’, `my-medium.cnf’, and `my-small.cnf’, you can use as a base to optimise your system.
If there are very many connections, “swapping problems” may occur unless mysqld has been configured to use very little memory for each connection. mysqld performs better if you have enough memory for all connections, of course.
Note that if you change an option to mysqld, it remains in effect only for that instance of the server.
To see the effects of a parameter change, do something like this:

shell> mysqld -O key_buffer=32m –help

Make sure that the –help option is last; otherwise, the effect of any options listed after it on the command-line will not be reflected in the output.
These parameters can be seen here 
We are going to see one example now this is the best tuned my.cnf file

[mysqld]
back_log = 75
skip-innodb
max_connections = 500
key_buffer = 384M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1800
thread_cache_size = 384
wait_timeout = 7200
connect_timeout = 10
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 64M
max_connect_errors = 1000
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 8M
query_cache_limit = 4M
query_cache_size =128M
query_cache_type = 1
query_prealloc_size = 65536
query_alloc_block_size = 131072
default-storage-engine = MyISAM
[mysqld_safe]
nice = -5
open_files_limit = 8192
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M

Let’s just look at the important bits.
max_connections = 500 – You need to use mysqlreport tool(http://hackmysql.com/mysqlreport) to check how many current connections you have, and under very heavy load (2000 simultaneous users) You may rarely hit 400 concurrent connections to the database. This is because most connections only last for a few milliseconds.
key_buffer = 384M – When tuning a MySQL server, key_buffer_size is very important. This number works well for me and with the mysqlreport script I rarely use 50% of the available memory.
table_cache = 1800 – After key_buffer the next most important variable is your table cache. Again this is set for vBulletin so you may be able to significantly reduce this value depending on the number of tables in your database.
wait_timeout = 7200 – This variable determines the timeout in seconds before mysql will dump a connection. If set to low you will likely receive mySQL server has gone away errors in your log, which in vBulletin’s case is quite common.
max_allowed_packet = 16M – Again if set to low (the default is 8M) users will likely experience errors. 16M has always worked fine for my production environments.
One more important tool we have already menctioned mysqlreport this is the best tool available for free you can check your mysql database server performance.
Optimize Mysql Database response time

The most important part for getting a system fast is of course the basic design. You also need to know what kinds of things your system will be doing, and what your bottlenecks are.
The most common bottlenecks are:
Disk seeks. It takes time for the disk to find a piece of data. With modern disks in 1999, the mean time for this is usually lower than 10ms, so we can in theory do about 100 seeks a second. This time improves slowly with new disks and is very hard to optimise for a single table. The way to optimise this is to spread the data on more than one disk.
Disk reading/writing. When the disk is at the correct position we need to read the data. With modern disks in 1999, one disk delivers something like 10-20 MB. This is easier to optimise than seeks because you can read in parallel from multiple disks.
CPU cycles. When we have the data in main memory (or if it already were there) we need to process it to get to our result. Having small tables compared to the memory is the most common limiting factor. But then, with small tables speed is usually not the problem.
Memory bandwidth. When the CPU needs more data than can fit in the CPU cache the main memory bandwidth becomes a bottleneck. This is an uncommon bottleneck for most systems, but one should be aware of it.
Some of the available options we will see now
Disable DNS Hostname Lookup

open you /etc/my.cnf file and use –skip-name-resolve option file looks like below.

[mysqld]
…..
……
skip-name-resolve

When this option is activated, you can only use IP numbers in the MySQL Grant table.
How MySQL uses DNS

When a new thread connects to mysqld, mysqld will spawn a new thread to handle the request. This thread will first check if the hostname is in the hostname cache. If not the thread will call gethostbyaddr_r() and gethostbyname_r() to resolve the hostname.
If the operating system doesn’t support the above thread-safe calls, the thread will lock a mutex and call gethostbyaddr() and gethostbyname() instead. Note that in this case no other thread can resolve other hostnames that is not in the hostname cache until the first thread is ready.
You can disable DNS host lookup by starting mysqld with –skip-name-resolve. In this case you can however only use IP names in the MySQL privilege tables.
If you have a very slow DNS and many hosts, you can get more performance by either disabling DNS lookop with –skip-name-resolve or by increasing the HOST_CACHE_SIZE define (default: 128) and recompile mysqld.
You can disable the hostname cache with –skip-host-cache. You can clear the hostname cache with FLUSH HOSTS or mysqladmin flush-hosts.
If you don’t want to allow connections over TCP/IP, you can do this by starting mysqld with –skip-networking.
Activate Query Cache

mysql> SHOW STATUS LIKE ‘Qcache%’;

If you find any problems with  query cache memory was left. It was neccessary to increase the query cache size.
To get an overview of your query_cache variables state, use the following syntax:

mysql> SHOW VARIABLES LIKE ‘%query_cache%’;

You need to have the query cache enabled in the first place (have_query_cache | YES) and make sure that query_cache_type is set to ON. This is usually activated by default on most linux distribution.
Now, you can increase the query cache size (let say you want 50M) using:

mysql> SET GLOBAL query_cache_size = 52428800;

If you want this setting to be kept when restarting mysql, add:

[mysqld]
…
…
query_cache_size = 52428800;
query_cache_type = 1

Enable the query cache in MySQL to improve performance ( Mysql Database Performance tuning )
If you want to get optimized and speedy response from your MySQL server then you need to add following two configurations directive to your MySQL server:
query_cache_size=SIZE
The amount of memory (SIZE) allocated for caching query results. The default value is 0, which disables the query cache.
query_cache_type=OPTION
Set the query cache type. Possible options are as follows:
0 : Don’t cache results in or retrieve results from the query cache.
1 : Cache all query results except for those that begin with SELECT S_NO_CACHE.
2 : Cache results only for queries that begin with SELECT SQL_CACHE
Howto enable caching in MySQL
You can setup caching as follows:

$ mysql -u root –p

Output:

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16 to server version: 4.1.15-Debian_1-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>

Now setup cache size 16Mb:

mysql> SET GLOBAL query_cache_size = 16777216;

Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'query_cache_size';

Output:

 +------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| query_cache_size | 16777216 |
+------------------+----------+

You can setup them in /etc/my.cnf (Red Hat) or /etc/mysql/my.cnf (Debian) file:

# vi /etc/my.cnf

Append config directives as follows:

query_cache_size = 268435456
query_cache_type=1
query_cache_limit=1048576

In above example the maximum size of individual query results that can be cached set to 1048576 using query_cache_limit system variable. Memory size in Kb.
High performance Innodb Mysql server configuration

# MySQL configuration

# InnoDB database with 3 MyISAM tables linked

port        = 3306

socket      = /var/run/mysql/mysql.sock

# MySQL server

[mysqld]

port        = 3306

socket      = /var/run/mysql/mysql.sock

datadir = /var/lib/mysql

skip-locking

key_buffer_size = 16M

max_allowed_packet = 2M

table_open_cache = 128

sort_buffer_size = 1024K

net_buffer_length = 8K

read_buffer_size = 512K

read_rnd_buffer_size = 1024K

myisam_sort_buffer_size = 8M

query_cache_size = 16M

# Added values after load testing

thread_cache_size = 4

tmp_table_size = 128M

max_heap_table_size = 128M

table_cache = 512

join_buffer_size = 512

# Replication Master Server (default)

# binary logging is required for replication

log-bin=mysql-bin

# binary logging format

binlog_format=mixed

# required unique id between 1 and 2^32 - 1

# defaults to 1 if master-host is not set

# but will not function as a master if omitted

server-id   = 1

# Innodb specifics

innodb_data_home_dir = /var/lib/mysql/

innodb_data_file_path = ibdata1:10M:autoextend

innodb_log_group_home_dir = /var/lib/mysql/

# 50 - 80 % of RAM but beware of setting memory usage too high

innodb_buffer_pool_size = 2048M

# innodb_additional_mem_pool_size generally not needed after tests

innodb_additional_mem_pool_size = 8M

# log_file_size to 25 % of buffer pool size

innodb_log_file_size = 512M

innodb_log_buffer_size = 16M

innodb_flush_log_at_trx_commit = 1

innodb_lock_wait_timeout = 50

# innodb_file_per_table to avoid defragmentation but when optimizing will lock tables

innodb_file_per_table = 1

# The safe_mysqld script

[safe_mysqld]

log-error   = /var/log/mysql/mysqld.log

socket      = /var/run/mysql/mysql.sock

!include_dir /etc/mysql

[mysqldump]

socket      = /var/run/mysql/mysql.sock

quick

max_allowed_packet = 32M

[mysql]

no-auto-rehash

# Remove the next comment character if you are not familiar with SQL

#safe-updates

[myisamchk]

key_buffer_size = 60M

sort_buffer_size = 60M

read_buffer = 8M

write_buffer = 8M

[mysqlhotcopy]

interactive-timeout

[mysqld_multi]

mysqld     = /usr/bin/mysqld_safe

mysqladmin = /usr/bin/mysqladmin

log        = /var/log/mysqld_multi.log

# Mysql Database Performance tuning : Please give new suggestions if you think you can optimize performance even more.