澳门新萄京官方网站-www.8455.com-澳门新萄京赌场网址

mySQL占用虚拟内存达8百多兆问题解决思路,mysq

2019-08-10 作者:数据库网络   |   浏览(88)

1、目的:

透过依据服务器近期情景,修改Mysql的体系参数,达到合理利用服务器现存财富,最大合理的增进MySQL质量。

 

1、目的:

通过依照服务器如今境况,修改Mysql的种类参数,达到合理施用服务器现存财富,最大合理的增加MySQL品质。

 

1、目的:

经过依照服务器如今气象,修改Mysql的系统参数,到达合理运用服务器现成财富,最大合理的增高MySQL品质。

 

为了装mysql碰到测量检验,装上后发觉运维后mysql占用了相当大的虚构内部存储器,达8百多兆。网络寻觅了瞬间,获得高人引导my.ini。再也没见再详尽的了..只可以张开my.ini逐行的啃,即便葡萄牙共和国语差了点,可是多少M如故看得明的^-^

MySQL占用内部存款和储蓄器很大与CPU过高测验与消除办法,mysql占用内部存款和储蓄器

改动后如下:

innodb_buffer_pool_size=576M ->256M InnoDB引擎缓冲区占了大头,首要就是拿它开刀
query_cache_size=100M ->16M 查询缓存
tmp_table_size=102M ->64M 有时表大小
key_buffer_size=256m ->32M

重启mysql服务后,虚构内存降到200以下.

其余mysql安装目录下有多少个文本:my-huge.ini 、my-large.ini、my-medium.ini...那多少个是基于内部存款和储蓄器大小作的建议配置,新手在安装的时候也能够参见一下。
2G内部存款和储蓄器的MYSQL数据库服务器 my.ini优化 (my.ini)
2G内部存款和储蓄器,针对站少,优质型的安装,试验特:
table_cache=1024 物理内部存款和储蓄器越大,设置就越大.默感到2402,调到512-1024极品
innodb_additional_mem_pool_size=8M 默认为2M
innodb_flush_log_at_trx_commit=0 等到innodb_log_buffer_size列队满后再统一积累,默许为1
innodb_log_buffer_size=4M 默认为1M
innodb_thread_concurrency=8 你的服务器CPU有多少个就设置为几,默以为8
key_buffer_size=256M 默认为218 调到128最佳
tmp_table_size=64M 默认为16M 调到64-256最挂
read_buffer_size=4M 默认为64K
read_rnd_buffer_size=16M 默认为256K
sort_buffer_size=32M 默认为256K
max_connections=1024 默认为1210

试验一: table_cache=512或1024
innodb_additional_mem_pool_size=2M
innodb_flush_log_at_trx_commit=0
innodb_log_buffer_size=1M
innodb_thread_concurrency=8 你的服务器CPU有多少个就安装为几,默以为8
key_buffer_size=128M
tmp_table_size=128M
read_buffer_size=64K或128K
read_rnd_buffer_size=256K
sort_buffer_size=512K
max_connections=1024

试验二:
table_cache=512或1024
innodb_additional_mem_pool_size=8M
innodb_flush_log_at_trx_commit=0
innodb_log_buffer_size=4M
innodb_thread_concurrency=8
key_buffer_size=128M
tmp_table_size=128M
read_buffer_size=4M
read_rnd_buffer_size=16M
sort_buffer_size=32M
max_connections=1024

一般:
table_cache=512
innodb_additional_mem_pool_size=8M
innodb_flush_log_at_trx_commit=0
innodb_log_buffer_size=4M
innodb_thread_concurrency=8
key_buffer_size=128M
tmp_table_size=128M
read_buffer_size=4M
read_rnd_buffer_size=16M
sort_buffer_size=32M
max_connections=1024

经过测验.未有极度情况,最棒依然用暗中同意的.
2G内部存款和储蓄器,针对站多,抗压型的装置,最好:
table_cache=1024 物理内部存款和储蓄器越大,设置就越大.默以为2402,调到512-1024一流
innodb_additional_mem_pool_size=4M 默认为2M
innodb_flush_log_at_trx_commit=1
(设置为0便是等到innodb_log_buffer_size列队满后再统一储存,默感到1)
innodb_log_buffer_size=2M 默认为1M
innodb_thread_concurrency=8 你的服务器CPU有多少个就安装为几,建议用暗许一般为8
key_buffer_size=256M 默认为218 调到128最佳
tmp_table_size=64M 默认为16M 调到64-256最挂
read_buffer_size=4M 默认为64K
read_rnd_buffer_size=16M 默认为256K
sort_buffer_size=32M 默认为256K
max_connections=1024 默认为1210
thread_cache_size=120 默认为60
query_cache_size=64M

优化mysql数据库品质的13个参数
(1)、max_connections:
同意的还要客户的数目。扩大该值扩展 mysqld 需求的文件陈诉符的数码。那些数字应该扩充,不然,你将平常看到 too many connections 错误。 默许数值是100,小编把它改为1024 。
(2)、record_buffer:
每种实行多个一一扫描的线程为其扫描的每张表分配这几个分寸的一个缓冲区。若是您做过多各样扫描,你可能想要扩展该值。暗许数值是131072(128k),作者把它改为16773120 (16m)
(3)、key_buffer_size:
索引块是缓冲的同期被抱有的线程分享。key_buffer_size是用来索引块的缓冲区大小,增加它可获得更加好管理的目录(对具有读和多种写),到你能担当得起那样多。如若您使它太大,系统将上马换页並且确实变慢了。私下认可数值是8388600(8m),小编的mysql主机有2gb内部存款和储蓄器,所以作者把它改为 402649088(400mb)。
4)、back_log:
务求 mysql 能部分连年数量。当主要mysql线程在多个非常的短期内获得足够多的总是央浼,那就起效果,然后主线程花些日子(尽管非常的短)检查三番五次并且运维叁个新线程。
back_log 值提议在mysql暂且停止回答新乞求从前的短期内某些个央浼可以被存在货仓中。独有若是指望在八个长时间内有过多连连,你必要扩展它,换句话说,那值对来到的tcp/ip连接的侦听队列的高低。你的操作系统在那么些队列大小上有它和煦的限量。试图设定back_log高于你的操作系统的限制将是不行的。
当您观望您的主机进度列表,开采大量 264084 | unauthenticated user | xxx.xxx.xxx.xxx | null | connect | null | login | null 的待连接进度时,将在加大 back_log 的值了。私下认可数值是50,作者把它改为500。
(5)、interactive_timeout:
服务器在关闭它前在二个相互连接上伺机行动的秒数。一个交互的客户被定义为对 mysql_real_connect()使用 client_interactive 选项的客户。 暗中认可数值是28800,作者把它改为7200。
(6)、sort_buffer:
各类需求实行排序的线程分配该大小的叁个缓冲区。增添那值加快order by或group by操作。默许数值是2097144(2m),笔者把它改为 16777208 (16m)。
(7)、table_cache:
为拥有线程展开表的数码。扩充该值能扩展mysqld供给的文本陈述符的数额。mysql对每种独一张开的表必要2个文件呈报符。暗许数值是64,作者把它改为512。
(8)、thread_cache_size:
能够复用的保留在中的线程的数额。假设有,新的线程从缓存中获取,当断开连接的时候假设有空间,客户的线置在缓存中。要是有广大新的线程,为了加强品质可以那一个变量值。通过比较connections 和 threads_created 状态的变量,能够看来那个变量的功能。笔者把它设置为 80。
(9)mysql的寻觅效率
用mysql进行检索,指标是能不分大小写,又能用汉语实行寻觅
只需起动mysqld时钦命 --default-character-set=gb2312
(10)、wait_timeout:
服务器在闭馆它前边在两个连接上伺机行动的秒数。 暗中同意数值是28800,笔者把它改为7200。

注:参数的调度能够因而改变 /etc/my.cnf 文件一碗水端平启 mysql 完成。那是二个相比严厉的做事,上边的结果也可是是自家的有的思想,你能够依照你自个儿主机的硬件情状(特别是内部存款和储蓄器大小)进一步修改。

更动后如下: innodb_buffer_pool_size=576M -256M InnoDB引擎缓冲区占了花边,首要正是拿...

2、服务器参数:

32G内存、4个CPU,每个CPU 8核。

2、服务器参数:

32G内存、4个CPU,每个CPU 8核。

2、服务器参数:

32G内存、4个CPU,每个CPU 8核。

转移后如下
innodb_buffer_pool_size=576M ->256M InnoDB引擎缓冲区占了金元,重要正是拿它开刀
query_cache_size=100M ->16M 查询缓存
tmp_table_size=102M ->64M 有的时候表大小
key_buffer_size=256m ->32M

3、MySQL近期设置境况。

    MySQL近期设置,用的是MySQL私下认可的最大支撑配置。拷贝的是my-huge.cnf.编码已修改为UTF-8.具体修改及安装MySQL,能够参见<<Linux系统上设置MySQL 5.5>>协助文书档案。

3、MySQL如今设置处境。

    MySQL近年来设置,用的是MySQL暗中认可的最大支撑配置。拷贝的是my-huge.cnf.编码已修改为UTF-8.具体修改及安装MySQL,能够参照<<Linux系统上设置MySQL 5.5>>援助文书档案。

3、MySQL目前设置情状。

    MySQL最近安装,用的是MySQL私下认可的最大支撑配置。拷贝的是my-huge.cnf.编码已修改为UTF-8.具体修改及安装MySQL,能够参谋<<Linux系统上设置MySQL 5.5>>扶助文书档案。

重启mysql服务后,虚构内部存款和储蓄器降到200以下
别的mysql安装目录下有多少个文本:my-huge.ini 、my-large.ini、my-medium.ini...那多少个是基于内部存款和储蓄器大小作的提出配置,菜鸟在安装的时候也能够参见一下。
2G内部存储器的MYSQL数据库服务器 my.ini优化 (my.ini)
2G内存,针对站少,优质型的安装,试验特:
table_cache=1024 物理内部存款和储蓄器越大,设置就越大.默以为2402,调到512-1024拔尖
innodb_additional_mem_pool_size=8M 默认为2M
innodb_flush_log_at_trx_commit=0 等到innodb_log_buffer_size列队满后再统一累积,默感到1
innodb_log_buffer_size=4M 默认为1M
innodb_thread_concurrency=8 你的服务器CPU有几个就设置为几,默感到8
key_buffer_size=256M 默认为218 调到128最佳
tmp_table_size=64M 默认为16M 调到64-256最挂
read_buffer_size=4M 默认为64K
read_rnd_buffer_size=16M 默认为256K
sort_buffer_size=32M 默认为256K
max_connections=1024 默认为1210

4、修改MySQL配置

开发MySQL配置文件my.cnf

 

vi  /etc/my.cnf

4、修改MySQL配置

张开MySQL配置文件my.cnf

 

vi  /etc/my.cnf

4、修改MySQL配置

开荒MySQL配置文件my.cnf

 

vi  /etc/my.cnf

