mysql管理之道-性能调优、高可用与监控的读书笔记

mysql5.5开始内置innodb并默认engine

充分利用CPU多核处理能力

my.cnf里添加
innodb_read_io_threads = 8;
innodb_write_io_threads = 8;

根据cpu核心来给出，如果是2个8核的默认可以这样配置
如果数据库读比写多的话可以

innodb_read_io_threads = 10;
innodb_write_io_threads = 6;

提高刷新脏页面数量和合并插入数量，改善磁盘I/O处理能力

innodb_io_capacity = 200; //默认值。单位是页，设置大小取决于磁盘的IOPS（每秒输入输出量或读写次数）

innodb_adpative_flushing 默认开启，可动态更新

innodb_buffer_pool 缓冲池（29页）

sublist of old blocks(旧数据) sublist of new blocks(热数据)。访问数据->如果缓冲区有数据直接返回，否则从磁盘读入缓冲区到old blocks 然后再移动到new blocks，并通过LRU（最近最少使用）算法来剔除旧数据页，mysql5.5.X开始.innodb_old_blocks_pct参数可以控制old blocks区域的数量，默认为37，占用整个缓冲池的3/8，当全表扫描一个达标或mysqldump时，innodb_old_blocks_pct设置的小写，如5，减少数据进入old blocks并移动到new blocks区域，从而让更多的热数据不被提出。在该过程中还可以设置参数innodb_old_blocks_time让old blocks里的数据不是码上移动到new blocks里，保证new blocks 数据不会马上被提出。innodb_buffer_pool_size 必须大于1GB，生成多innodb_buffer_pool实例才有效，最多支持64个innodb_buffer_pool实例
innodb_buffer_pool_instances = N;//来设置支持多bufferpool实例.
可用show engine innodb statusG; 查看状态

sysbench可以用mysql压力测试

https://github.com/akopytov/s...

自旋锁spin lock 轮询间隔

innodb_spin_wait_delay = 6;
set global spin_wait_delay = 6;

innodb支持创建压缩数据页

从mysql5.5.X版本开始支持innodb数据页压缩
create table compressed_table (

....

) engine = innodb
default charset = utf8mb4
ROW_FORMAT = COMPRESSED
KEY_BLOCK_SIZE = 8;

关闭innodb更新元数据的统计功能（mysql5.5.x开始支持）

innodb_stats_on_metadata = OFF;
innodb_stats_on_metadata 作用：每当查询information_schema元数据库里的表时，innodb还会随机提取其他数据库每隔表索引页的部分数据，从而更新information_schema.STATISTICS表，并返回刚才查询的结果。当你的表很大，且数量很多时，耗费的时间就会很长，很多经常不妨问的数据也会进入innodb）buffer_pool缓冲池里，那么就会污染缓冲池，并且analyze table和show table status 语句也会造成innodb随机提取数据

开启innodb的严格检查模式

innodb_strict_mode = 1; //默认是关闭的
当创建表、修改表、创建索引语句时，如果写法有错误，不会有警告信息，而是直接抛出错误；

中继日志relay-log可自我修复

relay_log_recovery = 1; //默认该属性是关闭的
如果slave从库当机后，加入relay-log损坏，导致一部分中继日志没有处理，则自动放弃所有未执行的relay-log，并且重新从master上获取日志，这样就保证了relay-log的完整性。默认该属性是关闭的

动态更改系统配置参数

innodb_file_per_table = 1; //默认关闭
从mysql5.1.x版本里，如果想采用独立表空间，那么就必须修改my.cnf，重启才可以生效
从mysql5.5.x开始可以动态修改改参数了，默认是关闭的，采用的是共享表空间:set global innodb_file_per_table = 1;
如果使用Barracuda格式的表压缩功能，必须开启独立表空间，共享表空间不支持该功能

采用mysql_upgrade升级授权表方式升级

修改innodb_fast_shutdown

默认为0；
1 不会做清楚脏页和插入缓冲区合并操作，也不会把脏页刷入磁盘
0 会做清楚脏页和插入缓冲区合并操作，并把脏页刷入磁盘，这样速度是最慢的
2 不会做清楚脏页和插入缓冲区合并操作，也不会把脏页刷入磁盘，而是刷入redo log事务日志里，待其下次启动mysql时再恢复，关闭速度是最快的
在做升级或降级操作中，应将其设置为0，保证数据的完整性，避免发生错误，支持动态修改(set global innodb_fast_shutdown = 0;)

关闭mysql服务

/etc/init.d/mysqld stop 或 mysqladmin -uroot -p shutdown

跳过mysql授权表启动mysql服务

mysqld --default-file=/etc/my.cnf --skip-grant-tables &

执行更新程序

/usr/local/mysql/bin/mysql_upgrade

再次关闭mysql服务

/etc/init.d/mysqld stop 或 mysqladmin -uroot -p shutdown

正常启动mysql

mysqld_safe --default-file=/etc/my.cnf --user=mysql &

半同步复制安装配置

cd mysql/plugin
查看存在semisync_master.so semisync_slave.so
install plugin rpl_semi_sync_master soname 'semisync_master.so'
install plugin rpl_semi_sync_slave soname 'semisync_slave.so'
初次加载插件后，mysql会将该插件记录到系统表mysql.plugin中，下次启动会自动加载该插件，无需再次执行上面命令
或者在my.cnf里配置
rpl_semi_sync_master_enabled = 1;
rpl_semi_sync_slave_enabled = 1;
这样以后启动mysql时会自动开启半同步复制功能

半同步主库

rpl_semi_sync_master_enabled = ON; //表示在master上已经开启半同步复制模式
rpl_semi_sync_master_timeout = 10 000; //默认参数为10000毫秒，即10秒，不过这个参数可以动态调整，用来表示如果主库在某次事务中的等待时间超过10秒，则降级为异步复制模式，不再等待slave从库。日过主库再次探测到slave从库恢复了，则自动再次回到半同步复制模式
rpl_semi_sync_master_wait_no_slave; //表示是否允许master每个事务提交后都要等待slave的接收确认信号。默认为NO，即每一个事务都会等待。如果为OFF，则slave追赶上后，也不会开启半同步复制模式，需要手工开启。
rpl_semi_sync_master_trace_level = 32; //用于开启半同步复制模式时的调试级别，默认为32.

半同步从库

rpl_semi_sync_slave_enabled = ON; //表示在slave上已经开启半同步复制模式
rpl_semi_sync_slave_trace_level = 32; //用于开启半同步复制模式时的调试级别，默认为32.

测试半同步复制模式是否正常工作（详见78页）

停止io_thread

stop slave io_thread; //切换到异步复制模式
start slave; //重新切换回半同步复制模式
单纯的sql错误是不会切换复制模式的，io_thread有关系，跟sql_thread没关系，也就是说slave从库接收完二进制日志后会给master主库一个确认，但它并不会关relay-log中继日志是否执行完。

linux下性能分析工具

vmstat
sar
iostat
netstat
free
ps
top
mpstat
dstat
collectl
淘宝的开源监控项目tsar