用户工具

站点工具


service:techmag:201906_035:11

差别

这里会显示出您选择的修订版和当前版本之间的差别。

到此差别页面的链接

两侧同时换到之前的修订记录 前一修订版
后一修订版
前一修订版
service:techmag:201906_035:11 [2019/09/11 09:34]
-
service:techmag:201906_035:11 [2019/09/17 08:13] (当前版本)
-
行 13: 行 13:
   MySQL使用内存主要分为全局共享内存和线程独享内存。(本文只介绍对内存影响较大的参数,参数默认值在不同的版本可能不一致,本文采用5.7.18的默认值。)计算公式:\\   MySQL使用内存主要分为全局共享内存和线程独享内存。(本文只介绍对内存影响较大的参数,参数默认值在不同的版本可能不一致,本文采用5.7.18的默认值。)计算公式:\\
 <WRAP centeralign>​ <WRAP centeralign>​
-total_memory = global_memory + thread_memory+{{ :​service:​techmag:​201906_035:​1aa.png |}}
 </​WRAP>​ </​WRAP>​
 +
 {{ :​service:​techmag:​201906_035:​1.mysqlneicunjiegou.jpg |}} {{ :​service:​techmag:​201906_035:​1.mysqlneicunjiegou.jpg |}}
 <WRAP centeralign>​ <WRAP centeralign>​
行 23: 行 24:
   全局共享内存主要是MySQL实例以及底层存储引擎用来缓存各种全局运算和可共享的缓存信息等。计算公式:\\   全局共享内存主要是MySQL实例以及底层存储引擎用来缓存各种全局运算和可共享的缓存信息等。计算公式:\\
 <WRAP centeralign>​ <WRAP centeralign>​
-global_memory ≈ key_buffer_size + innodb_buffer_pool_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + query_cache_size\\+{{ :​service:​techmag:​201906_035:​23aaa.png |}}
 </​WRAP>​ </​WRAP>​
