MySQL - 性能优化

• MySQL - 性能优化
• 1. innodb_io_capacity(来自MySQL45讲第12讲)
  • 1.1 查询
  • 1.2 最好设置成磁盘的IOPS，获取IOPS命令如下
• 2. 查询脏页比例(来自MySQL45讲第12讲)
• 3. 重建表(来自MySQL45讲第13讲)

MySQL - 性能优化

1. innodb_io_capacity(来自MySQL45讲第12讲)

innodb_io_capacity介绍

1.1 查询

show variables  like '%innodb_io_capacity%';

1.2 最好设置成磁盘的IOPS，获取IOPS命令如下

fio -filename=tmp -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=test 

2. 查询脏页比例(来自MySQL45讲第12讲)

show status like '%Innodb_buffer_pool_pages_dirty%';
show status like '%Innodb_buffer_pool_pages_total%';
Innodb_buffer_pool_pages_dirty / Innodb_buffer_pool_pages_total

3. 重建表(来自MySQL45讲第13讲)

数据删除、数据按非递增（递增不需要连续）插入，会造成索引树不紧凑，数据页不能100%利用
以下命令，MySQL会自动完成转存数据、交换表名、删除旧表的操作。
如果是Offline的话，在整个DDL过程中会阻塞DML，表A中不能有更新。如果DDL在MySQL5.6之后，才是Online DDL，可以在DLL过程中进行增删该查。

alter table A engine=InnoDB

重建表的这个语句alter table t engine=InnoDB，其实隐含的意思是

# inplace的意思是在引擎层新建一个tmp_file然后存数据，没有把数据移动到tmp_table，所以是一个“原地”操作
alter table t engine=innodb,ALGORITHM=inplace;

还有另外一种

# 在server层，把数据移动到tmp_table
alter table t engine=innodb,ALGORITHM=copy;

从MySQL 5.6版本开始

alter table t engine = InnoDB（也就是recreate），是Online DDL

analyze table t 其实不是重建表，只是对表的索引信息做重新统计，没有修改数据，这个过程中加了MDL读锁；
optimize table t 等于recreate+analyze。