主持人 > 博客 > 管理 > PostgreSQL 查詢的性能監控。 第 1 部分 - 報告

PostgreSQL 查詢的性能監控。 第 1 部分 - 報告

工程師 - 翻譯自拉丁語 - 受到啟發。
工程師可以做任何事情。 (c) R.迪塞爾。
題詞。
PostgreSQL 查詢的性能監控。 第 1 部分 - 報告
或者一個關於為什麼數據庫管理員需要記住他過去的編程經歷的故事。

前言

所有名稱均已更改。 比賽是隨機的。 該材料僅代表作者的個人意見。

免責聲明： 在計劃的系列文章中，將不會對所使用的表格和腳本進行詳細和準確的描述。 材料不能立即“按原樣”使用。
首先，由於材料量大，
其次，因為對真實客戶生產基地的敏銳度。
因此，文章中只會給出最一般形式的想法和描述。
也許將來這個系統會發展到在 GitHub 上發布的水平，也可能不會。 時間會證明。

故事的開始——你還記得這一切是如何開始的嗎“。
結果發生了什麼，用最籠統的話來說——”綜合作為提高 PostgreSQL 性能的方法之一»

為什麼我需要這一切？

嗯，首先，不要忘記自己，記住退休後的輝煌歲月。
其次，將所寫內容系統化。 對於我自己來說，有時我開始感到困惑並忘記單獨的部分。

好吧，最重要的是 - 突然間，它可以對某人派上用場，幫助您避免重新發明輪子，也不會收集耙子。 換句話說，提高你的業力（而不是哈布羅夫斯基）。 因為這個世界上最有價值的東西就是想法。 最重要的是找到一個想法。 而將想法變為現實已經是一個純粹的技術問題了。

那麼讓我們慢慢開始吧...

問題的表述。

可用的：

PostgreSQL(10.5)，混合負載（OLTP+DSS），中到輕負載，託管在AWS雲中。
沒有數據庫監控，基礎設施監控以最低配置作為標準 AWS 工具呈現。

必需的：

監視數據庫的性能和狀態，查找並獲取初始信息以優化繁重的數據庫查詢。

解決方案簡介或分析

首先，我們嘗試從對工程師的好處和麻煩進行比較分析的角度來分析解決問題的選項，並讓那些應該在人員名單上的人來處理好處和損失的管理。

選項 1 - “按需工作”

我們讓一切保持原樣。 如果客戶對數據庫或應用程序的運行狀況、性能不滿意，他將通過電子郵件或在票箱中創建事件來通知 DBA 工程師。
工程師收到通知後，會了解問題，提出解決方案，或者擱置問題，希望一切都能自行解決，反正一切很快就會被遺忘。
薑餅和甜甜圈、瘀傷和腫塊薑餅和甜甜圈：
1. 沒有什麼額外的事情可做
2. 總有機會出去弄髒。
3. 有很多時間可以自己度過。
瘀傷和腫塊：
1. 遲早，顧客會思考這個世界存在的本質和普遍正義，並再次問自己這個問題 - 為什麼我要付錢給他們？ 結果總是一樣的——唯一的問題是顧客什麼時候感到無聊並揮手告別。 而且餵食器是空的。 這是可悲的。
2.工程師的發展為零。
3、排工、裝車困難

選項2——“敲著手鼓跳舞，穿上鞋子”

第 1 款-為什麼我們需要一個監控系統，我們會收到所有請求。 我們對數據字典和動態視圖發起一系列各種查詢，打開各種計數器，將所有內容放入表中，定期分析列表和表格。 結果，我們得到了漂亮或不太漂亮的圖表、表格、報告。 最重要的是——那會更多，更多。
第 2 款-生成活動-運行所有這一切的分析。
第 3 款-我們正在準備一份文件，我們簡單地稱之為“我們如何裝備數據庫”。
第 4 款- 客戶看到所有這些宏偉的圖表和數字，充滿了幼稚的天真信心 - 現在一切都會為我們服務，很快。 而且，可以輕鬆無痛地放棄他們的財務資源。 管理層也確信我們的工程師正在努力工作。 最大負載。
第 5 款- 定期重複步驟 1。
薑餅和甜甜圈、瘀傷和腫塊薑餅和甜甜圈：
1. 經理和工程師的生活簡單、可預測且充滿活動。 一切都很熱鬧，每個人都很忙。
2. 客戶的生活也不錯——他總是確信你需要耐心一點，一切都會好起來的。 情況並沒有好轉，好吧，好吧——這個世界不公平，下輩子——你會幸運的。
瘀傷和腫塊：
1. 遲早會有一個更聰明的類似服務提供商會做同樣的事情，但價格會便宜一些。 如果結果是一樣的，為什麼要付出更多呢？ 這又會導致餵食器的消失。
2. 很無聊。 任何有意義的小活動都是多麼無聊啊。
3. 與之前的版本一樣 - 沒有開發。 但對於工程師來說，缺點是，與第一個選項不同，這裡您需要不斷生成 IDB。 這需要時間。 這筆錢可以用來造福您所愛的人。 因為你都照顧不了自己，所以大家都關心你。

