主頁(yè) > 知識(shí)庫(kù) > MySQL幾點(diǎn)重要的性能指標(biāo)計(jì)算和優(yōu)化方法總結(jié)

MySQL幾點(diǎn)重要的性能指標(biāo)計(jì)算和優(yōu)化方法總結(jié)

熱門(mén)標(biāo)簽:電銷(xiāo)機(jī)器人說(shuō)明書(shū) 手機(jī)用地圖標(biāo)注工具 安國(guó)在哪里辦理400電話 智能電銷(xiāo)機(jī)器人靠譜么 長(zhǎng)安區(qū)違法建房地圖標(biāo)注 電銷(xiāo)機(jī)器人公眾號(hào)推送 地圖標(biāo)注培訓(xùn) 昆明智能外呼系統(tǒng)中心 南宋地圖標(biāo)注黃河華山

1 QPS計(jì)算(每秒查詢數(shù))

針對(duì)MyISAM引擎為主的DB

MySQL> show GLOBAL status like 'questions';
+---------------+------------+
| Variable_name | Value   |
+---------------+------------+
| Questions   | 2009191409 |
+---------------+------------+
1 row in set (0.00 sec)

mysql> show global status like 'uptime';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| Uptime    | 388402 |
+---------------+--------+
1 row in set (0.00 sec)

QPS=questions/uptime=5172,mysql自啟動(dòng)以來(lái)的平均QPS,如果要計(jì)算某一時(shí)間段內(nèi)的QPS,可在高峰期間獲取間隔時(shí)間t2-t1,然后分別計(jì)算出t2和t1時(shí)刻的q值,QPS=(q2-q1)/(t2-t1)

針對(duì)InnnoDB引擎為主的DB

mysql> show global status like 'com_update';
+---------------+----------+
| Variable_name | Value  |
+---------------+----------+
| Com_update  | 87094306 |
+---------------+----------+
1 row in set (0.00 sec)

mysql> show global status like 'com_select';
+---------------+------------+
| Variable_name | Value   |
+---------------+------------+
| Com_select  | 1108143397 |
+---------------+------------+
1 row in set (0.00 sec)


mysql> show global status like 'com_delete';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| Com_delete  | 379058 |
+---------------+--------+
1 row in set (0.00 sec)

mysql> show global status like 'uptime';

+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| Uptime    | 388816 |
+---------------+--------+
1 row in set (0.00 sec)

QPS=(com_update+com_insert+com_delete+com_select)/uptime=3076,某一時(shí)間段內(nèi)的QPS查詢方法同上。

2 TPS計(jì)算(每秒事務(wù)數(shù))

mysql> show global status like 'com_commit';

+---------------+---------+
| Variable_name | Value  |
+---------------+---------+
| Com_commit  | 7424815 |
+---------------+---------+
1 row in set (0.00 sec)

mysql> show global status like 'com_rollback';
+---------------+---------+
| Variable_name | Value  |
+---------------+---------+
| Com_rollback | 1073179 |
+---------------+---------+
1 row in set (0.00 sec)

mysql> show global status like 'uptime';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| Uptime    | 389467 |
+---------------+--------+
1 row in set (0.00 sec)

TPS=(com_commit+com_rollback)/uptime=22

3 線程連接數(shù)和命中率

mysql> show global status like 'threads_%';
+-------------------+-------+
| Variable_name   | Value |
+-------------------+-------+
| Threads_cached  | 480  |   //代表當(dāng)前此時(shí)此刻線程緩存中有多少空閑線程
| Threads_connected | 153  |  //代表當(dāng)前已建立連接的數(shù)量,因?yàn)橐粋€(gè)連接就需要一個(gè)線程,所以也可以看成當(dāng)前被使用的線程數(shù)
| Threads_created  | 20344 |  //代表從最近一次服務(wù)啟動(dòng),已創(chuàng)建線程的數(shù)量
| Threads_running  | 2   |   //代表當(dāng)前激活的(非睡眠狀態(tài))線程數(shù)
+-------------------+-------+
4 rows in set (0.00 sec)

mysql> show global status like 'Connections';
+---------------+-----------+
| Variable_name | Value   |
+---------------+-----------+
| Connections  | 381487397 |
+---------------+-----------+
1 row in set (0.00 sec)

線程緩存命中率=1-Threads_created/Connections  = 99.994%

我們?cè)O(shè)置的線程緩存?zhèn)€數(shù)

