MySQL常见配置参数调优

从这篇开始,讲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的倍数。一般来说,可以适当调大该参数看是否能够
改善全表扫描的性能。在不同平台上可能会有不同表现,所以该参数的设置最好是在真是环境上面通
过多次更改测试调整,才能选找到一个最佳值

Innodb_buffer_pool_pages_total 

缓存池页总数目。
共占用了16382 页 。单位page

Innodb_buffer_pool_pages_free

缓存池剩余的页数目。
在16382 页中有2000页没有使用。 单位page

Innodb_buffer_pool_pages_data

缓存池中包含数据的页的数目,包括脏页。
14273个页含有数据。单位page

Innodb_buffer_pool_read_requests

innodb进行逻辑读的数量。
529670886次请求读。单位次数

Innodb_buffer_pool_reads

进行逻辑读取时无法从缓冲池中获取而执行单页读取的次数。
941147次是物理I/0读取。单位次数

Innodb_buffer_pool_write_requests

写入 InnoDB 缓冲池的次数。
48606702次请求写入。单位次数

Innodb_buffer_pool_read_ahead_rnd

记录进行随机读的时候产生的预读次数。
0次

Innodb_buffer_pool_read_ahead

预读到innodb buffer pool里次数。
1465370次。  单位page

Innodb_buffer_pool_read_ahead_evicted

预读的页数,但是没有被读取就从缓冲池中被替换的页的数量,一般用来判断预读的效率。 0次

Innodb_buffer_pool_wait_free

数据要写入buffer pool的时候,需要等待空闲页的次数。是指缓存池里没有干净页的时候读取或创建页,要先等待页被刷新。
2927次。 单位次数。

Innodb_buffer_pool_pages_dirty

buffer pool缓存池中脏页的数目。
0次。单位是page

Innodb_buffer_pool_pages_flushed

buffer pool缓存池中刷新页请求的数目。
15437744次。单位page

Innodb_buffer_pool_pages_misc

buffer pool缓存池中当前页已经被用作管理用途或hash index而不能用作为普通数据页的数目。
109次。单位page

Innodb_buffer_pool_pages_old

在旧区域存放着多少个页。
5249次。单位page

Innodb_buffer_pool_pages_made_young

移动到新区域的有多少个页。
353059次。单位page

Innodb_buffer_pool_pages_made_not_young

没有移动到新区域的有多少个页。
31725809次。单位page

1.1.8 Read_rnd_buffer_size(线程独有)

以random
scan方式扫描表数据时候使用的buffer。默认256kb,最大4gb。一般来讲,该值适当
调大对提高order by操作的性能有一定的效果

 

  Buffer Pool使用率:14328.0/16382.0 *100=87.46%
  缓存读命中率: (529670886-941147)/529670886.0 *100 =99.82%
  实际占用空间是:16382 *16(页单位)*1024=268402688 字节。
上面给buffer_pool_size分配的是268435456字节。

1.2 myisam缓存参数优化

影响myisam存储引擎效率的缓存参数是key_buffer_size(索引缓存大小)。用来缓存myisam表的索引。32为平台不要超过2G,64位平台不要超过4G,一般设置为可用内存的30%-40%,请至少保留16~32M的大小,以适应给予磁盘临时表所需。

对于key_buffer_size的设置可以通过三个指标来计算,第一个是索引的总大小,第二个是系统可用物理内存,第三个是系统当前的key
cache命中率。

Key_size=key_number*(key_length+4)/0.67

Max_key_buffer_size<系统可用物理内存 – 线程使用的内存(Thread_usage)

Thread_usage=max_connections*(sort_buffer_size+join_buffer_size+read_buffer_size+read_rnd_buffer_size+thread_stack)

通过以下三个比率数据,就可以知道key cache设置是否合理:

Key_buffer使用率=(1-key_blocks_used/(key_blocks_used+key_blocks_unused))*100%

Key_buffer_read_hitratio=(1-key_reads/key_read_requests)*100%

Key_buffer_write_hitratio=(1-key_writes/key_write_requests)*100%

一般来说key_buffer使用率应该在99%以上,key_buffer_read_hitratio也应该尽可能地高

  1. 设置buffer_pool参数

    — 从134217728设置成268435456 (另一台mysql)
    SET GLOBAL innodb_buffer_pool_size= 268435456

1.3 innodb缓存参数优化

innodb的缓存参数主要是两个方面,一类是数据索引(innodb_buffer_pool)结构,另一类是日志(Innodb_log_buffer)。

  由于SHOW
VARIABLES下的参数都是静态值。当mysql重启时,上面的缓存设置将失效。

1.3.1 innodb_buffer_pool

innodb_buffer_pool不同于key_buffer的地方是它不仅缓存索引还会缓存实际的数据。所以完全相同的数据库,使用innodb存储引擎可以使用更多的内存来缓存数据库相关的信息。