- 
   ■key_buffer_size:索引缓存大小。MyISAM索引缓存将MyISAM表的索引信息(.MYI文件)缓存在内存中,以提高其访问性能。决定索引处理的速度,尤其是索引读的速度。默认值为8M。如果应用系统中使用的表以MyISAM存储引擎为主,可以适当增加该参数的值,以便尽可能的缓存索引,提高访问的速度。可以通过检查状态值Key_read_requests和Key_reads,控制key_reads / key_read_requests的比例在1:​1000左右较为合理。如果请求并发数较大,为了进一步避免对索引缓存的争用,可为不同的索引键指定使用的索引缓存。如果应用系统中使用的表全部采用InnoDB存储引擎,仍需要定义一个索引缓存区,因为MySQL元信息与MyISAM定义相同。\\   ■key_buffer_size:索引缓存大小。MyISAM索引缓存将MyISAM表的索引信息(.MYI文件)缓存在内存中,以提高其访问性能。决定索引处理的速度,尤其是索引读的速度。默认值为8M。如果应用系统中使用的表以MyISAM存储引擎为主,可以适当增加该参数的值,以便尽可能的缓存索引,提高访问的速度。可以通过检查状态值Key_read_requests和Key_reads,控制key_reads / key_read_requests的比例在1:​1000左右较为合理。如果请求并发数较大,为了进一步避免对索引缓存的争用,可为不同的索引键指定使用的索引缓存。如果应用系统中使用的表全部采用InnoDB存储引擎,仍需要定义一个索引缓存区,因为MySQL元信息与MyISAM定义相同。\\
   ■innodb_buffer_pool_size:InnoDB缓存池大小。InnoDB Buffer Pool对 InnoDB存储引擎的作用类似于Key Buffer Cache对MyISAM存储引擎的影响,主要的不同在于InnoDB Buffer Pool不仅仅缓存索引数据,还会缓存表的数据,而且完全按照数据文件中的数据块结构信息来缓存。默认值128M。可以通过(Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100%计算得到InnoDB Buffer Pool的命中率。当缓存池大小大于1G时,将innodb_buffer_pool_instances设置大于1的值可以提高缓存读写的并发,能显著提高高I/​O负载时的性能。\\   ■innodb_buffer_pool_size:InnoDB缓存池大小。InnoDB Buffer Pool对 InnoDB存储引擎的作用类似于Key Buffer Cache对MyISAM存储引擎的影响,主要的不同在于InnoDB Buffer Pool不仅仅缓存索引数据,还会缓存表的数据,而且完全按照数据文件中的数据块结构信息来缓存。默认值128M。可以通过(Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100%计算得到InnoDB Buffer Pool的命中率。当缓存池大小大于1G时,将innodb_buffer_pool_instances设置大于1的值可以提高缓存读写的并发,能显著提高高I/​O负载时的性能。\\
行 33: 行 33:
 ==== 2.2 线程独享内存 ==== ==== 2.2 线程独享内存 ====
   线程独享内存主要用于各客户端连接线程存储各种操作的独享数据,如线程栈信息,分组排序操作,数据读写缓存,结果集暂存等等。计算公式:\\   线程独享内存主要用于各客户端连接线程存储各种操作的独享数据,如线程栈信息,分组排序操作,数据读写缓存,结果集暂存等等。计算公式:\\
-  thread_memory ≈ (read_buffer_size + read_rnd_buffer_size + sort_buffer_size + thread_stack + join_buffer_size + bulk_insert_buffer_size + binlog_cache_size + tmp_table_size) * connections\\+<WRAP centeralign>​ 
 +{{ :​service:​techmag:​201906_035:​4aa.png |}} 
 +</​WRAP>​ 
   ■read_buffer_size:顺序读取缓存大小。主要用于当需要顺序读取MyISAM表数据的时候的缓存(如无法使用索引的情况下的全表扫描,全索引扫描等)。默认值为128K。MySQL按照数据的存储顺序依次读取数据块,每次读取的数据块首先会缓存在Read Buffer中,当buffer空间被写满或者全部数据读取结束后,再将buffer中的数据返回给上层调用者,以提高效率。\\   ■read_buffer_size:顺序读取缓存大小。主要用于当需要顺序读取MyISAM表数据的时候的缓存(如无法使用索引的情况下的全表扫描,全索引扫描等)。默认值为128K。MySQL按照数据的存储顺序依次读取数据块,每次读取的数据块首先会缓存在Read Buffer中,当buffer空间被写满或者全部数据读取结束后,再将buffer中的数据返回给上层调用者,以提高效率。\\
   ■read_rnd_buffer_size:随机读取缓存大小。和顺序读取相反,主要用于非顺序读取(如排序后的顺序)数据的时候的缓存,但该缓存适用于任何存储引擎的数据。默认值为256K。\\   ■read_rnd_buffer_size:随机读取缓存大小。和顺序读取相反,主要用于非顺序读取(如排序后的顺序)数据的时候的缓存,但该缓存适用于任何存储引擎的数据。默认值为256K。\\
行 46: 行 49:
   案例背景:\\   案例背景:\\
   RDS部分实例内存使用率不断升高,无法释放,频繁触发预警值,即使对实例进行内存扩容后,内存使用率仍会继续升高,再次触发预警值,只能通过重启数据库实例进行内存释放。尤其是初始内存分配在2-8G左右的实例情况尤为突出。\\   RDS部分实例内存使用率不断升高,无法释放,频繁触发预警值,即使对实例进行内存扩容后,内存使用率仍会继续升高,再次触发预警值,只能通过重启数据库实例进行内存释放。尤其是初始内存分配在2-8G左右的实例情况尤为突出。\\
-  实例内存使用率不断增大,首先计算一下各主要内存相关参数配置是否合理。通过查看MySQL配置文件,计算结果为:total_memory = ((8388608 + 2576980378 + 67108864 + 0 + 0) + (475136 + 954437 + 477219 + 238592 + 262144 + 2097152 + 262144 + 8388608) * 114) / 1024 / 1024 / 1024 = 3.87G。考虑到计算公式是根据参数最大值计算,实际使用可能会更小,实例分配的最大内存为4个G,参数配置是合理的。\\+  实例内存使用率不断增大,首先计算一下各主要内存相关参数配置是否合理。通过查看MySQL配置文件,计算结果为:\\ 
 +total_memory = ((8388608 + 2576980378 + 67108864 + 0 + 0) + (475136 + 954437 + 477219 + 238592 + 262144 + 2097152 + 262144 + 8388608) * 114) / 1024 / 1024 / 1024 = 3.87G。考虑到计算公式是根据参数最大值计算,实际使用可能会更小,实例分配的最大内存为4个G,参数配置是合理的。\\
   其次需要确认是什么线程或事物消耗了大量内存。MySQL 5.7中performance_schema新增了几张表用于从不同维度查看内存消耗:\\   其次需要确认是什么线程或事物消耗了大量内存。MySQL 5.7中performance_schema新增了几张表用于从不同维度查看内存消耗:\\
-  用户:memory_summary_by_account_by_event_name\\ +<WRAP centeralign>​ 
-  主机:memory_summary_by_host_by_event_name\\ +{{ :​service:​techmag:​201906_035:​5aa.png |}} 
-  线程:memory_summary_by_thread_by_event_name\\ +</​WRAP>​ 
-  账号:memory_summary_by_user_by_event_name\\ +
-  全局:memory_summary_global_by_event_name\\+
   要开启所有内存的指标监控,需要通过instrument来实现,配置文件中添加performance_schema_instrument = '​memory%=counted'​。也可直接在线修改对应参数生效:update performance_schema.setup_instruments set enabled = '​yes'​ where name like '​memory%';​但在线修改参数后只对新增的内存对象有效。\\   要开启所有内存的指标监控,需要通过instrument来实现,配置文件中添加performance_schema_instrument = '​memory%=counted'​。也可直接在线修改对应参数生效:update performance_schema.setup_instruments set enabled = '​yes'​ where name like '​memory%';​但在线修改参数后只对新增的内存对象有效。\\
   打开内存监控后,通过查看全局内存使用情况发现2个问题:\\   打开内存监控后,通过查看全局内存使用情况发现2个问题:\\
-  select USER,​HOST,​EVENT_NAME,​CURRENT_NUMBER_OF_BYTES_USED from performance_schema.memory_summary_by_account_by_event_name order by CURRENT_NUMBER_OF_BYTES_USED desc;\\+select USER,​HOST,​EVENT_NAME,​CURRENT_NUMBER_OF_BYTES_USED ​\\ 
 +from performance_schema.memory_summary_by_account_by_event_name ​\\ 
 +order by CURRENT_NUMBER_OF_BYTES_USED desc;\\
   1、消耗最大的事务无疑是innodb_buffer_pool,但是发现该事务占用的内存为3G,与配置文件中配置的2.4G不一致。\\   1、消耗最大的事务无疑是innodb_buffer_pool,但是发现该事务占用的内存为3G,与配置文件中配置的2.4G不一致。\\
   memory/​innodb/​buf_buf_pool  \\   memory/​innodb/​buf_buf_pool  \\
阅读
service/techmag/201906_035/11.1568194470.txt.gz · 最后更改: 2019/09/11 09:34 由 -