mysql 数据库DBA课程11_01 Mysql优化 1. 优化哲学 1.1 优化有风险 1.2 优化的范围 存储、主机和操作系统: 主机架构稳定性 I/O规划及配置 Swap OS内核参数 网络问题 应用 :（Index，lock，session） 应用程序稳定性和性能 SQL语句性能 串行访问资源 性能欠佳会话管理 数据库优化:（内存、数据库设计、参数） 内存 数据库结构(物理&逻辑) 实例配置 2. 优化工具介绍 2.1 系统层 CPU : 计算(主)和调度[资源的统筹管理](次) MEM : 缓存和缓冲 IO : 输入和输出 (1) top命令 %Cpu(s): 0.0 us, 0.0 sy, 100.0 id, 0.0 wa id 空闲的CPU时间片占比 us 用户程序工作所占用的时间片占比 sy 内核工作花费的cpu时间片占比 ++++++++++++++++++++++ sy过高的原因: 系统内核本身bug mysql的并发连接很高，mysql 是单进程，多线程。 mysql 锁 +++++++++++++++++ wa cpu用来等待的时间片占比 wa 过高的原因： IO过高，等待大的处理事件，锁的问题 KiB Mem : 4937752 total, 3988956 free, 476100 used, 472696 buff/cache 4193924 avail Mem KiB Swap: 1048572 total, 1048572 free, 0 used. mysql 不建议你用swap ++++++++++++++++++++++++++++++++ iostat ，评估IO yum install sysstat -y[root@db01 ~]# iostat -dk 1 一般情况下，CPU高，IO也应该高。 如果：CPU 高 ，IO 比较低 wait 高： 有可能IO出问题了（Raid ，过度条带化） SyS 高： 有可能是锁的问题，需要进一步去数据库中判断 +++++++++++++= yum install epel-release -y yum install glances -y
vmstat ## 查看CPU 内存 [root@db01 ~]# vmstat 1
2.2 数据库层工具 show status; show variables; show index from wp_users; show processlist; show slave status\G show engine innodb status\G desc wp_users; desc select * from city where name= 'Kabul'; 或者 explain wp_users; explain select * from city where name= 'Kabul'; 扩展类深度优化: pt系列（pt-query-digest pt-osc pt-index 等） mysqlslap 做压力测试 sysbench 做压力测试 information_schema （I_S） performance_schema (P_S) 3. 优化思路 3.0 未优化前的压力测试 db01 [(none)]>create database oldguo charset utf8mb4; db01 [(none)]>use oldguo; db01 [oldguo]>set sql_log_bin=0; db01 [oldguo]>source /root/t100w.sql; db01 [oldguo]>grant all on *.* to root@'localhost' identified by '123'; [root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf \ --concurrency=100 --iterations=1 --create-schema='oldguo' \ --query="select * from oldguo.t100w where k2='ABxy'" engine=innodb \ --number-of-queries=200 -uroot -p123 -verbose
--concurrency=100 并发连接数 +++++++++++++ 3.1 主机,存储,网络 主机 真实的硬件（PC Server）: DELL R系列 ，华为，浪潮，HP，曙光,联想 云产品：ECS、数据库RDS（云端数据库）、DRDS（分片）、PolarDB IBM 小型机 P6 570 595 P7 720 750 780 P8 CPU根据数据库类型 OLTP ，在线事务处理，做增册改的比较多 OLAP ，专门做数据仓库的，统计分析，查询较多。 IO密集型：线上系统，OLTP主要是IO密集型的业务，高并发 IO密集型： E系列（至强），主频相对低，核心数量多 CPU密集型：数据分析数据处理，OLAP，cpu密集型的，需要CPU高计算能力（i系列，IBM power系列） CPU密集型： I 系列的，主频很高，核心少 内存： 建议2-3倍cpu核心数量 （ECC，带检验的） 磁盘选择： SATA-III SAS Fc SSD（sata） pci-e ssd（直接插主板） Flash 主机 RAID卡的BBU(Battery Backup Unit)关闭，给raid卡供电的，断电后，还可以工作几分钟，对于mysql 不是很安全，只是为了提供性能，实际上写数据，又经过了raid卡的缓存，再到磁盘。 存储： 根据存储数据种类的不同，选择不同的存储设备 配置合理的RAID级别(raid5、raid10、热备盘) ，读的操作比较多的话，用raid5，写数据性能比较低，因为每次写数据都要做检验。 r0 :条带化 ,性能高 r1 :镜像，安全 r5 :校验+条带化，安全较高+性能较高（读），写性能较低 （适合于读多写少） r10：安全+性能都很高，最少四块盘，浪费一半的空间（高IO要求） 网络： 1、硬件买好的（单卡单口） 2、网卡绑定（bonding），交换机堆叠 以上问题，提前规避掉。 +++++++++++++++++++++++++= 3.2 系统方面的优化 Swap调整 mysql 尽量避免使用swap，直接使用内存，更快。 echo 0 >/proc/sys/vm/swappiness的内容改成0（临时）， vim /etc/sysctl.conf 上添加vm.swappiness=0 #（永久） sysctl -p IO调度策略 centos 7 默认是deadline （不用调整了） cat /sys/block/sda/queue/scheduler
有效的去降低CPU的wait 指标 内核关于IO方面的调度算法： cfq：先进先出 deadline：最后期限 noop：电梯模式 #临时修改为deadline(centos6) echo deadline >/sys/block/sda/queue/scheduler vi /boot/grub/grub.conf 更改到如下内容: kernel /boot/vmlinuz-2.6.18-8.el5 ro root=LABEL=/ elevator=deadline rhgb IO ： raid no lvm，数据切忌不要存储在lvm上面，系统可以用。 ext4或xfs，文件系统最好是xfs，可以提供高并发能力。 ssd，选择SSD磁盘，更好是Flash IO调度策略 提前规划好以上所有问题，减轻MySQL优化的难度。 3.3 数据库实例(参数) 3.3.1 Max_connections ***** （1）简介 Mysql的最大连接数，如果服务器的并发请求量比较大，可以调高这个值，当然这是要建立在机器能够支撑的情况下，因为如果连接数越来越多，mysql会为每个连接提供缓冲区，就会开销的越多的内存，所以需要适当的调整该值，不能随便去提高设值。 （2）判断依据 show variables like 'max_connections'; ### 默认151 单机最高不超过3000
show status like 'Max_used_connections'; ##最大使用的并发连接数是多少，就是自mysql 启动起来，最大的并发连接数是多少，如果说已经超过或者等于最大连接数，那么就是我们的 最大连接数设置小了，可以调大，最大连接数。
（3）修改方式举例 vim /etc/my.cnf Max_connections=1024 补充: 1.开启数据库时,我们可以临时设置一个比较大的测试值 2.观察show status like 'Max_used_connections';变化 3.如果max_used_connections跟max_connections相同, 那么就是max_connections设置过低或者超过服务器的负载上限了 3.3.2 back_log *** （1）简介 mysql能暂存的连接数量，当主要mysql线程在一个很短时间内得到非常多的连接请求时候它就会起作用，如果mysql的连接数据达到max_connections时候，新来的请求将会被存在堆栈中，等待某一连接释放资源，该推栈的数量及back_log,如果等待连接的数量超过back_log，将不被授予连接资源。 back_log值指出在mysql暂时停止回答新请求之前的短时间内有多少个请求可以被存在推栈中，只有如果期望在一个短时间内有很多连接的时候需要增加它 （2）判断依据 show full processlist ##查看当前有多少个正在发生的并发连接，没有被释放的。 发现大量的待连接进程时，就需要加大back_log或者加大max_connections的值
（3）修改方式举例 vim /etc/my.cnf back_log=1024 3.3.3 wait_timeout和interactive_timeout **** （1）简介 wait_timeout：指的是mysql在关闭一个非交互的（连接上来，什么也不操作的会话）连接之前所要等待的秒数 就是，超过多久时间，不干活的连接，给自动断掉，可以有效的去释放连接数。默认以秒为单位
interactive_timeout：指的是mysql在关闭一个交互的连接之前所需要等待的秒数，比如我们在终端上进行mysql管理，使用的即使交互的连接，就是即使你正在干活，当到了interactive_time设置的时间，mysql系统也会给你断掉，默认的是28800，可调优为7200。 wait_timeout:如果设置太小，那么连接关闭的就很快，从而使一些持久的连接不起作用 一般是 配 wati_timeout，而interactive_timeout 不需要配。 （2）设置建议 如果wait_timeout设置太大，容易造成连接打开时间过长，在show processlist时候，能看到很多的连接 ，一般希望wait_timeout尽可能低。 （3）修改方式举例 wait_timeout=60 interactive_timeout=1200 长连接的应用，为了不去反复的回收和分配资源，降低额外的开销。 一般我们会将wait_timeout设定比较小，interactive_timeout要和应用开发人员沟通长链接的应用是否很多。如果他需要长链接，那么这个值可以不需要调整。 3.3.4 key_buffer_size ***** (1) myisam 表的索引缓冲区 (2) 临时表的缓冲区（在join操作的时候会用到临时表） mysql> show status like "created_tmp%"; ## 看临时表创建的情况，单位，个数。
Created_tmp_tables/(Created_tmp_disk_tables + Created_tmp_tables)##在内存中创建的临时表个数 越高越好 Created_tmp_disk_tables/(Created_tmp_disk_tables + Created_tmp_tables) #在磁盘上创建的临时表的个数 Created_tmp_disk_tables/(Created_tmp_disk_tables + Created_tmp_tables) 控制在5%-10%以内 磁盘生成的临时表个数，占总个数的比例，在5%--------10%，算是OK 的。 mysql> show variables like "key_buffer_size%"; ### 一般会控制在 512M 以内。
一边调整这个，key_buffer_size ，一边再看 show status like "created_tmp%"; 这个的变化 只要符合这个 磁盘生成的临时表个数，占总个数的比例，在5%--------10%，算是OK 的。 以上公式并不能真正反映到mysql 里临时表的利用率，有些情况下产生的 Tmp_table_on_disk 我们完全不用担心，因此没必要过分关注，如果Created_tmp_disk_tables 的值大的离谱的话，那就要好好查一下，你的服务到底都在执行什么查询了。 当我们的业务当中，有大量的join、group by、子查询这种操作的话，可以把这个值设置得大一些，但是不会太大，一般都不会超过512M。 配置方法： vim /etc/my.cnf key_buffer_size=64M 3.3.5 max_connect_errors *** max_connect_errors是一个mysql 中与安全有关的计数器值，它负责阻止过多尝试失败的客户端，以防止暴力破解密码等，当超过指定次数，mysql 服务器将禁止 host 的连接请求，直到mysql 服务器重启 或 通过 flush hosts命令清空此host的相关信息，max_connect_errors 的值与性能并无太大关系。 配置方法：vim /etc/my.cnf [mysqld] max_connect_errors=2000 ++++++++++++++++++++++++ 3.3.6、sort_buffer_size *** (排序缓冲区，这个参数是管排序的，当你要用到排序的时候，会用到这个) 每个会话独享 (1)、简介 每个需要进行排序的线程分配该大小的一个缓冲区，增加这值加速 order by group by distinct union (2)、配置依据 Sort_Buffer_Size 并不是越大越好，由于是connection级的参数，过大的设置 + 高并发可能会耗尽系统内存资源。 (3)、配置方法：vim /etcmy.cnf [mysqld] sort_buffer_size=1M 3.3.7、join_buffer_size *** select a.name,b.name from a join b on a.id=b.id where xxx; 用于表间关联缓存的大小，和 sort_buffer_size 一样，该参数对应的分配内存，也是每个连接独享。 尽量在SQL 与方面进行优化，效果较为明显。 优化的方法：在 on 条件列加索引，至少应当是有MUL索引 read_buffer_size = 1M ** Mysql 读入缓冲区大小，对表进行 顺序扫描的请求将分配一个读入缓冲区，Mysql 会为它分配一段内存缓冲区，如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该 变量值 以及内存缓冲区大小，提高其性能。和 sort_buffer_size一样，该参数对就的分配内存也是每个连接独享。 read_rnd_buffer_siz = 1M ** Mysql的随机读（查询操作）缓冲区大小，当按任意顺序读取行时（例如，按照排序顺序），将分配一个随机读缓冲区，进行排序，Mysql会首先扫描一遍该缓冲区，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高 该值，但MySql会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。 注意：顺序读是指 根据索引的叶节点数据就能顺序地读取所需要的行数据，随机读是指，一般需要根据辅助索引叶节点中的主键寻找实际行数据，而辅助索引和主键所在的数据段不同，因此访问方式是随机。 ++++++++++++++++++++++++++ 3.3.7、max_allowed_packet ****** 控制的是数据包大小，从客户端到服务端，从外往里进， (1)、简介：Mysql根据配置文件会限制，server接受的数据包大小。 (2)、配置依据： 有时候大的插入和更新会受 max_allowed_packet限制，导致写入或者更新失败，更大值是1G，必须是1024的倍数。 (3)、配置文件： vim /etc/my.cnf [mysqld] max_allowed_packet=256M 3.3.8、thread_cache_szie ***** （可以理解为一个连接池的作用，它是一个线程缓存个数） (1)简介 服务器线程缓存，表示可以重新利用保存在缓存中线程的数量，当断开连接时，那么客户端的线程将被放到缓存中，以响应下一个客户，而不是销毁（前提是缓存数未达上限），如果线程重新被请求，那么请求将从缓存中读取，如果缓存中是空的或者是新的请求，那么这个线程将被重新创建，如果有很多新的线程，增加这个值可以改善系统性能。 (2)配置依据 通过比较 Connections 和 Threads_created状态的变量，可以看到这个变量的作用。 设置规则如下：（官方建议） 1G内存配置为8 ，2G内存配置为16 ，3G内存配置为32 ，4G或4G以上内存，可配置更大。 试图连接到MySQL（不管是否连接成功）的连接数：
Threads_cached：当前此刻 线程缓存中有多少空闲 线程 Theads_connected：当前已建立连接的数量，因为一个连接就需要一个线程，也可以看成 当前被使用的线程数 Threads_created：从最近一次服务启动，已创建线程的数量，如果发现Threads_created值过大，表明Mysql服务器一直在创建线程，这也是比较耗CPU SYS 资源，可以适当增加 配置文件中 thread_cache_size 的值。 Threads_running：当前激活的（非睡眠状态）线程数，并不是正在使用的线程数，有时候连接已建立，但是连接处于sleep状态。 配置方法： thread_cache_size=32 一般在架构设计阶段，根据你的内存大小，按最大值设置，设置thread_cache_size一个测试值，做压力测试，结合zabbix监控Threads_created，看一段时间内Threads_created状态的变化，如果Theads_created趋于平稳，说明对应参数设定是OK的，如果一直陡峭的增长，或者出现大量峰值，那么继续增加thread_cache_size（阈值512）的大小，在系统资源（内存）够用的情况下，如果Threads_created一值在增加，则说明单台服务器已不能满足，需要调整架构。 3.3.9、innodb_buffer_pool_size ******* innodb_buffer_pool_size ****** (1)、简介 对于Innodb表来说，innodb_buffer_pool_size的作用就相当于key_buffer_size对于MyISAM表的作用一样 (2)、配置依据 Innodb使用该 参数 指定 大小的内存来缓冲数据和索引 对于单独的Mysql数据库服务器，最可以把该值设置成物理内存的80%，一般我们建议不要超过物理内存70% (3)、配置方法： vim /etc/my.cnf [mysqld] innodb_buffer_pool_size=2048M
怎么判断 innodb_buffer_pool_size 不够用了? innodb_flush_log_at_trx_commit ****** show engine innodb status\G
配置方法：(一般设置为物理内存的50------60%) vim /etc/my.cnf innodb_buffer_pool_size=2048M 3.3.10、innodb_flush_log_at_trx_commit ******* (1)、简介 主要控制了innodb将log buffer中的数据写入日志文件，并flush 到磁盘的时间点，取值为分别为0\1\2 0，当事务提交时，不做日志写入操作，而是每秒钟将log buffer 中的数据写入日志文件，并flush 磁盘一次。 1,每次事务提交都会引起redo日志文件写入、flush 磁盘的操作，确保了事务的ACID 2,每次事务提交引起写入日志文件的动作，但每秒钟完成一次flush磁盘 (2)、配置依据 实际测试发现，该值对插入数据的速度 影响非常大，设置为 2 时，插入10000条记录只需要2秒，设置为0，插入10000第记录只需要1秒，而设置为1时，则需要229秒，因此MySQL手册中也建议尽量将插入操作合并成一个事务，这样可以大幅提高速度，根据MySQL官方文档，在允许丢失最近部分事务的危险的前提下，可以该值设为0 或 2 (3)、配置方法：vim /etc/my.cnf [mysqld] innodb_flush_log_at_trx_commit=1; +++++++++++++++++++++++++++++++++++++++++++++ 3.3.11、innodb_thread_concurrency *** (1)、简介：此参数用来设置innodb 线程的并发数量，默认值为 0 ，表示不限制 (2)、配置依据 在官方文档中，对于 innodb_thread_concurrency 的使用，也给出了一些建议，如果一个工作负载中，并发用户线程的数量小于64，建议设置 innodb_thread_concurrency=0 ，如果工作负载一直较为严重甚至偶尔达到顶峰，建设先设置innodb_thread_concurrency=128 ，并通过不段的降低这个参数，96，80，64等，直到发现能够提供最佳性能的线程数 例如，假设系统中通常有40到50个用户，但定期的数量增加至60，70，甚至200 ，你会发现，性能在80个并发用户设置时表现稳定，如果高于这个数，性能反而下降，在这种情况下，建设设置innodb_thread_concurrency 参数为80，以避免影响性能。 如果你不希望InnoDB使用的虚拟CPU数量比用户线程使用的虚拟CPU更多（比如20个虚拟CPU），建议通过设置innodb_thread_concurrency 参数为这个值（也可能更低，这取决于性能体现），如果你的目标是将Mysql与其他应用隔离，你可以考虑绑定mysqld进程到专有的虚拟CPU 但是需要注意的是，这种绑定，在mysqld进程一直不是很忙的情况下，可能会导致非最优的硬件使用率。在这种情况下，你可能会设置mysqld进程绑定的虚拟cpu，允许其他应用程序使用虚拟cpu的数量小。 定期检测和分析系统，负载量、用户数或者工作环境的改变可能都需要对innodb_thread_concurrency的设置进行调整。 设置标准： 当前系统cpu使用情况，均不均匀。top 当前的连接数，有没有达到顶峰 show status like 'threads_%'; show processlist; 配置方法： innodb_thread_concurrency=8 方法： 看top，观察每个cpu的各自的负载情况 发现不平均，先设置参数为cpu个数，然后不断增加（一倍）这个数值 一直观察这个top状态，直到达到比较均匀时，说明已经到位了。
看 show processlist; 里面有没有 64个，如果没有64个的话，调整这个参数也没什么大的作用。Mysql默认最多只能并发使用 64个CPU，所以超过64，才开始有必要去调整。 3.3.12、innodb_log_buffer_size **** 此参数确定日志文件所用的内存大小，以M为单位，缓冲区更大，能提高性能，对于较大的事务，可以增大缓存大小。 3.3.13、innodb_log_buffer_size=128M 设定依据： 大事务：存储过程调用 CALL 多事务 innodb_log_file_size=100M ***** 设置ib_logfile0 ib_logfile1 的大小，此参数确定数据日志文件的大小，以M为单位，更大的设置可以提高性能。 innodb_log_files_in_group = 3 ***** 为提高性能，MySQL可以 以循环方式将日志写到多个文件，推荐设置为3 。 在针对事务量比较大的情况下，可以酌情调整上面三个参数的值，innodb_log_buffer_size innodb_log_file_size innodb_log_files_in_group
++++++++++++++++++++++++++++++++++++++++ 做完raid后，如何评估IO 用dd命令 先判断 你的磁盘最大能承受多大的IO
如果你的mysql IO 快到顶点了 那些情况下，可以影响到IO， 首先，你可以考虑，是不是有太多的脏页刷新，如果有太多的脏页刷新，如果太多的脏页刷新，会导致Mysql IO暴增， 我们这时可以尝试，去提高 redo buff 或者 redo 文件的大小，以及组数的多少，设置完后，再看IO有没有降低，如果有降低，说明有用，可以再继续微调，直到IO降低到最低的水平。 bulk_insert_buffer_size = 8M ** 批量插入数据缓存大小，可以有效提高插入效率，默认为8M ++++++++++++++++++++++++++++++ 当前业务有大量的插入操作的话(别的操作很少)，推荐使用其他产品替代，用得多的是 tokuDB myrocks binary log ***** log-bin=/data/mysql-bin binlog_cache_size=2M //为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存 提高记录bin-log的效率，没有什么大事务，dml 也不是很频繁的情况下可以设置小一点，如果事务大而且多，dml操作也频繁，则可以适当的调大一点。前者建议是----- 1M，后者建议是：2----4M max_binlog_cache_size=8M //表示的是binlog能够使用的最大cache 内存大小 max_binlog_size=512M //指定binlog日志文件的大小，如果当前的日志大小达到max_binlog_size设定的值，还会自动创建新的二进制日志。你不能将该变量设置为大于1G或小于4096字节。默认值是1G ，当导入大容量的SQL文件时，建议关闭sql_log_bin，否则硬盘找不住，而且建议定期做删除。 expire_logs_days=7 //定义了mysql 清除过期日志的时间。 二进制日志自动删除的天数，默认值为0，表示"没有自动删除"。 log-bin=/data/mysql-bin binlog_format=row sync_binlog=1 双1标准（基于安全的控制）： sync_binlog=1 什么时候刷新binlog到磁盘，每次事务commit就刷新到磁盘。 innodb_flush_log_at_trx_commit=1 安全参数 ***** innodb_flush_method=(O_DIRECT,fsync) innodb_flush_method =fsync 在数据页需要持久化时，首先将数据写入OS bufer中，然后由OS决定什么时候写入磁盘 在redo buffer 需要持久化时，首先将数据写入OS buffer中，然后由OS决定什么时候写入磁盘，但是，如果innodb_flush_log_at_trx_commit=1的话，日志还是直接每次 commit 时，直接写入磁盘。 innodb_flush_method=O_DIRECT 在数据页需要持久化时，直接写入磁盘 在redo buffer需要持久化时，首先将数据写入OS buffer中，然后OS决定什么时候写入磁盘，但是，如果innodb_flush_log_at_trx_commit=1的话，日志还是直接每次 commit 时，直接写入磁盘。 最安全模式： innodb_flush_log_at_trx_commit=1 innodb_flush_method=O_DIRECT 最高性能模式： innodb_flush_log_at_trx_commit=0 innodb_flush_method=fsync 一般情况下，我们更偏向于安全。 双一标准： innodb_flush_log_at_trx_commit=1 sync_binlog=1 innodb_flush_method=O_DIRECT 3.4 优化后的压力测试 max_connections=1024 back_log=128 wait_timeout=60 interactive_timeout=7200 key_buffer_size=16M query_cache_size=64M query_cache_type=1 query_cache_limit=50M max_connect_errors=20 sort_buffer_size=2M max_allowed_packet=32M join_buffer_size=2M thread_cache_size=200 innodb_buffer_pool_size=1024M innodb_flush_log_at_trx_commit=1 innodb_log_buffer_size=32M innodb_log_file_size=128M innodb_log_files_in_group=3 binlog_cache_size=2M max_binlog_cache_size=8M max_binlog_size=512M expire_logs_days=7 read_buffer_size=2M read_rnd_buffer_size=2M bulk_insert_buffer_size=8M
添加索引，后的测试： use oldguo; alter table t100w add index idx(k2);