mysql> show variables like '%thread_cache_size%';
+-------------------+-------+
| Variable_name   | Value |
+-------------------+-------+
| thread_cache_size | 500  |
+-------------------+-------+
1 row in set (0.00 sec)

根據(jù)Threads_connected可預(yù)估thread_cache_size值應(yīng)該設(shè)置多大,一般來(lái)說(shuō)250是一個(gè)不錯(cuò)的上限值,如果內(nèi)存足夠大,也可以設(shè)置成thread_cache_size值和threaads_connected值相同;

或者通過(guò)觀察threads_created值,如果該值很大或一直在增長(zhǎng),可以適當(dāng)增加thread_cache_size的值;在休眠狀態(tài)下每個(gè)線程大概占用256KB左右的內(nèi)存,所以當(dāng)內(nèi)存足夠時(shí),設(shè)置太小也不會(huì)節(jié)約太多內(nèi)存,除非該值已經(jīng)超過(guò)幾千。

4 表緩存

mysql> show global status like 'open_tables%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables  | 2228 |
+---------------+-------+
1 row in set (0.00 sec)

我們?cè)O(shè)置的打開(kāi)表的緩存和表定義緩存

mysql> show variables like 'table_open_cache';
+------------------+-------+
| Variable_name  | Value |
+------------------+-------+
| table_open_cache | 16384 |
+------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'table_defi%';
+------------------------+-------+
| Variable_name     | Value |
+------------------------+-------+
| table_definition_cache | 2000 |
+------------------------+-------+
1 row in set (0.00 sec)

針對(duì)MyISAM:

mysql每打開(kāi)一個(gè)表,都會(huì)讀入一些數(shù)據(jù)到table_open_cache 緩存 中,當(dāng)mysql在這個(gè)緩存中找不到相應(yīng)的信息時(shí),才會(huì)去磁盤(pán)上直接讀取,所以該值要設(shè)置得足夠大以避免需要重新打開(kāi)和重新解析表的定義,一般設(shè)置為max_connections的10倍,但最好保持在10000以內(nèi)。

還有種依據(jù)就是根據(jù)狀態(tài)open_tables的值進(jìn)行設(shè)置,如果發(fā)現(xiàn)open_tables的值每秒變化很大,那么可能需要增大table_open_cache的值。

table_definition_cache 通常簡(jiǎn)單設(shè)置為服務(wù)器中存在的表的數(shù)量,除非有上萬(wàn)張表。

針對(duì)InnoDB:

與MyISAM不同,InnoDB的open table和open file并無(wú)直接聯(lián)系,即打開(kāi)frm表時(shí)其相應(yīng)的ibd文件可能處于關(guān)閉狀態(tài);

故InnoDB只會(huì)用到table_definiton_cache,不會(huì)使用table_open_cache;

其frm文件保存于table_definition_cache中,而idb則由innodb_open_files決定(前提是開(kāi)啟了innodb_file_per_table),最好將innodb_open_files設(shè)置得足夠大,使得服務(wù)器可以保持所有的.ibd文件同時(shí)打開(kāi)。

5 最大連接數(shù)

mysql> show global status like 'Max_used_connections';
+----------------------+-------+
| Variable_name    | Value |
+----------------------+-------+
| Max_used_connections | 1785 |
+----------------------+-------+
1 row in set (0.00 sec)

我們?cè)O(shè)置的max_connections大小

mysql> show variables like 'max_connections%';
+-----------------+-------+
| Variable_name  | Value |
+-----------------+-------+
| max_connections | 4000 |
+-----------------+-------+
1 row in set (0.00 sec)

通常max_connections的大小應(yīng)該設(shè)置為比Max_used_connections狀態(tài)值大,Max_used_connections狀態(tài)值反映服務(wù)器連接在某個(gè)時(shí)間段是否有尖峰,如果該值大于max_connections值,代表客戶端至少被拒絕了一次,可以簡(jiǎn)單地設(shè)置為符合以下條件:Max_used_connections/max_connections=0.8

6 Innodb 緩存命中率

mysql> show global status like 'innodb_buffer_pool_read%';
+---------------------------------------+--------------+
| Variable_name             | Value    |
+---------------------------------------+--------------+
| Innodb_buffer_pool_read_ahead_rnd   | 0      |
| Innodb_buffer_pool_read_ahead     | 268720    |   //預(yù)讀的頁(yè)數(shù)
| Innodb_buffer_pool_read_ahead_evicted | 0      |   
| Innodb_buffer_pool_read_requests   | 480291074970 | //從緩沖池中讀取的次數(shù)
| Innodb_buffer_pool_reads       | 29912739   |     //表示從物理磁盤(pán)讀取的頁(yè)數(shù)
+---------------------------------------+--------------+
5 rows in set (0.00 sec)

