[Lnmpa] Mysql 内存分配&参数配置&优化方案详解

  • 时间:
  • 浏览:3
  • 来源:大发彩神UU直播现场_大发神彩UU直播现场官方

MYSQL_Service_Connect_Mem = read_buffer_size (0.25MB) + read_rnd_buffer_size (0.5MB) + sort_buffer_size (0.5MB) + join_buffer_size (0.25MB) + binlog_cache_size (1MB) + thread_stack (0.25MB) = 2.75MB

MySQL服务器用来作普通索引扫描、范围索引扫描和不使用索引而执行全表扫描哪些地方地方操作所用的缓存大小。通常,获取最快连接的法子是增加索引。当都上能 增加索引的以前,使全连接调快的法子是增大join_buffer_size参数。对于执行全连接的两张表,每张表都被分配一块连接内存。对于没办法 使用索引的多表冗杂连接,都上能 多块连接内存。通常来说,都上能 也能 将此参数在全局上设置那我较小的值,而在都上能 执行大连接的会话中在会话级别进行设置。默认值是256KB。

假如有一天再配置每个连接占用内存大小:MYSQL_Service_Connect_Mem = read_buffer_size + read_rnd_buffer_size + sort_buffer_size + join_buffer_size + binlog_cache_size + thread_stack,计划512MB分配给Mysql使用,以前可能分配了112MB的内存,剩下384MB内存,这个以前就都上能 考虑最大的连接数,可能这个参数对连接内存影响很大,计划最大连接数为128个,没办法 每个连接的内存就占用3MB左右。

对于1G内存的机器,可能不使用MyISAM表,推荐值是16M(8-64M)

MYSQL优化举例

类似一台2G内存的服务器,安装有Nginx、Apache、Php、Mysql等的环境,准备划分128MB~512MB的内存区间给MySQL使用。

比如查看当前系统中系统中

join_buffer_size 参数:

这个值的大小主要影响到刷磁盘的次数,设置的过小,Buffer容易满,就会增加fsync的次数,设置过大,占用内存。该值默认是8M,当事人实在目前每次提交一定会刷buffer,统统 除非有大事务的情形,一般buffer不太可能被占满,统统 没必要开的很大, 8M应该是满足需求的。

为甚也能知道tmp_table_size的设置有无合理呢,都上能 也能 通过 Created_tmp_disk_tables / Created_tmp_tables * 5000% 计算缓存命中率,并根据命中率来调整 tmp_table_size 参数大小进行优化。值都上能 也能 用以下命令查得:show global status like ‘created_tmp%’;

怎样才能查看设置的binlog_cache_size有无够用,都上能 也能 使用命令:show status like ‘binlog_%’;

是MySql读入缓冲区大小,对表进行顺序扫描的请求将分配那我读入缓冲区,MySql会为它分配一段内存缓冲区,read_buffer_size变量控制这个缓冲区的大小,可能对表的顺序扫描请求非常频繁,假如有一天你认为频繁扫描进行得太慢,都上能 也能 通过增加该变量值以及内存缓冲区大小提高其性能。

首先就都上能 也能 配置 MYSQL_Service_Mem( key_buffer_size + innodb_buffer_pool_size + tmp_table_size + query_cache_size + innodb_additional_mem_pool_size + innodb_log_buffer_size )这那我参数,保持在128MB左右。

read_rnd_buffer_size 参数:

也都上能 也能 用SQL命令:select (@@key_buffer_size +@@innodb_buffer_pool_size+ @@tmp_table_size +@@query_cache_size +@@innodb_additional_mem_pool_size +@@innodb_log_buffer_size )/1024/1024 as “MYSQL_Service_Mem”;

thread_stack 参数:

MYSQL_Service_Mem = key_buffer_size + innodb_buffer_pool_size + tmp_table_size + query_cache_size + innodb_additional_mem_pool_size + innodb_log_buffer_size

背景

