如何監控mysql性能

這篇文章主要介紹了如何監控mysql性能，具有一定借鑒價值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著大家一起了解一下。

查看mysql服務器靜態參數：

show variables;

查看mysql動態運行信息：

show status;

一、查詢用戶的連接數

--show processlist;只列出前100條，如果想全列出請使用show full processlist;

命令： show processlist; 

如果是root帳號，你能看到所有用戶的當前連接。如果是其它普通帳號，只能看到自己占用的連接。用 mysql -uroot -p123456 來登錄（注意：用戶名和密碼不用包含“”） 

二、查看數據庫運行狀態

命令：show global status;

命令：show status; 

命令：show status like '%下面變量%'; 

Aborted_clients 由于客戶沒有正確關閉連接已經死掉，已經放棄的連接數量。 

Aborted_connects 嘗試已經失敗的MySQL服務器的連接的次數。 

Connections 試圖連接MySQL服務器的次數。 

Created_tmp_tables 當執行語句時，已經被創造了的隱含臨時表的數量。 

Delayed_insert_threads 正在使用的延遲插入處理器線程的數量。 

Delayed_writes 用INSERT DELAYED寫入的行數。 

Delayed_errors 用INSERT DELAYED寫入的發生某些錯誤(可能重復鍵值)的行數。 

Flush_commands 執行FLUSH命令的次數。 

Handler_delete 請求從一張表中刪除行的次數。 

Handler_read_first 請求讀入表中第一行的次數。 

Handler_read_key 請求數字基于鍵讀行。 

Handler_read_next 請求讀入基于一個鍵的一行的次數。 

Handler_read_rnd 請求讀入基于一個固定位置的一行的次數。 

Handler_update 請求更新表中一行的次數。 

Handler_write 請求向表中插入一行的次數。 

Key_blocks_used 用于關鍵字緩存的塊的數量。 

Key_read_requests 請求從緩存讀入一個鍵值的次數。 

Key_reads 從磁盤物理讀入一個鍵值的次數。 

Key_write_requests 請求將一個關鍵字塊寫入緩存次數。 

Key_writes 將一個鍵值塊物理寫入磁盤的次數。 

Max_used_connections 同時使用的連接的最大數目。 

Not_flushed_key_blocks 在鍵緩存中已經改變但是還沒被清空到磁盤上的鍵塊。 

Not_flushed_delayed_rows 在INSERT DELAY隊列中等待寫入的行的數量。 

Open_tables 打開表的數量。 

Open_files 打開文件的數量。 

Open_streams 打開流的數量(主要用于日志記載） 

Opened_tables 已經打開的表的數量。 

Questions 發往服務器的查詢的數量。 

Slow_queries 要花超過long_query_time時間的查詢數量。 

Threads_connected 當前打開的連接的數量。 

Threads_running 不在睡眠的線程數量。 

Uptime 服務器工作了多少秒。

注釋：

如果Opened_tables太大，那么你的table_open_cache變量可能太小。 

如果key_reads太大，那么你的key_cache可能太小。緩存命中率可以用key_reads/key_read_requests計算。 

如果Handler_read_rnd太大，那么你很可能有大量的查詢需要MySQL掃描整個表或你有沒正確使用鍵值的聯結(join)。 

可以根據“show status”命令返回的狀態進行微調。主要注意以下變量的數值，越小越好，最好為零：

  Created_tmp_disk_tables

  Created_tmp_tables

  Created_tmp_files

  Slow_queries

 三、數據庫性能等查詢

1. Show status命令了解各種SQL的執行頻率

--查看當前會話執行的各項命令統計 com_XXX

show session status like 'Com_%' 其中session可省

show status like 'Com_%'

--顯示全局統計使用

SHOW GLOBAL STATUS LIKE 'COM_%';

2. 針對InnoDB存儲引擎狀態的統計

SHOW GLOBAL STATUS LIKE 'Innodb_%'

3. 查看試圖連接mySQL服務器的次數

show global status like 'connections';

4. 查看服務器工作時間

show global status like 'uptime';

5.查看所在的數據庫名

（1）用select database()語句;

 select database();

（2）用show tables語句，查詢出來的結果中，第一行為Tables_in_***，這里***就是當前所在的數據庫名稱。

 show tables;

（3）用status語句，查詢出來的結果中有一行是currrent database:***。這里***就是當前所在的數據庫名稱。

 status;

6.查看表的存儲格式信息

show table status like '%test%'\G;

--顯示當前使用或者指定的database中的每個表的信息。信息包括表類型和表的最新更新時間

show table status;

--顯示當前數據庫中所有表的名稱

show tables或show tables from database_name;

7.查看告警信息

show warnings\G;

8.查詢表結構信息

show create table test\G;

9.查詢當前架構(schema)下的基表

select * from information_schema.tables where table_type='BASE TABLE' and table_schema=database()\G;

10.查詢當前架構(schema)下的視圖信息

select * from information_schema.views where table_schema=database()\G';

11.查詢數據庫是否啟用了分區功能

show variables like '%partition%'\G;

show plugins\G;

12.顯示索引信息

show index from TABLE_NAME\G

show keys from  TABLE_NAME;

13.查詢當前架構(schema)下的分區信息

select * from information_schema.PARTITIONS where table_schema=database() and table_name='t3'\G;  

14.查詢當前架構(schema)下的表

select * from information_schema.tables where table_name='t' and table_schema=database()\G;

15.顯示mysql如何使用索引來處理select語句以及連接表

https://www.cnblogs.com/yycc/p/7338894.html

explain sql\G;

explain partitions sql\G;

17.根據已知表創建新表的結構

create table e2 like e;

18.將已知分區表的結構變為普通表

alter table e2 remove partitioning;

19.交換分區

 alter table e exchange partition p0 with table e2;

20.查看innodb存儲引擎中的latch

show engine innodb mutex;

21.查看innodb存儲引擎中的當前鎖引擎信息

show engine innodb status\G;

show full processlist;

select * from information_schema.innodb_trx\G;

select * from information_schema.innodb_locks\G;

select * from information_schema.innodb_lock_waits\G;

--鎖聯合查詢，如下：

select r.trx_id                waiting_trx_id,

       r.trx_requested_lock_id waiting_requested_lock_id,

       r.trx_mysql_thread_id   waiting_thread,

       r.trx_query             waiting_query,

       r.trx_state             waiting_status,

       b.trx_id                blocking_trx_id,

       b.trx_mysql_thread_id   blocking_thread,

       b.trx_query             blocking_query,

       b.trx_state             blocking_status

  from information_schema.innodb_lock_waits w,

       information_schema.innodb_trx        b,

       information_schema.innodb_trx        r

 where b.trx_id = w.blocking_trx_id

   and r.trx_id = w.requesting_trx_id\G;

22.mysql 顯示開啟事物與關閉事物

    開啟事物： start transaction 或者 begin

    關閉事物：commit---成功 與 rollback--回滾；

23.mysql修改事務隔離級別

用戶可以用SET TRANSACTION語句改變單個會話或者所有新進連接的隔離級別。它的語法如下：

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | 

REPEATABLE READ | SERIALIZABLE}