试验一
table_cache=512或1024
innodb_additional_mem_pool_size=2M
innodb_flush_log_at_trx_commit=0
innodb_log_buffer_size=1M
innodb_thread_concurrency=8 你的服务器CPU有多少个就安装为几,默认为8
key_buffer_size=128M
tmp_table_size=128M
read_buffer_size=64K或128K
read_rnd_buffer_size=256K
sort_buffer_size=512K
max_connections=1024

4.1 MySQL非缓存参数变量介绍及修改
4.1 MySQL非缓存参数变量介绍及修改
4.1 MySQL非缓存参数变量介绍及修改

试验二
table_cache=512或1024
innodb_additional_mem_pool_size=8M
innodb_flush_log_at_trx_commit=0
innodb_log_buffer_size=4M
innodb_thread_concurrency=8
key_buffer_size=128M
tmp_table_size=128M
read_buffer_size=4M
read_rnd_buffer_size=16M
sort_buffer_size=32M
max_connections=1024

4.1.1修改back_log参数值:由默许的50修改为500.(种种连接256kb,占用:125M)

          back_log=500

    back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。也就是说,如果MySql的连接数据达到max_connections时,新来的请求将会被存在堆栈中,以等待某一连接释放资源,该堆栈的数量即back_log,如果等待连接的数量超过back_log,将不被授予连接资源。将会报:unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待连接进程时.

back_log值不能超过TCP/IP连接的侦听队列的大小。若超过则无效,查看当前系统的TCP/IP连接的侦听队列的大小命令:cat /proc/sys/net/ipv4/tcp_max_syn_backlog目前系统为1024。对于Linux系统推荐设置为小于512的整数。

修改系统内核参数,)http://www.51testing.com/html/64/n-810764.html

查看mysql 当前系统默认back_log值,命令:

show variables like 'back_log'; 查看当前数量

 

4.1.1修改back_log参数值:由私下认可的50修改为500.(每一种连接256kb,占用:125M)

          back_log=500

    back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。也就是说,如果MySql的连接数据达到max_connections时,新来的请求将会被存在堆栈中,以等待某一连接释放资源,该堆栈的数量即back_log,如果等待连接的数量超过back_log,将不被授予连接资源。将会报:unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待连接进程时.

back_log值不能超过TCP/IP连接的侦听队列的大小。若超过则无效,查看当前系统的TCP/IP连接的侦听队列的大小命令:cat /proc/sys/net/ipv4/tcp_max_syn_backlog目前系统为1024。对于Linux系统推荐设置为小于512的整数。

修改系统内核参数,)http://www.51testing.com/html/64/n-810764.html

查看mysql 当前系统默认back_log值,命令:

show variables like 'back_log'; 查看当前数量

 

4.1.1修改back_log参数值:由默许的50改动为500.(每种连接256kb,占用:125M)

          back_log=500

    back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。也就是说,如果MySql的连接数据达到max_connections时,新来的请求将会被存在堆栈中,以等待某一连接释放资源,该堆栈的数量即back_log,如果等待连接的数量超过back_log,将不被授予连接资源。将会报:unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待连接进程时.

back_log值不能超过TCP/IP连接的侦听队列的大小。若超过则无效,查看当前系统的TCP/IP连接的侦听队列的大小命令:cat /proc/sys/net/ipv4/tcp_max_syn_backlog目前系统为1024。对于Linux系统推荐设置为小于512的整数。

修改系统内核参数,)http://www.51testing.com/html/64/n-810764.html

查看mysql 当前系统默认back_log值,命令:

show variables like 'back_log'; 查看当前数量

 

一般
table_cache=512
innodb_additional_mem_pool_size=8M
innodb_flush_log_at_trx_commit=0
innodb_log_buffer_size=4M
innodb_thread_concurrency=8
key_buffer_size=128M
tmp_table_size=128M
read_buffer_size=4M
read_rnd_buffer_size=16M
sort_buffer_size=32M
max_connections=1024

4.1.2修改wait_timeout参数值,由暗中认可的8钟头,修改为30分钟。(此番不用)

          wait_timeout=1800(单位为妙)

 

我对wait-timeout这个参数的理解:MySQL客户端的数据库连接闲置最大时间值。

说得比较通俗一点,就是当你的MySQL连接闲置超过一定时间后将会被强行关闭。MySQL默认的wait-timeout  值为8个小时,可以通过命令show variables like 'wait_timeout'查看结果值;。

设置这个值是非常有意义的,比如你的网站有大量的MySQL链接请求(每个MySQL连接都是要内存资源开销的 ),由于你的程序的原因有大量的连接请求空闲啥事也不干,白白占用内存资源,或者导致MySQL超过最大连接数从来无法新建连接导致“Too many connections”的错误。在设置之前你可以查看一下你的MYSQL的状态(可用show processlist),如果经常发现MYSQL中有大量的Sleep进程,则需要 修改wait-timeout值了。

interactive_timeout:服务器关闭交互式连接前等待活动的秒数。交互式客户端定义为在mysql_real_connect()中使用CLIENT_INTERACTIVE选项的客户端。

wait_timeout:服务器关闭非交互连接之前等待活动的秒数。在线程启动时,根据全局wait_timeout值或全局 interactive_timeout值初始化会话wait_timeout值,取决于客户端类型(由mysql_real_connect()的连接选项CLIENT_INTERACTIVE定义).

这两个参数必须配合使用。否则单独设置wait_timeout无效

 

4.1.2修改wait_timeout参数值,由暗中同意的8钟头,修改为30分钟。(此番不用)

          wait_timeout=1800(单位为妙)

 

我对wait-timeout这个参数的理解:MySQL客户端的数据库连接闲置最大时间值。

说得比较通俗一点,就是当你的MySQL连接闲置超过一定时间后将会被强行关闭。MySQL默认的wait-timeout  值为8个小时,可以通过命令show variables like 'wait_timeout'查看结果值;。

设置这个值是非常有意义的,比如你的网站有大量的MySQL链接请求(每个MySQL连接都是要内存资源开销的 ),由于你的程序的原因有大量的连接请求空闲啥事也不干,白白占用内存资源,或者导致MySQL超过最大连接数从来无法新建连接导致“Too many connections”的错误。在设置之前你可以查看一下你的MYSQL的状态(可用show processlist),如果经常发现MYSQL中有大量的Sleep进程,则需要 修改wait-timeout值了。

interactive_timeout:服务器关闭交互式连接前等待活动的秒数。交互式客户端定义为在mysql_real_connect()中使用CLIENT_INTERACTIVE选项的客户端。

wait_timeout:服务器关闭非交互连接之前等待活动的秒数。在线程启动时,根据全局wait_timeout值或全局 interactive_timeout值初始化会话wait_timeout值,取决于客户端类型(由mysql_real_connect()的连接选项CLIENT_INTERACTIVE定义).

这两个参数必须配合使用。否则单独设置wait_timeout无效

 

4.1.2修改wait_timeout参数值,由暗许的8时辰,修改为30分钟。(这一次不用)

          wait_timeout=1800(单位为妙)

 

我对wait-timeout这个参数的理解:MySQL客户端的数据库连接闲置最大时间值。

说得比较通俗一点,就是当你的MySQL连接闲置超过一定时间后将会被强行关闭。MySQL默认的wait-timeout  值为8个小时,可以通过命令show variables like 'wait_timeout'查看结果值;。

设置这个值是非常有意义的,比如你的网站有大量的MySQL链接请求(每个MySQL连接都是要内存资源开销的 ),由于你的程序的原因有大量的连接请求空闲啥事也不干,白白占用内存资源,或者导致MySQL超过最大连接数从来无法新建连接导致“Too many connections”的错误。在设置之前你可以查看一下你的MYSQL的状态(可用show processlist),如果经常发现MYSQL中有大量的Sleep进程,则需要 修改wait-timeout值了。

interactive_timeout:服务器关闭交互式连接前等待活动的秒数。交互式客户端定义为在mysql_real_connect()中使用CLIENT_INTERACTIVE选项的客户端。

wait_timeout:服务器关闭非交互连接之前等待活动的秒数。在线程启动时,根据全局wait_timeout值或全局 interactive_timeout值初始化会话wait_timeout值,取决于客户端类型(由mysql_real_connect()的连接选项CLIENT_INTERACTIVE定义).

这两个参数必须配合使用。否则单独设置wait_timeout无效

 

经过测量试验.没有卓绝意况,最佳还是用暗许的.
2G内部存款和储蓄器,针对站多,抗压型的安装,最棒:
table_cache=1024 物理内部存款和储蓄器越大,设置就越大.默感觉2402,调到512-1024特级
innodb_additional_mem_pool_size=4M 默认为2M
innodb_flush_log_at_trx_commit=1
(设置为0就是等到innodb_log_buffer_size列队满后再统一积累,默以为1)
innodb_log_buffer_size=2M 默认为1M
innodb_thread_concurrency=8 你的服务器CPU有多少个就设置为几,提议用暗中同意一般为8
key_mySQL占用虚拟内存达8百多兆问题解决思路,mysql占用内存。buffer_size=256M 默认为218 调到128最佳
tmp_table_size=64M 默认为16M 调到64-256最挂
read_buffer_size=4M 默认为64K
read_rnd_buffer_size=16M 默认为256K
sort_buffer_size=32M 默认为256K
max_connections=1024 默认为1210
thread_cache_size=120 默认为60
query_cache_size=64M

4.1.3修改max_connections参数值,由默许的151,修改为三千(750M)。

    max_connections=3000

max_connections是指MySql的最大连接数,如果服务器的并发连接请求量比较大,建议调高此值,以增加并行连接数量,当然这建立在机器能支撑的情况下,因为如果连接数越多,介于MySql会为每个连接提供连接缓冲区,就会开销越多的内存,所以要适当调整该值,不能盲目提高设值。可以过'conn%'通配符查看当前状态的连接数量,以定夺该值的大小。

MySQL服务器允许的最大连接数16384;

查看系统当前最大连接数:

show variables like 'max_connections';

 

4.1.3修改max_connections参数值,由暗许的151,修改为3000(750M)。

    max_connections=3000

max_connections是指MySql的最大连接数,如果服务器的并发连接请求量比较大,建议调高此值,以增加并行连接数量,当然这建立在机器能支撑的情况下,因为如果连接数越多,介于MySql会为每个连接提供连接缓冲区,就会开销越多的内存,所以要适当调整该值,不能盲目提高设值。可以过'conn%'通配符查看当前状态的连接数量,以定夺该值的大小。

MySQL服务器允许的最大连接数16384;

查看系统当前最大连接数:

show variables like 'max_connections';

 

4.1.3修改max_connections参数值,由暗中认可的151,修改为3000(750M)。

    max_connections=3000

max_connections是指MySql的最大连接数,如果服务器的并发连接请求量比较大,建议调高此值,以增加并行连接数量,当然这建立在机器能支撑的情况下,因为如果连接数越多,介于MySql会为每个连接提供连接缓冲区,就会开销越多的内存,所以要适当调整该值,不能盲目提高设值。可以过'conn%'通配符查看当前状态的连接数量,以定夺该值的大小。

