linux下mysql调化随谈

1. 为什么需要高优？

机器发飙、自己主动、客户要求

2. 机器发飙

网站或客户端打开非常慢，而webserver负载低，或打开静态页面很快，那就很可能是数据库的问题

load average >= 5

Iowait >= 10

vmstat  procs(r b) 值较高

top中CPU的idle很小，sys或wait较高

服务器的swap严重

mysql的内存命中率很低，例如 myisam_key_read_hit_ratio或innodb_buffer_hit_ratio较低

3. 瓶颈定位

vmstat,iostat,top等系统级别的工具

explain

slow query

show status/show processlist/show engine innodb status

其他，如mysqlreport，profiling

vmstat\iostat定位

vmstat是一个查看虚拟内存(virtual memory) 使用情况的工具，使用vmstat命令可以得到关于进程、内存、内存分页、堵塞IO、traps及cpu活动信息。以下对vmstat的参数做解释。

字段说明：

Procs（进程）：

r: 运行队列中进程数量

b: 等待IO的进程数量

Memory（内存）：

swpd: 使用虚拟内存大小

free: 可用内存大小

buff: 用作缓冲的内存大小

cache: 用作缓存的内存大小

Swap：

si: 每秒从交换区写到内存的大小

so: 每秒写入交换区的内存大小

IO：（现在的Linux版本块的大小为1024bytes）

bi: 每秒读取的块数

bo: 每秒写入的块数

系统：

in: 每秒中断数，包括时钟中断。

cs: 每秒上下文切换数。

CPU（以百分比表示）：

us: 用户进程执行时间(user time)

sy: 系统进程执行时间(system time)

id: 空闲时间(包括IO等待时间)

wa: 等待IO时间

显示活跃和非活跃内存

 

使用-a选项显示活跃和非活跃内存时，所显示的内容除增加inact和active外，其他显示内容与例子1相同。

字段说明：

Memory（内存）：

inact: 非活跃内存大小（当使用-a选项时显示）

active: 活跃的内存大小（当使用-a选项时显示）

注：

vmstat 的输出那些信息值得关注?

io bo: 磁盘写的数据量稍大，如果是大文件的写，10M以内基本不用担心，如果是小文件写2M以内基本正常

 IO  bi 从块设备读入数据的总量（读磁盘）（每秒kb）。 bo 块设备写入数据的总量（写磁盘）（每秒kb） 这里我们设臵的bi+bo参考值为1000，如果超过1000，而且wa值较大 应该考虑均衡磁盘负载，可以结合iostat输出来分析。 

 memory  swpd 切换到内存交换区的内存数量(k表示)。如果swpd的值不为0，或者比较大，比如超过了100m，只要si、so的值长期为0，系统性能还是正常  

 free 当前的空闲页面列表中内存数量(k表示)   

 cache: 作为page cache的内存数量，一般作为文件系统的cache，如果cache较大，说明用到cache的文件较多，如果此时IO中bi比较小，说明文件系统效率比较好。  

 iostat可以显示CPU和I/O系统的负载情况及分区状态信息. 

直接执行iostat可以显示下面内容:

# iostat

Linux 2.6.9-8.11.EVAL (ts3-150.ts.cn.tlan)      08/08/2007

avg-cpu:  %user   %nice    %sys %iowait   %idle

             12.01    0.00        2.15    2.30       83.54

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn

hda               7.13       200.12        34.73     640119     111076

各个输出项目的含义如下:

avg-cpu段:

%user: 在用户级别运行所使用的CPU的百分比.

%nice: nice操作所使用的CPU的百分比.

%sys: 在系统级别(kernel)运行所使用CPU的百分比.

%iowait: CPU等待硬件I/O时,所占用CPU百分比.

%idle: CPU空闲时间的百分比.

Device段:

tps: 每秒钟发送到的I/O请求数.

Blk_read /s: 每秒读取的block数.

Blk_wrtn/s: 每秒写入的block数.