方案3——不需要發明自行車，你需要購買並騎它。

其他公司的工程師故意吃披薩配啤酒（哦，90年代聖彼得堡的輝煌時代）。 讓我們使用已經製作、調試和運行的監控系統，一般來說，它們帶來了好處（嗯，至少對它們的創建者來說）。
薑餅和甜甜圈、瘀傷和腫塊薑餅和甜甜圈：
1. 無需浪費時間去發明已經發明的東西。 採取並使用。
2、監控系統不是傻子寫的，當然有用。
3. 工作監控系統通常會提供有用的過濾信息。
瘀傷和腫塊：
1.這種情況下的工程師並不是工程師，而只是別人產品的用戶，或者說是用戶。
2. 必須讓客戶確信需要購買他一般不想了解的東西，也不應該購買，而且一般來說當年的預算已經批准，不會改變。 然後你需要分配一個單獨的資源，為特定的系統配置它。 那些。 首先你需要付款、付款、再付款。 而且顧客很吝嗇。 這是此生的常態。

車爾尼雪夫斯基該怎麼辦？ 你的問題非常中肯。 （和）

在這種特殊情況和當前情況下，你可以做一些不同的事情 - 讓我們製作自己的監控系統。
PostgreSQL 查詢的性能監控。 第 1 部分 - 報告
好吧，不是一個系統，當然，從這個詞的完整意義上來說，這太響亮和自以為了然了，但至少在某種程度上讓你自己更容易並收集更多信息來解決性能事件。 為了不讓自己陷入這樣的境地——“去那裡，我不知道在哪裡，找到那個，我不知道是什麼。”

此選項的優點和缺點是什麼：

優點：
1. 很有趣。 好吧，至少比不斷的“收縮數據文件、更改表空間等”更有趣。
2. 這些是新技能和新發展。 將來，遲早，我們會得到當之無愧的薑餅和甜甜圈。
缺點：
1.必須工作。 工作了很多。
2. 您必須定期解釋所有活動的意義和觀點。
3. 必須犧牲一些東西，因為工程師唯一可用的資源——時間——受到宇宙的限制。
4. 最糟糕和最不愉快的事情 ——結果可能會出現“不是老鼠，不是青蛙，而是不知名的小動物”之類的垃圾。

不冒險的人不會喝香檳。
那麼，有趣的事情開始了。

總體思路 - 示意圖

PostgreSQL 查詢的性能監控。 第 1 部分 - 報告
(插圖取自文章 «綜合作為提高 PostgreSQL 性能的方法之一“）

說明：

  • 目標數據庫使用標準 PostgreSQL 擴展“pg_stat_statements”安裝。
  • 在監控數據庫中，我們創建一組服務表，用於存儲初始階段的pg_stat_statements歷史記錄，並用於配置未來的指標和監控
  • 在監控主機上，我們創建一組bash腳本，包括用於在工單系統中生成事件的腳本。

服務台

首先，一個示意性簡化的 ERD，最後發生了什麼：
PostgreSQL 查詢的性能監控。 第 1 部分 - 報告
表格的簡要說明端點 - 主機，實例的連接點
數據庫 - 數據庫選項
pg_stat_歷史記錄 - 歷史表，用於存儲目標數據庫的pg_stat_statements視圖的臨時快照
度量詞彙表 - 績效指標詞典
度量配置 - 單獨指標的配置
公制 - 正在監控的請求的特定指標
指標警報歷史記錄 - 性能警告的歷史記錄
日誌查詢 - 服務表，用於存儲從 AWS 下載的 PostgreSQL 日誌文件中解析的記錄
底線 - 用作基礎的時間段的參數
檢查站 - 配置用於檢查數據庫狀態的指標
檢查點警報歷史記錄 - 數據庫狀態檢查指標的警告歷史記錄
pg_stat_db_queries — 活動請求的服務表
活動日誌 — 活動日誌服務表
trap_oid - 陷阱配置服務表

第 1 階段 - 收集性能統計數據並獲取報告

表用於存儲統計信息。 pg_stat_歷史記錄
pg_stat_history表結構

                                          表“public.pg_stat_history”列| 類型 | 修飾符--------------------+--------------------- --+---- -------------------------------- ID | 整數 | 默認 nextval('pg_stat_history_id_seq'::regclass) snapshot_timestamp | not null 沒有時區的時間戳 | 數據庫 ID | 整數 | dbid | 類 | 用戶 ID | 類 | 查詢ID | 大整數 | 查詢 | 文字| 來電 | 大整數 | 總時間 | 雙精度 | 最短時間 | 雙精度 | 最大時間 | 雙精度 | 平均時間 | 雙精度 | stddev_時間 | 雙精度 | 行 | 大整數 | 共享_blks_hit | 大整數 | 共享_blks_read | 大整數 | 共享_blks_dirtied | 大整數 | 共享塊寫入| 大整數 | 本地_blks_hit | 大整數 | 本地_blks_read | 大整數 | 本地_blks_dirtied | 大整數 | 本地_blks_書面 | 大整數 | temp_blks_read | 大整數 | 臨時BLKS_書面| 大整數 | blk_read_time | 塊讀取時間雙精度 | blk_write_time | 塊寫入時間雙精度 | 基線_id | 整數 | 索引：“pg_stat_history_pkey”主鍵，btree（id）“database_idx”btree（database_id）“queryid_idx”btree（queryid）“snapshot_timestamp_idx”btree（snapshot_timestamp）外鍵約束：“database_id_fk”外鍵（database_id）引用數據庫（id） ) 刪除級聯