MySQL服务器允许的最大连接数16384;

查看系统当前最大连接数:

show variables like 'max_connections';

 

优化mysql数据库质量的十一个参数
(1)、max_connections:
允许的还要客户的多寡。增添该值增添 mysqld 必要的文本陈述符的数据。那个数字应该增添,不然,你将常常看看 too many connections 错误。 默许数值是100,我把它改为1024 。
(2)、record_buffer:
各种实行叁个一一扫描的线程为其扫描的每张表分配这么些分寸的八个缓冲区。要是您做过多种种扫描,你大概想要扩展该值。暗许数值是131072(128k),笔者把它改为16773120 (16m)
(3)、key_buffer_size:
索引块是缓冲的同时被有着的线程共享。key_buffer_size是用于索引块的缓冲区大小,扩大它可获得更加好管理的目录(对持有读和多种写),到你能担任得起那样多。假如你使它太大,系统将上马换页并且确实变慢了。暗中同意数值是8388600(8m),作者的mysql主机有2gb内部存款和储蓄器,所以小编把它改为 402649088(400mb)。
4)、back_log:
务求 mysql 能部分连年数量。当首要mysql线程在叁个异常的短期内获得丰硕多的连天央求,那就起效果,然后主线程花些日子(固然十分的短)检查三番五次并且运行一个新线程。
back_log 值提出在mysql权且甘休回答新央浼在此以前的短期内某些个哀告可以被存在饭馆中。独有若是指望在三个短期内有那贰个延续,你供给扩大它,换句话说,那值对来到的tcp/ip连接的侦听队列的深浅。你的操作系统在那个队列大小上有它本人的限量。试图设定back_log高于你的操作系统的限制将是无用的。
当您观看您的主机进度列表,开采多量 264084 | unauthenticated user | xxx.xxx.xxx.xxx | null | connect | null | login | null 的待连接进度时,将要加大 back_log 的值了。默许数值是50,小编把它改为500。
(5)、interactive_timeout:
服务器在关门它前在多个并行连接上等候行动的秒数。二个并行的客户被定义为对 mysql_real_connect()使用 client_interactive 选项的客户。 暗许数值是28800,笔者把它改为7200。
(6)、sort_buffer:
各个需求开始展览排序的线程分配该大小的三个缓冲区。扩大这值加速order by或group by操作。暗许数值是2097144(2m),小编把它改为 16777208 (16m)。
(7)、table_cache:
为富有线程张开表的数目。扩大该值能充实mysqld供给的文书呈报符的数码。mysql对种种唯一张开的表需求2个公文陈说符。暗中认可数值是64,笔者把它改为512。
(8)、thread_cache_size:
能够复用的保存在中的线程的数码。如若有,新的线程从缓存中赢得,当断开连接的时候要是有空中,客户的线置在缓存中。假使有多数新的线程,为了巩固性能能够那么些变量值。通过比较connections 和 threads_created 状态的变量,可以看出那么些变量的功用。笔者把它设置为 80。
(9)mysql的搜寻功用
用mysql进行查找,指标是能不分大小写,又能用汉语举办检索
只需起动mysqld时钦点 --default-character-set=gb2312
(10)、wait_timeout:
服务器在关闭它以前在贰个接连上等候行动的秒数。 暗中认可数值是28800,笔者把它改为7200。

4.1..4修改max_user_connections值,由暗中认可的0,修改为800

     max_user_connections=800

 max_user_connections是指每个数据库用户的最大连接

针对某一个账号的所有客户端并行连接到MYSQL服务的最大并行连接数。简单说是指同一个账号能够同时连接到mysql服务的最大连接数。设置为0表示不限制。

目前默认值为:0不受限制。

这儿顺便介绍下Max_used_connections:它是指从这次mysql服务启动到现在,同一时刻并行连接数的最大值。它不是指当前的连接情况,而是一个比较值。如果在过去某一个时刻,MYSQL服务同时有1000个请求连接过来,而之后再也没有出现这么大的并发请求时,则Max_used_connections=1000.请注意与show variables 里的max_user_connections的区别。默认为0表示无限大。

查看max_user_connections值

show variables like 'max_user_connections';

 

4.1..4修改max_user_connections值,由私下认可的0,修改为800

     max_user_connections=800

 max_user_connections是指每个数据库用户的最大连接

针对某一个账号的所有客户端并行连接到MYSQL服务的最大并行连接数。简单说是指同一个账号能够同时连接到mysql服务的最大连接数。设置为0表示不限制。

目前默认值为:0不受限制。

这儿顺便介绍下Max_used_connections:它是指从这次mysql服务启动到现在,同一时刻并行连接数的最大值。它不是指当前的连接情况,而是一个比较值。如果在过去某一个时刻,MYSQL服务同时有1000个请求连接过来,而之后再也没有出现这么大的并发请求时,则Max_used_connections=1000.请注意与show variables 里的max_user_connections的区别。默认为0表示无限大。

查看max_user_connections值

show variables like 'max_user_connections';

 

4.1..4修改max_user_connections值,由私下认可的0,修改为800

     max_user_connections=800

 max_user_connections是指每个数据库用户的最大连接

针对某一个账号的所有客户端并行连接到MYSQL服务的最大并行连接数。简单说是指同一个账号能够同时连接到mysql服务的最大连接数。设置为0表示不限制。

目前默认值为:0不受限制。

这儿顺便介绍下Max_used_connections:它是指从这次mysql服务启动到现在,同一时刻并行连接数的最大值。它不是指当前的连接情况,而是一个比较值。如果在过去某一个时刻,MYSQL服务同时有1000个请求连接过来,而之后再也没有出现这么大的并发请求时,则Max_used_connections=1000.请注意与show variables 里的max_user_connections的区别。默认为0表示无限大。

查看max_user_connections值

show variables like 'max_user_connections';

 

:参数的调解可以经过退换 /etc/my.cnf 文件一碗水端平启 mysql 实现。那是二个十分小心的办事,上边的结果也惟有是自个儿的部分见解,你能够根据你自身主机的硬件情形(极度是内部存款和储蓄器大小)进一步修改。

4.1.5修改thread_concurrency值,由近年来暗许的8,修改为64

     thread_concurrency=64

thread_concurrency的值的正确与否, 对mysql的性能影响很大, 在多个cpu(或多核)的情况下,错误设置了thread_concurrency的值, 会导致mysql不能充分利用多cpu(或多核), 出现同一时刻只能一个cpu(或核)在工作的情况。

thread_concurrency应设为CPU核数的2倍. 比如有一个双核的CPU, 那thread_concurrency  的应该为4; 2个双核的cpu, thread_concurrency的值应为8.

比如:根据上面介绍我们目前系统的配置,可知道为4个CPU,每个CPU为8核,按照上面的计算规则,这儿应为:4*8*2=64

查看系统当前thread_concurrency默认配置命令:

 show variables like 'thread_concurrency';

 

4.1.5修改thread_mySQL占用虚拟内存达8百多兆问题解决思路,mysql占用内存。concurrency值,由最近暗中认可的8,修改为64

     thread_concurrency=64

thread_concurrency的值的正确与否, 对mysql的性能影响很大, 在多个cpu(或多核)的情况下,错误设置了thread_concurrency的值, 会导致mysql不能充分利用多cpu(或多核), 出现同一时刻只能一个cpu(或核)在工作的情况。

thread_concurrency应设为CPU核数的2倍. 比如有一个双核的CPU, 那thread_concurrency  的应该为4; 2个双核的cpu, thread_concurrency的值应为8.

比如:根据上面介绍我们目前系统的配置,可知道为4个CPU,每个CPU为8核,按照上面的计算规则,这儿应为:4*8*2=64

查看系统当前thread_concurrency默认配置命令:

 show variables like 'thread_concurrency';

 

4.1.5修改thread_concurrency值,由最近暗中认可的8,修改为64

     thread_concurrency=64

thread_concurrency的值的正确与否, 对mysql的性能影响很大, 在多个cpu(或多核)的情况下,错误设置了thread_concurrency的值, 会导致mysql不能充分利用多cpu(或多核), 出现同一时刻只能一个cpu(或核)在工作的情况。

thread_concurrency应设为CPU核数的2倍. 比如有一个双核的CPU, 那thread_concurrency  的应该为4; 2个双核的cpu, thread_concurrency的值应为8.

比如:根据上面介绍我们目前系统的配置,可知道为4个CPU,每个CPU为8核,按照上面的计算规则,这儿应为:4*8*2=64

查看系统当前thread_concurrency默认配置命令:

 show variables like 'thread_concurrency';

 

您恐怕感兴趣的稿子:

  • 浅析MySQL内部存款和储蓄器的选用表明(全局缓存 线程缓存)
  • MySQL内部存款和储蓄器表的性状与使用介绍
  • PHP查询MySQL多量数目标时候内部存款和储蓄器占用剖判
  • mysql 数据库中my.ini的优化 2G内部存款和储蓄器针对站多 抗压型的装置
  • 用mysql内部存款和储蓄器表来代替php session的类
  • Nginx 0.7.x PHP 5.2.6(法斯特CGI) MySQL 5.1 在128M小内部存款和储蓄器VPS服务器上的配备优化
  • 分析mysql 缓存怎么样使用内存
4.1.6增多skip-name-resolve,暗中同意被解说掉,未有该参数。

skip-name-resolve

skip-name-resolve:禁止MySQL对外部连接进行DNS解析,使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意,如果开启该选项,则所有远程主机连接授权都要使用IP地址方式,否则MySQL将无法正常处理连接请求!

4.1.6加多skip-name-resolve,私下认可被讲明掉,未有该参数。

skip-name-resolve

skip-name-resolve:禁止MySQL对外部连接进行DNS解析,使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意,如果开启该选项,则所有远程主机连接授权都要使用IP地址方式,否则MySQL将无法正常处理连接请求!

4.1.6增多skip-name-resolve,暗中同意被解说掉,未有该参数。

skip-name-resolve

skip-name-resolve:禁止MySQL对外部连接进行DNS解析,使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意,如果开启该选项,则所有远程主机连接授权都要使用IP地址方式,否则MySQL将无法正常处理连接请求!

4.1.7 skip-networking,暗中认可被解说掉。未有该参数。(本次无用)

 skip-networking建议被讲授掉,不要开启

开启该选项可以彻底关闭MySQL的TCP/IP连接方式,如果WEB服务器是以远程连接的方式访问MySQL数据库服务器则不要开启该选项!否则将无法正常连接!

4.1.7 skip-networking,暗中同意被批注掉。未有该参数。(这一次无用)

 skip-networking提出被批注掉,不要开启

开启该选项可以彻底关闭MySQL的TCP/IP连接方式,如果WEB服务器是以远程连接的方式访问MySQL数据库服务器则不要开启该选项!否则将无法正常连接!

4.1.7 skip-networking,暗中认可被疏解掉。未有该参数。(此次无用)

 skip-networking提议被解说掉,不要开启

开启该选项可以彻底关闭MySQL的TCP/IP连接方式,如果WEB服务器是以远程连接的方式访问MySQL数据库服务器则不要开启该选项!否则将无法正常连接!

