Mysql性能調優與測試的方法

我們的mysql服務運行一段時間后,不知什么原因就變慢了,怎么查找原因呢?

一、關鍵性指標

在數據庫性能評測中,有幾項指標很重要,用它來評估數據庫的能力,不是他們能起著多么關鍵的作用,而是他們能夠較為明確的代表數據庫在某些方面的能力。

1.IOPS

IOPS:Input/Output operation Per Second, 每秒處理的IO請求次數。
我們知道I/O就是磁盤的讀寫能力,比如每秒讀 300M,寫 200M,這個即數據的吞吐量(I/O能力的另一個關鍵指標),但是 IOPS 指的可不是讀寫的數據吞吐量,IOPS 指的是每秒能夠處理的 I/O 請求次數。

如果想I/O 系統響應夠快,那么 IOPS 越高越好,因為IOPS 和硬件有關,所以,要提高IOPS,就目前來看基本只能拼硬件,傳統方案是使用多塊磁盤通過 RAID 條帶后,使 I/O 讀寫能力獲得提升,我們也可以使用固態硬盤SSD來提升IOPS,不過固態硬盤成本可能比較大。

2.QPS

QPS:Query Per Second,每秒請求(查詢)次數。
這個參數非常重要,可以直觀的反映系統的性能,這就像IOPS衡量磁盤每秒鐘能接收多少次請求。

我們可以在MySQL命令行模式下執行 status 命令,返回的最后一行輸出信息中就包含 QPS 指標。

3.TPS

TPS:Transaction Per Second,每秒事務數。
TPS參數MySQL原生沒有提供,如果需要我們自己算,可以利用計算的公式:

TPS?=?(Com_commit?+?Com_rollback)?/?Seconds

這個公式有兩個狀態變量,分別代表提交次數和回滾次數,Seconds 就是我們定義的時間間隔。

二、TPCC測試關鍵性指標

TPCC-MySQL 由Percona基于TPCC規范開發的一套MySQL基準測試程序,我們使用這套工具來測試前面的三個重要指標。

1.TPCC工具安裝及使用

具體的安裝,可以看這這兩篇博文 mysql壓力測試工具tpcc-mysql安裝測試使用,mysql性能測試-tpcc,TPCC更能模擬線上業務。

三、數據庫參數配置優化

如果數據庫參數配置合理,則可以大大的提高運行效率,即最大化利用系統資源。

1.連接相關參數

1.1 max_connections

max_connections:指定 MySQL 服務端最大并發連接數,值得范圍從 1~10 萬,默認值為151.
這個參數非常重要,因為它決定了同時最多能有多少個會話連接到 MySQL 服務。設定該參數時,根據數據庫服務器的配置和性能,一般將參數值設置在 500~2000 都沒太大的問題。

1.2 max_connect_errors

max_connect_errors:指定允許連接不成功的最大嘗試次數,值得范圍從 1~2^64 之間,在 5.6.6 版本默認值是 100。

一定不要忽視這個參數,如果嘗試連接的錯誤數量超過該參數指定值,則服務器就不再允許新的連接,沒錯,就是拒絕連接,盡管 MySQL 仍在提供服務,但無法創建新的連接了。可以使用 FLUSH HOSTS,使狀態清零或重新啟動數據庫服務,不過這個代價太高了,一般不會這么干,所以,這個參數的默認值太小,這里建議將之設置為 10 萬以上的量級。

1.3 interactive_timeout 和 wait_timeout

這兩個參數都與連接會話的自動超時斷開有關,前者用于指定關閉交互連接前等待的時間,后者用于指定關閉非交互連接前的等待時間,單位均是秒,默認值均為 28800,即 8 個小時。

1.4 skip-name-resolve

skip-name-resolve:可以將其簡單的理解為禁用 DNS 解析,注意啊,這個是服務端的行為,連接時不檢查客戶端主機名,而只使用IP。如果制定了該參數,那么在創建用戶及授予權限時,HOST 列必須是IP而不能是主機名。建議啟用該參數,對于加快網絡連接有一定的幫助,等于是跳過了主機名的解析。

1.5 back_log

back_log:指定 MySQL 連接請求隊列中存放的最大連接請求數量,在 5.6.6 版本之前,默認是 50 個,最大值不超過 65535。在 5.6.6 版本之后,默認值為 -1,表示由MySQL自動調節,所謂自行調節其實也有規則,即 50+(max_connections/5)。

該參數主要應對短時間內有大量的連接請求,MySQL 主線程無法及時為每一個連接請求分配(或創建)連接的線程,怎么辦呢,它也不能直接拒絕,于是就將一部分請求放到等待隊列中待處理,這個等待隊列的長度就是 back_log 的參數值,若等待隊列也被放滿了,那么后續的連接請求才會被拒絕。

