Mysql问题整理

MySQL 5.7内存使用分析

PHPer 2018-02-14 1546 0 0
MySQL如何使用内存?首先,介绍MySQL使用内存的一些方法:1. 会话级别的内存消耗(连接私有内存):如sort_buffer_size等,每个会话都会开辟一个sort_buffer_size来...

MySQL如何使用内存?

首先,介绍MySQL使用内存的一些方法:

1. 会话级别的内存消耗(连接私有内存):如sort_buffer_size等,每个会话都会开辟一个sort_buffer_size来进行排序操作。

2. 全局的内存消耗(共享内存):例如:innodb_buffer_pool_size等,全局共享的内存段。

MySQL内存计算器:http://www.mysqlcalculator.com

1ba04b1f93_normal.png

全局内存消耗(共享内存)相关参数

1)innodb_buffer_pool_size

使用过Innodb的同学都知道,这块内存是Innodb存储引擎最重要的内存,直接关系到MySQL的读写性能。与MyISAM表只缓存索引,数据寄望于OS系统缓存不同。Innodb一般都会关闭OS的缓存,所有读到数据页和索引都直接存在数据库层的innodb_buffer_pool中的。

InnoDB缓冲池缓存着InnoDB表,索引,及其它辅助缓冲器中的数据。为了实现大容量读取操作的效率,缓冲池被分成可以容纳多行的页。为了缓存管理的效率,缓冲池被实现为页面的链接列表,很少使用的数据使用LRU算法的变体进行页面替换。

缓冲池的大小对于系统性能很重要:

  • InnoDB使用malloc()方法在服务器启动时为整个缓冲池分配内存,通常,推荐innodb_buffer_pool_size值为系统内存的50%至75%。innodb_buffer_pool_size可以在服务器运行时动态配置。
  • 在具有大量内存的系统上,你可以通过将缓冲池划分为多个缓冲池实例来提高并发性,其innodb_buffer_pool_instances系统变量用来定义缓冲池实例的数量。
  • 缓冲池太小可能会导致过多的交换,因为页面从缓冲池中刷新后仅在短时间内可能再次需要。
  • 缓冲池太大可能会因为内存竞争而导致交换。

2)innodb_additional_mem_pool_size

主要用于存放MySQL内部的数据结构和Innodb的数据字典,所以大小主要与表的数量有关,表越多值越大。庆幸的是这个值是可变的,如果不够用的话,MySQL会向操作系统申请的。该值默认8M,AWS所有规格都是统一的2M,由于这个值可以动态申请,所以我觉得2M应该是满足需求的。

3)innodb_log_buffer_size

这个是redolog的缓冲区,为了提高性能,MySQL每次写日志都将日志先写到一个内存Buffer中,然后将Buffer按照innodb_flush_log_at_trx_commit的配置刷到disk上。目前,我们所有实例的innodb_flush_log_at_trx_commit设置为了1,即每次事务提交都会刷新Buffer到磁盘,保证已经提交的事务,redo是不会丢的。AWS该值也设的是1(为了保证不丢数据),这个值的大小主要影响到刷磁盘的次数,设置的过小,Buffer容易满,就会增加fsync的次数,设置过大,占用内存。该值默认是8M,AWS所有规格统一128M,我觉得目前每次提交都会刷buffer,所以除非有大事务的情况,一般buffer不太可能被占满,所以没必要开的很大, 8M应该是满足需求的。

4)key_buffer_size

MyISAM表的key缓存,这个只对MyISAM存储引擎有效,所以对于我们绝大多数使用Innodb的应用,无需关心。

5)query_cache_size

MySQL对于查询的结果会进行缓存来节省解析SQL、执行SQL的花销,query_cache是按照SQL语句的Hash值进行缓存的,同时SQL语句涉及的表发生更新,该缓存就会失效,所以这个缓存对于特定的读多更新少的库比较有用,对于绝大多数更新较多的库可能不是很适用,比较受限于应用场景,所以AWS也把这个缓存给关了。我觉得这个值默认应该关闭,根据需求调整。

会话级别的内存消耗(连接私有内存)

上面这些就是MySQL主要的共享内存空间,这些空间是在MySQL启动时就分配的,但是并不是立即使用的。MySQL还有一部分内存是在用户连接请求到达时动态分配的,即每个MySQL连接都单独一个缓存,这部分缓存主要包括:

1)read_buffer_size

每个线程连续扫描时为扫描的每个表分配的缓存区的大小(字节)。如果进行多次连续扫描,可能还需要增加该值。默认值为1311072,只有当查询需要的时候,才分配read_buffer_size指定的全部内存。

2)read_rnd_buffer_size

当以任意顺序读取行时,可以分配随机读取缓冲区,通过该缓冲区读取行,以避免磁盘寻找。read_rnd_buffer_size系统变量决定缓冲器大小。

3)sort_buffer_size

每一个要做排序的请求,都会分到一个sort_buffer_size大的缓存,用于做order by和group by的排序,如果设置的缓存大小无法满足需要,MySQL会将数据写入磁盘来完成排序。因为磁盘操作和内存操作不在一个数量级,所以sort_buffer_size对排序的性能影响很大。由于这部分缓存是即使不用这么大,也会全部分配的,所以对系统内存分配开销是比较大的,如果是希望扩大的话,建议在会话层设置,默认值2M,AWS也是2M。...

立即注册

更新于:2018-02-14 01:04:10
    您需要登录后才可以评论。 立即注册
    相关内容

    软件使用总结

    2018年必须要吐槽下迅雷,开了迅雷网页打开很慢

    推荐内容

    分享几个好用的bt搜索bt资源下载网址网站

    影视电影剧集动漫综艺bt资源在线播放网址网站推荐分享

    全网最新bt磁力搜索引擎bt资源bt网站网址分享

    人气美女女优百度指数排行榜(波多野结衣,苍井空,三上悠亚,深田咏美,桥本有菜,北条...

    最强人造人深田咏美,业界的社交女王

    人生起起落落的三上悠亚,成名前的清纯风,怎么搭配出来的?

    业内第一大长腿,桥本有菜的腿究竟有多长

    波多野结衣,岛国业界著作最多的超级劳模

    日本AV界NO.1,苍井空是多少宅男的疯狂?

    美女女优视频混剪 This Girl-Laza Morgan

    怎样使用V2Ray代理和SSTap玩如魔兽世界/绝地求生/LOL台服/战地3/黑色沙漠/彩...

    sstap游戏代理教程 从此玩如魔兽世界/绝地求生/LOL台服/战地3/黑色沙漠/彩虹六...

    影视电影剧集动漫综艺bt资源在线播放网址网站推荐分享