关于innodb_buffer_pool_size的设置,建议设置为系统物理内存的50%-80%之间,最好是比innodb的tablespace多10%的空间。

通过以下两个比率来查看innodb_buffer_pool_size的值是否设置过大:

使用率:(innodb_buffer_pool_pages_data/innodb_buffer_pool_pages_total)*100%
>90%

命中率:(innodb_buffer_pool_read_requests-innodb_buffer_pool_reads)/

innodb_buffer_pool_read_requests * 100%  >90%

[root@xuegod64 ~]# systemctl stop mysqld.service
[root@xuegod64 ~]# systemctl start  mysqld.service

1.3.2 Innodb_additional_mem_pool_size

Innodb_additional_mem_pool_size用于存放innodb的字典信息和其他一些内部结构所需要的内存空间。Innodb表越多,需要的空间就越大,系统默认为1M。

一个常规的几百个innodb表的mysql,如果不是每个表都是有上百个字段的话,20M内存就足够,设置超过实际所需要的内存并没有太大的意义,只是浪费内存而已。

 

  重启后还是134217728.如下图
  欧洲杯竞猜平台 4

1.3.3 innodb_log_buffer

innodb事物日志所使用的缓存,系统默认为1M,一般来说如果不是编写负载非常高且以大事物居多的话8M以内的大小完全够用。

 

  要永久改变,需要在操作系统里使用vim my.cnf 来修改. 如下图所示,
去掉#重新定义值。

2.个性化参数

mysql的个性化参数很多,下面只介绍对mysql影响较大的参数。

  欧洲杯竞猜平台 5

2.1 服务器级别的参数

log_bin= /var/log/mysql/mysql-bin.log 

打开二进制日志,/var/log/mysql/二进制日志的位置,mysql-bin为二进制日志名

max_binlog_size=256M

二进制日志的大小设为512或1G,不能超过1G。该大小并不能严格限制binlog的大小,尤其是当binlog
比较靠近尾部而又遇到一个较大事务的时候,为了保证事务完整性,系统不做切换日志的动作。

Sync_binlog  = 0

这个参数对mysql系统来说至关重要,它不仅影响binlog对mysql所带来的性能损耗,而且还影响到
mysql中数据的完整性,系统默认设置为0。Sync_binlog=0,事务提交后,mysql不做fsync之类的磁盘
同步指令刷新binlog——cache中的信息到磁盘,而让filesystem自行决定什么时候做同步,或者cache
满了之后才同步到磁盘。Sync_binlog=n,当每进行n次事务提交后,mysql进行一次fsync之类的磁盘同
步指令来见binlog_cache中的数据写入磁盘。

Binlog_format=mixed

二进制日志的存储格式statement、row、mixed

Expire_logs_days=10

控制mysql binlog日志的保存期限

log_slow_queries = mysql-slow

Slow query
log功能对系统性能的整体影响没有binlog那么大,带来的io损耗也比较小。但是系统需
要计算每一条query的执行时间,在cpu方面会有所消耗。在cpu资源比较紧张的情况下,可以在大部
分时间关闭该功能, 默认关闭。

#等价于

slow_query_log=on

slow_query_log_file=mysql-slow

Long_query_time = 0.05

该值决定,超过多少时间才可以算做是慢查询,单位为秒。

Sql_mode=””

mysql服务器的sql模式:非严格模式。

使用非严格模式时,存储的字符超过他们定义的长度时候,如果不是在sql服务器的严格模式(STRICT_ALL_TABLES)下,都会
自动截取合适的字段存储,而不会出现错误。但是,如果是中文的话同样要报错误:)比如定义char(4),
然后insert (‘c哈哈’).

Lower_case_table_names=0

Mysql在window环境下默认是忽略大小写的,而linux环境中则相反。通过lower_case_table_names
来解决由于大小写带来的数据库移植问题。0:区分大小写,1:不区分大小写

Back_log = 50

在mysql的连接请求等待队列中允许存放的最大连接请求数,默认值为50

Max_connections=500

整个mysql允许的最大连接数。这个参数主要影响整个mysql应用的并发处理能力,一般来说,只要
mysql主机性能允许,都将该参数设置得尽可能大一点。500~800是比较合适的值

Max_allowed_packet=1M

在网络传输中,一次消息数量的最大值。系统默认为1M,最大值是1GB,必须设定为1024的倍数,
单位为字节。

Thread_stack=192K

当mysql创建一个新的连接线程的时候,需要给他分配一定大小的内存堆栈空间,以便存放客户端的
请求query以及自身的各种状态和处理信息。系统默认值192KB

Skip_external_locking

跳过外部锁定,external-locking用于多进程条件下为myisam数据表进行锁定。如果有多台服务器使
用同一个数据库目录(不建议),那么每台服务器都必须开启external-locking

Thread_concurrency=4

设置该值的正确与否,对mysql的性能影响很大,在多个cpu(多核)工作的情况下,错误地设置了该
值,会导致mysql不能充分利用cpu,出现同一时刻只能使用一个cpu的情况。Thread_concurrency应
设为cpu核数的2倍。一个双核cpu,thread_concurrency=4

