mysql配置优化

4 压测验证
使用sysbench
百度云网盘：https://pan.baidu.com/s/1mjoa25y 密码：i4pm
参考sysbench 介绍

wget https://github.com/akopytov/sysbench/archive/1.0.zip -O "sysbench-1.0.zip" 
# 安装依赖库
yum install automake libtool -y
# 解压
unzip sysbench-1.0.zip
# 进入文件
cd sysbench-1.0
./autogen.sh
# 
./configure
make
make install
# 查看版本
sysbench --version

如果是非root用户

sudo yum install automake libtool -y
# 解压
unzip sysbench-1.0.zip
# 进入文件
cd sysbench-1.0
./autogen.sh
# 
./configure
make
sudo make install

如果提示mysql以来相关的，则执行sudo yum install mysql-devel -y

如果提示下面的错误

error while loading shared libraries: libmysqlclient.so.20:

则执行

# 将libmysqlclient.so.20文件复制
cp /appdata/mysql57/lib/libmysqlclient.so.20 /usr/local/lib/
# /etc/ld.so.conf
[root@bs104 sysbench-1.0]# cat /etc/ld.so.conf
include ld.so.conf.d/*.conf
/usr/local/lib
# 配置生效
ldconfig -v

在要压测的数据库中创建数据库dbtest，表它会自动创建

# 准备数据
sysbench /usr/local/share/sysbench/oltp_read_write.lua --mysql-db=dbtest --mysql-host=192.168.0.6 --mysql-user=dbtest --mysql-password=密码 --mysql-port=3306 --mysql_storage_engine=Innodb --tables=10 --table_size=10000 prepare
# 启动5个线程开始压测
sysbench /usr/local/share/sysbench/oltp_read_write.lua --mysql-db=dbtest --mysql-host=192.168.0.6 --mysql-user=dbtest --mysql-password=123456 --mysql-port=3306 --mysql_storage_engine=Innodb --tables=10 --table_size=10000 --threads=5 --time=3600 --report-interval=10 --rand-type=uniform run
# 清空数据
sysbench /usr/local/share/sysbench/oltp_read_write.lua --mysql-db=dbtest --mysql-host=192.168.0.6 --mysql-user=dbtest --mysql-password=123456 --mysql-port=3306 --mysql_storage_engine=Innodb --tables=10 --table_size=10000 cleanup

在目标数据库中执行innotop -ui用户 -p密码 -d 5,每5s刷新一下。
3 随着硬件资源调整配置
查看到服务器上的CPU核数为112
同一时刻能够进入innodb层并发执行的线程数量。如果超过CPU核数，某些线程就会处于就绪状态;若Server层线程数超过这个数值，多余的线程会被放到wait queue队列中等待;
默认值：0，表示不限制线程并发执行的数量，所有请求都会被认为是可调度的。此时，innodb_thread_sleep_delay的值会被忽略

范围：0 ~ 1000

set global innodb_thread_concurrency=56; 
# 对应my.inf
innodb_thread_concurrency=56; 

[root@bs6 ~]# cat /proc/cpuinfo | grep name | cut -f2 -d: | uniq -c
    112  Intel(R) Xeon(R) Gold 5117 CPU @ 2.00GHz
[root@bs6 ~]# cat /proc/cpuinfo |grep "physical id"|sort |uniq|wc -l
4
[root@bs6 ~]# cat /proc/cpuinfo |grep "processor"|wc -l
112

按照下面得my.cnf压测得结果

启动50个线程

启动100个线程,QPS没有太大变化，说明当前配置差不多到极限了

此时，再将innodb_buffer_pool_size设置为100G,启动100个线程,QPS没有什么变化

查看磁盘IO,3.645GB，磁盘已经到瓶颈

既然已经到这里了，顺道验证一下服务器的iops
参考sysbench、iostat测试服务器的iops

cd /drbd_data/test
sysbench --test=fileio --num-threads=4 --file-total-size=4G --file-test-mode=rndrw prepare
sysbench --test=fileio --num-threads=4 --file-total-size=4G --file-test-mode=rndrw run
sysbench --test=fileio --num-threads=4 --file-total-size=4G --file-test-mode=rndrw cleanup

参数说明

--test=fileio ##测试服务器的io性能，还可以测试内存，cpu等
--file-num=4 ##生成4个测试文件
--file-block-size=8k ##文件块大小，这个值最好与mysql数据库的innodb_page_size大小一致
--file-total-size=4G ##4个文件总大小
--file-test-mod=rndrd ##测试随机io读性能，随机io写为rndwr,一般只用测试随机读写io，顺序io性能不测试
--file-extra-flags=direct ##测试文件写入磁盘，跳过缓存，对应mysql中的参数innodb_flush_method=O_DIRECT,直接跳过先写入文件系统缓存，直接落盘。会影响mysql性能，但是可以提高数据安全性.
--max-requests=0##最多请求次数，0表示不限制
--time=1200 ##最大持续时间
--threads=4 ##处理线程数

执行iostat -xm 3，3s刷新一次，主要看%util，磁盘是否满载

查看磁盘调度算法,其中sdb是盘符

cat /sys/cat /sys/block/sdb/queue/scheduler
[root@bwscdb11 /sys/block]#cat sdb/queue/scheduler 
noop [deadline] cfq 

计算iops

# 读
219.78*1024/8(file-block-size)=28131.84
# 写
146.52*1024/8=18754.56

2 my.cnf
下面是一位有经验的DBA发给我的my.cnf配置

[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
#default-character-set = utf8
default-character-set = utf8mb4

[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
#log            = /appdata/mysql/logs/mysql.log

slow_query_log = 1
slow_query_log_file             = /appdata/mysql/logs/sql_query_slow.log


basedir=/usr/local/mysql
datadir=/appdata/mysql/data 
skip-external-locking
key_buffer_size = 256M
table_open_cache = 4096 
sort_buffer_size = 16M
read_buffer_size = 8M
read_rnd_buffer_size = 64M
net_buffer_length = 2K
thread_stack = 256K
query_cache_type=2
query_cache_size = 1024M
query_cache_limit=2M
lower_case_table_names=1
#character_set_server =  utf8
log_timestamps = SYSTEM
log_bin_trust_function_creators=1  
############################################
character-set-client-handshake = FALSE 
character-set-server = utf8mb4 
collation-server = utf8mb4_bin
init_connect='SET NAMES utf8mb4'
############################################
max_connections	 = 3000
bulk_insert_buffer_size = 128M
binlog_cache_size = 32M
tmp_table_size = 256M
max_heap_table_size =128M
thread_cache_size = 32
# 为当前服务取一个唯一的 id（MySQL5.7 之后需要配置）
server-id = 1
# 这个参数表示启用 binlog 功能，并指定 binlog 的存储目录
log-bin=mysql-bin
binlog_format = ROW
binlog_rows_query_log_events = on
binlog_row_image = full
slow_query_log
long_query_time = 1
#binlog-ignore-db = cattle
default-storage-engine=InnoDB
innodb_buffer_pool_size = 24096M 
innodb_log_file_size = 1024M
innodb_log_buffer_size = 4M
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 50
innodb_buffer_pool_instances=23
skip-name-resolve
skip-host-cache
max_allowed_packet = 134217728
innodb_log_files_in_group = 3
innodb_thread_concurrency=16
# 设置了 binlog 文件的有效期（单位：天）
expire_logs_days = 30  
innodb_file_per_table=1   
wait_timeout=7200
join_buffer_size=16M
######### 
innodb_print_all_deadlocks = 1
# by 5.7 slave
#slave_parallel_type=LOGICAL_CLOCK
#slave_parallel_workers=16 
#master_info_repository=TABLE
#relay_log_info_repository=TABLE
#relay_log_recovery=ON
#slave_preserve_commit_order=1
#sql_mode=ONLY_FULL_GROUP_BY,
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
######################################################

[mysqldump]
quick
max_allowed_packet = 2048M

[mysql]
#######################################
default-character-set = utf8mb4
#######################################
no-auto-rehash

[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

下面的配置，使用monyog检测上面的配置，有这么几个问题
sort_buffer_size = 16M,全局配置这里不应该超过2M。

This variable should not be set larger than 2M for most purposes, and then only with benchmarks to prove that it helps. Making it too large can actually slow down queries. Instead of setting it to a large value globally, leave the global setting small, and set the local value larger for queries you know will benefit from it
This buffer is used by ORDER BY, GROUP_BY, DISTINCT. You can improve the performance of 'ORDER BY' by increasing this value. The recommended value is 1MB. This buffer is allocated per client, so be careful about not making it very large

如果开启了binlog，则Innodb_support_xa=1，否则Innodb_support_xa=0
max_prepared_stmt_count没有配置,应该配置为多少呢？max_prepared_stmt_count设置太大内存随之影响。它的取值范围为“0 – 1048576”，默认为16382。

Ensure that an appropriate value for <b>max_prepared_stmt_count</b> is set, depending on your application requirement

通过 ENGINE= MEMORY创建内存表，max_heap_table_size,当内存表的数据大于max_heap_table_size,mysql会转换超出的数据存储到磁盘上,性能随之下降。首先在优化sql的时候就应该尽量避免临时表，如果要使用，则调整tmp_table_size。

This value defines the maximum size of a MEMORY (storage-engine) table that is created explicitly.
This variable should be at least as large as <b>tmp_table_size</b>, and may need to be much larger if you use explicit Memory tables.

binlog相关配置,MySQL binlog-do-db选项是危险的[转],在主从库中是需要谨慎处理，但我的生产库是单库，

All changes (DDL, DML and security) to a database are recorded by MySQL in the binary log. This enables point-in-time recovery, thus preventing catastrophic data loss due to outages.<br/>Using the <b>binlog-do-db</b> and the <b>binlog-ignore-db</b> options you can limit binary logging on particular databases. However, doing so will limit your recovery options and also your ability to review changes made to the system

Ensure that binary logging is enabled for all important databases by reviewing the <b>binlog-do-db</b> and <b>binlog-ignore-db</b> options in your configuration file

Enable <b>sync_binlog</b> in your configuration file. Setting it to 1 will ensure that a crash will not result in the binary log getting out of sync with your transactional data

mysql> show master status;
+------------------+-----------+--------------+------------------+-------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+-----------+--------------+------------------+-------------------+
| mysql-bin.000012 | 665431704 |              |                  |                   |
+------------------+-----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> show variables like 'log_bin%';
+---------------------------------+------------------------------------+
| Variable_name                   | Value                              |
+---------------------------------+------------------------------------+
| log_bin                         | ON                                 |
| log_bin_basename                | /appdata/mysqldata/mysql-bin       |
| log_bin_index                   | /appdata/mysqldata/mysql-bin.index |
| log_bin_trust_function_creators | ON                                 |
| log_bin_use_v1_row_events       | OFF                                |
+---------------------------------+------------------------------------+
5 rows in set (0.00 sec)

binlog调整的配置，max_binlog_size默认值为1G
sync_binlog=0高并发事务的系统来说，“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍,mysql 参数调优(7) binlog的写入优化 sync_binlog,保守用法最好还是不要设置为0，使用sync_binlog=100

# 写缓存多少次，刷一次磁盘，默认 0 表示这个操作由操作系统根据自身负载自行决定多久写一次磁盘
# 1 表示每一条事务提交都会立即写磁盘，n 则表示 n 个事务提交才会写磁盘
sync_binlog=0

join_buffer_size不能超过4M

This variable is set to a non-default size, which is usually not justified without benchmarks to support the non-default setting.
Also, this variable should not be set larger than 4M globally, because it is allocated to its full size when needed. If you know that a certain query will benefit from a large setting, it is likely due to poorly optimized JOINs. You should rather optimize schemas and/or queries.

This buffer is used for joins that don't use indexes. Such JOINs are bad. However you can decrease disk usage in such cases by increasing this value

read_buffer_size，这个跟join_buffer_size一样，全局应该保持较小值，布局根据调试设置较大值

This buffer is used for full table scans of MySQL tables. The recommended value is 1MB. This buffer is allocated per client, so be careful about not making it very large
This variable should not be set larger than 1MB for most purposes, and then only with benchmarks to prove that it helps.  Making it too large can actually slow down queries.  Instead of setting it to a large value globally, leave the global setting small, and set the local value larger for queries you know will benefit from it

下面做的优化为：

sort_buffer_size = 2M
innodb_support_xa=1
max_prepared_stmt_count=100000
max_heap_table_size =256M
sync_binlog=100
join_buffer_size=4M
read_buffer_size=1M

1 innotop
源码安装
github.com/innotop/innotop

yum install -y perl-DBD-MySQL
# 下面这个不能少，否则会报Can't locate ExtUtils/MakeMaker.pm in异常
yum -y install perl-CPAN
cd innotop-master
perl Makefile.PL
make
make install

执行命令后

Uptime  MaxSQL  ReplLag  QPS    Cxns  Run  Miss  Lock  Tbls  Repl  SQL
   24m                   2.45k   254    1  0.00     0   482    

When   Load  Cxns     QPS      Slow  Se/In/Up/De%  QCacheHit  KCacheHit  BpsIn    BpsOut
Now    0.00  257        2.19k     0  87/ 2/ 0/ 0       0.00%    100.00%  625.21k   6.87M
Total  0.00    2.93k  621.81      0  81/ 2/ 0/ 0       0.00%     64.29%  166.19k   1.78M

Cmd    ID      State               User   Host           DB      Time   Query                                               
Query     114  Sending data        root   10.101.10.191  acc     00:00  select accVoucher.as_id as_id_accVoucher, accVoucher
Query     128  Sending to client   root   10.101.10.194  acc     00:00  select as_id, period, period_year, period_month, sta
Query     207  Creating sort inde  root   10.101.10.194  acc     00:00  select accVoucher.as_id as_id_accVoucher, accVoucher

___________________________ Buffer Pool ____________________________
Size   Free Bufs  Pages  Dirty Pages  Hit Rate     Memory  Add'l Pool
1.62M    1687969   7088          712  1000 / 1000  26.49G            

____________________ Page Statistics _____________________
Reads  Writes  Created  Reads/Sec  Writes/Sec  Creates/Sec
 6832     607      256       0.00        0.00         0.00

______________________ Insert Buffers ______________________
Inserts  Merged Recs  Merges  Size  Free List Len  Seg. Size
                   0       0     1           3086       3088

__________________ Adaptive Hash Index __________________
Size   Cells Used  Node Heap Bufs  Hash/Sec  Non-Hash/Sec
6.55M                           9  76471.84       5018.22

_____________________ Command Summary ______________________
Name                       Value   Pct     Last Incr  Pct   
Com_select                 340323  83.89%      20809  87.59%
Com_set_option              42794  10.55%       1966   8.28%
Com_commit                  10467   2.58%        491   2.07%
Com_insert                   8964   2.21%        489   2.06%
Com_admin_commands           2628   0.65%          1   0.00%
Com_show_variables            279   0.07%          0   0.00%
Com_show_status               124   0.03%          2   0.01%
Com_show_engine_status         35   0.01%          0   0.00%
Com_show_slave_status          28   0.01%          0   0.00%
Com_show_processlist            8   0.00%          0   0.00%
Com_change_db                   3   0.00%          0   0.00%
Com_show_master_status          3   0.00%          0   0.00%
Com_show_databases              2   0.00%          0   0.00%
Com_show_fields                 2   0.00%          0   0.00%
Com_show_tables                 2   0.00%          0   0.00%
Com_show_warnings               2   0.00%          0   0.00%
Com_show_function_status        1   0.00%          0   0.00%
Com_show_procedure_status       1   0.00%          0   0.00%
Com_alter_db_upgrade            0   0.00%          0   0.00%
       

注意innotop需要两个权限,这两个是全局属性，需要使用*.*，并不是针对某个数据库,f否则会提示ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

GRANT SUPER,REPLICATION CLIENT,PROCESS ON *.* TO 'testuser'@'%' IDENTIFIED BY '123456';
flush privileges;

mysql 线程等待导致数据库性能低下