可以看到，該表只是一個累積視圖數據 pg_stat_語句 在目標數據庫中。

這個表的使用非常簡單。

pg_stat_歷史記錄 將表示每小時查詢執行的累積統計信息。 每小時開始，填表後，統計 pg_stat_語句 重置為 pg_stat_statements_reset().
注： 對持續時間超過 1 秒的請求進行統計。
填充 pg_stat_history 表

--pg_stat_history.sql
CREATE OR REPLACE FUNCTION pg_stat_history( ) RETURNS boolean AS $$
DECLARE
  endpoint_rec record ;
  database_rec record ;
  pg_stat_snapshot record ;
  current_snapshot_timestamp timestamp without time zone;
BEGIN
  current_snapshot_timestamp = date_trunc('minute',now());  
  
  FOR endpoint_rec IN SELECT * FROM endpoint 
  LOOP
    FOR database_rec IN SELECT * FROM database WHERE endpoint_id = endpoint_rec.id 
	  LOOP
	    
		RAISE NOTICE 'NEW SHAPSHOT IS CREATING';
		
		--Connect to the target DB	  
	    EXECUTE 'SELECT dblink_connect(''LINK1'',''host='||endpoint_rec.host||' dbname='||database_rec.name||' user=USER password=PASSWORD '')';
 
        RAISE NOTICE 'host % and dbname % ',endpoint_rec.host,database_rec.name;
		RAISE NOTICE 'Creating snapshot of pg_stat_statements for database %',database_rec.name;
		
		SELECT 
	      *
		INTO 
		  pg_stat_snapshot
	    FROM dblink('LINK1',
	      'SELECT 
	       dbid , SUM(calls),SUM(total_time),SUM(rows) ,SUM(shared_blks_hit) ,SUM(shared_blks_read) ,SUM(shared_blks_dirtied) ,SUM(shared_blks_written) , 
           SUM(local_blks_hit) , SUM(local_blks_read) , SUM(local_blks_dirtied) , SUM(local_blks_written) , SUM(temp_blks_read) , SUM(temp_blks_written) , SUM(blk_read_time) , SUM(blk_write_time)
	       FROM pg_stat_statements WHERE dbid=(SELECT oid from pg_database where datname=current_database() ) 
		   GROUP BY dbid
  	      '
	               )
	      AS t
	       ( dbid oid , calls bigint , 
  	         total_time double precision , 
	         rows bigint , shared_blks_hit bigint , shared_blks_read bigint ,shared_blks_dirtied bigint ,shared_blks_written	 bigint ,
             local_blks_hit	 bigint ,local_blks_read bigint , local_blks_dirtied bigint ,local_blks_written bigint ,
             temp_blks_read	 bigint ,temp_blks_written bigint ,
             blk_read_time double precision , blk_write_time double precision	  
	       );
		 
		INSERT INTO pg_stat_history
          ( 
		    snapshot_timestamp  ,database_id  ,
			dbid , calls  ,total_time ,
            rows ,shared_blks_hit  ,shared_blks_read  ,shared_blks_dirtied  ,shared_blks_written ,local_blks_hit , 	 	
            local_blks_read,local_blks_dirtied,local_blks_written,temp_blks_read,temp_blks_written, 	
            blk_read_time, blk_write_time 
		  )		  
	    VALUES
	      (
	       current_snapshot_timestamp ,
		   database_rec.id ,
	       pg_stat_snapshot.dbid ,pg_stat_snapshot.calls,
	       pg_stat_snapshot.total_time,
	       pg_stat_snapshot.rows ,pg_stat_snapshot.shared_blks_hit ,pg_stat_snapshot.shared_blks_read ,pg_stat_snapshot.shared_blks_dirtied ,pg_stat_snapshot.shared_blks_written , 
           pg_stat_snapshot.local_blks_hit , pg_stat_snapshot.local_blks_read , pg_stat_snapshot.local_blks_dirtied , pg_stat_snapshot.local_blks_written , 
	       pg_stat_snapshot.temp_blks_read , pg_stat_snapshot.temp_blks_written , pg_stat_snapshot.blk_read_time , pg_stat_snapshot.blk_write_time 	   
	      );		   
		  
        RAISE NOTICE 'Creating snapshot of pg_stat_statements for queries with min_time more than 1000ms';
	
        FOR pg_stat_snapshot IN
          --All queries with max_time greater than 1000 ms
	      SELECT 
	        *
	      FROM dblink('LINK1',
	        'SELECT 
	         dbid , userid ,queryid,query,calls,total_time,min_time ,max_time,mean_time, stddev_time ,rows ,shared_blks_hit ,
			 shared_blks_read ,shared_blks_dirtied ,shared_blks_written , 
             local_blks_hit , local_blks_read , local_blks_dirtied , 
			 local_blks_written , temp_blks_read , temp_blks_written , blk_read_time , 
			 blk_write_time
	         FROM pg_stat_statements 
			 WHERE dbid=(SELECT oid from pg_database where datname=current_database() AND min_time >= 1000 ) 
  	        '

	                  )
	        AS t
	         ( dbid oid , userid oid , queryid bigint ,query text , calls bigint , 
  	           total_time double precision ,min_time double precision	 ,max_time double precision	 , mean_time double precision	 ,  stddev_time double precision	 , 
	           rows bigint , shared_blks_hit bigint , shared_blks_read bigint ,shared_blks_dirtied bigint ,shared_blks_written	 bigint ,
               local_blks_hit	 bigint ,local_blks_read bigint , local_blks_dirtied bigint ,local_blks_written bigint ,
               temp_blks_read	 bigint ,temp_blks_written bigint ,
               blk_read_time double precision , blk_write_time double precision	  
	         )
	    LOOP
		  INSERT INTO pg_stat_history
          ( 
		    snapshot_timestamp  ,database_id  ,
			dbid ,userid  , queryid  , query  , calls  ,total_time ,min_time ,max_time ,mean_time ,stddev_time ,
            rows ,shared_blks_hit  ,shared_blks_read  ,shared_blks_dirtied  ,shared_blks_written ,local_blks_hit , 	 	
            local_blks_read,local_blks_dirtied,local_blks_written,temp_blks_read,temp_blks_written, 	
            blk_read_time, blk_write_time 
		  )		  
	      VALUES
	      (
	       current_snapshot_timestamp ,
		   database_rec.id ,
	       pg_stat_snapshot.dbid ,pg_stat_snapshot.userid ,pg_stat_snapshot.queryid,pg_stat_snapshot.query,pg_stat_snapshot.calls,
	       pg_stat_snapshot.total_time,pg_stat_snapshot.min_time ,pg_stat_snapshot.max_time,pg_stat_snapshot.mean_time, pg_stat_snapshot.stddev_time ,
	       pg_stat_snapshot.rows ,pg_stat_snapshot.shared_blks_hit ,pg_stat_snapshot.shared_blks_read ,pg_stat_snapshot.shared_blks_dirtied ,pg_stat_snapshot.shared_blks_written , 
           pg_stat_snapshot.local_blks_hit , pg_stat_snapshot.local_blks_read , pg_stat_snapshot.local_blks_dirtied , pg_stat_snapshot.local_blks_written , 
	       pg_stat_snapshot.temp_blks_read , pg_stat_snapshot.temp_blks_written , pg_stat_snapshot.blk_read_time , pg_stat_snapshot.blk_write_time 	   
	      );
		  
        END LOOP;

        PERFORM dblink_disconnect('LINK1');  
				
	  END LOOP ;--FOR database_rec IN SELECT * FROM database WHERE endpoint_id = endpoint_rec.id 
    
  END LOOP;

