MySQL/MariaDB 性能調整和優化重點

※在開始調整前，請不要全然接受所有的建議，每個MySQL/MariaDB設置皆不盡相同，在進行任何修改之前請先確認每一個項目的目的，並依照各自server配置作相對應得調整。
※MySQL/MariaDB 配置文件位於 /etc/mysql/my.cnf，每次更改此文件後需要重新啟動Mysql server，使更改生效。

1.啟用InnoDB 的每張表一個數據文件設置

innodb_file_per_table: 這個不是效能上的優化，而是使 InnoDB 資料表管理更方便。
當設定成 off 時，所有 InnoDB 資料表的資料及索引會儲存在一個 .ibd 檔案，
如果設定成 on, 每個資料表會有一個獨立的 .ibd 檔。
首先，有一個重要的解釋， InnoDB 是一個存儲引擎。MySQL 和MariaDB 使用InnoDB 作為默認存儲引擎。以前，MySQL 使用系統表空間來保存數據庫中的表和索引。
這意味著服務器唯一的目的就是數據庫處理，它們的存儲盤不用於其它目的。
InnoDB提供了更靈活的方式，它把每個數據庫的信息保存在一個.ibd數據文件中。
每個.idb文件代表它自己的表空間。通過這樣的方式可以更快地完成類似“TRUNCATE”的數據庫操作，當刪除或截斷一個數據庫表時，你也可以回收未使用的空間。
這樣配置的另一個好處是你可以將某些數據庫表放在一個單獨的存儲設備。
這可以大大提升你磁盤的I/O 負載。MySQL 5.6及以上的版本默認啟用innodb_file_per_table
該指令看起來是這樣的：

innodb_file_per_table = 1

2.優化使用InnoDB 的緩衝池

innodb_buffer_pool_size: 這個設定是 innodb 儲存引擎資料庫，所使用的資料及索引快取，
根據記憶體的大小䀆量設定大一點，可以減低讀取資料時從硬碟讀取的次數。
如果是獨立的 MySQL 伺服器，可以這樣設定： (約實體記憶體的60~80%)

1. 系統 8GB 記憶體： 5GB – 6GB 
2. 系統 32GB 記憶體： 20GB – 25GB 
3. 系統 128GB 記憶體： 100GB – 120GB

InnoDB 引擎在內存中有一個緩衝池用於緩存數據和索引。這當然有助於你更快地執行MySQL/MariaDB 查詢語句。選擇合適的內存大小需要一些重要的決策並對系統的內存消耗有較多的認識。下面是你需要考慮的: 

1. 其它的進程需要消耗多少內存。這包括你的系統進程，頁表，套接字緩衝。 
2. 你的服務器是否專門用於MySQL 還是你運行著其它非常消耗內存的服務。 

如果是Innodb的資料庫，innodb_buffer_pool_size就開的儘可能大點
該參數是用來緩存數據索引以及數據塊的數據，簡單的來說，
當我們操作Innodb資料庫獲取數據的時候，都會在這個內存的區域塊中走一遭，
獲取數據.Innodb_buffer_pool_size 設置了Innodb存儲引擎的內存區域塊的大小，
直接關係到Innodb的存儲引擎性能，因此我們如果有足夠大的內存，
應該將儘可能多的內存設置，從而將儘可能多的數據以及索引發到該內存數據塊中.
我們可以通過 (Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) /Innodb_buffer_pool_read_requests * 100% 計算緩存命中率，並根據命中率
來調整 innodb_buffer_pool_size 參數大小進行優化
在一個專用的機器上，你可能會把60-80％的內存分配給innodb_buffer_pool_size。
如果你打算在一個機器上運行更多的服務，
你應該重新考慮專門用於innodb_buffer_pool_size的內存大小。
該指令看起來是這樣的：

1. innodb_buffer_pool_size = 256M
2. 預修改innodb_buffer_pool_size，需要stop server，
   並刪除/var/lib/mysql/ib_logfile0, ib_logfile1........ib_logfilen，
   再來必須把預設註解的innodb_log_file_size開啟，
   innodb_log_file_size設定的值為innodb_buffer_pool_size的25%，之後再重啟server即可

