ねえリクエスト！ 生きていますか PostgreSQLでロックを簡単に処理する方法

一日の良い時間！ リレーショナルデータベースの管理とメンテナンスは、多くの場合、重要なタスクです。 不明な理由で、すぐに機能するクエリが突然「スローダウン」し始め、テーブルのサイズが大きくなり、データベース全体のパフォーマンスが低下する場合があります。



多くの場合、この動作の理由は、データベースで発生するさまざまなリソースのブロックと、それに応じてこれらのリソースの待機時間が長くなることです。 たとえば、異なるセッションの2つ以上のクエリがテーブル内の同じデータまたはテーブル構造自体を同時に変更しようとする状況で問題が発生します。



この状況を理解するには、DBAはどのプロセスがブロックされ、どのプロセスがブロックされているかを理解する必要があります。また、ブロックプロセスをキャンセルまたは「強制終了」し、最終的に結果を確認できます。



この記事では、PostgreSQLのロックのトピックに触れて、ロックを操作するツールについて説明します。 しかし、最初に、トピック自体を理解してみましょう。

ちょっとした理論：ロックに関する教育プログラム

データベースロックとは何ですか？ ウィキペディアの定義は次のとおりです。「DBMSのロック（Eng。Lock）-衝突を防ぎ、データの整合性を維持するために、アクセスを制限または排他的に行うトランザクションによるオブジェクトのキャプチャのマーク。」



PostgeSQLは、MVCCモデルを実装することにより、データの整合性を維持します。 MVCC（MultiVersion Concurrency Control）は、データベースへの並列アクセスを確保するためのメカニズムの1つです。これは、各ユーザーにデータベースのいわゆる「スナップショット」を提供することで構成されます。 このようなスナップショットの特別な「プロパティ」は、ユーザーがデータベースに加えた変更が、トランザクションがコミットされるまで他のユーザーには見えないことです。



PostgreSQLは、革新的なSSI分離レベル（シリアル化可能なスナップショット分離、シリアル化可能なスナップショット分離）を使用して、最も厳しいトランザクション分離レベルでも整合性を保証します。



このトピックをよりよく理解するには、ロック、その作業、一般的な競争アクセスに関するHabré の記事とAlexander Zhuravlevのブログの記事を読むことができます。

予期せぬ状況

残念ながら、データの整合性を確保するためのメカニズムを実装すると、ブロックせずに着信要求に対処できない状況が発生します。 これはめったに起こりませんが、クエリがテーブル全体を長時間ブロックしているという状況が発生すると、トラブルにつながる可能性があります。



たとえば、1秒あたり100のUPDATEクエリが発生する1000レコードのテーブルに対して長時間処理されたクエリを開始すると、5〜6時間でテーブルのサイズがそれぞれ180万レコードに増加し、テーブルの物理サイズも増加します（データベースにはすべてのバージョンが格納されるため長いトランザクションが完了するまで行。



この状況をより詳細に検討してください。

ブロッキングの例

あるデータベースにpgsqlblocks_testingテーブルがあり、 rule_pgsqlblocks_testingルールがあるとします。 たとえば、pgAdmin SQLエディターを使用して、「長い」クエリを10分間エミュレートします。

SELECT * FROM public.pgsqlblocks_testing LIMIT 1000; SELECT pg_sleep(600);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

Pidプロセス16728



もう1つのエディターを開き、別のリクエストを実行してルールを削除します。

 DROP RULE rule_pgsqlblocks_testing ON public.pgsqlblocks_testing;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

Pidプロセス16726



また、ドロップ要求はSELECT要求によってブロックされます。 この場合のMVCCは、 pgsqlblocks_testingテーブルを明示的にロックしないと実行できませんでした。

ロックツール

既存のロックをどのように表示しますか？ pg_locksロックテーブルとpg_stat_activityビューのクエリを自分で作成するか、pgAdminに組み込まれているツールを使用できます。

pgAdminのサーバーステータス

pgAdminは、PostgreSQLデータベースを操作するための非常に便利でシンプルなソフトウェアです。 現時点では、現在のバージョンはpgAdmin IIIであり、9月末にのみリリースされます。

pgAdmin III