RETURN TRUE;  
END
$$ LANGUAGE plpgsql;

結果，經過一定時間後表中 pg_stat_歷史記錄 我們將有一組表內容的快照 pg_stat_語句 目標數據庫。

實際報導

使用簡單的查詢，您可以獲得非常有用且有趣的報告。

給定時間段內的匯總數據

詢問

SELECT 
  database_id , 
  SUM(calls) AS calls ,SUM(total_time)  AS total_time ,
  SUM(rows) AS rows , SUM(shared_blks_hit)  AS shared_blks_hit,
  SUM(shared_blks_read) AS shared_blks_read ,
  SUM(shared_blks_dirtied) AS shared_blks_dirtied,
  SUM(shared_blks_written) AS shared_blks_written , 
  SUM(local_blks_hit) AS local_blks_hit , 
  SUM(local_blks_read) AS local_blks_read , 
  SUM(local_blks_dirtied) AS local_blks_dirtied , 
  SUM(local_blks_written)  AS local_blks_written,
  SUM(temp_blks_read) AS temp_blks_read, 
  SUM(temp_blks_written) temp_blks_written , 
  SUM(blk_read_time) AS blk_read_time , 
  SUM(blk_write_time) AS blk_write_time
FROM 
  pg_stat_history
WHERE 
  queryid IS NULL AND
  database_id = DATABASE_ID  AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT
GROUP BY database_id ;

數據庫時間

to_char(間隔 '1 毫秒' * pg_total_stat_history_rec.total_time, 'HH24:MI:SS.MS')

輸入/輸出時間

to_char(間隔 '1 毫秒' * ( pg_total_stat_history_rec.blk_read_time + pg_total_stat_history_rec.blk_write_time ), 'HH24:MI:SS.MS')

按總時間排序的 TOP10 SQL

詢問

SELECT 
  queryid , 
  SUM(calls) AS calls ,
  SUM(total_time)  AS total_time  	