4.1.8  default-storage-engine(设置MySQL的默许存款和储蓄引擎)

default-storage-engine= InnoDB(设置InnoDB类型,别的还足以安装MyISAM类型)

设置创建数据库及表默认存储类型

show table status like ‘tablename’显示表的当前存储状态值

查看MySQL有哪些存储状态及默认存储状态

 show engines;

创建表并指定存储类型

CREATE TABLE mytable (id int, title char(20)) ENGINE = INNODB;

修改表存储类型:

  Alter table tableName engine =engineName

 

备注:设置完后把以下几个开启:

# Uncomment the following if you are using InnoDB tables

innodb_data_home_dir = /var/lib/mysql

#innodb_data_file_path = ibdata1:1024M;ibdata2:10M:autoextend(要注释掉,否则会创建一个新的把原来的替换的。)

innodb_log_group_home_dir = /var/lib/mysql

# You can set .._buffer_pool_size up to 50 - 80 %

# of RAM but beware of setting memory usage too high

innodb_buffer_pool_size = 1000M

innodb_additional_mem_pool_size = 20M

# Set .._log_file_size to 25 % of buffer pool size

innodb_log_file_size = 500M

innodb_log_buffer_size = 20M

innodb_flush_log_at_trx_commit = 0

innodb_lock_wait_timeout = 50

设置完后一定记得把MySQL安装目录地址(我们目前是默认安装所以地址/var/lib/mysql/)下的ib_logfile0和ib_logfile1删除掉。否则重启MySQL起动失败。

 

4.1.8  default-storage-engine(设置MySQL的暗中认可存款和储蓄引擎)

default-storage-engine= InnoDB(设置InnoDB类型,另外还足以设置MyISAM类型)

设置创建数据库及表默认存储类型

show table status like ‘tablename’显示表的当前存储状态值

查看MySQL有哪些存储状态及默认存储状态

 show engines;

创建表并指定存储类型

CREATE TABLE mytable (id int, title char(20)) ENGINE = INNODB;

修改表存储类型:

  Alter table tableName engine =engineName

 

备注:设置完后把以下几个开启:

# Uncomment the following if you are using InnoDB tables

innodb_data_home_dir = /var/lib/mysql

#innodb_data_file_path = ibdata1:1024M;ibdata2:10M:autoextend(要注释掉,否则会创建一个新的把原来的替换的。)

innodb_log_group_home_dir = /var/lib/mysql

# You can set .._buffer_pool_size up to 50 - 80 %

# of RAM but beware of setting memory usage too high

innodb_buffer_pool_size = 1000M

innodb_additional_mem_pool_size = 20M

# Set .._log_file_size to 25 % of buffer pool size

innodb_log_file_size = 500M

innodb_log_buffer_size = 20M

innodb_flush_log_at_trx_commit = 0

innodb_lock_wait_timeout = 50

设置完后一定记得把MySQL安装目录地址(我们目前是默认安装所以地址/var/lib/mysql/)下的ib_logfile0和ib_logfile1删除掉。否则重启MySQL起动失败。

 

4.1.8  default-storage-engine(设置MySQL的暗中认可存款和储蓄引擎)

default-storage-engine= InnoDB(设置InnoDB类型,另外还是能够安装MyISAM类型)

设置创建数据库及表默认存储类型

show table status like ‘tablename’显示表的当前存储状态值

查看MySQL有哪些存储状态及默认存储状态

 show engines;

创建表并指定存储类型

CREATE TABLE mytable (id int, title char(20)) ENGINE = INNODB;

修改表存储类型:

  Alter table tableName engine =engineName

 

备注:设置完后把以下几个开启:

# Uncomment the following if you are using InnoDB tables

innodb_data_home_dir = /var/lib/mysql

#innodb_data_file_path = ibdata1:1024M;ibdata2:10M:autoextend(要注释掉,否则会创建一个新的把原来的替换的。)

innodb_log_group_home_dir = /var/lib/mysql

# You can set .._buffer_pool_size up to 50 - 80 %

# of RAM but beware of setting memory usage too high

innodb_buffer_pool_size = 1000M

innodb_additional_mem_pool_size = 20M

# Set .._log_file_size to 25 % of buffer pool size

innodb_log_file_size = 500M

innodb_log_buffer_size = 20M

innodb_flush_log_at_trx_commit = 0

innodb_lock_wait_timeout = 50

设置完后一定记得把MySQL安装目录地址(我们目前是默认安装所以地址/var/lib/mysql/)下的ib_logfile0和ib_logfile1删除掉。否则重启MySQL起动失败。

 

4.2 MySQL缓存变量介绍及修改

数据库属于IO密集型的应用程序,其主职责就是数据的管理及存储工作。而我们知道,从内存中读取一个数据库的时间是微秒级别,而从一块普通硬盘上读取一个 IO是在毫秒级别,二者相差3个数量级。所以,要优化数据库,首先第一步需要优化的就是IO,尽可能将磁盘IO转化为内存IO。本文先从MySQL数据库 IO相关参数(缓存参数)的角度来看看可以通过哪些参数进行IO优化

 

4.2 MySQL缓存变量介绍及修改

数据库属于IO密集型的应用程序,其主职责就是数据的管理及存储工作。而我们知道,从内存中读取一个数据库的时间是微秒级别,而从一块普通硬盘上读取一个 IO是在毫秒级别,二者相差3个数量级。所以,要优化数据库,首先第一步需要优化的就是IO,尽可能将磁盘IO转化为内存IO。本文先从MySQL数据库 IO相关参数(缓存参数)的角度来看看可以通过哪些参数进行IO优化

 

4.2 MySQL缓存变量介绍及修改

数据库属于IO密集型的应用程序,其主职责就是数据的管理及存储工作。而我们知道,从内存中读取一个数据库的时间是微秒级别,而从一块普通硬盘上读取一个 IO是在毫秒级别,二者相差3个数量级。所以,要优化数据库,首先第一步需要优化的就是IO,尽可能将磁盘IO转化为内存IO。本文先从MySQL数据库 IO相关参数(缓存参数)的角度来看看可以通过哪些参数进行IO优化

 

4.2.1大局缓存

启动MySQL时就要分配并且总是存在的全局缓存。目前有:key_buffer_size(默认值:402653184,即384M)、innodb_buffer_pool_size(默认值:134217728即:128M)、innodb_additional_mem_pool_size(默认值:8388608即:8M)、innodb_log_buffer_size(默认值:8388608即:8M)、query_cache_size(默认值:33554432即:32M)等五个。总共:560M.

这些变量值都可以通过命令如:show variables like '变量名';查看到。

 

4.2.1.1:key_buffer_size,本系统当下为384M,可修改为400M

    key_buffer_size=400M

    key_buffer_size是用于索引块的缓冲区大小,增加它可得到更好处理的索引(对所有读和多重写),对MyISAM(MySQL表存储的一种类型,可以百度等查看详情)表性能影响最大的一个参数。如果你使它太大,系统将开始换页并且真的变慢了。严格说是它决定了数据库索引处理的速度,尤其是索引读的速度。对于内存在4GB左右的服务器该参数可设置为256M或384M.

怎么才能知道key_buffer_size的设置是否合理呢,一般可以检查状态值Key_read_requests和Key_reads   ,比例key_reads / key_read_requests应该尽可能的低,比如1:100,1:1000 ,1:10000。其值可以用以下命令查得:show status like 'key_read%';

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

------------------- -------

| Variable_name     | Value |

------------------- -------

| Key_read_requests | 28535 |

| Key_reads         | 269   |

------------------- -------

可知道有28535个请求,有269个请求在内存中没有找到直接从硬盘读取索引.

未命中缓存的概率为:0.94%=269/28535*100%.  一般未命中概率在0.1之下比较好。目前已远远大于0.1,证明效果不好。若命中率在0.01以下,则建议适当的修改key_buffer_size值。

http://dbahacker.com/mysql/innodb-myisam-compare(InnoDB与MyISAM的六大区别)

http://kb.cnblogs.com/page/99810/(查看存储引擎介绍)

MyISAM、InnoDB、MyISAM Merge引擎、InnoDB、memory(heap)、archive

4.2.1.2:innodb_buffer_pool_size(默认128M)

innodb_buffer_pool_size=1024M(1G)

   innodb_buffer_pool_size:主要针对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,是最大限度地利用内存了。

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

当我们操作一个 InnoDB 表的时候,返回的所有数据或者去数据过程中用到的任何一个索引块,都会在这个内存区域中走一遭。

可以通过 (Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100% 计算缓存命中率,并根据命中率来调整 innodb_buffer_pool_size 参数大小进行优化。值可以用以下命令查得:show status like 'Innodb_buffer_pool_read%';

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

| Innodb_buffer_pool_read_requests      | 1283826 |

| Innodb_buffer_pool_reads              | 519     |

--------------------------------------- ---------

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

4.2.1.3:innodb_additional_mem_pool_size(默认8M)

  innodb_additional_mem_pool_size=20M

     innodb_additional_mem_pool_size 设置了InnoDB存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小,所以当我们一个MySQL Instance中的数据库对象非常多的时候,是需要适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率的。

这个参数大小是否足够还是比较容易知道的,因为当过小的时候,MySQL会记录Warning信息到数据库的error log中,这时候你就知道该调整这个参数大小了。

查看当前系统mysql的error日志  cat  /var/lib/mysql/机器名.error 发现有很多waring警告。所以要调大为20M.

根据MySQL手册,对于2G内存的机器,推荐值是20M。

    32G内存的 100M

4.2.1.4:innodb_log_buffer_size(默认8M)

innodb_log_buffer_size=20M

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

   InnoDB 将日志写入日志磁盘文件前的缓冲大小。理想值为 1M 至 8M。大的日志缓冲允许事务运行时不需要将日志保存入磁盘而只到事务被提交(commit)。 因此,如果有大的事务处理,设置大的日志缓冲可以减少磁盘I/O。 在 my.cnf中以数字格式设置。

默认是8MB,系的如频繁的系统可适当增大至4MB~8MB。当然如上面介绍所说,这个参数实际上还和另外的flush参数相关。一般来说不建议超过32MB

注:innodb_flush_log_trx_commit参数对InnoDB Log的写入性能有非常关键的影响,默认值为1。该参数可以设置为0,1,2,解释如下:

0:log buffer中的数据将以每秒一次的频率写入到log file中,且同时会进行文件系统到磁盘的同步操作,但是每个事务的commit并不会触发任何log buffer 到log file的刷新或者文件系统到磁盘的刷新操作;

1:在每次事务提交的时候将log buffer 中的数据都会写入到log file,同时也会触发文件系统到磁盘的同步;

2:事务提交会触发log buffer到log file的刷新,但并不会触发磁盘文件系统到磁盘的同步。此外,每秒会有一次文件系统到磁盘同步操作。

实际测试发现,该值对插入数据的速度影响非常大,设置为2时插入10000条记录只需要2秒,设置为0时只需要1秒,而设置为1时则需要229秒。因此,MySQL手册也建议尽量将插入操作合并成一个事务,这样可以大幅提高速度。根据MySQL手册,在存在丢失最近部分事务的危险的前提下,可以把该值设为0。

 

4.5.1.5:query_cache_size(默认32M)

query_cache_size=40M

     query_cache_size: 主要用来缓存MySQL中的ResultSet,也就是一条SQL语句执行的结果集,所以仅仅只能针对select语句。当我们打开了 Query Cache功能,MySQL在接受到一条select语句的请求后,如果该语句满足Query Cache的要求(未显式说明不允许使用Query Cache,或者已经显式申明需要使用Query Cache),MySQL会直接根据预先设定好的HASH算法将接受到的select语句以字符串方式进行hash,然后到Query Cache中直接查找是否已经缓存。也就是说,如果已经在缓存中,该select请求就会直接将数据返回,从而省略了后面所有的步骤(如SQL语句的解析,优化器优化以及向存储引擎请求数据等),极大的提高性能。根据MySQL用户手册,使用查询缓冲最多可以达到238%的效率。

当然,Query Cache也有一个致命的缺陷,那就是当某个表的数据有任何任何变化,都会导致所有引用了该表的select语句在Query Cache中的缓存数据失效。所以,当我们的数据变化非常频繁的情况下,使用Query Cache可能会得不偿失

   Query Cache的使用需要多个参数配合,其中最为关键的是query_cache_size和query_cache_type,前者设置用于缓存 ResultSet的内存大小,后者设置在何场景下使用Query Cache。在以往的经验来看,如果不是用来缓存基本不变的数据的MySQL数据库,query_cache_size一般256MB是一个比较合适的大小。当然,这可以通过计算Query Cache的命中率(Qcache_hits/(Qcache_hits Qcache_inserts)*100))来进行调整。 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的值也非常大,则表明查询缓冲使用非常频繁,此时需要增加缓冲大小;