2.文件相關參數

2.1 sync_binlog

sync_binlog:指定同步二進制日志文件的平率,默認為0.
如果要性能,則指定該參數為0,為了安全起見則指定該參數值為 1.

2.2 expire_logs_day

expire_logs_day:指定設置二進制日志文件的生命周期,超出則將自動被刪除,參數值以天為單位,值得范圍從0~99,默認值是0,建議將該參數設置為 7~14 之間,保存一到兩周就足夠了。

2.2 max_binlog_size

max_binlog_size: 指定二進制日志的大小,值得范圍從 4KB~1GB,默認為 1GB。

3.緩存控制參數

3.1 thread_cache_size

thread_cache_size:指定MySQL為快速重用而緩存的線程數量。值得范圍從 0~16384,默認值為0.
一般當客戶端中斷連接后,為了后續再有連接創建時,能夠快速創建成功,MySQL 會將客戶端中斷的連接放入緩存區,而不是馬上中斷釋放資源。這樣當有新的客戶端請求連接時,就可以快速創建成功。因此,本參數最好保持一定的數量,建議設置在 300~500 之間均可.另外,線程緩存的命中率也是一項比較重要的監控指標,計算規則為(1-Threads_created/Connections)* 100%,我們可以通過該指標來優化和調整thread_cache_size參數。

3.2 query_cache_type

sql_cache意思是說,將查詢結果放入查詢緩存中。
sql_no_cache意思是查詢的時候不緩存查詢結果。
sql_buffer_result意思是說,在查詢語句中,將查詢結果緩存到臨時表中。

這三者正好配套使用。sql_buffer_result將盡快釋放表鎖,這樣其他sql就能夠盡快執行。

使用 FLUSH QUERY CACHE 命令,你可以整理查詢緩存,以更好的利用它的內存。這個命令不會從緩存中移除任何查詢。FLUSH TABLES 會轉儲清除查詢緩存。
RESET QUERY CACHE 使命從查詢緩存中移除所有的查詢結果。

那么mysql到底是怎么決定到底要不要把查詢結果放到查詢緩存中呢?

是根據query_cache_type這個變量來決定的。

這個變量有三個取值:0,1,2,分別代表了off、on、demand
mysql默認為開啟 on

意思是說,如果是0,那么query cache 是關閉的。
如果是1,那么查詢總是先到查詢緩存中查找,即使使用了sql_no_cache仍然查詢緩存,因為sql_no_cache只是不緩存查詢結果,而不是不使用查詢結果。

select?count(*)?from?innodb;  1?row?in?set?(1.91?sec)    select?sql_no_cache?count(*)?from?innodb;  1?row?in?set?(0.25?sec)

如果是2,DEMAND。
在my.ini中增加一行
query_cache_type=2
重啟mysql服務

select?count(*)?from?innodb;  1?row?in?set?(1.56?sec)    select?count(*)?from?innodb;  1?row?in?set?(0.28?sec)

沒有使用sql_cache,好像仍然使用了查詢緩存

select?sql_cache?count(*)?from?innodb;  1?row?in?set?(0.28?sec)

使用sql_cache查詢時間也一樣,因為sql_cache只是將查詢結果放入緩存,沒有使用sql_cache查詢也會先到查詢緩存中查找數據

結論:只要query_cache_type沒有關閉,sql查詢總是會使用查詢緩存,如果緩存沒有命中則開始查詢的執行計劃到表中查詢數據。

query cache優缺點
優點很明顯,對于一些頻繁select query,mysql直接從cache中返回相應的結果集,而不用再從表table中取出,減少了IO開銷。
即使query cache的收益很明顯,但是也不能忽略它所帶來的一些缺點:

  1. query語句的hash計算和hash查找帶來的資源消耗。mysql會對每條接收到的select類型的query進行hash計算然后查找該query的cache是否存在,雖然hash計算和查找的效率已經足夠高了,一條query所帶來的消耗可以忽略,但一旦涉及到高并發,有成千上萬條query時,hash計算和查找所帶來的開銷就的重視了;

  2. query cache的失效問題。如果表變更比較頻繁,則會造成query cache的失效率非常高。表變更不僅僅指表中的數據發生變化,還包括結構或者索引的任何變化;

  3. 對于不同sql但同一結果集的query都會被緩存,這樣便會造成內存資源的過渡消耗。sql的字符大小寫、空格或者注釋的不同,緩存都是認為是不同的sql(因為他們的hash值會不同);

  4. 相關參數設置不合理會造成大量內存碎片,相關的參數設置會稍后介紹。

合理利用query cache
query cache有利有弊,合理的使用query cache可以使其發揮優勢,并且有效的避開其劣勢。

  1. 并不是所有表都適合使用query cache。造成query cache失效的原因主要是相應的table發生了變更,那么就應該避免在變化頻繁的table上使用query cache。mysql中針對query cache有兩個專用的sql hint:SQL_NO_CACHE和SQL_CACHE,分別表示強制不使用和強制使用query cache,通過強制不使用query cache,可以讓mysql在頻繁變化的表上不使用query cache,這樣減少了內存開銷,也減少了hash計算和查找的開銷;

更多有關query cache詳情文章,請看這里的原文:mysql query cache優化

3.3 query_cache_size

query_cache_size:指定用于緩存查詢結果集的內存區大小,該參數值應為 1024 的整數倍。

這個參數不能太大,也不能太小,查詢緩存至少會需要 40KB 的空間分配給其自身結構,太小時緩存結果集就沒有意義,熱點數據保存不了多少,而且總是很快就被刷新出去;但也不能太大,否則可能過多占用內存資源,影響整機性能,再說太大也沒有意義,因為即便數據不被刷新,但只要源數據發生變更,緩存中的數據也就自動失效了,這種情況下分配多大都沒有意義。個人建議設置不要超過 256MB。

3.4 query_cache_limit

query_cache_limit:用來控制查詢緩存,能夠緩存的單條 SQL 語句生成的最大結果集,默認是 1MB,超出的就不要進入查詢緩存。這個大小對于很多場景都夠了,縮小可以考慮,加大就不用了。

3.5 sort_buffer_size

sort_buffer_size:指定單個會話能夠使用的排序區的大小,默認值為 256KB,建議設置為 1~4MB 之間。

3.6 read_buffer_size

read_buffer_size:指定隨機讀取時的數據緩存區大小,默認是 256KB,最大能夠支持4GB,適當加大本參數,對于提升全表掃描的效率會有幫助。

4.InnoDB專用參數

4.1 innodb_buffer_pool_size

innodb_buffer_pool_size:指定InnoDB引擎專用的緩存區大小,用來緩存表對象的數據及索引信息,默認值為 128MB,最大能夠支持(2^64 -1)B.

如果你有很多事務的更新,插入或刪除很操作,通過修改innodb_buffer_pool 大小這個參數會大量的節省了磁盤I / O。

innodb_buffer_pool_size 是個全局參數,其所分配的緩存區將供所有被訪問到的InnoDb表對象使用,若MySQL數據庫中的表對象以 InnoDb 為主,那么本參數的值就越大越好,官方文檔中建議,可以將該參數設置為服務器物理內存的70%~80%。

4.2 innodb_buffer_instances

innodb_buffer_instances:指定 InnoDB 緩存池分為多少個區域來使用,值得范圍從 1~64,默認值為-1,表示由 InnoDB 自行調整。

只有當innodb_buffer_pool_size參數值大于1GB時,本參數才有效,那么本參數怎么設置呢?個人感覺可以參照 InnoDB 緩存池的大小,以 GB 為單位,每GB指定一個instances。例如當innodb_buffer_pool_size設置為16GB時,則指定 innodb_buffer_instances 設置為 16 即可。

5.參數優化案例

測試服務器有 16GB的物理內存,假定其峰值最大的連接數為 500 個,表對象使用InnoDB 存儲引擎,我們的內存參數如何配置呢?

具體配置如下:
(1)、首先,為操作系統預留 20% 的內存,約為 3GB。
(2)、與線程相關的幾個關鍵參數設置如下:

??sort_buffer_size=2m  ??read_buffer_size=2m  ??read_rnd_buffer_size=2m  ??join_buffer_size=2m

預計連接數達到峰值時,線程預計最大將有可能占用 500 *(2+2+2+2)= 4GB內存(理論最大值)。

(3)、剩下的空間 16-3-4=9GB,就可以全部都分配給InnoDB 的緩存池,設定相關的參數如下:

innodb_buffer_pool_size=9g  innodb_thread_concurrency=8  innodb_flush_method=O_DIRECT  innodb_log_buffer_size=16m  innodb_flush_log_at_trx_commit=2

四、MySQL系統狀態

想要了解MySQL服務當前在做什么,有個非常重要并且極為常用的命令:

SHOW?[FULL]?PROCESSLIST

SHOW PROCESSLIST 命令將每一個連接的線程,作為一條獨立的記錄輸出。

還有相似的語句,
SHOW PROFILES 和 SHOW PROFILE可以獲取會話執行語句過程中,資源的使用情況。


Mysql性能調優與測試的方法

? 版權聲明
THE END
喜歡就支持一下吧
點贊10 分享