3.設置 redo logs 的體積

innodb_log_file_size: 這是 redo logs 的體積，redo logs 可以更快速地寫入，預設值是 2GB，

這個設定可以從 2GB 開始逐步增加，以找到最適合的設定。

MySQL 5.5 最大可以設定成 4GB, 而 MySQL 5.6 開始則沒有這個限制。

對於大量數據集的編寫密集型工作負載非常重要。 較大的尺寸提供更好的性能，

但增加恢復時間，所以要小心。 我通常使用值64M-512M取決於服務器大小。

4.設定資料及快取如果寫入硬碟

innodb_flush_method: 設定資料及快取如果寫入硬碟，

當系統有 RAID 卡及支援電池保護 write-back 快取，建議設定成 O_DIRECT.

5. 設定transactions 的緩存大小

innodb_log_buffer_size: 這是 transactions 的緩存大小，預設值是 1MB,

如果 transactions 的欄位是 blob 或 text，援存很快會用盡，可以逐步增加。

對於具有中等寫入負載和較短事務的許多工作負載，這個默認值是可以的。

如果您有更新活動峰值，或者與blob工作很多，您可能希望增加它。

不要設置太高，因為它會浪費內存 - 它每1秒刷新一次，所以你不需要空間超過1秒的更新。

8MB-16MB通常就夠了。 較小的安裝應使用較小的值。

6.設置MySQL 的最大連接數

max_connections: 如果 MySQL 伺服器經常出現 “Too many connections” 錯誤，

那便表示 “max_connections” 設定太低，如果還遇到 “Too many connections” 問題，

逐步增加測試。 

max_connections指令告訴你當前你的服務器允許多少並發連接。

MySQL/MariaDB 服務器允許有SUPER 權限的用戶在最大連接之外再建立一個連接。

只有當執行MySQL 請求的時候才會建立連接，執行完成後會關閉連接並被新的連接取代。

請記住，太多的連接會導致內存的使用量過高並且會鎖住你的MySQL 服務器。

一般小網站需要100-200 的連接數，而較大可能需要500-800 甚至更多。

這裡的值很大程度上取決於你MySQL/MariaDB 的使用情況。

該指令看起來是這樣的：

1. max_connections = 300

你可以用以下公式來計算線程池的命中率：

1. 100 - ((Threads_created / Connections) * 100)

如果你得到一個較低的數字，這意味著大多數mysql連接使用新的線程，而不是從緩存加載。在這種情況下，你需要增加thread_cache_size。

這裡有一個好處是可以動態地改變thread_cache_size而無需重啟MySQL服務。
你可以通過以下方式來實現：

1. mysql> set global thread_cache_size = 16;

7.在MySQL 中避免使用Swappiness

thread_cache_size指令用來設置你服務器緩存的線程數量。當客戶端斷開連接時，

如果當前線程數小於thread_cache_size，它的線程將被放入緩存中。

下一個請求通過使用緩存池中的線程來完成。

要提高服務器的性能，你可以設置thread_cache_size的值相對高一些。

你可以通過以下方法來查看線程緩存命中率：

1. mysql> show status like 'Threads_created'; 
2. mysql> show status like 'Connections';

8.配置MySQL 的查詢緩存容量

如果你有很多重複的查詢並且數據不經常改變–請使用緩存查詢。人們常常不理解

query_cache_size的實際含義而將此值設置為GB級，這實際上會降低服務器的性能。

背後的原因是，在更新過程中線程需要鎖定緩存。通常設置為200-300 MB應該足夠了。

如果你的網站比較小的，你可以嘗試給64M 並在以後及時去增加。

在你的MySQL 配置文件中添加以下設置：

1. query_cache_type = 1
2. query_cache_limit = 256K
3. query_cache_min_res_unit = 2k
4. query_cache_size = 64M

如果您的應用程序讀取密集，並且沒有應用程序級別的緩存，

這可以幫助您優化MySQL數據庫。

不要設置太大，因為它可能會減慢，因為它的維護可能會變得昂貴。

從32M到512M的值通常是有意義的。 經過一段時間檢查，看看它是否被很好地使用。

對於某些工作負載，緩存命中率低於允許其啟用的理由。