注意：默認的行為（不帶session和global）是為下一個（未開始）事務設置隔離級別。如果你使用GLOBAL關鍵字，

語句在全局對從那點開始創建的所有新連接（除了不存在的連接）設置默認事務級別。你需要SUPER權限來做這個。

使用SESSION 關鍵字為將來在當前連接上執行的事務設置默認事務級別。 任何客戶端都能自由改變會話隔離級別（甚至

在事務的中間），或者為下一個事務設置隔離級別。 

你可以用下列語句查詢全局和會話事務隔離級別：

SELECT @@global.tx_isolation; 

SELECT @@session.tx_isolation; 

SELECT @@tx_isolation;

24.SHOW COLUMNS顯示有關給定表中列的信息。它也適用于視圖。 SHOW COLUMNS僅顯示您具有某些權限的列的信息。    

-- 顯示表中列名稱

show columns from table_name from database_name; 或show columns from database_name.table_name;

SHOW [FULL] {COLUMNS | FIELDS}

    {FROM | IN} tbl_name

    [{FROM | IN} db_name]

    [LIKE 'pattern' | WHERE expr]

25. QPS(每秒Query量) 

QPS = Questions(or Queries) / seconds 

mysql > show global status like 'Question%'; 

26. TPS(每秒事務量) 

TPS = (Com_commit + Com_rollback) / seconds 

mysql > show global status like 'Com_commit'; 

mysql > show global status like 'Com_rollback'; 

27. key Buffer 命中率 

mysql>show global status like 'key%'; 

key_buffer_read_hits = (1-key_reads / key_read_requests) * 100% 

key_buffer_write_hits = (1-key_writes / key_write_requests) * 100% 

28. InnoDB Buffer命中率 

mysql> show status like 'innodb_buffer_pool_read%'; 

innodb_buffer_read_hits = (1 - innodb_buffer_pool_reads / innodb_buffer_pool_read_requests) * 100% 

29. Query Cache命中率 

mysql> show status like 'Qcache%'; 

Query_cache_hits = (Qcahce_hits / (Qcache_hits + Qcache_inserts )) * 100%; 

30. Table Cache狀態量 

mysql> show global status like 'open%'; 

比較 open_tables 與 opend_tables 值 

31. Thread Cache 命中率 

mysql> show global status like 'Thread%'; 

mysql> show global status like 'Connections'; 

Thread_cache_hits = (1 - Threads_created / connections ) * 100% 

32. 鎖定狀態 

mysql> show global status like '%lock%'; 

Table_locks_waited/Table_locks_immediate=0.3% 如果這個比值比較大的話，說明表鎖造成的阻塞比較嚴重 

Innodb_row_lock_waits innodb行鎖，太大可能是間隙鎖造成的 

33. 復制延時量 

mysql > show slave status 

查看延時時間 

34. Tmp Table 狀況(臨時表狀況) 

mysql > show status like 'Create_tmp%'; 

Created_tmp_disk_tables/Created_tmp_tables比值最好不要超過10%，如果Created_tmp_tables值比較大， 

可能是排序句子過多或者是連接句子不夠優化 

35. Binlog Cache 使用狀況 

mysql > show status like 'Binlog_cache%'; 

如果Binlog_cache_disk_use值不為0 ，可能需要調大 binlog_cache_size大小 

36. Innodb_log_waits 量 

mysql > show status like 'innodb_log_waits'; 

Innodb_log_waits值不等于0的話，表明 innodb log buffer 因為空間不足而等待 

比如命令： 

>#show global status; 

雖然可以使用： 

>#show global status like %...%; 

來過濾，但是對應長長的list，每一項都代表什么意思，還是有必要弄清楚。

37.查看是否啟動了分布式事物XA(默認是ON):

 show variables like '%innodb_support_xa%';

38.MySQL XA事務基本語法

XA {START|BEGIN} xid [JOIN|RESUME] 啟動xid事務 (xid 必須是一個唯一值; 不支持[JOIN|RESUME]子句)

XA END xid [SUSPEND [FOR MIGRATE]] 結束xid事務 ( 不支持[SUSPEND [FOR MIGRATE]] 子句)

XA PREPARE xid 準備、預提交xid事務

XA COMMIT xid [ONE PHASE] 提交xid事務

XA ROLLBACK xid 回滾xid事務

XA RECOVER 查看處于PREPARE 階段的所有事務

39.判斷數據庫內存是否已經達到瓶頸

show global status like '%innodb%read%'\G;

40.查看索引使用情況

show status like '%Handler_read%';

41.查看innodb存儲引擎上行鎖的爭用情況：

 show status like '%innodb_row_lock%';

如果發現鎖爭用比較嚴重，則Innodb_row_lock_time_avg和Innodb_row_lock_waits比較高。

另外就是設置innodbDB Monitors進一步發生鎖沖突的表、數據行等，并分析鎖爭用的原因！

注意：innodb行鎖是通過給索引上的索引項加鎖實現的。這意味著：只有通過索引條件檢索數據，innodb才使用行級鎖，

否則，innodb將使用表鎖！

42.查看mysql服務器參數的詳細定義

mysqld --verbose --help|more

比如想要知道當前數據庫字符集的設置，如下：

mysqld --verbose --help|grep character-set-server

43.二進制日志

查看二進制日志狀態

show variables like '%log_bin%';

查看當前服務器所有的二進制日志文件

show binary logs;

show master logs;

查看當前二進制日志文件狀態

show master status;

切換二進制日志

flush logs;

44.查詢mysql數據庫中的當前時間

select now();

45.在mysql數據庫中查詢操作系統當前的時間

system date;

46.查看數據庫字符集查看MYSQL數據庫服務器和數據庫字符集

show variables like '%character%';

查看MYSQL所支持的字符集

show charset;

查看庫的字符集

show create database test72\G;

查看表的字符集

show table status from test72 like 'zs'\G;

查看表中所有列的字符集

show full columns from 表名;

show full columns from zs\G;

47.顯示mysql中所有數據庫的名稱 

show databases; 

48.顯示一個用戶的權限，顯示結果類似于grant 命令

show grants for user_name;

49.顯示服務器所支持的不同權限 

show privileges;  

50.顯示create database 語句是否能夠創建指定的數據庫

show create database database_name;  

51.顯示安裝以后可用的存儲引擎和默認引擎

show engines; 

52.顯示innoDB存儲引擎的狀態

show engine innodb status\G;

53.統計每個庫大小

select TABLE_SCHEMA,SUM(DATA_LENGTH)/1024/1024/1024 as data_length,SUM(INDEX_LENGTH)/1024/1024/10

24 as index_length,SUM(DATA_LENGTH+INDEX_LENGTH)/1024/1024/1024 as sum_data_index from informatio

n_schema.tables where TABLE_SCHEMA!='information_schema' and TABLE_SCHEMA!='mysql' group by TABL

E_SCHEMA;

54.統計庫中每個表大小，此以test庫為例

select TABLE_NAME,DATA_LENGTH,INDEX_LENGTH,SUM(DATA_LENGTH+INDEX_LENGTH)/1024/1024/102

4 as TOTAL_SIZE from information_schema.tables where TABLE_SCHEMA='test' group by TABLE_SCHEMA;

55.統計所有數據庫大小

select SUM(DATA_LENGTH+INDEX_LENGTH)/1024/1024/1024 as sum_data_index from information_schema.tab

les;

感謝你能夠認真閱讀完這篇文章，希望小編分享的“如何監控mysql性能”這篇文章對大家有幫助，同時也希望大家多多支持億速云，關注億速云行業資訊頻道，更多相關知識等著你來學習!