PostgreSQL クエリのパフォーマンス監視。 パート 1 - レポート

エンジニア - ラテン語から翻訳 - インスピレーションを得た。
エンジニアは何でもできます。 (c) R. ディーゼル。
エピグラフ。

または、データベース管理者が過去のプログラミングを思い出す必要がある理由についての話。

序文

すべての名前が変更されました。 一致はランダムです。 内容はあくまで著者の個人的な意見です。

保証の否認: 予定されている一連の記事では、使用されるテーブルとスクリプトの詳細かつ正確な説明はありません。 素材を「現状のまま」すぐに使用することはできません。
まず、材料の量が多いので、
第二に、実際の顧客の生産拠点との緊密さのためです。
したがって、記事には最も一般的な形式のアイデアと説明のみが含まれます。
おそらく将来的には、システムが GitHub に投稿できるレベルまで成長するかもしれませんし、そうでないかもしれません。 時間が表示されます。

物語の始まり――すべてがどのように始まったか覚えていますか'。
最も一般的な言葉で言えば、その結果何が起こったのか - 」PostgreSQL のパフォーマンスを向上させる方法の XNUMX つとしての合成»

なぜこれすべてが必要なのでしょうか?

さて、まず忘れないように、退職後の輝かしい日々を思い出してください。
XNUMXつ目は、書かれた内容を体系化することです。 私自身、時々混乱し始めたり、別々の部分を忘れたりすることがあります。

まあ、そして最も重要なことは、おそらくそれが誰かにとって役立ち、車輪の再発明や熊手集めを避けるのに役立つかもしれません。 言い換えれば、（ハブロフスキーではなく）自分のカルマを改善することです。 なぜなら、この世で最も価値のあるものはアイデアだからです。 主なことはアイデアを見つけることです。 そして、そのアイデアを現実に移すことは、すでに純粋に技術的な問題です。

それでは、ゆっくり始めましょう...

問題の定式化。

あります：

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. 顧客は、通常は理解したくないものを購入する必要があることを納得する必要があり、購入すべきではありません。通常、その年の予算は承認されており、変更されることはありません。 次に、別のリソースを割り当て、特定のシステム用に構成する必要があります。 それらの。 まず、支払い、支払い、そして再度支払う必要があります。 そして顧客はケチです。 これがこの人生の標準です。

どうする、チェルニシェフスキー？ あなたの質問は非常に適切です。 （と）

この特定のケースと現在の状況では、少し異なる方法で実行できます。 自分たちで監視システムを作ってみましょう。

もちろん、言葉の完全な意味でシステムではありませんが、これはあまりにも大声でおこがましいことですが、少なくとも何らかの方法で自分にとって楽になり、パフォーマンスのインシデントを解決するためにより多くの情報を収集できるようにする必要があります。 状況に陥らないようにするために、「そこに行ってください、どこにいるかわかりません、それを見つけてください、何がわかりません。」

このオプションの長所と短所は次のとおりです。

長所：
1. 面白いですね。 まあ、少なくとも「データファイルの縮小、表領域の変更など」という定数よりは興味深いです。
2. これらは新しいスキルと新しい開発です。 遅かれ早かれ、当然のジンジャーブレッドとドーナツが手に入るでしょう。
短所：
1. 仕事をしなければなりません。 たくさん働く。
2. すべての活動の意味と視点を定期的に説明する必要があります。
3. エンジニアが利用できる唯一のリソースである時間は宇宙によって制限されているため、何かを犠牲にする必要があります。
4. 最悪で最も不愉快な出来事 その結果、「ネズミでもない、カエルでもない、未知の小動物」のようなゴミが出てくるかもしれません。

何かを危険にさらさない人はシャンパンを飲みません。
それで、楽しいことが始まります。

一般的なアイデア - 概略図