注意：您可以看到所有這些都是全局變量。 這些變量取決於硬件和存儲引擎的混合，

而每個會話變量通常是工作負載特定的。 如果您有簡單的查詢，

即使您有64GB的內存浪費， 也沒有理由增加sort_buffer_size 。

此外，這樣做可能會降低性能。

我可以分析工作負載後，通常將每個會話變量調整到第二步。

query_cache_size/query_cache_type:

Query cache 作用於整個 MySQL Instance，主要用來緩存 MySQL 中的 ResultSet，

也就是一條SQL語句執行的結果集，所以僅僅只能針對select語句。

當我們打開了Query Cache 功能，MySQL在接受到一條select語句的請求後，

如果該語句滿足Query Cache的要求（未顯式說明不允許使用Query Cache，

或者已經顯式申明需要使用QueryCache），

MySQL 會直接根據預先設定好的HASH算法將接受到的select語句以字符串方式進行hash，

然後到Query Cache 中直接查找是否已經緩存。也就是說，如果已經在緩存中，

該select請求就會直接將數據返回，從而省略了後面所有的步驟（如 SQL語句的解析，

優化器優化以及向存儲引擎請求數據等），極大的提高性能。 當然，Query Cache 也有一個致命的缺陷，那就是當某個表的數據有任何任何變化，

都會導致所有引用了該表的select語句在Query Cache 中的緩存數據失效。

所以，當我們的數據變化非常頻繁的情況下，使用Query Cache 可能會得不償失。 Query Cache的使用需要多個參數配合，其中最為關鍵的是 query_cache_size 和 query_cache_type ，

前者設置用於緩存 ResultSet 的內存大小，後者設置在何場景下使用Query Cache。

在以往的經驗來看，如果不是用來緩存基本不變的數據的MySQL資料庫，

query_cache_size 一般 256MB 是一個比較合適的大小。當然，這可以通過計算

Query Cache的命中率（Qcache_hits/(Qcache_hits+Qcache_inserts)*100)）來進行調整。

query_cache_type可以設置為0(OFF)，1(ON)或者2(DEMOND)，

分別表示完全不使用query cache，除顯式要求不使用query cache（使用sql_no_cache）

之外的所有的select都使用query cache，只有顯示要求才使用querycache（sql_cache）。

9.設置binlog_cache_size 

binlog_cache_size （global）Binlog Cache 用於在打開了二進位日誌（binlog）記錄功能的環

境，是 MySQL 用來提高binlog的記錄效率而設計的一個用於短時間內臨時緩存binlog數據的

內存區域。

一般來說，如果我們的資料庫中沒有什麼大事務，寫入也不是特別頻繁，

2MB～4MB是一個合適的選擇。但是如果我們的資料庫大事務較多，

寫入量比較大，可與適當調高binlog_cache_size。同時，

我們可以通過binlog_cache_use 以及 binlog_cache_disk_use來分析設置的binlog_cache_size

是否足夠，是否有大量的binlog_cache由於內存大小不夠而使用臨時文件

（binlog_cache_disk_use）來緩存了

10.innodb_additional_mem_pool_size（global）

這個參數我們平時調整的可能不是太多，很多人都使用了默認值，

可能很多人都不是太熟悉這個參數的作用。innodb_additional_mem_pool_size

設置了InnoDB存儲引擎用來存放數據字典信息以及一些內部數據結構的內存空間大小，

所以當我們一個MySQL Instance中的資料庫對象非常多的時候，

是需要適當調整該參數的大小以確保所有數據 都能存放在內存中提高訪問效率的。

這個參數大小是否足夠還是比較容易知道的，因為當過小的時候，

MySQL 會記錄 Warning 信息到資料庫的 error log 中，

這時候你就知道該調整這個參數大小了。

這個並沒有真正影響性能太多，至少在操作系統與體面的內存分配器。 仍然你可能想要

20MB（有時更大），所以你可以看到Innodb分配多少內存的雜項需求。

11.innodb_log_buffer_size （global）

innodb_log_buffer_size （global）這是 InnoDB 存儲引擎的事務日誌所使用的緩衝區。

