介紹

我們?cè)谧鰯?shù)據(jù)庫性能壓力測(cè)試、做監(jiān)控和告警項(xiàng)，或者想要真實(shí)地了解業(yè)務(wù)數(shù)據(jù)庫負(fù)載的時(shí)候，常常需要使用兩個(gè)數(shù)字化的衡量指標(biāo)。他們是什么？相信很多數(shù)據(jù)庫從業(yè)的讀者已經(jīng)呼之欲出了，那就是 QPS 和 TPS。

我們經(jīng)常使用到這兩個(gè)指標(biāo)，那我們是否清楚他們是什么，在 MySQL 中應(yīng)該如何計(jì)算獲得呢？今天這里就是刨根問底欄目組...

QPS的定義和計(jì)算方法

首先我們來確認(rèn)一下什么是 QPS。

(資料圖片僅供參考)

根據(jù)百度百科，QPS 即 Queries-per-second，是每秒查詢率的意思。這個(gè)定義是非常明確的。

下面我們探討一下，他在 MySQL 里是如何計(jì)算的。

我們先去官網(wǎng)查詢下是否有官方的說明~

很遺憾，MySQL 官網(wǎng)并沒有對(duì) QPS 做出明確的解釋，那么就由我來帶大家一起探討一下這個(gè) QPS 應(yīng)該怎么計(jì)算吧。經(jīng)過我的研究，網(wǎng)上普遍存在三種 QPS 的計(jì)算方法。

大家看到這三個(gè)方法可能已經(jīng)懵逼了，請(qǐng)大家稍安勿躁，我來詳細(xì)講解一下。

方法一、QPS = DQL(select)-per-second

使用這個(gè)計(jì)算方法的人，普遍認(rèn)為 QPS 的 Query，中文意思是查詢的意思，所以對(duì)應(yīng)的就是所有 DQL 語句，即 select 打頭的語句。這種計(jì)算方式算出來的 QPS 意味著是數(shù)據(jù)庫服務(wù)器的只讀壓力，如果數(shù)據(jù)庫沒有讀只有寫，那么他的 QPS 即為 0，這顯然是不合理的，相信只有極少數(shù)人采用了這種計(jì)算方法。

方法二、Queries-per-second

方法一的計(jì)算方法是有問題的，原因在于把 QPS 中的 Q ，即 Query 理解為"查詢"，并偏執(zhí)地理解為 DQL了，理解為 select only了，這是一種對(duì) Q 的狹義的理解。而實(shí)際上這里的 Q 是廣義的，大家想想什么是 SQL？SQL = DQL + DML + DDL + DCL，所以 QPS 中的 Q 應(yīng)該和 SQL 中的 Q 一樣，都是廣義上的 Query，也就是所有的 SQL 語句。 那么我們?nèi)绾潍@取 MySQL 數(shù)據(jù)庫服務(wù)器上所有的 SQL 語句總數(shù)?

我們知道，從show global status like "Queries"能獲取 Queries 數(shù)，官方對(duì)這個(gè) status 值的解釋是:

QueriesThe number of statements executed by the server. This variable includes statements executed within stored programs, unlike the Questionsvariable. It does not count COM_PINGor COM_STATISTICScommands.

中文的意思是，Queries 計(jì)數(shù)表示服務(wù)器執(zhí)行的語句數(shù)。與 Questions 計(jì)數(shù)不同，此變量包括了存儲(chǔ)過程中執(zhí)行的語句。它不計(jì)數(shù)COM_PING或COM_STATISTICS命令。

所以方法二的計(jì)算方法就是，從show global status like "Queries"能獲取 Queries 數(shù)，然后經(jīng)過 t 秒，再從show global status like "Queries"能獲取 Queries 數(shù)，前后 Queries 數(shù)的差值，除以 t 秒，即算出 Queries-per-second。

看起來這種計(jì)算方式是正確的。

先別那么快下定論，咱們?cè)倏纯吹谌N計(jì)算方法。

方法三、Questions-per-second

方法三的計(jì)算方法和方法二類似，只是把show global status like "Queries"修改為從show global status like "Questions"。

那么 Questions 代表什么呢？以下是官方文檔的解釋:

QuestionsThe number of statements executed by the server. This includes only statements sent to the server by clients and not statements executed within stored programs, unlike the Queries variable. This variable does not count COM_PING, COM_STATISTICS, COM_STMT_PREPARE,COM_STMT_CLOSE, orCOM_STMT_RESETcommands

這里我們可以看出，Queries 計(jì)數(shù)和 Questions 計(jì)數(shù)的區(qū)別在

因?yàn)?Queries 計(jì)數(shù)統(tǒng)計(jì)的更多，所以理論上 Queries 計(jì)數(shù)總是大于等于 Questions 計(jì)數(shù)。

采用 Queries 計(jì)數(shù)還是 Questions 計(jì)數(shù)，基本是見人見智了。而我們這邊由于幾乎沒有業(yè)務(wù)使用到存儲(chǔ)過程和預(yù)準(zhǔn)備語句，所以用哪一種方式都一樣。

