MySQL 5.6.24 線上版本設定檔解析

標籤：設定檔   伺服器   engine   資料庫   緩衝區   

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

MySQL 5.6.24 線上版本設定檔解析