類似於 Binlog Buffer，InnoDB 在寫事務日誌的時候，為了提高性能，

也是先將信息寫入 Innofb Log Buffer 中，

當滿足 innodb_flush_log_trx_commit 參數所設置的相應條件（或者日誌緩衝區寫滿）之後，

才會將日誌寫到文件（或者同步到磁碟）中。

可以通過 innodb_log_buffer_size參數設置其可以使用的最大內存空間。

註：innodb_flush_log_trx_commit 參數對 InnoDB Log 的寫入性能有非常關鍵的影響。

該參數可以設置為0，1，2，解釋如下： 0：log buffer中的數據將以每秒一次的頻率寫入到log

file中，且同時會進行文件系統到磁碟的同步操作，

但是每個事務的commit並不會觸發任何log buffer 到log file的刷新或者 文件系統到磁碟的刷新

操作； 1：在每次事務提交的時候將log buffer 中的數據都會寫入到log file，同時也會觸發文件

系統到磁碟的同步； 2：事務提交會觸發log buffer 到log file的刷新，

但並不會觸發磁碟文件系統到磁碟的同步。

此外，每秒會有一次文件系統到磁碟同步操作。 此外，MySQL文檔中還提到，

這幾種設置中的每秒同步一次的機制，

可能並不會完全確保非常準確的每秒就一定會發生同步，還取決於進程調度的問題。

實際上，InnoDB 能否真正滿足此參數所設置值代表的意義正常 Recovery

還是受到了不同 OS 下文件系統以及磁碟本身的限制，

可能有些時候在並沒有真正完成磁碟同步的情況下也會告訴 mysqld 已經完成了磁碟同步。

下面是轉載的一些常用參數的設置： query_cache_type : 如果全部使用innodb存儲引擎，

建議為0，如果使用MyISAM 存儲引擎，建議為2，

同時在SQL語句中顯式控制是否使用gquery cachequery_cache_size:

根據 命中率（Qcache_hits/(Qcache_hits+Qcache_inserts)*100)）進行調整，

一般不建議太大，256MB可能已經差不多了，大型的配置型靜態數據可適當調大

binlog_cache_size: 一般環境2MB～4MB是一個合適的選擇，事務較大且寫入頻繁的資料庫環

境可以適當調大， 但不建議超過32MBkey_buffer_size: 如果不使用MyISAM存儲引擎，

16MB足以，用來緩存一些系統表信息等。 如果使用 MyISAM存儲引擎，

在內存允許的情況下，儘可能將所有索引放入內存， 簡單來說就是「越大越好」

12.bulk_insert_buffer_size

如果經常性的需要使用批量插入的特殊語句（上面有說明）來插入數據，

可以適當調大該參數至16MB～32MB，不建議繼續增大，某人8MBinnodb_buffer_pool_size:

如果不使用InnoDB存儲引擎，可以不用調整這個參數，如果需要使用， 在內存允許的情況

下，儘可能將所有的InnoDB數據文件存放如內存中， 同樣將但來說也是「越大越好」

13.innodb_additional_mem_pool_size

一般的資料庫建議調整到8MB～16MB，如果表特別多，可以調整到32MB，

可以根據error log中的信息判斷是否需要增大

14.innodb_log_buffer_size

默認是1MB，系的如頻繁的系統可適當增大至4MB～8MB。當然如上面介紹所說，

這個參數實際上還和另外的flush參數相關。一般來說不建議超過32MB

15.innodb_max_dirty_pages_pct

根據以往的經驗，重啟恢復的數據如果要超過1GB的話，啟動速度會比較慢，

幾乎難以接受，所以建議不大於 1GB/innodb_buffer_pool_size(GB)*100 這個值。

當然，如果你能夠忍受啟動時間比較長， 而且希望儘量減少內存至磁碟的flush，

可以將這個值調整到90，但不建議超過90

16.配置臨時表容量和內存表最大容量

tmp_table_size和max_heap_table_size這兩個變量的大小應該相同，它們可以讓你避免磁盤寫

入。 tmp_table_size是內置內存表的最大空間。如果表的大小超出限值將會被轉換為磁盤上的

MyISAM表。 這會影響數據庫的性能。管理員通常建議在服務器上設置這兩個值為實體記憶

