« 宗教論争になりやすいテーマ | トップページ | フラグはbooleanで頼みます »

2015.02.19

postgresql.logの有効活用

性能改善を行なう場合にスロークエリ分析は大きな比重を占める。
設定によって遅いクエリの結果をpostgresl.logに出力でき、かつcsv形式での出力もできる。
今回はその出力したログをどう活用しようか、というお話。

Let's Postgresで紹介されている[スロークエリの分析]を参考に、私がやっている微修正をちょこっとご紹介。

CSV形式で出力されたログファイルをPostgreSQL内に取り込んで再利用を行なう方法だが、対象となるログファイルのテーブル定義は下記の通り。(CSV書式のログ出力の利用)
CREATE TABLE postgres_log
(
log_time timestamp(3) with time zone,
user_name text,
database_name text,
process_id integer,
connection_from text,
session_id text,
session_line_num bigint,
command_tag text,
session_start_time timestamp with time zone,
virtual_transaction_id text,
transaction_id bigint,
error_severity text,
sql_state_code text,
message text,
detail text,
hint text,
internal_query text,
internal_query_pos integer,
context text,
query text,
query_pos integer,
location text,
application_name text,
PRIMARY KEY (session_id, session_line_num)
)
ログファイルを複数回誤って読み込んでしまっても大丈夫なように、ユニークキー制約/Primary Keyは大事。
データの取り込み方は次の構文でOK。COPY postgres_log FROM 'C:\\xxxx\postgresql-2015-02-05_000000.csv' with( format csv);FORMAT指定をすると()でくくらないとエラーになる。COPY postgres_log FROM 'C:\\xxxx\postgresql-2015-02-05_000000.csv' with csv;こちらもエラーにならない構文である。ただしこれは9.0以前で採用されていた構文のため、将来的にも使えるかどうかは保証されないので注意が必要だ。

遅延ログを抽出するVIEWの定義もちょっとだけ改変してみる。

CREATE OR REPLACE VIEW pg_query_log AS
 SELECT
  substring(message,
   E'^duration: ([0-9]*\\.[0-9]*)')::numeric(1000,3) AS duration
  ,trim(((regexp_matches(replace(message,chr(10), ' ')
  ,'^duration: .*ms (execute|statement).*: (.*)'))[2])) AS sql
  , *
 FROM postgres_log
 WHERE error_severity = 'LOG'
  AND command_tag IN ('SELECT', 'INSERT', 'UPDATE', 'DELETE');

1.messageの改行コード除去
 SQLの指定方法によっては改行コードを吐き出していることがある。そこでreplaceを使って改行コードを除去。指定する改行コードは環境によって変更する必要があるだろう。ここではUNIX系ということでLF(char(10))を除去している。

2.MessageのTrim
 吐き出しているSQLがスペースがあったりすることがあったのでtrimして空白を除去。

対応できてないこと:
吐き出されているSQLが連結演算子(||)を含んでいる場合、||よりも前のSQLが消えてしまうようだ。うーむ。

|

« 宗教論争になりやすいテーマ | トップページ | フラグはbooleanで頼みます »

コメント

コメントを書く



(ウェブ上には掲載しません)




トラックバック

この記事のトラックバックURL:
http://app.cocolog-nifty.com/t/trackback/2022/61088404

この記事へのトラックバック一覧です: postgresql.logの有効活用:

« 宗教論争になりやすいテーマ | トップページ | フラグはbooleanで頼みます »