Blk_read:   读入的block总数.

Blk_wrtn:  写入的block总数.

explain

Explain 都能提供什么信息呢？

表的读取顺序

每个表都是如何读取的

可能用到哪些索引，实际使用了哪些索引

表是如何引用的

查询优化器从每个表中预计读取的记录数

其他额外信息，例如是否使用了内存表，是否引发排序等

slow query 

记录操作比较慢的语句，方便对系统进行排查

从show status/show processlist/show engine innodb status分析当前运行状态，从结果中发现问题

利用mysqlreport产生可读性报表 

利用profiling剖析一次查询瓶颈所在

mysql参数调优

有些选项是每个线程分配的，需要注意不能设置太大

innodb log buffer size不宜设置过大，如果事务量相对较大可以考虑稍微大点

mysql自身的query cache效率一般，可以采用memcached来补充

4. 查询优化

确保索引合理利用，尽量使用联合索引

适当加大查询缓存(query cache)

尽量减少交互次数

尽量使用固定格式的SQL语句，查询语句中少用运算或函数

缩短每个事务

使用适当的字段类型；适当的长度，有需要的时候再扩充

分解复杂查询为多个小查询

字符型字段采用前缀索引

其他 ……

注：

尽量以联合索引为主，去掉不需要的索引，提高数据更新效率；建立索引时需要根据索引的效率，如果基数太小，则考虑是否真的必须

尽量避免全表扫描

测试索引效果时，一定要关闭query cache和key buffer

t_char表实例，原先有80个字段，10个text字段，text字段实际最长才2k，拆分成非text字段表+2个text字段表，结果表空间文件大小从40g降到了总合不到8g，IO也快了不少。因此说，尽量把变长类型字段和定长型字段拆分开

5. 调优方法

选择合适的引擎

MyISAM

这个是默认类型,基于传统的ISAM类型，它是存储记录和文件的标准方法。与其他存储引擎比较，MyISAM具有检查和修复表格的大多数工具。MyISAM表格可以被压缩,而且它们支持全文搜索。它们不是事务安全的，而且也不支持外键。

InnoDB

ACID、外键、日志修复。InnoDB表格速度很快。如果需要一个事务安全的存储引擎或者是需要大量并发的INSERT或UPDATE，应该使用InnoDB表。

NDB

支持事务，用于cluster，实现高可用，但性能仍欠佳。

不直接执行 COUNT(*) – innodb

多个操作放在一起提交，但要注意事务不能太大

日志文件并非越大越好，需要考虑恢复和检查点

左连接时把数据量小的表放在前面

innodb_flush_log_at_trx_commit 可以尝试设置为 2，甚至是 0

导入数据时关闭 AUTOCOMMIT 以及 UNIQUE_CHECKS、FOREIGN_KEY_CHECKS

复杂的查询总是先用EXPLAIN来分析一下

定期执行OPTIMIZE TABLE整理碎片

用char来代替varchar，MyISAM是这样，InnoDB则相反

例子：一个表几十个字段，其中有将近10个text字段，实际最大长度只有2k，因此把变长字段单独放到几个小表里，表空间文件直接从40g变成总和不到8g，总体读写性能也提高不少

innodb表空间文件以及myisam的索引文件一段时间后，都会产生碎片，可以在维护期间执行optimize，如果一直是在线表，则可以考虑分表，定期倒腾表。在线表=>历史表=>永久表，避免产生碎片，并可以根据需要随时维护 

字符串尽量少用字符集，变长型字段尽量和定长型字段分开存储

When innodb_flush_log_at_trx_commit is set to 0, the log buffer is written out to the log file once per second and the flush to disk operation is performed on the log file, but nothing is done at a transaction commit. When this value is 1 (the default), the log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file. When set to 2, the log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it. However, the flushing on the log file takes place once per second also when the value is 2. Note that the once-per-second flushing is not 100% guaranteed to happen every second, due to process scheduling issues.