根据命中率(Qcache_hits/(Qcache_hits Qcache_inserts)*100))进行调整,一般不建议太大,256MB可能已经差不多了,大型的配置型静态数据可适当调大.

可以通过命令:show status like 'Qcache_%';查看目前系统Query catch使用大小

| Qcache_hits             | 1892463  |

| Qcache_inserts          | 35627  

命中率98.17%=1892463/(1892463  35627 )*100

4.2.1大局缓存

启动MySQL时就要分配并且总是存在的全局缓存。目前有:key_buffer_size(默认值:402653184,即384M)、innodb_buffer_pool_size(默认值:134217728即:128M)、innodb_additional_mem_pool_size(默认值:8388608即:8M)、innodb_log_buffer_size(默认值:8388608即:8M)、query_cache_size(默认值:33554432即:32M)等五个。总共:560M.

这些变量值都可以通过命令如:show variables like '变量名';查看到。

 

4.2.1.1:key_buffer_size,本系统当下为384M,可修改为400M

    key_buffer_size=400M

    key_buffer_size是用于索引块的缓冲区大小,增加它可得到更好处理的索引(对所有读和多重写),对MyISAM(MySQL表存储的一种类型,可以百度等查看详情)表性能影响最大的一个参数。如果你使它太大,系统将开始换页并且真的变慢了。严格说是它决定了数据库索引处理的速度,尤其是索引读的速度。对于内存在4GB左右的服务器该参数可设置为256M或384M.

怎么才能知道key_buffer_size的设置是否合理呢,一般可以检查状态值Key_read_requests和Key_reads   ,比例key_reads / key_read_requests应该尽可能的低,比如1:100,1:1000 ,1:10000。其值可以用以下命令查得:show status like 'key_read%';

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

------------------- -------

| Variable_name     | Value |

------------------- -------

| Key_read_requests | 28535 |

| Key_reads         | 269   |

------------------- -------

可知道有28535个请求,有269个请求在内存中没有找到直接从硬盘读取索引.

未命中缓存的概率为:0.94%=269/28535*100%.  一般未命中概率在0.1之下比较好。目前已远远大于0.1,证明效果不好。若命中率在0.01以下,则建议适当的修改key_buffer_size值。

http://dbahacker.com/mysql/innodb-myisam-compare(InnoDB与MyISAM的六大区别)

http://kb.cnblogs.com/page/99810/(查看存储引擎介绍)

MyISAM、InnoDB、MyISAM Merge引擎、InnoDB、memory(heap)、archive

4.2.1.2:innodb_buffer_pool_size(默认128M)

innodb_buffer_pool_size=1024M(1G)

   innodb_buffer_pool_size:主要针对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,是最大限度地利用内存了。

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

当我们操作一个 InnoDB 表的时候,返回的所有数据或者去数据过程中用到的任何一个索引块,都会在这个内存区域中走一遭。

可以通过 (Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100% 计算缓存命中率,并根据命中率来调整 innodb_buffer_pool_size 参数大小进行优化。值可以用以下命令查得:show status like 'Innodb_buffer_pool_read%';

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

| Innodb_buffer_pool_read_requests      | 1283826 |

| Innodb_buffer_pool_reads              | 519     |

--------------------------------------- ---------

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

4.2.1.3:innodb_additional_mem_pool_size(默认8M)

  innodb_additional_mem_pool_size=20M

     innodb_additional_mem_pool_size 设置了InnoDB存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小,所以当我们一个MySQL Instance中的数据库对象非常多的时候,是需要适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率的。

这个参数大小是否足够还是比较容易知道的,因为当过小的时候,MySQL会记录Warning信息到数据库的error log中,这时候你就知道该调整这个参数大小了。

查看当前系统mysql的error日志  cat  /var/lib/mysql/机器名.error 发现有很多waring警告。所以要调大为20M.

根据MySQL手册,对于2G内存的机器,推荐值是20M。

    32G内存的 100M

4.2.1.4:innodb_log_buffer_size(默认8M)

innodb_log_buffer_size=20M

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

   InnoDB 将日志写入日志磁盘文件前的缓冲大小。理想值为 1M 至 8M。大的日志缓冲允许事务运行时不需要将日志保存入磁盘而只到事务被提交(commit)。 因此,如果有大的事务处理,设置大的日志缓冲可以减少磁盘I/O。 在 my.cnf中以数字格式设置。

默认是8MB,系的如频繁的系统可适当增大至4MB~8MB。当然如上面介绍所说,这个参数实际上还和另外的flush参数相关。一般来说不建议超过32MB

注:innodb_flush_log_trx_commit参数对InnoDB Log的写入性能有非常关键的影响,默认值为1。该参数可以设置为0,1,2,解释如下:

0:log buffer中的数据将以每秒一次的频率写入到log file中,且同时会进行文件系统到磁盘的同步操作,但是每个事务的commit并不会触发任何log buffer 到log file的刷新或者文件系统到磁盘的刷新操作;

1:在每次事务提交的时候将log buffer 中的数据都会写入到log file,同时也会触发文件系统到磁盘的同步;

2:事务提交会触发log buffer到log file的刷新,但并不会触发磁盘文件系统到磁盘的同步。此外,每秒会有一次文件系统到磁盘同步操作。

实际测试发现,该值对插入数据的速度影响非常大,设置为2时插入10000条记录只需要2秒,设置为0时只需要1秒,而设置为1时则需要229秒。因此,MySQL手册也建议尽量将插入操作合并成一个事务,这样可以大幅提高速度。根据MySQL手册,在存在丢失最近部分事务的危险的前提下,可以把该值设为0。

 

4.5.1.5:query_cache_size(默认32M)

query_cache_size=40M

     query_cache_size: 主要用来缓存MySQL中的ResultSet,也就是一条SQL语句执行的结果集,所以仅仅只能针对select语句。当我们打开了 Query Cache功能,MySQL在接受到一条select语句的请求后,如果该语句满足Query Cache的要求(未显式说明不允许使用Query Cache,或者已经显式申明需要使用Query Cache),MySQL会直接根据预先设定好的HASH算法将接受到的select语句以字符串方式进行hash,然后到Query Cache中直接查找是否已经缓存。也就是说,如果已经在缓存中,该select请求就会直接将数据返回,从而省略了后面所有的步骤(如SQL语句的解析,优化器优化以及向存储引擎请求数据等),极大的提高性能。根据MySQL用户手册,使用查询缓冲最多可以达到238%的效率。

当然,Query Cache也有一个致命的缺陷,那就是当某个表的数据有任何任何变化,都会导致所有引用了该表的select语句在Query Cache中的缓存数据失效。所以,当我们的数据变化非常频繁的情况下,使用Query Cache可能会得不偿失

   Query Cache的使用需要多个参数配合,其中最为关键的是query_cache_size和query_cache_type,前者设置用于缓存 ResultSet的内存大小,后者设置在何场景下使用Query Cache。在以往的经验来看,如果不是用来缓存基本不变的数据的MySQL数据库,query_cache_size一般256MB是一个比较合适的大小。当然,这可以通过计算Query Cache的命中率(Qcache_hits/(Qcache_hits Qcache_inserts)*100))来进行调整。 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的值也非常大,则表明查询缓冲使用非常频繁,此时需要增加缓冲大小;

根据命中率(Qcache_hits/(Qcache_hits Qcache_inserts)*100))进行调整,一般不建议太大,256MB可能已经差不多了,大型的配置型静态数据可适当调大.

可以通过命令:show status like 'Qcache_%';查看目前系统Query catch使用大小

| Qcache_hits             | 1892463  |

| Qcache_inserts          | 35627  

命中率98.17%=1892463/(1892463  35627 )*100

4.2.1大局缓存

启动MySQL时就要分配并且总是存在的全局缓存。目前有:key_buffer_size(默认值:402653184,即384M)、innodb_buffer_pool_size(默认值:134217728即:128M)、innodb_additional_mem_pool_size(默认值:8388608即:8M)、innodb_log_buffer_size(默认值:8388608即:8M)、query_cache_size(默认值:33554432即:32M)等五个。总共:560M.

这些变量值都可以通过命令如:show variables like '变量名';查看到。

 

4.2.1.1:key_buffer_size,本系统当下为384M,可修改为400M

    key_buffer_size=400M

    key_buffer_size是用于索引块的缓冲区大小,增加它可得到更好处理的索引(对所有读和多重写),对MyISAM(MySQL表存储的一种类型,可以百度等查看详情)表性能影响最大的一个参数。如果你使它太大,系统将开始换页并且真的变慢了。严格说是它决定了数据库索引处理的速度,尤其是索引读的速度。对于内存在4GB左右的服务器该参数可设置为256M或384M.

怎么才能知道key_buffer_size的设置是否合理呢,一般可以检查状态值Key_read_requests和Key_reads   ,比例key_reads / key_read_requests应该尽可能的低,比如1:100,1:1000 ,1:10000。其值可以用以下命令查得:show status like 'key_read%';

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

------------------- -------

| Variable_name     | Value |

------------------- -------

| Key_read_requests | 28535 |

| Key_reads         | 269   |

------------------- -------

可知道有28535个请求,有269个请求在内存中没有找到直接从硬盘读取索引.

