MYSQL Tuning

Mysql Tuning Methods

1. Parameter Tuning

2. System Tuning

3. SQL Tuning

Parameter Tuning

Innodb structure

1. Caching: Buffer Pool Pool

2. Transaction System: redo log buffer / redo log file

3. Storage: file per table / ibdata

parameter 1: Innodb_buffer_pool_size

1. single instance: Innodb_buffer_pool_size= 75% system memory

2. instance requirement: try to increase the memory hit ratio > 90%

3. instance standardlization

parameter 2: innodb_buffer_pool_instances

divide innodb buffer pool into several instances

If the system memory size is big, use it. 

parameter 3: Innodb_log_file_size

redo log file size: more bigger more better to reduce I/O operation; After crash, the recover time is much longer. 

But normally mysql is master - slave structure. If master crash, system will connect to slave one. 

parameter 4: Innodb_log_buffer_size

redo log write into buffer; when buffer full or transaction finish, write into file. 

Increase innodb_log_buffer_size to reduce the I/O operation.

parameter 5: Innodb_thread_concurrency

control the concurrency number. By default the value is 0, mysql system will internally manage.

Suggest to set value to the number of physical cpu core.

parameter 6: Innodb_io_capacity

Innodb every second max number of datapage process; it is used for the whole  Innodb buffer pool. 

If has set innodb buffer pool instance, it should be innodb_io_capacity / innodb_buffer_pool_instance

normally 750. If the I/O process ability is strong, increase it.

parameter 7: Innodb_max_dirty_pages_pct

decide when Innodb should refresh innodb buffer.

refresh page number: Innodb_max_dirty_pages_pct * innodb_io_capacity

normally 75%

parameter 8: Innodb_flush_method

refresh method: 

A. O_DSYNC: O_SYNC open and refresh log file, fsync() refresh data file;

B. O_DIRECT: O_DIRECT open datafile, fsync() refresh log and data file; 

If storage is RAID, use O_DIRECT

parameter 9: Innodb_file_per_table

Increase the data directory flexibility; 

Can use soft link to move the datafile to other disk in order to redice I/O

Every table one file

parameter 10: Innodb_flush_log_at_trx_commit

value: 

A. 0: every second write the context of log buffer to log file and fsync() refresh data file;

B. 1: every transaction commit, write the context of log buffer to log file and fsync() refresh data file;

C. 2: every transaction commit, write the context of log buffer to log file;

In order reduce the master - slave data missing, set Innodb_flush_log_at_trx_commit = 1 and sync_binlog = 1

System Tuning

1: numa

OS level numa close, BIOS level numa open, QPS reduce 15% - 30%;

BIOS level numa close, OS level numa open or close, no impact

single instance: close numa

one server two/four instance: start numa --> increase memory efficency

2. malloc

install 3rd party jemalloc increase mysql CPU performance

#wget http://www.canonware.com/download/jemalloc/jemalloc-3.6.0.tar.bz2

#tar -xjf jemalloc-3.6.0.tar.bz2

#cd jemalloc-3.6.0; ./configure; make && make install

#vim /etc/my.cnf

malloc-lib=$PATH/libjemalloc.so

http://blog.chinaunix.net/uid-26896862-id-3865087.html

3. network adaptor(need verify)

Use RPS, RFS(hash table) to reduce the CPU cache exchange; 

CPU load low, RT performance better; CPU load high, RT performance worse;

RFS hash table impact performance

1. 检查查询速度：

a. 登录：mysql -uroot

b. 查询：show profiles;

如果没有结果：

开启：set profiling=1;

c. 输入一些查询命令：

show databases;

use mysql;

select * from users;

之类

d. 查询结果：show profiles;

 

存储引擎都使用innodb的时候需要考虑如下参数设置：

Innodb_buffer_pool_size

1. 查看当前值：

show variable like 'Innodb_buffer_pool%';

 

2. 使用情况查看：

show status like 'Innodb_buffer_pool_%';

判断buffer是否过小：查看Innodb_buffer_pool_pages_free

 

buffer命中率:

(Innodb_buffer_pool_read_requests - Innodb_buffer_pool_read) / Innodb_buffer_pool_read_request * 100%

 

如果命中率过低，可能是buffer设置过大

 

3. 调整：

修改mysql配置文件/etc/my.cnf

innodb_buffer_pool_size = 4096M

 

存储引擎使用MYISAM时候需要考虑如下参数设置：

1. key_buffer_size: 一般使用物理服务器内存的30%-40%；

2. sort_buffer_size: 设置key_buffer_size的25%；

3. read_buffer_size: 设置key_buffer_size的25%；

 

4. 检查查询问题

在/etc/my.cnf中添加：

 

指定日志文件：

log_slow_queries=<文件>

 

查询时间超过时间：

long_query_time=<数字>

 

没有索引的查询语句

log-queries-not-using-indexes

 

例如：

log_slow_queries=/usr/local/mysql/var/slowquery.log

long_query_time=10

log-queries-not-using-indexes

 

注意：如果开启后发觉文件增加很快，需要及时调整关闭相关参数。

 

分析slow log:

-- 得出运行总数前100个的sql统计
/usr/local/mysql/bin/mysqldumpslow -s c -t 100 xxx.log > xxx_count.log

-- 得出执行时间前100个的sql统计
/usr/local/mysql/bin/mysqldumpslow -s at -t 100 xxx.log > xxx_quest_time.log