mg娱乐电子4355_mg娱乐电子游戏平台
做最好的网站

MySQL常见配置参数调优

时间:2019-11-11 08:26来源:计算机数据
从这篇开始,讲innodb存储引擎中,对于几个重要的服务器参数配置。这些参数以innodb_xx开头。 mysql的各种参数有300余种,可以将其分为两类:一是缓存参数,二是个性化参数。对缓存参

从这篇开始,讲innodb存储引擎中,对于几个重要的服务器参数配置。这些参数以innodb_xx 开头。

mysql的各种参数有300余种,可以将其分为两类:一是缓存参数,二是个性化参数。对缓存参数的配置在一定程度内对mysql性能的影响是显著的。同时各种个性化参数的设置,可以使mysql表现出不同的性状。

  1. innodb_buffer_pool_size的设置

1.缓存参数

目前常用的存储引擎有两种,一是myisam,另一种是innodb。关于这两种存储引擎的异同这里就不做过多的介绍。使用存储引擎的不同,对参数的优化也会不一样。但有一些缓存参数是跨存储引擎的,就是无论使用何种存储引擎,它都会发挥其作用。下面将按三类对其进行详细的介绍。

         这个参数定义了innodb存储引擎的表数据和索引数据的最大内存缓冲区大小,和myisam不同,myisam的key_buffer_size只缓存索引键,而innodb_buffer_pool_size是同时为数据块和索引块做缓存的。这个特性与oracle是一样的,这个值设得越高,访问表中数据需要的磁盘i/o就越少(物理I/O)。在一个专用的数据库服务器上,可以设置这个参数达机器物理内存大小的50--80%。考虑点:在单独给 MySQL 使用的主机里,内存分配还包括系统使用,线程独享,myisam缓存等。还有允许的并发连接数。还有建议不要把它设置得太大,因为对物理内存的竞争可能在操作系统上导致内存调度。

1.1 跨引擎缓存参数优化

这类缓存参数是针对查询的优化,优化方向是sql、表、日志、线程对象的缓存优化。具体说明如下:

-- innodb缓存区大小(kb)
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

1.1.1 query cache

query cache将已经执行过的sql和结果集放在缓存区中,如果再有同样的select语句(区分大小写),将直接从缓存区中读取,这样能大大提高query语句的查询效率,但是,如果要query的表经常被更新,则会导致在cache中的sql失效,这时使用query cache不但不能提高效率反而会使数据库的性能变得更差。所以使用query cache时需要对这点留意。

使用query cache时,需要先将query_cache_type设置为ON(打开查询缓存)。同时需要对要缓存的结果集的大小进行限制。query_cache_limit=1M(最大结果集为1M),query_cache_min_res_unit=1K(结果集最小为1K),不在这个范围内的结果集将不会被缓存。query cahce的大小以字节为单位,须为1024的整数倍,建议为系统内存的1/8,不超过256M。通过查看query的global status来查看设置是否合理:

Qcache_free_blocks:目前还处于空闲状态的 Query Cache 中内存 Block 数目

Qcache_free_memory:目前还处于空闲状态的 Query Cache 内存总量

Qcache_hits:Query Cache 命中次数

Qcache_inserts:向 Query Cache 中插入新的 Query Cache 的次数,也就是没有命中的次数

Qcache_lowmem_prunes:当 Query Cache 内存容量不够,需要从中删除老的 Query Cache 以给新的 Cache 对象使用的次数

Qcache_not_cached:没有被 Cache 的 SQL 数,包括无法被 Cache 的 SQL 以及由于

Qcache_queries_in_cache:目前在 Query Cache 中的 SQL 数量

Qcache_total_blocks:Query Cache 中总的 Block 数量

命中率: [Qcache_hits /( Qcache_hits+ Qcache_inserts)]*100%

如果命中率高,且Qcache_free_memory大则说明query_cache_size设置过大

如果命中率低,且Qcache_lowmem_prunes大则说明query_cache_size设置过小

 

图片 1  SELECT 268435456/1024.0/2014.0=130M。

1.1.2 table cache

为了解决打开表描述文件符太过频繁的问题,mysql在系统中实现了一个table cache机制,用来cache打开的所有表文件的描述符。通过这样的方式来减少因为频繁打开关闭文件描述符所带来的资源消耗。

table cache的设置与mysql设置的最大连接数成正比,其比例值等于一个connection打开多少表,计算方式如下:

table_cache=max_connection*N

使用flush table来关闭所有文件描述符,通过查看table open状态来查看参数设置是否合理,合理的设置应该如下:

open_tables/opened_tables>=0.85

open_tables/table_cache<=0.95

查看内存大小:
[root@xuegod64 ~]# cat /proc/meminfo

1.1.3 join buffer(线程独有)

当join查询的类型是all,index,range或者index_merge的时候就会使用到join buffer。实际上参与join的每个表都会用到join buffer,所以一条sql会用到至少两个join buffer。join buffer在5.1.23的版本前的最大值是4G,但之后,除了windows外,其它64位的平台可以超出4G的限制。系统默认为128k,但是如果join语句多,建议为1M,内存充足则可以增加到2M。

    图片 2

1.1.4 sort buffer(线程独有)

当sql需要进行排序操作时会用到sort buffer。通过增大sort buffer的大小可以提高order by或group by的处理性能。系统默认为2M,最大限制和join buffer一样,一般设置在2mb~4mb之间可以满足大多数应用的需求。

  上面内存约等于2031912/1024.0=1984M。
2. buffer_pool 运行参数

1.1.5 Binlog_cache_size(线程独有)

二进制日志缓存支持事务存储引擎并且服务器启用了二进制日志的前提下为每个客户端分配内存。通过binlog_cache_use和binlog_cache_disk_use来判断当前binlog_cache_size是否合适,默认值1M

与binlog_cache_size对应,max_binlog_cache_size,代表binlog能够使用的最大的cache的大小。注意,当执行多语句事务的时候,max_binlog_cache_size不够大的话,系统会保出”multi-statement transaction required more than ‘max_binlog_cache_size’ bytes of storage”的错误。

-- 下面是buffer_pool 运行相关参数
SHOW STATUS LIKE  'Innodb_buffer_pool_%';

1.1.6 Thread_cache_size

Thread cache池中应该存放的连接线程数,当系统最初启动的时候,并不会马上就创建thread_cache_size池中,而是随着连接线程的创建及使用,慢慢的将用完的连接线程存入其中。当到达指定值后,mysql就不会再续保存用完的连接线程了。在长连接的环境中我们不需要将thread_cache_size参数设置太大,一般来说50~100就可以了。

Thread cache命中率:thread_cache_hit=(connections-threads_created)/connections * 100%,thread cache命中率应该保持在90%左右甚至更高的比率才正常。

  图片 3

1.1.7 Read_buffer_size(线程独有)

以sequential scan方式扫描表数据时候使用的buffer。系统默认128kb,最大2GB,设置的值必须是4KB的倍数,否则系统会自动改成小于设置值的4kb的倍数。一般来说,可以适当调大该参数看是否能够 改善全表扫描的性能。在不同平台上可能会有不同表现,所以该参数的设置最好是在真是环境上面通 过多次更改测试调整,才能选找到一个最佳值

编辑:计算机数据 本文来源:MySQL常见配置参数调优

关键词: