MySQL的一些认知

最新推荐文章于 2024-10-28 14:51:05 发布

danssion

最新推荐文章于 2024-10-28 14:51:05 发布

阅读量372

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/danssion/article/details/34426977

MyISAM与InnoDB

MyISAM的优点:

1.快速查询唯一键

2.支持全文索引

3.选择count（*）速度快

4.磁盘空间占用较少

缺点：

1.表级别的锁定，运用程序写入时间大于5%，表锁定会降低运用程序速度

2.不支持事务

3.有持久性问题，表崩溃后需要冗长的修复操作时间

InnoDB的对应优点：

1.支持事务

2.行级别锁定，可以并发写入不同行

3.支持全部ACID的多版本控制。ACID 事务控制的级别（原子性，一致性，隔离性，持久性），以多版本的并发控制技术（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）定义属性（字符集，路径等）指令

每个服务器及每个连接内存使用

per_connection_memory =

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 &times; ( 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 的表空间