pgAdmin IIIでロックおよびアクティブプロセスに関する情報を表示するには、データベースにadminpack拡張機能が必要です。 この拡張機能をインストールすると、[ツール]-[サーバーステータス]メニューから必要なウィンドウが開きます。



このウィンドウには、プロセスが含まれるテーブルと、データベース内に既存のロックが含まれるテーブルが表示されます。 多数のプロセス間で迷子にならないように、ステータスに応じてプロセスの色を調整できます：アクティブ、ブロック、非アクティブ、または「遅い」。









表では、各ブロックおよびブロックプロセスは別々の行で表されており、誰が誰をブロックしているかを迅速に判断する方法はありません。 この問題を解決するには、リレーション列の共通の値と許可された列の優れた値によって結合された行を見つけるために、異なる行を互いに比較する必要があります。



ウィンドウには、選択したプロセスをキャンセルまたは終了するための2つのボタンがあります。 いずれかのプロセスを終了した後、ウィンドウを更新し、行を再度照合して結果を評価する必要があります。



したがって、pgAdmin IIIはロックを操作するためのツールとして使用できますが、いくつかのマイナス点があります：データベースの予備設定が必要であり、ロックをフラットな形式で表示し（ブロックされたブロックされたプロセスのツリービューなし）、問題のあるプロセスの検索とその終了の評価を複雑にします。 このため、タスクにとって最も便利なツールではありません。

pgAdmin IV

pgAdmin IVをインストールして実行すると、pgAdmin IIIと同じ形式で既存のロックを確認できます。









しかし...ここでできることはこれだけです。 PgAdmin IVはプロセスに対するアクションのツールバーを失い、このビューからプロセスをキャンセルまたは終了できなくなりました。これにより、pgAdmin IVはロックを操作するための不便なツールになります。

DBクエリ

データベースには、ブロックされたクエリとブロックされたクエリを表示するためのさまざまなクエリ実装がネットワーク上にあります。



クエリ「pg_locks monitoring」に対する検索エンジンの最初の結果は、クエリオプションとのリンクを提供します。







リクエスト1：

 SELECT blocked_locks.pid AS blocked_pid, blocked_activity.usename AS blocked_user, blocking_locks.pid AS blocking_pid, blocking_activity.usename AS blocking_user, blocked_activity.query AS blocked_statement, blocking_activity.query AS current_statement_in_blocking_process FROM pg_catalog.pg_locks blocked_locks JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid AND blocking_locks.pid != blocked_locks.pid JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid WHERE NOT blocked_locks.GRANTED;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

エディターを開き、クエリを入力してロックに関する情報を取得します。







かなり複雑に見えますが、結果は目に優しいものです。 一般に、PostgreSQLコミュニティは、通常のDBAの情報の検索を支援および促進する非常に多くのリソースを作成およびサポートしています。 たとえば、同じwiki wiki.postgresql.org



したがって、誰が誰をブロックしているのかがわかります。 そのようなリクエストにはオプションがあり、プロセスが待機している時間に関する情報を表示できます。



2番目のリンク（ちなみに、オフィシャルからのドキュメント）は、非常に簡単なリクエストを提供します。



リクエスト2：

 SELECT * FROM pg_locks pl LEFT JOIN pg_stat_activity psa ON pl.pid = psa.pid;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

これらすべてのバリエーションの意味は、本質的に同じクエリです：ロックに関する情報を表示します。 私たちは必要な情報を受け取りましたが、答えは表面に直接はありません。 特に、データベースへのクエリが多数ある場合。 座って、誰が誰を、なぜブロックしているのかを把握しましょう ブロックされたリソースのグラフを誰もが頭の中で作成できるわけではありません！







さらに、ブロックプロセスを破棄または停止する必要があります。 そして、はい、これはプロセスのpidを示す別の要求を介して、手動で行う必要があります-

 select pg_backend_pid(16728);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

または

 select pg_terminate_backend(16728);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

結果を確認するには、クエリ1を再度実行するか、 SELECT * FROM pg_catalog.pg_stat_activity WHERE pid=16728;



。

pgSqlBlocksのすべてがシンプルで便利です！

