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

wfds

线上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"