Mysql平台优化参考.ppt

Mysql优化 linux平台 Linux 下Mysql优化 需求 监控/分析 解决 总结 需求 从应用提需求的时候就要开始优化 1 table engine的选择 2 表结构 Table Engine 选择 table engine: Myisam Innodb 一般情况下我们只用到上述两种table engine 事务支持 = Innodb 并发读写 = Innodb 其他情况 = Myisam 表结构 Myisam 空间换速度? Row_format = Fixed 不用varchar,用char 不用text,blob 表结构 Innodb 情况与Mysiam正好相反,空间==速度 用varchar,不用char primary key很重要,尽量缩短它的长度. 监控 OS 级别(vmstat,iostat,top等等) slow query log show status, show processlist, show innodb status profiling (mysql 5.0+) explain sql 其他工具,如cacti 监控 os 级别 vmstat -S M 3 监控 os 级别 iostat -dkx 3 监控 slow log # Time: 090220 15:28:45 # User@Host: dbuser[dbuser] @ [] # Query_time: 11 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 delete from data_stat where pkey IN (0013a96190db, 00e04d0706f8, 00e04d0710b0, 00e04d074a41, 5JT46KT7, PFD210S2C2UVJE); 监控 slow log 几个slow log 分析工具 mysqldumpslow - mysql 自带 mysqlsla - 据说不错 mysql-explain-slow-log mysql-log-filter myprofi 监控 show status/processlist show [global] status 关注key hit ratio, qcache hit ratio,lock,show query,innodb buffer hit ratio,innodb data/log write/read show processlist 关注异常状态的查询,或者耗时长的查询 show innodb status 关注log flush状态,锁状态,事务状态,内存分配消耗 监控 show status/processlist show innodb status 显示不全? 启用innodb_monitor: CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB; 相关信息会输出到.err日志文件 停止innodb_monitor: DROP TABLE innodb_monitor; Profiling Mysql (5.0+) 要使用profiling, 在编译的时候加入--enable-profiling, 然后打开profile功能. mysqSET profiling=1; 执行一条语句看看 mysql select * from user_xxx limit 1000; +----------+-----+------+ | a | b | c | +----------+-----+------+ || 3 | 3 | || 3 | 1 | || 3 | 1 | || 3 | 2 | || 1 | 1 | 刚才执行的语句的profile已经被记录下来了. mysql show profiles; +----------+------------+--------------------------------------+ | Query_ID | Duration | Query | +----------+------------+--------------------------------------+ | 1 | 0| select * from user_xxx limit 1000 | +----------+------------+---------------------