MySQL管理之道：性能调优、高可用与监控
跳转至： 导航、 搜索
目录

    1 MySQL 5.5介绍
    2 半同步复制
    3 故障诊断
    4 同步复制报错故障处理
    5 性能调优
    6 备份与恢复
    7 目前流行的4种HA架构
    8 批量管理服务器
    9 性能监控
    10 服务监控
    11 项目案例讲解

MySQL 5.5介绍

    InnoDB Plugin文件格式：Antelope --> Barracuda
    充分利用CPU多核：innodb_read/write_io_threads=8
    提高脏页刷新和合并插入数量：innodb_io_capacity=200
        自适应：innodb_adaptive_flushing
    InnoDB_Buffer_Pool
        innodb_old_blocks_pct/time
    数据恢复时间加快
        redo log最大可设为4G
    InneDB同时支持多个BufferPool实例：innodb_buffer_pool_instances
    可关闭自适应Hash索引
        ？Hash索引是基于表上已存在的Ｂ树索引建立的
        高并发下，可造成RW-latch争用，进而阻塞进程
        可通过“show engine innodb status\G;”监控SEMAPHORES，如果有很多waits，那么就应该关闭该功能
    可选择内存分配器（差距不是很大）
        export LD_PRELOAD=/usr/local/lib/libtcmalloc.so
    提高默认线程并发数：innodb_thread_concurrency innodb_concurrency_tickets=500
    预读算法
        线性 innodb_read_ahead_threshold=<pages>,默认56
        随机
    首次在Linux上实现了异步I/O：innodb_use_native_aio
        cat /proc/slabinfo | grep kio
    组提交（group commit）
        sync_binlog必须=0
    多个回滚段提升性能
    innodb_purge_threads
    添加删除缓冲和清除缓冲
        SET GLOBAL innodb_change_buffering = all;
    控制自旋锁轮询间隔：innodb_spin_wait_delay=6
    快速创建、删除、修改索引*
    支持创建压缩数据页
        CREATE TABLE ... ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
    *innodb_stats_on_metadata
    安全性、稳定性的显著改变
        复制功能加强
        relay-log可自我修复
        set global innodb_strict_mode=1;
    动态更改系统配置参数
    SQL语法的改变
        存储过程支持limit变量
    5.1升级为5.5
        MySQL_update

半同步复制

    install plugin rpl_semi_sync_master_soname 'semisync_master.so';
    SET GLOBAL rpl_semi_sync_master_enabled=ON;
    功能验证：
        略
    性能测试

故障诊断

    性能主要因素：磁盘I/O
    CPU性能指标：%us %sy %wa %id %ni
    磁盘性能指标
        I/O等待
        队列平均长度
        平均等待时间
        每秒传输数量（多少次I/O操作，包括读写）
        每秒读写块的数量
        每秒读写字节的数量
    开源工具：
        dstat（Python编写的？）
        sysstat（iostat、mpstat、cifsiostat、sar）
    “连接数过多”
        闲置连接？
        整理表空间碎片
        预热
            my.cnf：innodb_buffer_pool_dump_at_shutdown=1（必须pkill mysql正常关闭）
    加内存!
    子查询问题
        5.6：强劲优化？（自动改写成join？）
    分析binlog，查找读写操作频繁的表？（使用awk）
        mysqlbinlog ... | awk '/###/{if($0~/UPDATE|INSERT|DELETE/)count[$2" "$NF]++}END{for(i in count)print i,"\t",count[i]}' | column -t | sort -k3nr | more
    谨慎设置binlog_format=MIXED
        p92 如果采用默认隔离级别（可隔离读），设置＝ROW；如果是读提交，MIXED和ROW的效果是一样的
            5.6 binlog_row_image=minimal
    未设置swap分区导致内存耗尽死机
        echo 0 > /proc/sys/vm/swappiness
            只能减少swap概率，并不能避免Linux swap
        内存监控：当使用率>90%，重启MySQL
    ＊故障（主从）切换时事件调度器注意事项
        p96 event只能在master上触发，不能在slave，如果slave上触发了同步复制就会坏掉
    误删InnoDB ibdata和ib_logfile
        找到mysqld的PID
        ll /proc/<pid>/fd | egrep 'ib_|ibdata'
        FLUSH TABLES WITH READ LOCK;
        set global innodb_max_dirty_pages_pct=0;
        cd /proc/<pid>/fd
            cp 10 /u2/mysql/data/ib_logfile1（哦，文件虽然被误删除，对应inode还在）
            ...
        cd /u2/mysql/data/ && chown mysql:mysql ib*
        /etc/init.d/mysql restart
    ＊模拟Oracle闪回（这里的大段代码真无聊～）

同步复制报错故障处理

    避免在master上执行大事务
    set global slave_exec_mode='IDEMPOTENT';
    如何验证主从一致
        Maatkit：mk-table-checksum
    p124 想在slave上忽略一个表，不要用binlog_ignore_db，用replicate-ignore-db=testdb代替
    不支持低版本向高版本同步？
    5.5.20+ MySQL>reset slave all;

性能调优

    5.6一个表里多个字段可以是TIMESTAMP，5.5只能有一个 ？
    p152 pt-online-schema-change（实际就是创建了一个新表，st）
        实际上还是MVCC没实现好吧
    p177 Read Commited：会导致不可重复读、虚读和第二类丢失更新等并发问题
    p179 like '%xxx%'：覆盖索引
    p182 加快count(*)
        count(辅助索引)
        count(distinct x)
    ON REPLICATE KEY UPDATE
    my.cnf配置文件调优
        per_thread_buffers优化＊
        global_buffers优化＊
    5.6同步复制新特性
        会通过内部GTID机制自动找同步点
        多个库之间可多线程复制

备份与恢复

    --dump-slave 降低主库压力
        master_LOG_POS
    新工具mydumper
    热备份
        Percona xtrabackup

目前流行的4种HA架构

    自带的Replication
        Keepalived
        MMM（主主，By Google）
    Heartbeat + DRBD
    RHCS共享存储架构

批量管理服务器
性能监控
服务监控
项目案例讲解