緩沖池命中率 = (Innodb_buffer_pool_read_requests)/(Innodb_buffer_pool_read_requests + Innodb_buffer_pool_read_ahead + Innodb_buffer_pool_reads)=99.994%

如果該值小于99.9%,建議就應(yīng)該增大innodb_buffer_pool_size的值了,該值一般設(shè)置為內(nèi)存總大小的75%-85%,或者計(jì)算出操作系統(tǒng)所需緩存+mysql每個(gè)連接所需的內(nèi)存(例如排序緩沖和臨時(shí)表)+MyISAM鍵緩存,剩下的內(nèi)存都給innodb_buffer_pool_size,不過(guò)也不宜設(shè)置太大,會(huì)造成內(nèi)存的頻繁交換,預(yù)熱和關(guān)閉時(shí)間長(zhǎng)等問(wèn)題。

7 MyISAM Key Buffer命中率和緩沖區(qū)使用率

mysql> show global status like 'key_%';
+------------------------+-----------+
| Variable_name     | Value   |
+------------------------+-----------+
| Key_blocks_not_flushed | 0     |
| Key_blocks_unused   | 106662  |
| Key_blocks_used    | 107171  |
| Key_read_requests   | 883825678 |
| Key_reads       | 133294  |
| Key_write_requests   | 217310758 |
| Key_writes       | 2061054  |
+------------------------+-----------+
7 rows in set (0.00 sec)

mysql> show variables like '%key_cache_block_size%';
+----------------------+-------+
| Variable_name    | Value |
+----------------------+-------+
| key_cache_block_size | 1024 |
+----------------------+-------+
1 row in set (0.00 sec)


mysql> show variables like '%key_buffer_size%';
+-----------------+-----------+
| Variable_name  | Value   |
+-----------------+-----------+
| key_buffer_size | 134217728 |
+-----------------+-----------+
1 row in set (0.00 sec)

緩沖區(qū)的使用率=1-(Key_blocks_unused*key_cache_block_size/ key_buffer_size)=18.6%

讀命中率=1-Key_reads /Key_read_requests=99.98%

寫(xiě)命中率=1-Key_writes / Key_write_requests =99.05%

可看到緩沖區(qū)的使用率并不高,如果很長(zhǎng)一段時(shí)間后還沒(méi)有使用完所有的鍵緩沖,可以考慮把緩沖區(qū)調(diào)小一點(diǎn)。

鍵緩存命中率可能意義不大,因?yàn)樗蛻?yīng)用相關(guān),有些應(yīng)用在95%的命中率下就工作良好,有些則需要99.99%,所以從經(jīng)驗(yàn)上看,每秒的緩存未命中次數(shù)更重要,假設(shè)一個(gè)獨(dú)立磁盤(pán)每秒能做100個(gè)隨機(jī)讀,那么每秒有5個(gè)緩沖未命中可能不會(huì)導(dǎo)致I/O繁忙,但每秒80個(gè)就可能出現(xiàn)問(wèn)題。

每秒緩存未命中=Key_reads/uptime=0.33

8 臨時(shí)表使用情況

mysql> show global status like 'Created_tmp%';
+-------------------------+----------+
| Variable_name      | Value  |
+-------------------------+----------+
| Created_tmp_disk_tables | 19226325 |
| Created_tmp_files    | 117   |
| Created_tmp_tables   | 56265812 |
+-------------------------+----------+
3 rows in set (0.00 sec)

mysql> show variables like '%tmp_table_size%';
+----------------+----------+
| Variable_name | Value  |
+----------------+----------+
| tmp_table_size | 67108864 |
+----------------+----------+
1 row in set (0.00 sec)

可看到總共創(chuàng)建了56265812 張臨時(shí)表,其中有19226325 張涉及到了磁盤(pán)IO,大概比例占到了0.34,證明數(shù)據(jù)庫(kù)應(yīng)用中排序,join語(yǔ)句涉及的數(shù)據(jù)量太大,需要優(yōu)化SQL或者增大tmp_table_size的值,我設(shè)的是64M。該比值應(yīng)該控制在0.2以內(nèi)。

9 binlog cache使用情況