未命中缓存的概率为:0.94%=269/28535*100%.  一般未命中概率在0.1之下比较好。目前已远远大于0.1,证明效果不好。若命中率在0.01以下,则建议适当的修改key_buffer_size值。

http://dbahacker.com/mysql/innodb-myisam-compare(InnoDB与MyISAM的六大区别)

http://kb.cnblogs.com/page/99810/(查看存储引擎介绍)

MyISAM、InnoDB、MyISAM Merge引擎、InnoDB、memory(heap)、archive

4.2.1.2:innodb_buffer_pool_size(默认128M)

innodb_buffer_pool_size=1024M(1G)

   innodb_buffer_pool_size:主要针对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,是最大限度地利用内存了。

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

当我们操作一个 InnoDB 表的时候,返回的所有数据或者去数据过程中用到的任何一个索引块,都会在这个内存区域中走一遭。

可以通过 (Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100% 计算缓存命中率,并根据命中率来调整 innodb_buffer_pool_size 参数大小进行优化。值可以用以下命令查得:show status like 'Innodb_buffer_pool_read%';

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

| Innodb_buffer_pool_read_requests      | 1283826 |

| Innodb_buffer_pool_reads              | 519     |

--------------------------------------- ---------

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

4.2.1.3:innodb_additional_mem_pool_size(默认8M)

  innodb_additional_mem_pool_size=20M

     innodb_additional_mem_pool_size 设置了InnoDB存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小,所以当我们一个MySQL Instance中的数据库对象非常多的时候,是需要适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率的。

这个参数大小是否足够还是比较容易知道的,因为当过小的时候,MySQL会记录Warning信息到数据库的error log中,这时候你就知道该调整这个参数大小了。

查看当前系统mysql的error日志  cat  /var/lib/mysql/机器名.error 发现有很多waring警告。所以要调大为20M.

根据MySQL手册,对于2G内存的机器,推荐值是20M。

    32G内存的 100M

4.2.1.4:innodb_log_buffer_size(默认8M)

innodb_log_buffer_size=20M

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

   InnoDB 将日志写入日志磁盘文件前的缓冲大小。理想值为 1M 至 8M。大的日志缓冲允许事务运行时不需要将日志保存入磁盘而只到事务被提交(commit)。 因此,如果有大的事务处理,设置大的日志缓冲可以减少磁盘I/O。 在 my.cnf中以数字格式设置。

默认是8MB,系的如频繁的系统可适当增大至4MB~8MB。当然如上面介绍所说,这个参数实际上还和另外的flush参数相关。一般来说不建议超过32MB

注:innodb_flush_log_trx_commit参数对InnoDB Log的写入性能有非常关键的影响,默认值为1。该参数可以设置为0,1,2,解释如下:

0:log buffer中的数据将以每秒一次的频率写入到log file中,且同时会进行文件系统到磁盘的同步操作,但是每个事务的commit并不会触发任何log buffer 到log file的刷新或者文件系统到磁盘的刷新操作;

1:在每次事务提交的时候将log buffer 中的数据都会写入到log file,同时也会触发文件系统到磁盘的同步;

2:事务提交会触发log buffer到log file的刷新,但并不会触发磁盘文件系统到磁盘的同步。此外,每秒会有一次文件系统到磁盘同步操作。

实际测试发现,该值对插入数据的速度影响非常大,设置为2时插入10000条记录只需要2秒,设置为0时只需要1秒,而设置为1时则需要229秒。因此,MySQL手册也建议尽量将插入操作合并成一个事务,这样可以大幅提高速度。根据MySQL手册,在存在丢失最近部分事务的危险的前提下,可以把该值设为0。

 

4.5.1.5:query_cache_size(默认32M)

query_cache_size=40M

     query_cache_size: 主要用来缓存MySQL中的ResultSet,也就是一条SQL语句执行的结果集,所以仅仅只能针对select语句。当我们打开了 Query Cache功能,MySQL在接受到一条select语句的请求后,如果该语句满足Query Cache的要求(未显式说明不允许使用Query Cache,或者已经显式申明需要使用Query Cache),MySQL会直接根据预先设定好的HASH算法将接受到的select语句以字符串方式进行hash,然后到Query Cache中直接查找是否已经缓存。也就是说,如果已经在缓存中,该select请求就会直接将数据返回,从而省略了后面所有的步骤(如SQL语句的解析,优化器优化以及向存储引擎请求数据等),极大的提高性能。根据MySQL用户手册,使用查询缓冲最多可以达到238%的效率。

当然,Query Cache也有一个致命的缺陷,那就是当某个表的数据有任何任何变化,都会导致所有引用了该表的select语句在Query Cache中的缓存数据失效。所以,当我们的数据变化非常频繁的情况下,使用Query Cache可能会得不偿失

   Query Cache的使用需要多个参数配合,其中最为关键的是query_cache_size和query_cache_type,前者设置用于缓存 ResultSet的内存大小,后者设置在何场景下使用Query Cache。在以往的经验来看,如果不是用来缓存基本不变的数据的MySQL数据库,query_cache_size一般256MB是一个比较合适的大小。当然,这可以通过计算Query Cache的命中率(Qcache_hits/(Qcache_hits Qcache_inserts)*100))来进行调整。 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的值也非常大,则表明查询缓冲使用非常频繁,此时需要增加缓冲大小;

根据命中率(Qcache_hits/(Qcache_hits Qcache_inserts)*100))进行调整,一般不建议太大,256MB可能已经差不多了,大型的配置型静态数据可适当调大.

可以通过命令:show status like 'Qcache_%';查看目前系统Query catch使用大小

| Qcache_hits             | 1892463  |

| Qcache_inserts          | 35627  

命中率98.17%=1892463/(1892463  35627 )*100

4.2.2部分缓存

除了全局缓冲,MySql还会为每个连接发放连接缓冲。个连接到MySQL服务器的线程都需要有自己的缓冲。大概需要立刻分配256K,甚至在线程空闲时,它们使用默认的线程堆栈,网络缓存等。事务开始之后,则需要增加更多的空间。运行较小的查询可能仅给指定的线程增加少量的内存消耗,然而如果对数据表做复杂的操作例如扫描、排序或者需要临时表,则需分配大约read_buffer_size,

sort_buffer_size,read_rnd_buffer_size,tmp_table_size 大小的内存空间. 不过它们只是在需要的时候才分配,并且在那些操作做完之后就释放了。有的是立刻分配成单独的组块。tmp_table_size 可能高达MySQL所能分配给这个操作的最大内存空间了

。注意,这里需要考虑的不只有一点——可能会分配多个同一种类型的缓存,例如用来处理子查询。一些特殊的查询的内存使用量可能更大——如果在MyISAM表上做成批的插入

时需要分配 bulk_insert_buffer_size 大小的内存;执行 ALTER TABLE, OPTIMIZE TABLE, REPAIR TABLE 命令时需要分配 myisam_sort_buffer_size 大小的内存。

4.2.2.1:read_buffer_size(默认值:2097144即2M)

read_buffer_size=4M

       read_buffer_size 是MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区,MySql会为它分配一段内存缓冲区。read_buffer_size变量控制这一

缓冲区的大小。如果对表的顺序扫描请求非常频繁,并且你认为频繁扫描进行得太慢,可以通过增加该变量值以及内存缓冲区大小提高其性能.

 

4.2.2.2:sort_buffer_size(默认值:2097144即2M)

sort_buffer_size=4M

    sort_buffer_size是MySql执行排序使用的缓冲大小。如果想要增加ORDER BY的速度,首先看是否可以让MySQL使用索引而不是额外的排序阶段。如果不能,可以尝试增加sort_buffer_size变量的大小

4.2.2.3:  read_rnd_buffer_size(默认值:8388608即8M)

read_rnd_buffer_size=8M

read_rnd_buffer_size 是MySql的随机读缓冲区大小。当按任意顺序读取行时(例如,按照排序顺序),将分配一个随机读缓存区。进行排序查询时,MySql会首先扫描一遍该缓冲,以避免磁盘搜索,提高查询速度,如果需要排序大量数据,可适当调高该值。但MySql会为每个客户连接发放该缓冲空间,所以应尽量适当设置该值,以避免内存开

销过大。

4.2.2.4:  tmp_table_size(默认值:8388608 即:16M)

tmp_table_size=16M

   tmp_table_size是MySql的heap (堆积)表缓冲大小。所有联合在一个DML指令内完成,并且大多数联合甚至可以不用临时表即可以完成。大多数临时表是基于内

存的(HEAP)表。具有大的记录长度的临时表 (所有列的长度的和)或包含BLOB列的表存储在硬盘上。如果某个内部heap(堆积)表大小超过tmp_table_size,MySQL可以根据需要自

动将内存中的heap表改为基于硬盘的MyISAM表。还可以通过设置tmp_table_size选项来增加临时表的大小。也就是说,如果调高该值,MySql同时将增加heap表的大小,可达到提高

联接查询速度的效果。

4.2.2.5:record_buffer:(默认值:)

  record_buffer每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描,你可能想要增加该值。默认数值是131072

(128K)

4.2.2有的缓存

除了全局缓冲,MySql还会为每个连接发放连接缓冲。个连接到MySQL服务器的线程都需要有自己的缓冲。大概需要立刻分配256K,甚至在线程空闲时,它们使用默认的线程堆栈,网络缓存等。事务开始之后,则需要增加更多的空间。运行较小的查询可能仅给指定的线程增加少量的内存消耗,然而如果对数据表做复杂的操作例如扫描、排序或者需要临时表,则需分配大约read_buffer_size,

sort_buffer_size,read_rnd_buffer_size,tmp_table_size 大小的内存空间. 不过它们只是在需要的时候才分配,并且在那些操作做完之后就释放了。有的是立刻分配成单独的组块。tmp_table_size 可能高达MySQL所能分配给这个操作的最大内存空间了

。注意,这里需要考虑的不只有一点——可能会分配多个同一种类型的缓存,例如用来处理子查询。一些特殊的查询的内存使用量可能更大——如果在MyISAM表上做成批的插入

时需要分配 bulk_insert_buffer_size 大小的内存;执行 ALTER TABLE, OPTIMIZE TABLE, REPAIR TABLE 命令时需要分配 myisam_sort_buffer_size 大小的内存。

4.2.2.1:read_buffer_size(默认值:2097144即2M)

read_buffer_size=4M

       read_buffer_size 是MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区,MySql会为它分配一段内存缓冲区。read_buffer_size变量控制这一

缓冲区的大小。如果对表的顺序扫描请求非常频繁,并且你认为频繁扫描进行得太慢,可以通过增加该变量值以及内存缓冲区大小提高其性能.

 

4.2.2.2:sort_buffer_size(默认值:2097144即2M)

sort_buffer_size=4M

    sort_buffer_size是MySql执行排序使用的缓冲大小。如果想要增加ORDER BY的速度,首先看是否可以让MySQL使用索引而不是额外的排序阶段。如果不能,可以尝试增加sort_buffer_size变量的大小

4.2.2.3:  read_rnd_buffer_size(默认值:8388608即8M)

read_rnd_buffer_size=8M