有趣的現(xiàn)象是，官方用的是第二種方法"Queries-per-second"。納尼？不是說官方文檔沒定義和說明嗎？ emem，這是我的發(fā)現(xiàn)，見下圖。

截圖是登錄 mysql 客戶端后輸入\s（status）的結(jié)果，經(jīng)過我的驗(yàn)證，這里的 Queris per second avg，等于 Questions/Uptime 而不是 Queries/Uptime。

當(dāng)然這里顯示的 Queris per second avg 參考意義不大，因?yàn)榉帜甘?Uptime，也就是 mysqld 服務(wù)啟動(dòng)的時(shí)間。不能真實(shí)的反饋真實(shí)的、瞬時(shí)的 QPS 指標(biāo)。還是老老實(shí)實(shí)用方法二提到的計(jì)算思路，獲取 t 秒 Questions 的變化值，然后除以 t 秒這種方法來計(jì)算吧。

稍等，好像有點(diǎn)問題！你不是說官方采用的是 Queries-per-second 方法嗎，怎么用 Questions/Uptime 而不是 Queries/Uptime？請(qǐng)看下圖~

其實(shí)官方的\s（status）的 Questions 值的結(jié)果是來源于 show global status like "Queries"而不是 show global status like "Questions"。所以這里的 Questions 實(shí)際上是 Queries，所以我歸類為第二種方法"Queries-per-second"，沒有毛病。

不清楚是否算是一個(gè)文字顯示上的 BUG ，也許官方自己都在糾結(jié)中吧。

TPS的定義和計(jì)算方法

相對(duì)于 QPS 的定義，TPS 的定義我們不用查看百度百科了，因?yàn)樵诠俜轿臋n就可以找到對(duì)于 TPS 的說明：

TPSAcronym for “transactionsper second”, a unit of measurement sometimes used in benchmarks. Its value depends on the workloadrepresented by a particular benchmark test, combined with factors that you control such as the hardware capacity and database configuration.

TPS 是 "Transactions Per Second" （每秒事務(wù)數(shù)）的縮寫，是一種用于基準(zhǔn)測(cè)試的測(cè)量單位，是一臺(tái)數(shù)據(jù)庫服務(wù)器在單位時(shí)間內(nèi)處理的事務(wù)的個(gè)數(shù)。它的值取決于一個(gè)特定的基準(zhǔn)測(cè)試所代表的工作負(fù)荷，以及其他的因素，如硬件容量和數(shù)據(jù)庫配置。

明確了 TPS 的含義為每秒的事務(wù)數(shù)，還需要知道在 MySQL 數(shù)據(jù)庫中只有使用了 Innodb 數(shù)據(jù)庫引擎的數(shù)據(jù)庫或表才支持事務(wù)，在 MySQL 中現(xiàn)在最常用的存儲(chǔ)引擎就是 InnoDB，它從 MySQL 5.5.5 版本開始成為了默認(rèn)存儲(chǔ)引擎。

潛臺(tái)詞: 別傻傻的和我討論 MyISAM 存儲(chǔ)引擎下的 TPS 了。

關(guān)于 TPS 的計(jì)算方法，網(wǎng)上也是眾說紛紜，我們繼續(xù)探討一下真正的 TPS 計(jì)算方法。

方法一、計(jì)算增刪改查總和

在前面的 QPS 計(jì)算中，我們學(xué)會(huì)了從show global status里獲取一些 SQL 語句計(jì)數(shù)統(tǒng)計(jì)值，用于計(jì)算 QPS，TPS 同樣地可以。 我們可以獲取 com_insert、com_delete、com_update、com_select來計(jì)算 TPS 。

官方文檔的解釋如下:

Com_xxx The Com_xxx statement counter variables indicate the number of times each xxx statement has been executed. There is one status variable for each type of statement. For example, Com_delete and Com_update count DELETE and UPDATE statements, respectively. Com_delete_multi and Com_update_multi are similar but apply to DELETE and UPDATE statements that use multiple-table syntax.

中文意思是，這個(gè) Com_xxx 語句計(jì)數(shù)器變量指示每個(gè)變量的次數(shù)。xxx 語句已執(zhí)行。每種類型的語句都有一個(gè)狀態(tài)變量。例如,Com_delete和Com_update分別表示執(zhí)行 DELETE 和 UPDATE 語句的次數(shù)。Com_delete_multi和Com_update_multi相似，但適用于 DELETE 和 UPDATE 使用多表語法的語句。

這里能發(fā)現(xiàn)如果涉及多表刪除或者多表更新情況，需要使用的計(jì)數(shù)變量是Com_delete_multi和Com_update_multi。

也就是方法一的計(jì)算公式為: TPS = 單位時(shí)間 t 內(nèi) (com_insert+ com_delete+ com_update+ com_select+ Com_delete_multi+ Com_update_multi)的增長值/ 單位時(shí)間 t

這里，我們不深究計(jì)數(shù)器使用得是否正確，由于我們是要計(jì)算每秒事務(wù)數(shù)，鑒于一個(gè)事務(wù)里可以跑多個(gè) SQL，這種計(jì)算公式明顯違反了定義，是錯(cuò)誤的。

方法二: 計(jì)算 commit、rollback 總和