FROM 
  pg_stat_history
WHERE 
  queryid IS NOT NULL AND 
  database_id = DATABASE_ID AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT 
GROUP BY queryid 
ORDER BY 3 DESC 
LIMIT 10
-------------------------------------------------- ------------------------------------------------ | 總執行時間排名前 10 的 SQL | #| 查詢ID| 來電| 來電 %| 總時間（毫秒）| dbtime % +----+------------+------------+------------+-------- ------------------+---------- | 1| 821760255| 2| .00001|00:03:23.141(203141.681 毫秒)| 5.42 | 2 4152624390| 2| 00001| .00|03:13.929:193929.215(5.17 毫秒)| 3 | 1484454471 4| 00001| 00| .02|09.129:129129.057:3.44(4 毫秒)| 655729273 | 1 00000| 00| 02| .01.869|121869.981:3.25:5(2460318461 毫秒)| 1 | 00000 00| 01| 33.113| .93113.835|2.48:6:2194493487(4 毫秒)| 00001 | 00 00| 17.377| 17377.868| .46|7:1053044345:1(00000 毫秒)| .00 | 00| 06.156| 6156.352| .16|8:3644780286:1( 00000 毫秒)| .00 | 00| 01.063| 1063.830| .03|XNUMX:XNUMX:XNUMX(XNUMX 毫秒)| .XNUMX

按總 I/O 時間排列的 TOP10 SQL

詢問

SELECT 
  queryid , 
  SUM(calls) AS calls ,
  SUM(blk_read_time + blk_write_time)  AS io_time
FROM 
  pg_stat_history
WHERE 
  queryid IS NOT NULL AND 
  database_id = DATABASE_ID  AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT
GROUP BY  queryid 
ORDER BY 3 DESC 
LIMIT 10
-------------------------------------------------- -------------------------------------- | 總 I/O 時間排名前 10 的 SQL | #| 查詢ID| 來電| 來電 %| I/O 時間 (ms)|db I/O 時間 % +----+-----------+-----------+-------- ---+--------------------------------+------------ --| 1| 4152624390| 2| .00001|00:08:31.616(511616.592 毫秒)| 31.06 月 2 日 | 821760255| 2| 00001| .00|08:27.099:507099.036(30.78 毫秒)| 3 | 655729273| 1| 00000| .00|05:02.209:302209.137( 18.35 毫秒)| 4 | 2460318461 1| 00000| 00| .04|05.981:245981.117:14.93(5 毫秒)| 1484454471 | 4 00001| 00| 00| .39.144|39144.221:2.38:6( 2194493487 毫秒)| 4 | 00001 00| 00| 18.182| .18182.816|1.10:7:1053044345( 1 毫秒)| 00000 | 00 00| 16.611| 16611.722| .1.01|8:3644780286:1( 00000 毫秒)| 00 | 00 00.436| 436.205| 03| .XNUMX|XNUMX:XNUMX:XNUMX( XNUMX 毫秒)| .XNUMX

按最長執行時間排序的 TOP10 SQL

詢問

SELECT 
  id AS snapshotid , 
  queryid , 
  snapshot_timestamp ,  
  max_time 
FROM 
  pg_stat_history 
WHERE 
  queryid IS NOT NULL AND 
  database_id = DATABASE_ID  AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT
ORDER BY 4 DESC 
LIMIT 10

-------------------------------------------------- ------------------------------------------------ | 最大執行時間排名前 10 的 SQL | #| 快照| 快照ID| 查詢ID| 最大時間（毫秒）+----+------------------+------------+--------- --+---------------------------------------- | 1| 05.04.2019/01/03 4169:655729273| 00| 02| 01.869:121869.981:2（04.04.2019 毫秒）| 17| 00/4153/821760255 00:01| 41.570| 101570.841| 3:04.04.2019:16（00 毫秒）| 4146| 821760255/00/01 41.570:101570.841| 4| 04.04.2019| 16:00:4144（4152624390 毫秒）| 00| 01/36.964/96964.607 5:04.04.2019| 17| 00| 4151:4152624390:00（01 毫秒）| 36.964| 96964.607/6/05.04.2019 10:00| 4188| 1484454471| 00:01:33.452（93452.150 毫秒）| 7| 04.04.2019/17/00 4150:2460318461 | 00| 01| 33.113:93113.835:8（04.04.2019 毫秒）| 15| 00/4140/1484454471 00:00| 11.892| 11892.302| 9:04.04.2019:16（00 毫秒）| 4145| 1484454471/00/00 11.892:11892.302| 10| 04.04.2019| 17:00:4152（1484454471 毫秒）| 00| 00/11.892/11892.302 XNUMX:XNUMX| XNUMX| XNUMX| XNUMX:XNUMX:XNUMX（XNUMX 毫秒）| XNUMX| XNUMX/XNUMX/XNUMX XNUMX:XNUMX| XNUMX| XNUMX| XNUMX:XNUMX:XNUMX（XNUMX 毫秒）

共享緩衝區讀/寫的 TOP10 SQL

詢問

SELECT 
  id AS snapshotid , 
  queryid ,
  snapshot_timestamp , 
  shared_blks_read , 
  shared_blks_written 
FROM 
  pg_stat_history
WHERE 
  queryid IS NOT NULL AND 
  database_id = DATABASE_ID  AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT AND
  ( shared_blks_read > 0 OR shared_blks_written > 0 )
ORDER BY 4 DESC  , 5 DESC 
LIMIT 10
-------------------------------------------------- ------------------------------------------------ | 共享緩衝區讀/寫排名前十的 SQL | #| 快照| 快照ID| 查詢ID| 共享塊讀取| 共享塊寫入 +----+--------------------+------------+------------ -+---------------------------------+--------------------- | 10| 1/04.04.2019/17 00:4153| 821760255| 797308| 0| 2 | 04.04.2019| 16/00/4146 821760255:797308| 0| 3| 05.04.2019| 01 | 03| 4169/655729273/797158 0:4| 04.04.2019| 16| 00| 4144 | 4152624390| 756514/0/5 04.04.2019:17| 00| 4151| 4152624390| 756514 | 0| 6/04.04.2019/17 00:4150| 2460318461| 734117| 0| 7 | 04.04.2019| 17/00/4155 3644780286:52973| 0| 8| 05.04.2019| 01 | 03| 4168/1053044345/52818 0:9| 04.04.2019| 15| 00| 4141 | 2194493487| 52813/0/10 04.04.2019:16| 00| 4147| 2194493487| 52813 | 0| XNUMX/XNUMX/XNUMX XNUMX:XNUMX| XNUMX| XNUMX| XNUMX| XNUMX | XNUMX| XNUMX/XNUMX/XNUMX XNUMX:XNUMX| XNUMX| XNUMX| XNUMX| XNUMX -------------------------------------------------- -------------------------------------------------

按最大執行時間排列的查詢分佈直方圖

請求

SELECT  
  MIN(max_time) AS hist_min  , 
  MAX(max_time) AS hist_max , 
  (( MAX(max_time) - MIN(min_time) ) / hist_columns ) as hist_width
FROM 
  pg_stat_history 
WHERE 
  queryid IS NOT NULL AND
  database_id = DATABASE_ID  AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT ;

SELECT 
  SUM(calls) AS calls
FROM 
  pg_stat_history 
WHERE 
  queryid IS NOT NULL AND
  database_id =DATABASE_ID  AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT AND 
  ( max_time >= hist_current_min AND  max_time < hist_current_max ) ;

|------------------------------------------------ ---------------------------------------------------- | 最大時間直方圖 | 總通話次數：33851920 | 最短時間：00:00:01.063 | 最長時間：00:02:01.869 ---------------------------------- -------- ---------------------------- | 最短持續時間| 最長持續時間| 致電+--------------------------------+------------- ------------------+---------- | 00:00:01.063（1063.830 毫秒）| 00:00:13.144（13144.445 毫秒）| 9 | 00:00:13.144（13144.445 毫秒）| 00:00:25.225（25225.060 毫秒）| 0 | 00:00:25.225（25225.060 毫秒）| 00:00:37.305（37305.675 毫秒）| 0 | 00:00:37.305（37305.675 毫秒）| 00:00:49.386（49386.290 毫秒）| 0 | 00:00:49.386（49386.290 毫秒）| 00:01:01.466（61466.906 毫秒）| 0 | 00:01:01.466（61466.906 毫秒）| 00:01:13.547（73547.521 毫秒）| 0 | 00:01:13.547（73547.521 毫秒）| 00:01:25.628（85628.136 毫秒）| 0 | 00:01:25.628（85628.136 毫秒）| 00:01:37.708（97708.751 毫秒）| 4 | 00:01:37.708（97708.751 毫秒）| 00:01:49.789（109789.366 毫秒）| 2 | 00:01:49.789（109789.366 毫秒）| 00:02:01.869（121869.981 毫秒）| 0

按每秒查詢次數排序的 TOP10 快照

請求

--pg_qps.sql
--Calculate Query Per Second 
CREATE OR REPLACE FUNCTION pg_qps( pg_stat_history_id integer ) RETURNS double precision AS $$
DECLARE
 pg_stat_history_rec record ;
 prev_pg_stat_history_id integer ;
 prev_pg_stat_history_rec record;
 total_seconds double precision ;
 result double precision;
BEGIN 
  result = 0 ;
  
  SELECT *
  INTO pg_stat_history_rec
  FROM 
    pg_stat_history
  WHERE id = pg_stat_history_id ;

  IF pg_stat_history_rec.snapshot_timestamp IS NULL 
  THEN
    RAISE EXCEPTION 'ERROR - Not found pg_stat_history for id = %',pg_stat_history_id;
  END IF ;  
  
 --RAISE NOTICE 'pg_stat_history_id = % , snapshot_timestamp = %', pg_stat_history_id , 
 pg_stat_history_rec.snapshot_timestamp ;
  
  SELECT 
    MAX(id)   
  INTO
    prev_pg_stat_history_id
  FROM
    pg_stat_history
  WHERE 
    database_id = pg_stat_history_rec.database_id AND
	queryid IS NULL AND
	id < pg_stat_history_rec.id ;

  IF prev_pg_stat_history_id IS NULL 
  THEN
    RAISE NOTICE 'Not found previous pg_stat_history shapshot for id = %',pg_stat_history_id;
	RETURN NULL ;
  END IF;
  
  SELECT *
  INTO prev_pg_stat_history_rec
  FROM 
    pg_stat_history
  WHERE id = prev_pg_stat_history_id ;
  
  --RAISE NOTICE 'prev_pg_stat_history_id = % , prev_snapshot_timestamp = %', prev_pg_stat_history_id , prev_pg_stat_history_rec.snapshot_timestamp ;    

  total_seconds = extract(epoch from ( pg_stat_history_rec.snapshot_timestamp - prev_pg_stat_history_rec.snapshot_timestamp ));
  
  --RAISE NOTICE 'total_seconds = % ', total_seconds ;    
  
  --RAISE NOTICE 'calls = % ', pg_stat_history_rec.calls ;      
  
  IF total_seconds > 0 
  THEN
    result = pg_stat_history_rec.calls / total_seconds ;
  ELSE
   result = 0 ; 
  END IF;
   
 RETURN result ;
END
$$ LANGUAGE plpgsql;


SELECT 
  id , 
  snapshot_timestamp ,
  calls , 	
  total_time , 
  ( select pg_qps( id )) AS QPS ,
  blk_read_time ,
  blk_write_time
FROM 
  pg_stat_history
WHERE 
  queryid IS NULL AND 
  database_id = DATABASE_ID  AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT AND
  ( select pg_qps( id )) IS NOT NULL 
ORDER BY 5 DESC 
LIMIT 10

|------------------------------------------------ ---------------------------------------------------- | 按 QueryPerSeconds 數字排序的 TOP10 快照 -------------------------------------- ------ -------------------------------------------------------- ------ ------------------------------------------- | #| 快照| 快照ID| 來電| 總數據庫時間| 每秒查詢量 | I/O 時間 | I/O 時間% +-----+--------------------+------------+-------- ---+--------------------------------+---------- ----------------------------------+------------------------ | 1| 04.04.2019/20/04 4161:5758631| 00| 06| 30.513:390513.926:1573.396（00 毫秒）| 00| 01.470:1470.110:376（2 毫秒）| .04.04.2019| 17| 00/4149/3529197 00:11| 48.830| 708830.618| 980.332:00:12（47.834 毫秒）| 767834.052| 108.324:3:04.04.2019（16 毫秒）| 00 | 4143 3525360| 00/10/13.492 613492.351:979.267| 00| 08| 41.396:521396.555:84.988（4 毫秒）| 04.04.2019| 21:03:4163（2781536 毫秒）| 00 | 03| 06.470/186470.979/785.745 00:00| 00.249| 249.865| 134:5:04.04.2019（19 毫秒）| 03| 4159:2890362:00（03 毫秒）| .16.784| 196784.755| 776.979/00/00 01.441:1441.386| 732| 6| 04.04.2019:14:00（4137 毫秒）| 2397326| 00:04:43.033（283033.854 毫秒）| .665.924| 00| 00/00.024/24.505 009:7| 04.04.2019| 15| 00:4139:2394416（00 毫秒）| 04| 51.435:291435.010:665.116（00 毫秒）| .00| 12.025| 12025.895/4.126/8 04.04.2019:13| 00| 4135| 2373043:00:04（26.791 毫秒）| 266791.988| 659.179:00:00（00.064 毫秒）| 64.261 | 024 9| 05.04.2019/01/03 4167:4387191| 00| 06| 51.380:411380.293:609.332（00 毫秒）| 05| 18.847:318847.407:77.507（10 毫秒）| .04.04.2019| 18| 01/4157/1145596 00:01| 19.217| 79217.372| 313.004:00:00（01.319 毫秒）| 1319.676| 1.666:XNUMX:XNUMX（XNUMX 毫秒）| XNUMX | XNUMX XNUMX| XNUMX/XNUMX/XNUMX XNUMX:XNUMX| XNUMX| XNUMX| XNUMX:XNUMX:XNUMX（XNUMX 毫秒）| XNUMX| XNUMX:XNUMX:XNUMX（XNUMX 毫秒）| XNUMX

具有 QueryPerSeconds 和 I/O 時間的每小時執行歷史記錄

詢問

SELECT 
  id , 
  snapshot_timestamp ,
  calls , 	
  total_time , 
  ( select pg_qps( id )) AS QPS ,
  blk_read_time ,
  blk_write_time
FROM 
  pg_stat_history
WHERE 
  queryid IS NULL AND 
  database_id = DATABASE_ID  AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT
ORDER BY 2

|-----------------------------------------------------------------------------------------------
| HOURLY EXECUTION HISTORY  WITH QueryPerSeconds and I/O Time
-----------------------------------------------------------------------------------------------------------------------------------------------
| QUERY PER SECOND HISTORY
|    #|          snapshot| snapshotID|      calls|                      total dbtime|        QPS|                          I/O time| I/O time %
+-----+------------------+-----------+-----------+----------------------------------+-----------+----------------------------------+-----------
|    1|  04.04.2019 11:00|       4131|       3747|  00:00:00.835(       835.374 ms.)|      1.041|  00:00:00.000(          .000 ms.)|       .000
|    2|  04.04.2019 12:00|       4133|    1002722|  00:01:52.419(    112419.376 ms.)|    278.534|  00:00:00.149(       149.105 ms.)|       .133
|    3|  04.04.2019 13:00|       4135|    2373043|  00:04:26.791(    266791.988 ms.)|    659.179|  00:00:00.064(        64.261 ms.)|       .024
|    4|  04.04.2019 14:00|       4137|    2397326|  00:04:43.033(    283033.854 ms.)|    665.924|  00:00:00.024(        24.505 ms.)|       .009
|    5|  04.04.2019 15:00|       4139|    2394416|  00:04:51.435(    291435.010 ms.)|    665.116|  00:00:12.025(     12025.895 ms.)|      4.126
|    6|  04.04.2019 16:00|       4143|    3525360|  00:10:13.492(    613492.351 ms.)|    979.267|  00:08:41.396(    521396.555 ms.)|     84.988
|    7|  04.04.2019 17:00|       4149|    3529197|  00:11:48.830(    708830.618 ms.)|    980.332|  00:12:47.834(    767834.052 ms.)|    108.324
|    8|  04.04.2019 18:01|       4157|    1145596|  00:01:19.217(     79217.372 ms.)|    313.004|  00:00:01.319(      1319.676 ms.)|      1.666
|    9|  04.04.2019 19:03|       4159|    2890362|  00:03:16.784(    196784.755 ms.)|    776.979|  00:00:01.441(      1441.386 ms.)|       .732
|   10|  04.04.2019 20:04|       4161|    5758631|  00:06:30.513(    390513.926 ms.)|   1573.396|  00:00:01.470(      1470.110 ms.)|       .376
|   11|  04.04.2019 21:03|       4163|    2781536|  00:03:06.470(    186470.979 ms.)|    785.745|  00:00:00.249(       249.865 ms.)|       .134
|   12|  04.04.2019 23:03|       4165|    1443155|  00:01:34.467(     94467.539 ms.)|    200.438|  00:00:00.015(        15.287 ms.)|       .016
|   13|  05.04.2019 01:03|       4167|    4387191|  00:06:51.380(    411380.293 ms.)|    609.332|  00:05:18.847(    318847.407 ms.)|     77.507
|   14|  05.04.2019 02:03|       4171|     189852|  00:00:10.989(     10989.899 ms.)|     52.737|  00:00:00.539(       539.110 ms.)|      4.906
|   15|  05.04.2019 03:01|       4173|       3627|  00:00:00.103(       103.000 ms.)|      1.042|  00:00:00.004(         4.131 ms.)|      4.010
|   16|  05.04.2019 04:00|       4175|       3627|  00:00:00.085(        85.235 ms.)|      1.025|  00:00:00.003(         3.811 ms.)|      4.471
|   17|  05.04.2019 05:00|       4177|       3747|  00:00:00.849(       849.454 ms.)|      1.041|  00:00:00.006(         6.124 ms.)|       .721
|   18|  05.04.2019 06:00|       4179|       3747|  00:00:00.849(       849.561 ms.)|      1.041|  00:00:00.000(          .051 ms.)|       .006
|   19|  05.04.2019 07:00|       4181|       3747|  00:00:00.839(       839.416 ms.)|      1.041|  00:00:00.000(          .062 ms.)|       .007
|   20|  05.04.2019 08:00|       4183|       3747|  00:00:00.846(       846.382 ms.)|      1.041|  00:00:00.000(          .007 ms.)|       .001
|   21|  05.04.2019 09:00|       4185|       3747|  00:00:00.855(       855.426 ms.)|      1.041|  00:00:00.000(          .065 ms.)|       .008
|   22|  05.04.2019 10:00|       4187|       3797|  00:01:40.150(    100150.165 ms.)|      1.055|  00:00:21.845(     21845.217 ms.)|     21.812

所有 SQL 選擇的文本

詢問

SELECT 
  queryid , 
  query 
FROM 
  pg_stat_history
WHERE 
  queryid IS NOT NULL AND 
  database_id = DATABASE_ID  AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT
GROUP BY queryid , query

總

正如您所看到的，通過相當簡單的方法，您可以獲得有關工作負載和數據庫狀態的大量有用信息。

筆記：如果您修復查詢中的queryid，那麼我們將獲得單獨請求的歷史記錄（為了節省空間，省略了單獨請求的報告）。

因此，可以獲取並收集有關查詢性能的統計數據。
第一階段“統計數據收集”已經完成。

您可以繼續第二階段——“配置性能指標”。
PostgreSQL 查詢的性能監控。 第 1 部分 - 報告

但這是另一個故事。

待續...

來源： www.habr.com

添加評論