mysql> show status like 'Binlog_cache%'; 
+-----------------------+----------+
| Variable_name     | Value  |
+-----------------------+----------+
| Binlog_cache_disk_use | 15    |
| Binlog_cache_use   | 95978256 |
+-----------------------+----------+
2 rows in set (0.00 sec)

mysql> show variables like 'binlog_cache_size';
+-------------------+---------+
| Variable_name   | Value  |
+-------------------+---------+
| binlog_cache_size | 1048576 |
+-------------------+---------+
1 row in set (0.00 sec)

Binlog_cache_disk_use表示因?yàn)槲覀僢inlog_cache_size設(shè)計(jì)的內(nèi)存不足導(dǎo)致緩存二進(jìn)制日志用到了臨時(shí)文件的次數(shù)

Binlog_cache_use 表示 用binlog_cache_size緩存的次數(shù)

當(dāng)對(duì)應(yīng)的Binlog_cache_disk_use 值比較大的時(shí)候 我們可以考慮適當(dāng)?shù)恼{(diào)高 binlog_cache_size 對(duì)應(yīng)的值

10 Innodb log buffer size的大小設(shè)置

mysql> show variables like '%innodb_log_buffer_size%';
+------------------------+---------+
| Variable_name     | Value  |
+------------------------+---------+
| innodb_log_buffer_size | 8388608 |
+------------------------+---------+
1 row in set (0.00 sec)
mysql> show status like 'innodb_log_waits';
+------------------+-------+
| Variable_name  | Value |
+------------------+-------+
| Innodb_log_waits | 0   |
+------------------+-------+
1 row in set (0.00 sec)

innodb_log_buffer_size我設(shè)置了8M,應(yīng)該足夠大了;Innodb_log_waits表示因log buffer不足導(dǎo)致等待的次數(shù),如果該值不為0,可以適當(dāng)增大innodb_log_buffer_size的值。

11 表掃描情況判斷

mysql> show global status like 'Handler_read%';
+-----------------------+--------------+
| Variable_name     | Value    |
+-----------------------+--------------+
| Handler_read_first  | 19180695   |
| Handler_read_key   | 30303690598 |
| Handler_read_last   | 290721    |
| Handler_read_next   | 51169834260 |
| Handler_read_prev   | 1267528402  |
| Handler_read_rnd   | 219230406  |
| Handler_read_rnd_next | 344713226172 |
+-----------------------+--------------+
7 rows in set (0.00 sec)

Handler_read_first:使用索引掃描的次數(shù),該值大小說(shuō)不清系統(tǒng)性能是好是壞

Handler_read_key:通過(guò)key進(jìn)行查詢的次數(shù),該值越大證明系統(tǒng)性能越好

Handler_read_next:使用索引進(jìn)行排序的次數(shù)
Handler_read_prev:此選項(xiàng)表明在進(jìn)行索引掃描時(shí),按照索引倒序從數(shù)據(jù)文件里取數(shù)據(jù)的次數(shù),一般就是ORDER BY ... DESC

Handler_read_rnd:該值越大證明系統(tǒng)中有大量的沒(méi)有使用索引進(jìn)行排序的操作,或者join時(shí)沒(méi)有使用到index

Handler_read_rnd_next:使用數(shù)據(jù)文件進(jìn)行掃描的次數(shù),該值越大證明有大量的全表掃描,或者合理地創(chuàng)建索引,沒(méi)有很好地利用已經(jīng)建立好的索引

12 Innodb_buffer_pool_wait_free

mysql> show global status like 'Innodb_buffer_pool_wait_free';
+------------------------------+-------+
| Variable_name        | Value |
+------------------------------+-------+
| Innodb_buffer_pool_wait_free | 0   |
+------------------------------+-------+
1 row in set (0.00 sec)

該值不為0表示buffer pool沒(méi)有空閑的空間了,可能原因是innodb_buffer_pool_size設(shè)置太大,可以適當(dāng)減少該值。

13 join操作信息

mysql> show global status like 'select_full_join';
+------------------+-------+
| Variable_name  | Value |
+------------------+-------+
| Select_full_join | 10403 |
+------------------+-------+
1 row in set (0.00 sec)

該值表示在join操作中沒(méi)有使用到索引的次數(shù),值很大說(shuō)明join語(yǔ)句寫(xiě)得很有問(wèn)題

mysql> show global status like 'select_range';
+---------------+----------+
| Variable_name | Value  |
+---------------+----------+
| Select_range | 22450380 |
+---------------+----------+
1 row in set (0.00 sec)

該值表示第一個(gè)表使用ranges的join數(shù)量,該值很大說(shuō)明join寫(xiě)得沒(méi)有問(wèn)題,通??刹榭磗elect_full_join和select_range的比值來(lái)判斷系統(tǒng)中join語(yǔ)句的性能情況

mysql> show global status like 'Select_range_check';
+--------------------+-------+
| Variable_name   | Value |
+--------------------+-------+
| Select_range_check | 0   |
+--------------------+-------+
1 row in set (0.00 sec)

如果該值不為0需要檢查表的索引是否合理,表示在表n+1中重新評(píng)估表n中的每一行的索引是否開(kāi)銷(xiāo)最小所做的聯(lián)接數(shù),意味著表n+1對(duì)該聯(lián)接而言并沒(méi)有有用的索引。

mysql> show GLOBAL status like 'select_scan';
+---------------+-----------+
| Variable_name | Value   |
+---------------+-----------+
| Select_scan  | 116037811 |
+---------------+-----------+
1 row in set (0.00 sec)

select_scan表示掃描第一張表的連接數(shù)目,如果第一張表中每行都參與聯(lián)接,這樣的結(jié)果并沒(méi)有問(wèn)題;如果你并不想要返回所有行但又沒(méi)有使用到索引來(lái)查找到所需要的行,那么計(jì)數(shù)很大就很糟糕了。

14 慢查詢

mysql> show global status like 'Slow_queries';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| Slow_queries | 114111 |
+---------------+--------+
1 row in set (0.00 sec)

該值表示mysql啟動(dòng)以來(lái)的慢查詢個(gè)數(shù),即執(zhí)行時(shí)間超過(guò)long_query_time的次數(shù),可根據(jù)Slow_queries/uptime的比值判斷單位時(shí)間內(nèi)的慢查詢個(gè)數(shù),進(jìn)而判斷系統(tǒng)的性能。

15表鎖信息

mysql> show global status like 'table_lock%';
+-----------------------+------------+
| Variable_name     | Value   |
+-----------------------+------------+
| Table_locks_immediate | 1644917567 |
| Table_locks_waited  | 53     |
+-----------------------+------------+
2 rows in set (0.00 sec)

這兩個(gè)值的比值:Table_locks_waited /Table_locks_immediate 趨向于0,如果值比較大則表示系統(tǒng)的鎖阻塞情況比較嚴(yán)重

以上這篇MySQL幾點(diǎn)重要的性能指標(biāo)計(jì)算和優(yōu)化方法總結(jié)就是小編分享給大家的全部?jī)?nèi)容了,希望能給大家一個(gè)參考,也希望大家多多支持腳本之家。