是事務(wù)就需要有 begin 和 commit/rollback 語句，對(duì)吧。所以計(jì)算 commit、rollback 的總和，即計(jì)算com_commit+com_rollback，也可以計(jì)算出 TPS，對(duì)吧？

錯(cuò)錯(cuò)錯(cuò)! MySQL 區(qū)別于 Oracle，在 Oracle 里事務(wù)是需要顯示提交的，必須執(zhí)行 commit 提交事務(wù)。而 MySQL 默認(rèn)是設(shè)置了自動(dòng)提交的(參數(shù) autocommit=1)。 所以 MySQL 不 commit 也是可以的，只要不顯式地包裹了 begin 和 commit/rollback，那么一條 SQL 下發(fā)完，就會(huì)自動(dòng)提交，就是一條事務(wù)?，F(xiàn)在大多數(shù) MySQL 的開發(fā)人員都是大量地使用自動(dòng)提交。

所以會(huì)有一個(gè)很尬尷的現(xiàn)象，就是: 在一套一主一從的 MySQL 數(shù)據(jù)庫集群里，主庫因?yàn)椴恢鲃?dòng)執(zhí)行commit，com_commit為 0 ，所以采用方法二計(jì)算出來的 TPS 為 0，而 binlog 是會(huì)自動(dòng)補(bǔ) commit 語句的，復(fù)制到從庫時(shí)，從庫回放 SQL，會(huì)帶 commit，那么從庫會(huì)有com_commit，從庫的 TPS 是真實(shí)的。

這種統(tǒng)計(jì)方法，顯然是我們不能接受的。

那么我認(rèn)為，對(duì)于方法二，可以按以下思路來改造：

Handler_commit+Handler_rollback+Com_commit+Com_rollback+Com_rollback_to_savepoint+Handler_savepoint_rollback

其中Handler_commit、Handler_rollback等打頭的計(jì)數(shù)器是隱式提交的計(jì)數(shù)器。我只提供思路，不保證數(shù)據(jù)正確性，具體計(jì)算方法，讀者可以嘗試按這個(gè)思路改造。

方法三、用 GTID 計(jì)算 TPS

熟悉 MySQL 的同學(xué)肯定清楚開啟數(shù)據(jù)庫的 GTID 是一項(xiàng)硬性指標(biāo)，那么 GTID 是什么？

GTID（ Global Transaction Identifier）全局事務(wù)標(biāo)識(shí)，其保證為每一個(gè)在 master 提交的事務(wù)在復(fù)制集群中可以生成一個(gè)唯一的 ID。一個(gè) GITD 由兩部分組成的，分別是 source_id 和 transaction_id，結(jié)構(gòu)為 GTID=source_id:transaction_id，其中 source_id 就是執(zhí)行事務(wù)的主庫的 server-uuid 值，server-uuid 值是在 mysql 服務(wù)首次啟動(dòng)生成的，保存在數(shù)據(jù)庫的數(shù)據(jù)目錄中，在數(shù)據(jù)目錄中有一個(gè) auto.conf 文件，這個(gè)文件保存了 server-uuid 值（唯一的）。而 transaction_id 則是從 1 開始自增的序列，表示這個(gè)事務(wù)是在主庫上執(zhí)行的第幾個(gè)事務(wù)，MySQL 會(huì)保證這個(gè)事務(wù)和 GTID 是一比一的關(guān)系。

既然一個(gè)事務(wù)只會(huì)生成一個(gè)唯一的 GTID，而且 transaction_id 的部分還是順序遞增的序列，那么根據(jù)這個(gè)值來計(jì)算 TPS 是應(yīng)該是最準(zhǔn)確的一種方式了。

MySQL 5.6 版本開始支持 GTID 功能。

知道了基于GTID來計(jì)算TPS最準(zhǔn)確，那如何計(jì)算呢？在 MySQL 上，可以使用 show master status命令來查看 Executed_Gtid_Set的值，這個(gè)值表示已經(jīng)在這個(gè)實(shí)例上執(zhí)行的 GTID集合。

如果是從庫，執(zhí)行show slave status中輸出的對(duì)應(yīng)列Executed_Gtid_Set，含義也相同。

比如下面這種情況，直接可以根據(jù)單位時(shí)間內(nèi)兩次輸出結(jié)果 GTID 數(shù)值差值與單位時(shí)間之商計(jì)算得出 TPS。

mysql> show master status \G*************************** 1. row ***************************             File: mysql-bin.000006         Position: 926206     Binlog_Do_DB:  Binlog_Ignore_DB: Executed_Gtid_Set: 59bf2dea-e3b5-11eb-ae63-02000aba3f7b:1-25161 row in set (0.00 sec)

這里可能有人會(huì)說，我這沒算對(duì)，因?yàn)槲疫@里使用的 GTID 確實(shí)可以保證所有計(jì)數(shù)都是事務(wù)的，但并沒有包含 select 類型的事務(wù)。

我給出兩個(gè)解釋吧:

總結(jié)一下

本文探討了 QPS 和 TPS 的各種計(jì)算方法，并給出我們認(rèn)為的最佳計(jì)算方法。