MySQL 5.6.24 线上版本配置文件解析

线上MySQL服务器配置文件解析



innodb_buffer_pool_size 

非常重要的一个参数,用于配置InnoDB的缓冲池,如果数据库中只有哦Innodb表,则推荐配置量为总内存的75%


select  engine,round(sum(data_length + index_length)/1024/1024,1) as ‘Total MB‘

from information_schema.tables  where table_schema not in (‘information_schema‘,‘performance_schema‘) group by engine;



innodb_buffer_pool_instances 

可以控制把 缓冲池的大小分为几份,默认情况下只有一个缓冲池,5.5版本后引入的新参数,如果是一个缓冲区,有可能增加阻塞的频率



innodb_log_buffer_size

innodb log 缓存的大小,由于日志最长每秒钟就会刷新,所以一般不用太大



innodb_flush_log_at_trx_commit 

这是一个非常关键的参数,对InnoDB的IO效率影响很大,默认值为1,可以取0,1,2三个值,一般建议为2,但如果数据安全性要求比较高则使用默认值1.

决定MySQL多长时间把变更刷新到磁盘。如果为0,就是每次提交时不刷新的,等到每一秒的时候,再把事务刷新到磁盘中。如果是2,则是每次事务提交时,先把变化刷新到缓冲区,再等到1秒后,将日志缓冲区变化刷新到磁盘中。


innodb_read_io_threads

innodb_write_io_threads

以上两个参数决定了InnoDB读写的IO线程数,默认为4,在5.5以后,可以根据CPU的核心数,调整这两个参数的的值



innodb_file_per_table

关键参数,控制InnoDB每一个表使用独立的表空间,默认为OFF,也就是所有的表都会建立在共享的表空间中。

主要有两个问题:IO读写冲突,不能收缩表空间,要把整个表空间的表导出后,然后删除才能收缩空间。



借助第三方工具对配置参数进行优化Percona 公司 在线配置向导。



key_buffer_size  

指定用于索引的缓冲区大小,增加它可得到更好处理的索引



query-cache-type               = 1

query-cache-size               = 256M

开启查询缓存功能

设置查询缓存大小为256M,可以通过状态变量来查看该值设备是否合理?

mysql> show status like ‘Qcache%‘;

+-------------------------+-----------+

| Variable_name           | Value     |

+-------------------------+-----------+

| Qcache_free_blocks      | 75        |

| Qcache_free_memory      | 268195744 |

| Qcache_hits             | 1531      |    

| Qcache_inserts          | 377996    |  

| Qcache_lowmem_prunes    | 0         |

| Qcache_not_cached       | 63820     |

| Qcache_queries_in_cache | 95        |

| Qcache_total_blocks     | 283       |

+-------------------------+-----------+

查询缓存分为两种:

1、可以放入到查询缓冲区中的,又可以分为已命中,现在未命中,准备下次查询命中的。

2、不可以放入到查询缓冲区中的;

具体解释一下,每一个状态变量的意思:

Qcache_free_blocks  目前还处于空闲状态的Query Cache中的内存Block数目,数目大说明可能有碎片。

Qcache_free_memory  查询缓冲区中空闲的内存数

Qcache_hits    命中的查询缓存数

Qcache_inserts  未命中的查询缓存数,准备加入到命中缓存区中的数量。

Qcache_lowmem_prunes   因为查询命中缓冲区的内存不够,需要从缓冲区中删除的数量。

Qcache_not_cached  不可以放入查询缓冲区中的数量.

Qcache_queries_in_cache    当前在query_cache中‘注册’的select语句条数

Qcache_total_blocks   缓冲区中的总的块数



key_buffer_size 

这个参数是用来设置索引块缓存的大小,它被所有的线程共享,严格说是它决定了数据库索引处理的速度,尤其是索引读的速度,那我们怎么知道key_buffer_size的设置是否合理呢,一般可以检查状态 key_read_request 和 key_reads,比如key_reads / key_read_request 应该尽可能的低,比如1:100,1:1000,1:10000












[mysql]

# CLIENT #

port                           = 3306

socket                         = /tmp/mysql.sock


[mysqld]


# GENERAL #

user                           = mysql

default-storage-engine         = InnoDB

socket                         = /tmp/mysql.sock

pid-file                       = /data/mysql_data/mysql.pid


# MyISAM #

myisam-recover                 = FORCE,BACKUP


# SAFETY #

max-connect-errors             = 1000000

skip-name-resolve

sysdate-is-now                 = 1

innodb                         = FORCE

innodb-strict-mode             = 1


# DATA STORAGE #

datadir                        = /data/mysql_data/


# BINARY LOGGING #

log-bin                        = /data/mysql_data/mysql-bin

expire-logs-days               = 14

sync-binlog                    = 1



# CACHES AND LIMITS #

tmp-table-size                 = 512M

max-heap-table-size            = 512M

query-cache-type               = 1

query-cache-size               = 256M

max-connections                = 5000

thread-cache-size              = 50

open-files-limit               = 65535

table-definition-cache         = 4096

table-open-cache               = 1024


key_buffer_size = 512M

max_allowed_packet = 128M

sort_buffer_size = 128M

read_buffer_size = 128M

read_rnd_buffer_size = 128M

myisam_sort_buffer_size = 512M

thread_cache_size = 16

wait_timeout=172800

interactive_timeout=172800


# INNODB #

innodb-flush-method            = O_DIRECT

innodb-log-files-in-group      = 2

innodb-log-file-size           = 512M

innodb-flush-log-at-trx-commit = 2

innodb-file-per-table          = 1

innodb-buffer-pool-size        = 8G

innodb_log_buffer_size=1M

innodb_lock_wait_timeout =50

innodb_read_io_threads = 4

innodb_write_io_threads = 4


# LOGGING #

log-error                      = /data/mysql_data/mysql-error.log

log-queries-not-using-indexes  = 1

slow-query-log                 = 1

slow-query-log-file            = /data/mysql_data/mysql-slow.log


#Rep

binlog-format=ROW

log-slave-updates=true

server_id = 13


#Other

sql_mode="NO_ENGINE_SUBSTITUTION"


郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。