您可能感興趣的文章:
  • mysql計(jì)算時(shí)間差函數(shù)
  • Mysql中通過(guò)生日計(jì)算年齡的多種方法
  • 在php和MySql中計(jì)算時(shí)間差的方法
  • mysql 字符串長(zhǎng)度計(jì)算實(shí)現(xiàn)代碼(gb2312+utf8)
  • Mysql數(shù)據(jù)庫(kù)的QPS和TPS的意義和計(jì)算方法
  • 淺談mysql explain中key_len的計(jì)算方法
  • MySQL的查詢計(jì)劃中ken_len的值計(jì)算方法
  • mysql日期和時(shí)間的間隔計(jì)算實(shí)例分析
  • MySQL日期加減函數(shù)詳解
  • mysql 觸發(fā)器創(chuàng)建與使用方法示例
  • MySQL觸發(fā)器基本用法詳解【創(chuàng)建、查看、刪除等】
  • mysql累加計(jì)算實(shí)現(xiàn)方法詳解

標(biāo)簽:江門(mén) 長(zhǎng)沙 吉安 東莞 潛江 武漢 合肥 南昌

巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《MySQL幾點(diǎn)重要的性能指標(biāo)計(jì)算和優(yōu)化方法總結(jié)》,本文關(guān)鍵詞  MySQL,幾點(diǎn),重要的,性能,;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題,煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們,我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無(wú)關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《MySQL幾點(diǎn)重要的性能指標(biāo)計(jì)算和優(yōu)化方法總結(jié)》相關(guān)的同類(lèi)信息!
  • 本頁(yè)收集關(guān)于MySQL幾點(diǎn)重要的性能指標(biāo)計(jì)算和優(yōu)化方法總結(jié)的相關(guān)信息資訊供網(wǎng)民參考!
  • 推薦文章