MyISAM与InnoDB
MyISAM的优点:
1.快速查询唯一键
2.支持全文索引
3.选择count（*）速度快
4.磁盘空间占用较少
缺点：
1.表级别的锁定，运用程序写入时间大于5%，表锁定会降低运用程序速度
2.不支持事务
3.有持久性问题，表崩溃后需要冗长的修复操作时间
InnoDB的对应优点：
1.支持事务
2.行级别锁定，可以并发写入不同行
事务控制的级别（原子性，一致性，隔离性，持久性），以多版本的并发控制技术（MVCC multiversion concurrency Control ），InnoDB、 Falcon、 PBXT引擎采用了该技术
4.多种联机备份策略
缺点：
1.选中count（*） 速度比较低
2.无全文索引
3.自增字段必须是表的第一个字段，当迁移时可能会导致问题
4.占用更多的空间
5.一些简单查询慢与MyISAM
MyISAM与InnoDB内存使用
1）影响缓冲区和缓存大小的指令，适用于所有存储引擎
2）仅影响MyISAM的存储引擎指令
3）仅影响InnoDB的存储引擎指令，以“innodb_”开头
4）各种资源（如连接数量等）限制指令
5）定义属性（字符集，路径等）指令
每个服务器及每个连接内存使用
=
read_buffer_size + // memory for sequential table scans
read_rnd_buffer_size + // memory for buffering reads
sort_buffer_size + // memory for in mem sortsthread_stack + // per connection memory
join_buffer_size // memory for in mem table join
per_server_memory =
tmp_table_size + // memory for all temp tables
max_heap_table_size + // max size of single temp table
key_buffer_size + // memory allocated for index blocks
max_tmp_table_size + // max memory for temp tablesinnodb_buffer_pool_size + // main cache for InnoDb data
innodb_additional_mem_pool_size +// InnoDb record structure cache
innodb_log_buffer_size + // log file write buffer
query_cache_size // compiled statement cache
Mysql 可以使用过的 最大内存
max_memory = (per_connection_memory * max_connection) + per_server_memory
优化MySQL内存使用mysqltuner.pl
以 iostat -d -c -x 2 查看I/O 性能
rrqm/s: 每秒进行 merge 的读操作数目.即 delta(rmerge)/s
wrqm/s: 每秒进行 merge 的写操作数目.即 delta(wmerge)/s
r/s: 每秒完成的读 I/O 设备次数.即 delta(rio)/s
w/s: 每秒完成的写 I/O 设备次数.即 delta(wio)/s
rsec/s: 每秒读扇区数.即 delta(rsect)/s
wsec/s: 每秒写扇区数.即 delta(wsect)/s
rkB/s: 每秒读K字节数.是 rsect/s 的一半, 为每扇区大小为512字节.(需要计算)
wkB/s: 每秒写K字节数.是 wsect/s 的一半.(需要计算)
avgrq-sz: 平均每次设备I/O操作的数据大小 (扇区).delta(rsect+wsect)/delta(rio+wio)
avgqu-sz: 平均I/O队列长度.即 delta(aveq)/s/1000 ( 为aveq的单位为毫秒).
await: 平均每次设备I/O操作的等待时间 (毫秒).即 delta(ruse+wuse)/delta(rio+wio)
svctm: 平均每次设备I/O操作的服务时间 (毫秒).即 delta(use)/delta(rio+wio)
%util: 一秒中有百分之多少的时间用于 I/O 操作,或者说一秒中有多少时间 I/O 队列是非空的.即 delta(use)/s/1000 ( 为use的单位为毫秒)
如果 %util 接近 100%,说明产生的I/O请求太多,I/O系统已经满负荷,该磁盘可能存在瓶颈.
%util是设备的利用率。如果它接近100%，通常说明设备能力趋于饱和(并不绝对，比如设备有写缓存)。有时候可能会出现大于100%的情况，这多半是计算时四舍五入引起的，但如果是磁盘阵列等多盘系统， 为具有并发IO操作的能力，也会出现大于100%的情况。
idle小于70% IO压力就较大了,一般读取速度有较多的wait.
同时可以结合vmstat 查看查看b参数(等待资源的进程数)和wa参数(IO等待所 用的CPU时间的百分比,高过30%时IO压力高)
另外 await 的参数也要多和 svctm 来参考.差的过高就一定有 IO 的问题.
avgqu-sz 也是个做 IO 调优时需要注意的地方,这个就是直接每次操作的数据的大小,如果次数多,但数据拿的小的话,其实 IO 也会很小.如果数据拿的大,才IO 的数据会高.也可以通过 avgqu-sz × ( r/s or w/s ) = rsec/s or wsec/s.也就是讲,读定速度是这个来决定的.
mysqltuner 给出需要优化的报告
其他优化选项
key_buffer_size
o Cache MyISAM Tables Indexes.
o Does Not cache data.
o Up to 30% of memory if using MyISAM only
myisam_recover
o Automatically repair corrupted MyISAM tables
after crash. BACKUP,FORCE is a good value.
myisam_sort_buffer_size
o Buffer used for building MyISAM indexes by Sort.
o 8MB-256MB are good values
innodb_buffer_pool_size
o The most important setting. Often 80%+ of memory is allocated here. 只是用InnoDB 可设置为可用内存的70%，有MyISAM表，可减少点给MyISAM
innodb_log_buffer_size
o Buffer for log files. Good Values 4MB-128MB
o Not only reduce writes but help contention 如果有很大的文本字段或blob字段可以设置大些，一般4M
innodb_log_file_size
o Size of redo log file. Larger logs better
performance but longer recovery 推荐256M恢复数据库与较高的运行性能之间的平衡点
innodb_flush_log_at_trx_commit
o Control Durability
o 1=flush and sync; 2=flush; 0=neither 设置为2时可减少磁盘写入及I/O负担
innodb_file_per_table
o Store each Innodb table in separate file. innoDB 的表空间
-x 1
2.6.33-fukai (fukai-laptop) _i686_ (2 CPU)
%user %nice%system %iowait %steal %idle
5.47 0.50 8.96 48.26 0.00 36.82
rrqm/s wrqm/s r/s w/s rsec/s wsec/savgrq-sz avgqu-sz await svctm %util
6.00 273.00 99.00 7.00 2240.00 2240.00 42.26 1.12 10.57 7.96 84.40
0.00 4.00 0.00 350.00 0.00 2068.00 5.91 0.55 1.58 0.54 18.80
#mysql #linux