Transaction_isolation

事务的隔离级别,read uncommited 、read commited、 

repeatable read(innodb默认隔离级别)、serializable

Tmp_table_size

断开mysql的连接后系统会自动删除临时表中的数据,在一个数据库连接里面发行多次sql的话系统是
不会自动清空临时表数据的。系统默认32M,如果有很多高级group by查询,增加
tmp_table_size的值。Tmp_table_size不宜过大,否则一旦超过此限制,copy
to disk的过程确实会很 长。查询需要order by或者group
by等需要用到结果集时,参数中设置的临时表的大小小于结果集的
大小时,就会将该表放在磁盘上,造成io负载。另外如果Max_heap_table_size比tmp_table_size小的
话,会把Max_heap_table_size作为最大的内存临时表限制。

Skip_name_resolve

禁止mysql
server对外部连接进行DNS解析,使用这一选项可以消除mysql进行DNS解析的时间。
注意,启动该选项后,所有主机连接授权都要使用ip地址方式

Slave_skip_errors

使用slave_skip_errors=error_code设置跳过重复的记录。Error_code设置过大或过小都会无效。通
过show slave
status\G;的last_error里获取才是正确的。但是以slave-skip-errors条件运行得mysql默
认在以后所有的从mysql上有错误,都自动跳过。

Replicate_ignore_db=dbtest

主从时忽略同步指定数据库,用replicate_do_db和replicate_ignore_db时有一个隐患,跨库更新时会
出错,会忽略use dbtest下的sql,而这些sql可能是修改其它库的内容,如:update mysql.user……。可 以使用replicate_wild_do_table和replicate_wild_ignore_table来代替如

replicate_wild_do_table=test.%

或replicate_wild_ignore_table=mysql.%这样就可以避免出现上述问题了

Relay_log

中继日志

delayed_queue_size

此参数在执行其他insert delayed语句的客户机阻塞以前,确定来自insert
delayed语句的放入队列的
行的数目。增加这个参数的值使服务 能从这种请求中接受更多的行,因而客户机可以继续执行而不
阻塞。

 

2.2 myisam存储引擎级别的参数

Query_cache_wlock_invalidate

针对myisam存储引擎,设置当有write
lock在某个table上面的时候,读请求是要等待write lock释
放资源之后再查询还是允许直接从query
cache中读取数据。这个选项指是否缓存其他连接已经锁定 的表,默认值是off

Key_cache_block_size=1024

设置cache block的大小,限定将‘.myi’文件中的index block被读入时候的file
block的大小

Concurrent_insert=0

Myisam默认读锁是read local,使用read local时concurrent_insert设置才有效

此参数可以控制myisam存储引擎查询和插入操作的并发进行。

0:不允许并发插入

1:如果表中没有被删除的行,myisam允许一个进程读表的同时,另一个进程从表尾插入记录

2:无论myisam表中是否有空洞,都允许在表尾插入记录。

Low-priority-updates

给myisam给予读请求优先的权力

Max_write_lock_count=100

设置一个合适的值,当一个表的读锁到达这个值后,mysql就暂时将写请求的优先级降低,给读进程
一个获得锁的机会。

Bulk_insert_buffer_size=8388608

myisam存储引擎在进行批量插入时会用到的缓存参数。批量插入有:

insert into …value(…),(…),(…);

load data infile ….

Insert into …select …

2.3 innodb存储引擎级别的参数

Innodb_max_dirty_pages_pct=75

当脏数据达到多少时,刷新innodb_buffer_pool,此参数的设置一方面可以加速数据的处理,另
一方面也可能导致数据的不一致。

Innodb_flush_log_at_trx_commit

控制innodb事务日志刷新方式的参数

0 每个一秒刷新

1 每次事务结束同步

2
每次事务结束同步,由于调用了文件系统的写入操作,而文件系统是有缓存的,所以并没有真正同
步,但性能最好

Innodb_log_files_in_group=2

指定几个日志组,默认是2

Innodb_log_file_size

指定innodb日志文件大小

Innodb_file_io_threads=4

文件读写io数,这个参数只在window下起作用,linux下只会等于4    —废弃了吗?

Innodb_file_per_table   

使用单独的表空间

innodb_doublewrite

使用的是一种较为独特的flush实现技术,主要作用是为了通过减少文件同步次数提高io性能的情况
下,提高系统crash或者断电情况下数据的安全性,避免写入的数据不完整。

innodb_adaptive_hash_index

不是为了改善磁盘io的性能,而是为了提高buffer
pool中的数据的访问效率,就是给buffer pool中的 数据做的索引。

Innodb_thread_concurrency=0

默认设置为0,让系统自己控制

Innodb_lock_wait_timeout = 50

该参数主要用于出现死锁的时候等待指定时间后回滚

相关文章