介绍Lnmpa、Lamp、Lnmp等环境的教程统统 ,假如有一天在默认安装后,很容易出現 内存占用光,出現 OOM等间题,为了更好的让服务器稳定运行,都上能 对每个应用服务进行内存配置方面的优化。

query_cache_size 参数:

直接在MysQL命令提示符里输入就都上能 也能 输出结果。

原文地址:http://blog.daobidao.com/lnmpa-mysql-5-6-mem-configure-optimize.html

可能设置不需要 句子,会比较消耗内存资源(Cache本质很久内存),更加都上能 注意的是:binlog_cache是一定会全局的,是按SESSION为单位独享分配的,也很久说当那我线程池池开始那我事务的以前,Mysql就会为这个SESSION分配那我binlog_cache。

类似innodb_log_buffer_size缓存事务日志,binlog_cache_size缓存Binlog,不同的是这个是每个线程池池单独那我,主要对于大事务有较大性能提升。默认32K。

+——————————–+———+

| Variable_name   | Value |

+———————————-+———+

| Created_tmp_disk_tables | 21197 |

| Created_tmp_files   | 58  |

| Created_tmp_tables  | 1771587 |

+——————————–+———–+

对MyISAM表起作用。即使你不使用MyISAM表,假如有一天内部的临时磁盘表是MyISAM表,也要使用该值。都上能 也能 使用检查情形值created_tmp_disk_tables得知详情。

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

一些参数优化建议:http://blog.daobidao.com/mysql-optimize-advise.html

没办法 连接数max_connections都上能 也能 设置为:384/2.75=139个

可知道有2853四个请求,有269个请求在内存中没办法 找到直接从硬盘读取索引。未命中缓存的概率为:0.94%=269/28535*5000%。一般未命中概率在0.1之下比较好,目前已远远大于0.1,证明效果不好。若命中率在0.01以下,则建议适当的修改key_buffer_size值。

MYSQL_Service_Connect_Mem = read_buffer_size + read_rnd_buffer_size + sort_buffer_size + join_buffer_size + binlog_cache_size + thread_stack

直接在MysQL命令提示符里输入就都上能 也能 输出结果。

比如查看系统当前key_read和key_read_request值为:

innodb_buffer_pool_size 参数:(InnoDB)

未命中率为:Created_tmp_disk_tables / Created_tmp_tables * 5000% =1.20%,数字越低越好。

MySQL介绍

MySQL是那我关系型数据库管理系统,由瑞典MySQL AB 公司开发,目前属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一,在 WEB 应用方面,MySQL是最好的 RDBMS (Relational Database Management System,关系数据库管理系统) 应用软件。

+————————-+———–+

| Variable_name | Value |

+————————-+———–+

| Qcache_hits | 1892463 |

| Qcache_inserts | 35627 |

+————————-+———–+

主要针对InnoDB表性能影响最大的那我参数。功能与Key_buffer_size一样。InnoDB占用的内存,除innodb_buffer_pool_size用于存储页面缓存数据外,另外正常情形下还有相当于8%的开销,主要用在每个缓存页帧的描述、adaptive hash等数据特征,可能一定会安全关闭,启动时都上能 恢复句子,都上能 另开相当于12%的内存用于恢复,两者相加一定会差不需要 21%的开销。假设:12G的innodb_buffer_pool_size,最多的以前InnoDB就可能占用到14.5G的内存。若系统都上能 也能 16G,假如有一天只运行MySQL,且MySQL只用InnoDB,没办法 为MySQL开12G,是最大限度地利用内存了。

设置太小句子,可能用户提交那我“长事务(long_transaction)”,比如:批量导入数据。没办法 该事务必然会产生统统 binlog,那我cache可能过低用(默认binlog_cache_size是32K),过低用的以前mysql会把uncommitted的每种写入临时文件(临时文件cache的效率必然没办法 内存cache高),等到committed的以前才会写入正式的持久化日志文件。

当以任意顺序读取行时,都上能 也能 分配随机读取缓冲区,通过该缓冲区读取行,以处里磁盘寻找。read_rnd_buffer_size系统变量决定缓冲器大小。但MySql会为每个客户连接发放该缓冲空间,统统 应尽量适当设置该值,以处里内存开销过大。

命中率 98.17% = 1892463/(1892463 +35627 )*5000,命中率越高越好。

innodb_additional_mem_pool_size 参数:

自从Msql 5.6.3 开始,就都上能 这个参数。

key_buffer_size 参数:(MyISAM)

可能你使它不需要 ,系统将开始换页假如有一天真的调快了。严格说是它决定了数据库索引处里的效率,尤其是索引读的效率。

会话级别的内存消耗(连接私有内存)[Mysql 单个连接最大占内存,单位 M]:

也都上能 也能 用SQL命令:select (@@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size + @@join_buffer_size + @@binlog_cache_size + @@thread_stack)/1024/1024 as “MYSQL_Service_Connect_Mem”;

这个参数用来设置 InnoDB 存储的数据目录信息和其它内部数据特征的内存池大小。应用线程池池池里的表不需要 ,你都上能 在这里分配不需要 的内存。对于那我相对稳定的应用,这个参数的大小也是相对 稳定的,也没办法 必要预留非常大的值。可能 InnoDB 用光了这个池内的内存, InnoDB 开始从操作系统分配内存,假如有一天往 MySQL 错误日志写警告信息。默认值是 1MB ,当发现错误日志中可能有相关的警告信息时,就应该适当的增加该参数的大小。

+—————————————+———+

| Innodb_buffer_pool_read_requests | 1283826 |

| Innodb_buffer_pool_reads | 519 |

+—————————————+———+

MySQL内存计算法子

全局内存消耗(共享内存)[单纯MYSQL服务(不计算连接占用内存)单位 M]:

tmp_table_size 参数:

这是InnoDB存储引擎的事务日志所使用的缓冲区。类似Binlog Buffer,InnoDB在写事务日志的以前,为了提高性能,也是先将信息写入Innofb Log Buffer中,当满足innodb_flush_log_trx_commit参数所设置的相应条件(可能日志缓冲区写满)以前,才会将日志写到文件 (可能同步到磁盘)中。都上能 也能 通过innodb_log_buffer_size 参数设置其都上能 也能 使用的最大内存空间。

控制内存临时表的最大值,超过限值后就往硬盘写,写的位置由变量 tmpdir 决定。

+——————-+———————–+

| Variable_name | Value |

+——————-+———————–+

| Key_read_requests | 28535 |

| Key_reads | 269 |

+——————-+———————–+

+———————–+———–+

| Variable_name | Value |

+———————–+———–+

| Binlog_cache_disk_use | 0 |

| Binlog_cache_use | 120402264 |

+———————–+———–+

运行情形Binlog_cache_use 表示binlog_cache内存法子被用上了几块次,Binlog_cache_disk_use表示binlog_cache临时文件法子被用上了几块次。Binlog_cache_disk_use现在等于0,表示内存cache是够用的,从来都上能 使用到临时文件。

sort_buffer_size 参数:

256K适用于512MB内存,1GB内存则都上能 也能 设置成1M,依次类推即可。

直接在MysQL命令提示符里输入就都上能 也能 输出结果。

每那我要做排序的请求,一定会分到那我sort_buffer_size大的缓存,用于做order by和group by的排序,可能设置的缓存大小无法满足都上能 ,MySQL会将数据写入磁盘来完成排序。可能磁盘操作和内存操作没哟那我数量级,统统 sort_buffer_size对排序的性能影响很大。可能这每种缓存是即使不需要没办法 大,也会全每种配的,统统 对系统内存分配开销是比较大的,可能是希望扩大句子,建议在会话层设置,默认值2M。

binlog_cache_size 参数:

通过设置tmp_table_size选项来增加一张临时表的大小,类似做高级分组排序操作生成的临时表。可能调高该值,MySQL同时将增加heap表的大小,可达到提高联接查询效率的效果,建议尽量优化查询,要确保查询过程中生成的临时表在内存中,处里临时表过大导致 生成基于硬盘的MyISAM表。

innodb_log_buffer_size 参数:

互联网一定会类似计算MysqL占用内存的网站:http://www.mysqlcalculator.com/

其命中率99.959% =(1283826-519)/1283826*5000% 命中率越高越好。

Query Cache的使用都上能 多个参数配合,其中最为关键的是query_cache_size和query_cache_type,前者设置用于缓存 ResultSet的内存大小,后者设置在何场景下使用Query Cache。query_cache_type都上能 也能 设置为0(OFF),1(ON)可能2(DEMOND),分别表示删剪不使用query cache,除显式要求不使用query cache(使用sql_no_cache)之外的所有的select都使用query cache,都上能 也能 显示要求才使用query cache(使用sql_cache)。可能Qcache_lowmem_prunes的值非常大,则表明总是出現 缓冲. 可能Qcache_hits的值也非常大,则表明查询缓冲使用非常频繁,此时都上能 增加缓冲大小;

read_buffer_size 参数:

MYSQL服务最大使用内存(富含连接访问数据库)单位 M:

另外InnoDB和 MyISAM 存储引擎不同, MyISAM 的 key_buffer_size 都上能 也能 缓存索引键,而 innodb_buffer_pool_size 却都上能 也能 缓存数据块和索引键。适当的增加这个参数的大小,都上能 也能 有效的减少 InnoDB 类型的表的磁盘 I/O 。

为甚也能知道innodb_buffer_pool_size的设置有无合理呢,都上能 也能 通过 (Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 5000% 计算缓存命中率,并根据命中率来调整 innodb_buffer_pool_size 参数大小进行优化。值都上能 也能 用以下命令查得:show status like ‘Innodb_buffer_pool_read%’;

MYSQL_Service_Max_Mem = key_buffer_size + innodb_buffer_pool_size + tmp_table_size + query_cache_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + max_connections * ( read_buffer_size + read_rnd_buffer_size + sort_buffer_size + join_buffer_size + binlog_cache_size + thread_stack )

512K使用于 512MB内存,1GB内存则都上能 也能 设置成1MB,依次类推即可。

为甚也能知道key_buffer_size的设置有无合理呢,一般都上能 也能 检查情形值Key_read_requests和Key_reads ,比例key_reads / key_read_requests应该尽可能的低,比如1:5000,1:50000 ,1:500000。其值都上能 也能 用以下命令查得:show status like ‘key_read%’;

默认256K,MySQL为每个线程池池分配的堆栈大小,当线程池池堆栈太小时,这限制了服务器都上能 也能 处里的SQL句子的冗杂性。这个值一般认为默认就都上能 也能 应用于大每种场景了,除非必要非则并非动它。

也都上能 也能 用SQL命令:select (@@key_buffer_size +@@innodb_buffer_pool_size+ @@tmp_table_size +@@query_cache_size +@@innodb_additional_mem_pool_size +@@innodb_log_buffer_size + @@max_connections*(@@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size + @@join_buffer_size + @@binlog_cache_size + @@thread_stack) )/1024/1024 as “MYSQL_Service_Max_Mem”;

512K使用于 512MB内存,1GB内存则都上能 也能 设置成1MB,依次类推即可。

为甚也能知道query_cache_size的设置有无合理呢,命中率:(Qcache_hits/(Qcache_hits+Qcache_inserts)*5000))进行调整,通过命令:show status like ‘Qcache_%’;

MYSQL_Service_Mem = key_buffer_size (32MB)+ innodb_buffer_pool_size(32MB)+ tmp_table_size(32MB)+ query_cache_size(8MB) + innodb_additional_mem_pool_size(0MB) + innodb_log_buffer_size(8MB)= 112MB