(イラストは記事から引用 «PostgreSQL のパフォーマンスを向上させる方法の XNUMX つとしての合成»）

説明：

• ターゲット データベースは、標準の PostgreSQL 拡張機能「pg_stat_statements」とともにインストールされます。
• 監視データベースでは、初期段階で pg_stat_statements の履歴を保存し、将来のメトリクスと監視を構成するための一連のサービス テーブルを作成します。
• モニタリング ホスト上で、チケット システムでインシデントを生成するためのスクリプトを含む、一連の bash スクリプトを作成します。

サービステーブル

まず、ERD を簡略化した図、最終的に何が起こったか:

テーブルの簡単な説明終点 - ホスト、インスタンスへの接続ポイント
データベース - データベースオプション
pg_stat_history - ターゲット データベースの pg_stat_statements ビューの一時スナップショットを保存するための履歴テーブル
メトリック用語集 - パフォーマンス指標の辞書
metric_config - 個々のメトリクスの構成
メトリック - 監視されているリクエストの特定のメトリック
metric_alert_history - パフォーマンス警告の履歴
ログクエリ - AWS からダウンロードした PostgreSQL ログ ファイルから解析されたレコードを保存するためのサービス テーブル
ベースライン - ベースとして使用される期間のパラメータ
チェックポイント - データベースのステータスを確認するためのメトリクスの構成
チェックポイント_アラート_履歴 — データベースヘルスチェックメトリクスの警告履歴
pg_stat_db_queries — アクティブなリクエストのサービス テーブル
活動記録 — アクティビティ ログ サービス テーブル
トラップオイド — トラップ構成サービステーブル

ステージ 1 - パフォーマンス統計を収集し、レポートを取得する

テーブルは統計情報を保存するために使用されます。 pg_stat_history
pg_stat_history テーブル構造

                                          テーブル「public.pg_stat_history」列 | タイプ | 修飾子---------------------+---------- ---+---- -------------------------------- ID | 整数 | null ではありません デフォルト nextval('pg_stat_history_id_seq'::regclass) snapshot_timestamp | タイムゾーンなしのタイムスタンプ | データベースID | 整数 | dbid | オイド | ユーザーID | オイド | クエリID | ビッグイント | クエリ | テキスト | 呼び出し | ビッグイント | 合計時間 | 倍精度 | 分時間 | 倍精度 | 最大時間 | 倍精度 | 平均時間 | 倍精度 | stddev_time | 倍精度 | 行 | ビッグイント | 共有_blks_hit | ビッグイント | 共有_blks_read | ビッグイント | 共有_blks_dirtied | ビッグイント | 共有_blks_write | ビッグイント | ローカルブロックヒット | ビッグイント | ローカル_blks_read | ビッグイント | ローカル_blks_dirtied | ビッグイント | local_blks_write | ビッグイント | temp_blks_read | ビッグイント | temp_blks_write | ビッグイント | ブロック読み取り時間 | 倍精度 | ブロック書き込み時間 | 倍精度 | ベースライン ID | 整数 | インデックス: "pg_stat_history_pkey" PRIMARY KEY、btree (id) "database_idx" btree (database_id) "queryid_idx" btree (queryid) "snapshot_timestamp_idx" btree (snapshot_timestamp) 外部キー制約: "database_id_fk" FOREIGN KEY (database_id) REFERENCES database(id) ) カスケード削除時

ご覧のとおり、テーブルは単なる累積ビュー データです。 pg_stat_statements ターゲットデータベース内。

このテーブルの使い方はとても簡単です。

pg_stat_history は、時間ごとのクエリ実行の累積統計を表します。 各時間の初めに、表に記入した後、統計が表示されます。 pg_stat_statements でリセット 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_history テーブルの内容のスナップショットのセットが作成されます。 pg_stat_statements ターゲットデータベース。

実際にレポートする

単純なクエリを使用すると、非常に便利で興味深いレポートを取得できます。

一定期間の集計データ

問い合わせ

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 ;

DB時間

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

I/O時間

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

total_time 別の SQL TOP10

問い合わせ

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

-------------------------------------------------- ------------------------------------ | 合計実行時間ごとの SQL トップ 10 | #| クエリ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

合計 I/O 時間ごとの SQL TOP10

問い合わせ

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 時間ごとの SQL のトップ 10 | #| クエリID| 電話| %| を呼び出しますI/O 時間 (ミリ秒)|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

最大実行時間別の SQL TOP10

問い合わせ

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

-------------------------------------------------- ------------------------------------ | SQL の最大実行時間別トップ 10 | #| スナップショット| スナップショットID| クエリID| max_time (ミリ秒) +------+---------------------+-----------+--------- -------------------------------------------- | 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 by SHAREDバッファ読み取り/書き込み

問い合わせ

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 のトップ 10 | #| スナップショット| スナップショットID| クエリID| 共有ブロックの読み取り| 共有ブロック書き込み +--------+---------------------+----------+---------- -+---------------------+--------------------- | 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| 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 ) ;

|------------------------------------------------- -------------------------------------- | MAX_TIME ヒストグラム | 合計通話数: 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

10 秒あたりのクエリ数によるトップ XNUMX のスナップショット

リクエスト

--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| 電話| 合計データベース時間| QPS | 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

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

合計

ご覧のとおり、非常に簡単な方法で、ワークロードとデータベースの状態に関する多くの有用な情報を取得できます。

注：クエリでクエリ ID を修正すると、別のリクエストの履歴が取得されます (スペースを節約するために、別のリクエストのレポートは省略されています)。

したがって、クエリのパフォーマンスに関する統計データが利用可能であり、収集されます。
第一段階「統計データの収集」が完了しました。

第 XNUMX 段階の「パフォーマンス指標の設定」に進むことができます。


しかし、それはまた別の話です。

継続するには...

出所： habr.com