體每GB 內存給64M。

EX:4G -> 256M 8G -> 512M 16G -> 1G
[mysqld]
tmp_table_size= 64M
max_heap_table_size= 64M

17.啟用MySQL 慢查詢日誌

記錄慢查詢可以幫助你定位數據庫中的問題並幫助你調試。

這可以通過在你的MySQL 配置文件中添加以下值來啟用：

slow-query-log = 1
slow-query-log-file = /var/lib/mysql/mysql-slow.log
long_query_time = 1

第一個變量啟用慢查詢日誌，第二個告訴MySQL實際的日誌文件存儲位置。

使用long_query_time來定義完成MySQL查詢多少用時算長。

18.參考文件

1. 15 個有用的MySQL/MariaDB 性能調整和優化技巧
2. 改變innodb_log_file_size後無法啟動mysql --- unknow表引擎InnoDB
3. 安裝 MySQL 後的優化
4. mysql的從頭到腳優化之伺服器參數的調優
5. MySQL服務器安裝後要調整什麼

19.修改範例

# MariaDB database server configuration file.
#
# You can copy this file to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0

[mysqld]
#
# * Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc_messages_dir = /usr/share/mysql
lc_messages = en_US
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 127.0.0.1
#
# * Fine Tuning
#
max_connections = 300
connect_timeout = 5
wait_timeout = 60
max_allowed_packet = 16M
thread_cache_size       = 128
sort_buffer_size = 4M
bulk_insert_buffer_size = 16M
tmp_table_size = 256M
max_heap_table_size = 256M
#
# * MyISAM
#
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched. On error, make copy and try a repair.
myisam_recover_options = BACKUP
key_buffer_size = 128M
#open-files-limit = 2000
table_open_cache = 400
myisam_sort_buffer_size = 512M
concurrent_insert = 2
read_buffer_size = 2M
read_rnd_buffer_size = 1M
#
# * Query Cache Configuration
#
# Cache only tiny result sets, so we can fit more in the query cache.
query_cache_limit = 256K
query_cache_size = 64M
# for more write intensive setups, set to DEMAND or OFF
#query_cache_type = DEMAND
query_cache_type = 1
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
#
# we do want to know about network errors and such
log_warnings = 2
#
# Enable the slow query log to see queries with especially long duration
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 1
#log_slow_rate_limit = 1000
log_slow_verbosity = query_plan

#log-queries-not-using-indexes
#log_slow_admin_statements
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id = 1
#report_host = master1
#auto_increment_increment = 2
#auto_increment_offset = 1
log_bin = /var/log/mysql/mariadb-bin
log_bin_index = /var/log/mysql/mariadb-bin.index
# not fab for performance, but safer
#sync_binlog = 1
expire_logs_days = 10
max_binlog_size         = 100M
# slaves
#relay_log = /var/log/mysql/relay-bin
#relay_log_index = /var/log/mysql/relay-bin.index
#relay_log_info_file = /var/log/mysql/relay-bin.info
#log_slave_updates
#read_only
#
# If applications support it, this stricter sql_mode prevents some
# mistakes like inserting invalid dates etc.
#sql_mode = NO_ENGINE_SUBSTITUTION,TRADITIONAL
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
default_storage_engine = InnoDB
# you can't just change log file size, requires special procedure
innodb_log_file_size = 640M
innodb_buffer_pool_size = 2560M
innodb_log_buffer_size = 8M
innodb_file_per_table = 1
innodb_open_files = 400
innodb_io_capacity = 400
innodb_flush_method = O_DIRECT
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

#
# * Galera-related settings
#
[galera]
# Mandatory settings
#wsrep_on=ON
#wsrep_provider=
#wsrep_cluster_address=
#binlog_format=row
#default_storage_engine=InnoDB
#innodb_autoinc_lock_mode=2
#
# Allow server to accept connections on all interfaces.
#
#bind-address=0.0.0.0
#
# Optional setting
#wsrep_slave_threads=1
innodb_flush_log_at_trx_commit=0

[mysqldump]
quick
quote-names
max_allowed_packet = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completion

[isamchk]
key_buffer = 16M

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/