別のツールを見せて、なぜそれがそんなに便利なのかを共有したい-pgSqlBlocks。 pgSqlBlocksツールは私たち自身が作成したものであり、1年以上使用してきたPostgreSQLのロック問題の解決を促進するために特別に作成されました。



これは、2プロセスの例の場合のpgSqlBlocksウィンドウの外観です（ここでは、pid 29981（SELECT）と28710（DROP RULE）があります）。







ウィンドウの左側には、データベースへの接続状態に関する情報を表示するデータベースのリストがあります（接続、切断、更新情報、接続エラー、データベースにロックがあります）。



アプリケーションの主要部分は、選択したデータベースに現在あるプロセスのツリーで占められています。 ブロックされたプロセスには、閉じた灰色のロックのアイコンがあり、アイコンが赤いロックのブロックプロセスの子孫です。 通常のプロセスのアイコンは緑色の点です。



プロセスをこのように表現することで、プロセス内を簡単にナビゲートし、ブロックおよび待機中のプロセス、およびそれらの相互関係に関する情報を受信できます。 明確にするために、通常の（ブロックされていない、およびブロックされていない）プロセスを非表示にすることができます。









SELECTクエリが長いpid 29981のプロセスは、pid 28710のプロセスをブロックしていることがはっきりとわかります。



必要に応じて、プロセスをキャンセルまたは強制終了するためのシグナルを送信できます。 たとえば、ブロックされたプロセス28710を破棄すると、プロセスツリーの情報がすぐに更新され、結果が表示されます-長いSELECT要求を持つプロセス29981は他のユーザーをブロックしません。 速くて便利。



アプリケーションの小さくて快適な機能のもう1つに注目できます。



-ロック履歴をファイルに保存し、アプリケーションにロードし直します。 保存時のすべてのロックのスナップショットの一種。これにより、データベース内のロックがいつでも表示および分析できます。

-接続されたデータベースの少なくとも1つがロックされている場合、トレイアイコンが変わります。

-ロックが表示されたときのトレイ内の通知。

-カスタマイズ可能な自動更新プロセスリスト。



pgSqlBlocksのインストール方法と、上記のオプションと比較してどのように便利ですか？

インストールとセットアップ

システムにはJRE 8が事前にインストールされている必要があります。



アドレスpgcodekeeper.ru/pgsqlblocksにアクセスして、プログラムの最新の最新バージョンを選択します。 フォルダーには4つのjarファイルが含まれます。 OSとシステムのビット深度に適したものを選択してください。 ダウンロード、実行、出来上がり！



アプリケーションを実行するために必要なのはこれだけです。 すべてが箱から出して動作します。



アプリケーションの使用を開始するには、リストにデータベースを入力する必要があります。 新しいデータベースを追加するには、データベースリストの上にある「+」記号の付いたデータベースアイコンをクリックし、表示されるダイアログで必要なデータを入力します。 パスワードをpgpassファイルに保存することをお勧めします。









バージョン9.2-9.6 PostgreSQLでテスト済み。



さらに、データベースから情報を更新する頻度、アイドルプロセスを表示する必要性、表示される列のリストを構成できます。

おわりに

データベース内のクエリをブロックする問題は非常に深刻であり、データベースの著しい低下とディスク領域の枯渇につながる可能性があります。 したがって、ロックを検出し、（場合によっては）操作アクションを実行するための便利で迅速なツールを用意することが重要です。



このようなツールはpgSqlBlocksです。これにより、プロセス間を簡単に移動し、リクエストのブロックと保留に関する情報を簡単に受信できます。



その利点には、提供された情報の可視性、およびプロセスに関する情報の表示、プロセスのリスト内の問題の検出、プロセスのキャンセルまたは終了、結果の評価などの一般的なタスクの実行の利便性が含まれます。 さらに、状況をさらに分析するためにロック履歴をファイルに保存することも良い機会です。 これにより、PostgreSQLデータベースのロックをすばやく簡単に操作できます。



PS：このアプリケーションを作成するためのインスピレーションは、 MSSQL Blocksユーティリティでした。 ただし、MSSQLデータベースを操作するために特別に設計されています。 PostgreSQLの場合、類似物はありませんでした。



→OSの最新バージョンはこちらからダウンロードしてください。