read_rnd_buffer_size 是MySql的随机读缓冲区大小。当按任意顺序读取行时(例如,按照排序顺序),将分配一个随机读缓存区。进行排序查询时,MySql会首先扫描一遍该缓冲,以避免磁盘搜索,提高查询速度,如果需要排序大量数据,可适当调高该值。但MySql会为每个客户连接发放该缓冲空间,所以应尽量适当设置该值,以避免内存开

销过大。

4.2.2.4:  tmp_table_size(默认值:8388608 即:16M)

tmp_table_size=16M

   tmp_table_size是MySql的heap (堆积)表缓冲大小。所有联合在一个DML指令内完成,并且大多数联合甚至可以不用临时表即可以完成。大多数临时表是基于内

存的(HEAP)表。具有大的记录长度的临时表 (所有列的长度的和)或包含BLOB列的表存储在硬盘上。如果某个内部heap(堆积)表大小超过tmp_table_size,MySQL可以根据需要自

动将内存中的heap表改为基于硬盘的MyISAM表。还可以通过设置tmp_table_size选项来增加临时表的大小。也就是说,如果调高该值,MySql同时将增加heap表的大小,可达到提高

联接查询速度的效果。

4.2.2.5:record_buffer:(默认值:)

  record_buffer每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描,你可能想要增加该值。默认数值是131072

(128K)

4.2.2片段缓存

除了全局缓冲,MySql还会为每个连接发放连接缓冲。个连接到MySQL服务器的线程都需要有自己的缓冲。大概需要立刻分配256K,甚至在线程空闲时,它们使用默认的线程堆栈,网络缓存等。事务开始之后,则需要增加更多的空间。运行较小的查询可能仅给指定的线程增加少量的内存消耗,然而如果对数据表做复杂的操作例如扫描、排序或者需要临时表,则需分配大约read_buffer_size,

sort_buffer_size,read_rnd_buffer_size,tmp_table_size 大小的内存空间. 不过它们只是在需要的时候才分配,并且在那些操作做完之后就释放了。有的是立刻分配成单独的组块。tmp_table_size 可能高达MySQL所能分配给这个操作的最大内存空间了

。注意,这里需要考虑的不只有一点——可能会分配多个同一种类型的缓存,例如用来处理子查询。一些特殊的查询的内存使用量可能更大——如果在MyISAM表上做成批的插入

时需要分配 bulk_insert_buffer_size 大小的内存;执行 ALTER TABLE, OPTIMIZE TABLE, REPAIR TABLE 命令时需要分配 myisam_sort_buffer_size 大小的内存。

4.2.2.1:read_buffer_size(默认值:2097144即2M)

read_buffer_size=4M

       read_buffer_size 是MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区,MySql会为它分配一段内存缓冲区。read_buffer_size变量控制这一

缓冲区的大小。如果对表的顺序扫描请求非常频繁,并且你认为频繁扫描进行得太慢,可以通过增加该变量值以及内存缓冲区大小提高其性能.

 

4.2.2.2:sort_buffer_size(默认值:2097144即2M)

sort_buffer_size=4M

    sort_buffer_size是MySql执行排序使用的缓冲大小。如果想要增加ORDER BY的速度,首先看是否可以让MySQL使用索引而不是额外的排序阶段。如果不能,可以尝试增加sort_buffer_size变量的大小

4.2.2.3:  read_rnd_buffer_size(默认值:8388608即8M)

read_rnd_buffer_size=8M

read_rnd_buffer_size 是MySql的随机读缓冲区大小。当按任意顺序读取行时(例如,按照排序顺序),将分配一个随机读缓存区。进行排序查询时,MySql会首先扫描一遍该缓冲,以避免磁盘搜索,提高查询速度,如果需要排序大量数据,可适当调高该值。但MySql会为每个客户连接发放该缓冲空间,所以应尽量适当设置该值,以避免内存开

销过大。

4.2.2.4:  tmp_table_size(默认值:8388608 即:16M)

tmp_table_size=16M

   tmp_table_size是MySql的heap (堆积)表缓冲大小。所有联合在一个DML指令内完成,并且大多数联合甚至可以不用临时表即可以完成。大多数临时表是基于内

存的(HEAP)表。具有大的记录长度的临时表 (所有列的长度的和)或包含BLOB列的表存储在硬盘上。如果某个内部heap(堆积)表大小超过tmp_table_size,MySQL可以根据需要自

动将内存中的heap表改为基于硬盘的MyISAM表。还可以通过设置tmp_table_size选项来增加临时表的大小。也就是说,如果调高该值,MySql同时将增加heap表的大小,可达到提高

联接查询速度的效果。

4.2.2.5:record_buffer:(默认值:)

  record_buffer每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描,你可能想要增加该值。默认数值是131072

(128K)

4.2.3任何缓存:

4.2.3.1:table_cache(默认值:512)

 

TABLE_CACHE(5.1.3及以后版本又名TABLE_OPEN_CACHE)

table_cache指定表高速缓存的大小。每当MySQL访问一个表时,如果在表缓冲区中还有空间,该表就被打开并放入其中,这样可以更快地访问表内容。通过检查峰值时间的状态值Open_tables和Opened_tables,可以决定是否需要增加table_cache的值。如果你发现open_tables等于table_cache,并且opened_tables在不断增长,那么你就需要增加table_cache的值了(上述状态值可以使用SHOW STATUS LIKE ‘Open%tables’获得)。注意,不能盲目地把table_cache设置成很大的值。如果设置得太高,可能会造成文件描述符不足,从而造成性能不稳定或者连接失败。

SHOW STATUS LIKE 'Open%tables';

--------------- -------

| Variable_name | Value |

--------------- -------

| Open_tables   | 356   |

| Opened_tables | 0     |

--------------- -------

2 rows in set (0.00 sec)

open_tables表示当前打开的表缓存数,如果执行flush tables操作,则此系统会关闭一些当前没有使用的表缓存而使得此状态值减小;

opend_tables表示曾经打开的表缓存数,会一直进行累加,如果执行flush tables操作,值不会减小。

在mysql默认安装情况下,table_cache的值在2G内存以下的机器中的值默认时256到512,如果机器有4G内存,则默认这个值 是2048,但这决意味着机器内存越大,这个值应该越大,因为table_cache加大后,使得mysql对SQL响应的速度更快了,不可避免的会产生 更多的死锁(dead lock),这样反而使得数据库整个一套操作慢了下来,严重影响性能。所以平时维护中还是要根据库的实际情况去作出判断,找到最适合你维护的库的 table_cache值。

由于MySQL是多线程的机制,为了提高性能,每个线程都是独自打开自己需要的表的文件描 述符,而不是通过共享已经打开的.针对不同存储引擎处理的方法当然也不一样

在myisam表引擎中,数据文件的描述符 (descriptor)是不共享的,但是索引文件的描述符却是所有线程共享的.Innodb中和使用表空间类型有关,假如是共享表空间那么实际就一个数 据文件,当然占用的数据文件描述符就会比独立表空间少.
mysql手册上给的建议大小 是:table_cache=max_connections*n

n表示查询语句中最大表数, 还需要为临时表和文件保留一些额外的文件描述符。

这个数据遭到很多质疑,table_cache够用就好,检查 Opened_tables值,如果这个值很大,或增长很快那么你就得考虑加大table_cache了.

  table_cache:所有线程打开的表的数目。增大该值可以增加mysqld需要的文件描述符的数量。默认值是64.

 

4.2.3.2 thread_cache_size (服务器线程缓存)

thread_cache_size=64

默认的thread_cache_size=8,不过看看众多布署的样例里的值一般是32,64,以致是128,感到这一个参数对优化应该有支持,于是查了下:
基于检察发掘上述劳动器线程缓存thread_cache_size没有进行安装,或然安装过小,那几个值表示能够重新利用保存在缓存中线程的多寡,当断开连接时一旦缓存中还恐怕有空间,那么客户端的线程将被平放慢存中,要是线程重新被呼吁,那么央求将从缓存中读取,要是缓存中是空的或许是新的呼吁,那么这么些线程将被重复创制,要是有众多新的线程,扩张这些值能够革新系统质量.通过相比较 Connections 和 Threads_created 状态的变量,能够看出这些变量的功能。(–>表示要调节的值)   依据物理内部存款和储蓄器设置法则如下:
1G —> 8
2G —> 16
3G —> 32     >3G —> 64

  mysql> show status like 'thread%';
——————- ——-
| Variable_name     | Value |
——————- ——-
| Threads_cached    | 0     |  <—当前被缓存的空余线程的数目
| Threads_connected | 1     |  <—正在接纳(处于连接意况)的线程
| Threads_created   | 1498  |  <—服务运维以来,创立了略微个线程
| Threads_running   | 1     |  <—正在忙的线程(正在询问数据,传输数据等等操作)
——————- ——-

翻看开机起来数据库被接连了多少次?

mysql> show status like '%connection%';
———————- ——-
| Variable_name        | Value |
———————- ——-
| Connections          | 1504  |          –>服务运行以来,历史连接数
| Max_used_connections | 2     |
———————- ——-

透过连接线程池的命中率来决断设置值是还是不是适合?命中率当先百分之八十上述,设定合理。

 (Connections -  Threads_created) / Connections * 100 %

5.查看命令总计

5.1.0翻看各参数内存配置格局
#全局分享内部存款和储蓄器 9个变量
show variables like 'innodb_buffer_pool_size'; /* InnoDB 数据和目录缓存(InnoDB Buffer Pool) */
show variables like 'innodb_additional_mem_pool_size'; /* InnoDB 字典音信缓存(InnoDB Additional Memory Pool)*/
show variables like 'innodb_log_buffer_size'; /* InnoDB 日志缓冲区(InnoDB Log Buffer) */
show variables like 'binlog_cache_size'; /* 二进制日志缓冲区(Binlog Buffer)*/
show variables like 'thread_cache_size'; /* 连接线程缓存(Thread Cache)*/
show variables like 'query_cache_size'; /* 查询缓存(Query Cache)*/
show variables like 'table_open_cache'; /* 表缓存(Table Cache) */
show variables like 'table_definition_cache'; /* 表定义音信缓存(Table definition Cache) */
show variables like 'key_buffer_size'; /* MyISAM索引缓存(Key Buffer) */
#最大线程数
show variables like 'max_connections';
#线程独享内部存储器 6个变量
show variables like 'thread_stack'; /* 线线程栈音讯运用内部存款和储蓄器(thread_stack) */
show variables like 'sort_buffer_size'; /* 排序使用内部存储器(sort_buffer_size) */
show variables like 'join_buffer_size'; /* Join操作使用内部存款和储蓄器(join_buffer_size) */
show variables like 'read_buffer_size'; /* 顺序读取数据缓冲区使用内部存款和储蓄器(read_buffer_size) */
show variables like 'read_rnd_buffer_size'; /* 随机读取数据缓冲区使用内部存款和储蓄器(read_rnd_buffer_size) */
show variables like 'tmp_table_size'; /* 不经常表使用内部存款和储蓄器(tmp_table_size) ,笔者实际总结把tmp_table_size放入全局共享内*/
也得以透过系统变量的秘籍直接拿走
select @@key_buffer_size;
select @@max_connections

