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 # userhost: dbuserdbuser # 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 | +-+-+-+ | 22494895 | 3 | 3 | | 32781498 | 3 | 1 | | 36392153 | 3 | 1 | | 31142213 | 3 | 2 | | 12447081 | 1 | 1 | 刚才执行的语句的profile已经被记录下来了. mysql show profiles; +-+-+-+ | query_id | duration | query | +-+-+-+ | 1 | 0.00005900 | select * from user_xxx limit 1000 | +-+-+-+ 还可以看cpu, io这些详细的情况. mysql show profile cpu, block io for query 1; +-+-+-+-+-+-+ | status | duration | cpu_user | cpu_system | block_ops_in | block_ops_out | +-+-+-+-+-+-+ | starting | 0.000017 | 0.001000 | 0.000000 | 0 | 0 | | checking query cache for query | 0.000007 | 0.000000 | 0.000000 | 0 | 0 | | checking privileges on cached | 0.000004 | 0.000000 | 0.000000 | 0 | 0 | | sending cached result to clien | 0.000018 | 0.000000 | 0.000000 | 0 | 0 | | logging slow query | 0.000002 | 0.000000 | 0.000000 | 0 | 0 | | cleaning up | 0.000011 | 0.000000 | 0.000000 | 0 | 0 | +-+-+-+-+-+-+,explain sql,表的读取顺序 可能用到哪些索引,实际用了哪些索引 查询优化器从每个表中预计读取的记录数(rows) 其他额外信息,尽量避免using filesort/using temporary,其他工具,用工具生产监控图,如cacti,解决,硬件 cpu,内存,磁盘,网络 软件 kernel (简洁的kernel,sysctl参数,io策略) mysql (编译参数,版本,patch) 文件系统 程序架构,硬件,升级硬件,立竿见影 更快cpu 更大内存 更快磁盘/阵列 应用服务器跟数据库服务器在同一内网, 至少采用千兆以太网互联. 升级bios, 阵列卡firmware.,软件,kernel 使用更稳定更高效的版本 编译最小化kernel,不用的module一律去掉. sysctl调整参数 io策略 title red hat enterprise linux as () root (hd0,0) kernel /boot/vmlinuz- ro root=label=/ elevator=deadline,软件,mysql 编译优化,采用静态编译,专用编译器等/doc/refman/5.1/zh/installing.html 采用合适的mysql版本. 通常新版本效率不如旧版本,但是可以利用新版本带来的新功能来提升系统性能. 第三方发布的patch 如google mysql patch,percona patch: /p/google-mysql-tools/ /,软件,mysql 4. 参数调整 key buffer = myisam 索引缓存 query cache = 查询结果缓存 sort/read/join buffer = 排序/全表扫描/连表缓存 slow query = 记录执行时间长的查询 tmpdir = 可以放在tmpfs上 innodb_buffer_pool_size = innodb 最重要设置,软件,文件系统 个人推荐使用xfs,比古老的ext3提供更好的性能. 另外,zfs可以提供文件系统级别的raid,snapshot,文件系统crc等等功能,可惜由于授权原因,暂时在linux上还看不到.,程序架构,垂直/水平切分服务