MySQL性能诊断与实践.pptx

技术创新，变革未来MySQL性能诊断与实践 大纲 了解有关性能诊断的方法 介绍—些观测工具用法 分享两个案例 几个定律 Little’s Law (queueing theory) Amdahl’s Law (1967) Universal Scalability Law (1993) 通用方法 USE(Utilization Saturation and Errors) 火焰图on-cpu off-cpu 观测工具很重要 基线对比 MySQL体系结构 快速诊断 top 判断主机负载情况 dmesg | tail 是否存在oom-killer 或tcp drop等错误信息 vmstat 1 检查r、free、si、so、us, sy, id, wa, st列 mpstat -P ALL 1 检查CPU使用率是否均衡 pidstat 1 检查进程的CPU使用率，多核利用情况 iostat -xz 1 检查r/s, w/s, rkB/s, wkB/s, await, avgqu-sz, %util free -m 检查内存使用情况 sar -n DEV 1 检查网络吞吐量 sar -n TCP,ETCP 1 检查tcp连接情况active/s, passive/s, retrans/s MySQL诊断工具 error log slow log general log MySQL SHOW [SESSION|GLOBAL] STATUS SHOW PROCESSLIST InnoDB 存储引擎状态 SHOW ENGINE INNODB STATUS Explain 查看执行计划 performance schema 诊断步骤 1. 检查系统全局资源负载 2. 检查MySQL错误日志 3. 检查MySQL在做什么 检查InnoDB事务情况 检查MySQL复制状态 InnoDB InnoDB表必须有主键或唯—索引 SELECT t.table_schema, t.table_name FROM information_schema.tables t LEFT JOIN information_schema.table_constraints c ON (t.table_schema = c.table_schema AND t.table_name = c.table_name AND c.constraint_type IN (PRIMARY KEY,UNIQUE)) WHERE t.table_schema NOT IN (mysql,information_schema, ‘performance_schema) AND t.engine = ‘InnoDB AND c.table_name IS NULL; 主键应使用较小数据类型且有序 避免大事务(运行时间长或变更记录多) SELECT a.requesting_trx_id 被阻塞事务ID ,b.trx_mysql_thread_id 被阻塞线程ID, TIMESTAMPDIFF(SECOND,b.trx_wait_started,NOW()) ‘被阻塞秒数, b.trx_query 被阻塞的语句 , a.blocking_trx_id 阻塞事务ID ,c.trx_mysql_thread_id 阻塞线程ID,d.INFO 阻塞事务信息 FROM information_schema.INNODB_LOCK_WAITS a INNER JOIN information_schema.INNODB_TRX b ON a.requesting_trx_id=b.trx_id INNER JOIN information_schema.INNODB_TRX c ON a.blocking_trx_id=c.trx_id INNER JOIN information_schema.PROCESSLIST d ON c.trx_mysql_thread_id=d.ID ; 重要参数 max_connection innodb_buffer_pool_size Innodb_flush_neighbors Innodb_io_capacity Innodb_log_file_size innodb_thread_concurrency SQL优化 Note 优化的核心就是“少做事” 切勿盲目追求最优配置模板 避免过早优化 BPF是什么 BPF = Berkeley Packet Filter The Berkeley Packet Filter (BPF) provides a raw interface to data link layers, permittin