5.1.1mysql内部存款和储蓄器总计公式
mysql使用的内部存款和储蓄器 = 全局分享内存 最大线程数×线程独享内部存款和储蓄器
mysql used mem=innodb_buffer_pool_size innodb_additional_mem_pool_size innodb_log_buffer_size binlog_cache_size thread_cache_size query_cache_size table_open_cache table_definition_cache key_buffer_size
max_connections*(
thread_stack sort_buffer_size join_buffer_size read_buffer_size read_rnd_buffer_size tmp_table_size)

SET @kilo_bytes=1024;
SET @mega_bytes=@kilo_bytes*1024;
SET @giga_bytes=@mega_bytes*1024;
SELECT (@@innodb_buffer_pool_size @@innodb_additional_mem_pool_size @@innodb_log_buffer_size @@binlog_cache_size @@thread_cache_size @@query_cache_size @@table_open_cache @@table_definition_cache @@key_buffer_size @@max_connections*(@@thread_stack @@sort_buffer_size @@join_buffer_size @@read_buffer_size @@read_rnd_buffer_size @@tmp_table_size))/@giga_bytes AS MAX_MEMORY_GB;

以此理论最大的内部存款和储蓄器使用量,在5.5本子中tmp_table_size暗中认可是16M,按暗许u自卢萨卡接数151乘除,光线程独享的一时表占有的空中都是2416M,笔者骨子里总计把tmp_table_size归入全局分享内
本身的计算公式
mysql使用的内部存储器 = 全局分享内部存储器 最大线程数×线程独享内部存款和储蓄器
mysql used mem=innodb_buffer_pool_size innodb_additional_mem_pool_size innodb_log_buffer_size binlog_cache_size thread_cache_size query_cache_size table_open_cache table_definition_cache key_buffer_size tmp_table_size
max_connections*(
thread_stack sort_buffer_size join_buffer_size read_buffer_size read_rnd_buffer_size)

4.2.3另外缓存:

4.2.3.1:table_cache(默认值:512)

 

TABLE_CACHE(5.1.3及以后版本又名TABLE_OPEN_CACHE)

table_cache指定表高速缓存的大小。每当MySQL访问一个表时,如果在表缓冲区中还有空间,该表就被打开并放入其中,这样可以更快地访问表内容。通过检查峰值时间的状态值Open_tables和Opened_tables,可以决定是否需要增加table_cache的值。如果你发现open_tables等于table_cache,并且opened_tables在不断增长,那么你就需要增加table_cache的值了(上述状态值可以使用SHOW STATUS LIKE ‘Open%tables’获得)。注意,不能盲目地把table_cache设置成很大的值。如果设置得太高,可能会造成文件描述符不足,从而造成性能不稳定或者连接失败。

SHOW STATUS LIKE 'Open%tables';

--------------- -------

| Variable_name | Value |

--------------- -------

| Open_tables   | 356   |

| Opened_tables | 0     |

--------------- -------

2 rows in set (0.00 sec)

open_tables表示当前打开的表缓存数,如果执行flush tables操作,则此系统会关闭一些当前没有使用的表缓存而使得此状态值减小;

opend_tables表示曾经打开的表缓存数,会一直进行累加,如果执行flush tables操作,值不会减小。

在mysql默认安装情况下,table_cache的值在2G内存以下的机器中的值默认时256到512,如果机器有4G内存,则默认这个值 是2048,但这决意味着机器内存越大,这个值应该越大,因为table_cache加大后,使得mysql对SQL响应的速度更快了,不可避免的会产生 更多的死锁(dead lock),这样反而使得数据库整个一套操作慢了下来,严重影响性能。所以平时维护中还是要根据库的实际情况去作出判断,找到最适合你维护的库的 table_cache值。

由于MySQL是多线程的机制,为了提高性能,每个线程都是独自打开自己需要的表的文件描 述符,而不是通过共享已经打开的.针对不同存储引擎处理的方法当然也不一样

在myisam表引擎中,数据文件的描述符 (descriptor)是不共享的,但是索引文件的描述符却是所有线程共享的.Innodb中和使用表空间类型有关,假如是共享表空间那么实际就一个数 据文件,当然占用的数据文件描述符就会比独立表空间少.
mysql手册上给的建议大小 是:table_cache=max_connections*n

n表示查询语句中最大表数, 还需要为临时表和文件保留一些额外的文件描述符。

这个数据遭到很多质疑,table_cache够用就好,检查 Opened_tables值,如果这个值很大,或增长很快那么你就得考虑加大table_cache了.

  table_cache:所有线程打开的表的数目。增大该值可以增加mysqld需要的文件描述符的数量。默认值是64.

 

4.2.3.2 thread_cache_size (服务器线程缓存)

thread_cache_size=64

默认的thread_cache_size=8,但是看到好多配置的样例里的值一般是32,64,甚至是128,感觉这个参数对优化应该有帮助,于是查了下:
根据调查发现以上服务器线程缓存thread_cache_size没有进行设置,或者设置过小,这个值表示可以重新利用保存在缓存中线程的数量,当断开连接时如果缓存中还有空间,那么客户端的线程将被放到缓存中,如果线程重新被请求,那么请求将从缓存中读取,如果缓存中是空的或者是新的请求,那么这个线程将被重新创建,如果有很多新的线程,增加这个值可以改善系统性能.通过比较 Connections 和 Threads_created 状态的变量,可以看到这个变量的作用。(–>表示要调整的值)   根据物理内存设置规则如下:
1G —> 8
2G —> 16
3G —> 32     >3G —> 64

  mysql> show status like 'thread%';
——————- ——-
| Variable_name     | Value |
——————- ——-
| Threads_cached    | 0     |  <—当前被缓存的空闲线程的数量
| Threads_connected | 1     |  <—正在使用(处于连接状态)的线程
| Threads_created   | 1498  |  <—服务启动以来,创建了多少个线程
| Threads_running   | 1     |  <—正在忙的线程(正在查询数据,传输数据等等操作)
——————- ——-

查看开机起来数据库被连接了多少次?

mysql> show status like '%connection%';
———————- ——-
| Variable_name        | Value |
———————- ——-
| Connections          | 1504  |          –>服务启动以来,历史连接数
| Max_used_connections | 2     |
———————- ——-

通过连接线程池的命中率来判断设置值是否合适?命中率超过90%以上,设定合理。

 (Connections -  Threads_created) / Connections * 100 %

4.2.3别的缓存:

4.2.3.1:table_cache(默认值:512)

 

TABLE_CACHE(5.1.3及以后版本又名TABLE_OPEN_CACHE)

table_cache指定表高速缓存的大小。每当MySQL访问一个表时,如果在表缓冲区中还有空间,该表就被打开并放入其中,这样可以更快地访问表内容。通过检查峰值时间的状态值Open_tables和Opened_tables,可以决定是否需要增加table_cache的值。如果你发现open_tables等于table_cache,并且opened_tables在不断增长,那么你就需要增加table_cache的值了(上述状态值可以使用SHOW STATUS LIKE ‘Open%tables’获得)。注意,不能盲目地把table_cache设置成很大的值。如果设置得太高,可能会造成文件描述符不足,从而造成性能不稳定或者连接失败。

SHOW STATUS LIKE 'Open%tables';

--------------- -------

| Variable_name | Value |

--------------- -------

| Open_tables   | 356   |

| Opened_tables | 0     |

--------------- -------

2 rows in set (0.00 sec)

open_tables表示当前打开的表缓存数,如果执行flush tables操作,则此系统会关闭一些当前没有使用的表缓存而使得此状态值减小;

opend_tables表示曾经打开的表缓存数,会一直进行累加,如果执行flush tables操作,值不会减小。

在mysql默认安装情况下,table_cache的值在2G内存以下的机器中的值默认时256到512,如果机器有4G内存,则默认这个值 是2048,但这决意味着机器内存越大,这个值应该越大,因为table_cache加大后,使得mysql对SQL响应的速度更快了,不可避免的会产生 更多的死锁(dead lock),这样反而使得数据库整个一套操作慢了下来,严重影响性能。所以平时维护中还是要根据库的实际情况去作出判断,找到最适合你维护的库的 table_cache值。

由于MySQL是多线程的机制,为了提高性能,每个线程都是独自打开自己需要的表的文件描 述符,而不是通过共享已经打开的.针对不同存储引擎处理的方法当然也不一样

在myisam表引擎中,数据文件的描述符 (descriptor)是不共享的,但是索引文件的描述符却是所有线程共享的.Innodb中和使用表空间类型有关,假如是共享表空间那么实际就一个数 据文件,当然占用的数据文件描述符就会比独立表空间少.
mysql手册上给的建议大小 是:table_cache=max_connections*n

n表示查询语句中最大表数, 还需要为临时表和文件保留一些额外的文件描述符。

这个数据遭到很多质疑,table_cache够用就好,检查 Opened_tables值,如果这个值很大,或增长很快那么你就得考虑加大table_cache了.

  table_cache:所有线程打开的表的数目。增大该值可以增加mysqld需要的文件描述符的数量。默认值是64.

 

4.2.3.2 thread_cache_size (服务器线程缓存)

thread_cache_size=64

默认的thread_cache_size=8,但是看到好多配置的样例里的值一般是32,64,甚至是128,感觉这个参数对优化应该有帮助,于是查了下:
根据调查发现以上服务器线程缓存thread_cache_size没有进行设置,或者设置过小,这个值表示可以重新利用保存在缓存中线程的数量,当断开连接时如果缓存中还有空间,那么客户端的线程将被放到缓存中,如果线程重新被请求,那么请求将从缓存中读取,如果缓存中是空的或者是新的请求,那么这个线程将被重新创建,如果有很多新的线程,增加这个值可以改善系统性能.通过比较 Connections 和 Threads_created 状态的变量,可以看到这个变量的作用。(–>表示要调整的值)   根据物理内存设置规则如下:
1G —> 8
2G —> 16
3G —> 32     >3G —> 64

  mysql> show status like 'thread%';
——————- ——-
| Variable_name     | Value |
——————- ——-
| Threads_cached    | 0     |  <—当前被缓存的空闲线程的数量
| Threads_connected | 1     |  <—正在使用(处于连接状态)的线程
| Threads_created   | 1498  |  <—服务启动以来,创建了多少个线程
| Threads_running   | 1     |  <—正在忙的线程(正在查询数据,传输数据等等操作)
——————- ——-

查看开机起来数据库被连接了多少次?

mysql> show status like '%connection%';
———————- ——-
| Variable_name        | Value |
———————- ——-
| Connections          | 1504  |          –>服务启动以来,历史连接数
| Max_used_connections | 2     |
———————- ——-

通过连接线程池的命中率来判断设置值是否合适?命中率超过90%以上,设定合理。

 (Connections -  Threads_created) / Connections * 100 %

 

本文由澳门新萄京官方网站发布于数据库网络,转载请注明出处:mySQL占用虚拟内存达8百多兆问题解决思路,mysq

关键词: