クエリ実行を速くするためのPostgreSQLパフォーマンスチューニング
目的
私たちの目標は、データベース内にある組み込みツールのみを使用して、PostgreSQLデータベース上でダミークエリの実行を速くすることです。
オペレーティングシステムとソフトウェアのバージョン
- オペレーティングシステム: Red Hat Enterprise Linux 7.5
- ソフトウェア： PostgreSQLサーバー 9.2
要件
PostgreSQLサーバーの基本インストールが完了し、実行中です。コマンドラインツールpsqlへのアクセスと、サンプルデータベースの所有権があります。
協定
- # – 指定されたLinuxコマンドは、rootユーザーとして直接実行するか、
sudoコマンドを使用して実行する必要があります。 - $- 一般的な権限のないユーザーとして実行されるLinuxコマンド
紹介
PostgreSQLは、多くの現代的なディストリビューションのリポジトリで入手可能な信頼性の高いオープンソースデータベースです。その使いやすさ、拡張機能の利用能力、そして提供する安定性が人気の要因です。
SQLクエリへの応答、一貫性のある挿入データの保存、トランザクションの処理などの基本機能を提供しながら、ほとんどの成熟したデータベースソリューションは、データベースを調整し、可能なボトルネックを特定し、システムが成長するにつれて発生しがちなパフォーマンス問題を解決するためのツールやノウハウを提供します。
PostgreSQLも例外ではなく、このガイドでは組み込みツールexplainを使用して、遅いクエリをより早く完了させる方法を説明します。これは実際のデータベースからはかけ離れていますが、組み込みツールの使用に関するヒントを得ることはできます。私たちはRed Hat Linux 7.5上のPostgreSQLサーバーバージョン9.2を使用しますが、このガイドに示されているツールは、はるかに古いデータベースおよびオペレーティングシステムのバージョンにも存在しています。
解決すべき問題
このシンプルなテーブルを考えてみてください（列名は自己説明的です）：
foobardb=# \d+ employees
Table "public.employees"
Column | Type | Modifiers | Storage | Stats target | Description
------------------+---------+-----------------------------------------------------+----------+--------------+-------------
emp_id | numeric | not null default nextval('employees_seq'::regclass) | main | |
first_name | text | not null | extended | |
last_name | text | not null | extended | |
birth_year | numeric | not null | main | |
birth_month | numeric | not null | main | |
birth_dayofmonth | numeric | not null | main | |
Indexes:
"employees_pkey" PRIMARY KEY, btree (emp_id)
Has OIDs: no
以下のような記録で:
foobardb=# select * from employees limit 2;
emp_id | first_name | last_name | birth_year | birth_month | birth_dayofmonth
--------+------------+-----------+------------+-------------+------------------
1 | Emily | James | 1983 | 3 | 20
2 | John | Smith | 1990 | 8 | 12
この例では、私たちはナイスカンパニーであり、「HBapp」というアプリケーションを展開しました。このアプリケーションは、従業員の誕生日に「お誕生日おめでとう」と書かれたメールを送信します。アプリケーションは、毎朝データベースにクエリを実行して、その日の受取人を見つけます（勤務時間前に行うのは、親切からですが、私たちの人事データベースを壊さないためです）。
アプリケーションは、受取人を見つけるために次のクエリを実行します:
foobardb=# select emp_id, first_name, last_name from employees where birth_month = 3 and birth_dayofmonth = 20;
emp_id | first_name | last_name
--------+------------+-----------
1 | Emily | James
すべてはうまくいっており、ユーザーはメールを受け取っています。会計やビジネスインテリジェンスなど、他の多くのアプリケーションがデータベースを使用しており、その中に従業員テーブルがあります。ナイスカンパニーは成長しており、従業員テーブルも成長しています。時が経つにつれ、アプリケーションの実行が長くなり、業務時間の開始と重なることで、ミッションクリティカルなアプリケーションのデータベース応答時間が遅くなります。このクエリを高速化するために何か手を打たなければなりません。さもなければ、アプリケーションは廃止され、その結果、ナイスカンパニーの中での善意が減少します。
この例では、問題を解決するために高度なツールは使用せず、基本インストールで提供されているものだけを使用します。データベースプランナーがexplainを使用してクエリをどのように実行するかを見てみましょう。
私たちは本番環境でテストしているわけではなく、テスト用のデータベースを作成し、テーブルを作成し、前述の2人の従業員を追加します。このチュートリアルの中では、クエリに対して同じ値を使用しますので、どの実行でもクエリに一致するレコードは1つだけになります：エミリー・ジェイムズ。その後、explain analyze を前置してクエリを実行し、テーブル内の最小限のデータでどのように実行されるかを確認します。
foobardb=# explain analyze select emp_id, first_name, last_name from employees where birth_month = 3 and birth_dayofmonth = 20;
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Seq Scan on employees (cost=0.00..15.40 rows=1 width=96) (actual time=0.023..0.025 rows=1 loops=1)
Filter: ((birth_month = 3::numeric) AND (birth_dayofmonth = 20::numeric))
Rows Removed by Filter: 1
Total runtime: 0.076 ms
(4 rows)
それは本当に早いですね。おそらく、会社がHBappを最初に展開したときと同じくらいの速さです。現在の本番環境のfoobardbの状態を模倣するために、本番環境にいるのと同じ数の（偽の）従業員をデータベースに読み込もうと思います（注意：テストデータベースのストレージサイズは本番環境と同じである必要があります）。
テストデータベースをPopulateするためにbashを使います（本番環境に500,000人の従業員がいると仮定して）:
for j in {1..500000} ; do echo "insert into employees (first_name, last_name, birth_year, birth_month, birth_dayofmonth) values ('user$j','Test',1900,01,01);"; done | psql -d foobardb
今、私たちは500002人の従業員がいます。
foobardb=# select count(*) from employees;
count
--------
500002
(1 row)
再度解説クエリを実行しましょう:
foobardb=# explain analyze select emp_id, first_name, last_name from employees where birth_month = 3 and birth_dayofmonth = 20;
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Seq Scan on employees (cost=0.00..11667.63 rows=1 width=22) (actual time=0.012..150.998 rows=1 loops=1)
Filter: ((birth_month = 3::numeric) AND (birth_dayofmonth = 20::numeric))
Rows Removed by Filter: 500001
Total runtime: 151.059 ms
まだ試合は1つしかありませんが、クエリは大幅に遅くなっています。プランナーの最初のノードに注目する必要があります：Seq Scanは逐次スキャンを表し、データベースは全体のテーブルを読み込みますが、私たちは1つのレコードだけが必要です。これはbashでのgrepのようなものです。実際、grepよりも遅くなることがあります。テーブルを/tmp/exp500k.csvという名前のcsvファイルにエクスポートするとします：
foobardb=# copy employees to '/tmp/exp500k.csv' delimiter ',' CSV HEADER;
COPY 500002
そして、私たちが必要な情報をgrepします（CSVファイルの各行の20日目の3月、最後の2つの値を検索します）：
time grep ",3,20" /tmp/exp500k.csv
1,Emily,James,1983,3,20
real 0m0.067s
user 0m0.018s
sys 0m0.010s
これは、キャッシングを除けば、テーブルが大きくなるにつれてますます遅くなると見なされます。
解決策は、インデックス作成です。従業員は一つの誕生日しか持てず、それは正確に一つのbirth_year、birth_month、およびbirth_dayofmonthで構成されているため、これら三つのフィールドは特定のユーザーに対して一意の値を提供します。そして、ユーザーは彼/彼女のemp_idによって識別されます（同じ名前の従業員が会社に複数いることがあります）。これらの四つのフィールドに制約を宣言すると、暗黙的なインデックスも作成されます。
foobardb=# alter table employees add constraint birth_uniq unique (emp_id, birth_year, birth_month, birth_dayofmonth);
NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "birth_uniq" for table "employees"
さて、私たちは4つのフィールドのインデックスを取得しました。クエリがどのように実行されるか見てみましょう:
foobardb=# explain analyze select emp_id, first_name, last_name from employees where birth_month = 3 and birth_dayofmonth = 20;
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Seq Scan on employees (cost=0.00..11667.19 rows=1 width=22) (actual time=103.131..151.084 rows=1 loops=1)
Filter: ((birth_month = 3::numeric) AND (birth_dayofmonth = 20::numeric))
Rows Removed by Filter: 500001
Total runtime: 151.103 ms
(4 rows)
それは最後のものと同じで、計画も同じで、インデックスは使用されていません。ユニーク制約を使用して emp_id、birth_month、および birth_dayofmonth のみに基づく別のインデックスを作成しましょう（結局、HBappでは birth_year をクエリしないので）:
foobardb=# alter table employees add constraint birth_uniq_m_dom unique (emp_id, birth_month, birth_dayofmonth);
NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "birth_uniq_m_dom" for table "employees"
私たちの調整の結果を見てみましょう:
foobardb=# explain analyze select emp_id, first_name, last_name from employees where birth_month = 3 and birth_dayofmonth = 20;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
Seq Scan on employees (cost=0.00..11667.19 rows=1 width=22) (actual time=97.187..139.858 rows=1 loops=1)
Filter: ((birth_month = 3::numeric) AND (birth_dayofmonth = 20::numeric))
Rows Removed by Filter: 500001
Total runtime: 139.879 ms
(4 rows)
何もありません。上記の違いはキャッシュの使用によるものですが、計画は同じです。さらに進みましょう。次に、emp_idとbirth_monthの別のインデックスを作成します:
foobardb=# alter table employees add constraint birth_uniq_m unique (emp_id, birth_month);
NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "birth_uniq_m" for table "employees"
もう一度クエリを実行してください:
foobardb=# explain analyze select emp_id, first_name, last_name from employees where birth_month = 3 and birth_dayofmonth = 20;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Index Scan using birth_uniq_m on employees (cost=0.00..11464.19 rows=1 width=22) (actual time=0.089..95.605 rows=1 loops=1)
Index Cond: (birth_month = 3::numeric)
Filter: (birth_dayofmonth = 20::numeric)
Total runtime: 95.630 ms
(4 rows)
成功しました！クエリは40％速くなり、計画が変更されたことがわかります：データベースはもはや全テーブルをスキャンせず、birth_monthとemp_idのインデックスを使用します。私たちは4つのフィールドの混合をすべて作成しましたが、1つだけが残っています。試す価値があります:
foobardb=# alter table employees add constraint birth_uniq_dom unique (emp_id, birth_dayofmonth);
NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "birth_uniq_dom" for table "employees"
最後のインデックスはフィールドemp_idとbirth_dayofmonthに作成されます。そして、結果は次のとおりです:
foobardb=# explain analyze select emp_id, first_name, last_name from employees where birth_month = 3 and birth_dayofmonth = 20;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Index Scan using birth_uniq_dom on employees (cost=0.00..11464.19 rows=1 width=22) (actual time=0.025..72.394 rows=1 loops=1)
Index Cond: (birth_dayofmonth = 20::numeric)
Filter: (birth_month = 3::numeric)
Total runtime: 72.421 ms
(4 rows)
現在、私たちのクエリは49%速くなりました。これは、最後に作成されたインデックス（そして唯一のインデックス）のみを使用しています。私たちのテーブルと関連するインデックスは以下のようになります:
foobardb=# \d+ employees
Table "public.employees"
Column | Type | Modifiers | Storage | Stats target | Description
------------------+---------+-----------------------------------------------------+----------+--------------+-------------
emp_id | numeric | not null default nextval('employees_seq'::regclass) | main | |
first_name | text | not null | extended | |
last_name | text | not null | extended | |
birth_year | numeric | not null | main | |
birth_month | numeric | not null | main | |
birth_dayofmonth | numeric | not null | main | |
Indexes:
"employees_pkey" PRIMARY KEY, btree (emp_id)
"birth_uniq" UNIQUE CONSTRAINT, btree (emp_id, birth_year, birth_month, birth_dayofmonth)
"birth_uniq_dom" UNIQUE CONSTRAINT, btree (emp_id, birth_dayofmonth)
"birth_uniq_m" UNIQUE CONSTRAINT, btree (emp_id, birth_month)
"birth_uniq_m_dom" UNIQUE CONSTRAINT, btree (emp_id, birth_month, birth_dayofmonth)
Has OIDs: no
中間インデックスは必要ありません。計画にはそれらを使用しないと明記されているので、削除します。
foobardb=# alter table employees drop constraint birth_uniq;
ALTER TABLE
foobardb=# alter table employees drop constraint birth_uniq_m;
ALTER TABLE
foobardb=# alter table employees drop constraint birth_uniq_m_dom;
ALTER TABLE
結局、私たちのテーブルはわずか1つの追加インデックスを獲得するだけであり、HBappの倍速に対してコストは低いです:
foobardb=# \d+ employees
Table "public.employees"
Column | Type | Modifiers | Storage | Stats target | Description
------------------+---------+-----------------------------------------------------+----------+--------------+-------------
emp_id | numeric | not null default nextval('employees_seq'::regclass) | main | |
first_name | text | not null | extended | |
last_name | text | not null | extended | |
birth_year | numeric | not null | main | |
birth_month | numeric | not null | main | |
birth_dayofmonth | numeric | not null | main | |
Indexes:
"employees_pkey" PRIMARY KEY, btree (emp_id)
"birth_uniq_dom" UNIQUE CONSTRAINT, btree (emp_id, birth_dayofmonth)
Has OIDs: no
そして、私たちは最も有用だと見なしたインデックスを追加することによって、調整を生産に導入することができます:
alter table employees add constraint birth_uniq_dom unique (emp_id, birth_dayofmonth);結論
言うまでもなく、これは単なるダミーの例です。従業員の誕生日を、整数として月と日の値を比較するよりもずっと簡単に日付関連の操作を可能にする日付型フィールドを使用することができるのに、3つの別々のフィールドに保存することは考えにくいです。また、上記のいくつかの説明クエリは過剰なテストとして適していないことにも注意してください。実際のシナリオでは、新しいデータベースオブジェクトがデータベースを使用する他のアプリケーションや、HBappと相互作用するシステムのコンポーネントに与える影響をテストする必要があります。
たとえば、この場合、受取人のためのテーブルを元の応答時間の50％で処理できれば、実質的にアプリケーションの他の側で200％のメールを生成できる（仮に、HBappがNice Companyの500の子会社すべてのためにシーケンシャルに実行されるとすると）、これにより他の場所でピーク負荷が発生する可能性があります。おそらくメールサーバーは、日常の報告書を経営陣に送信する直前に、たくさんの「お誕生日おめでとう」メールを中継することになります。これが納品の遅延につながる可能性があります。また、データベースを調整する誰かが盲目的な試行錯誤でインデックスを作成するのは現実からかけ離れている少し不自然です。少なくとも、これがそのような多くの人々を雇用している会社ではそうであることを願いたいです。
ただし、組み込みのPostgreSQL explain機能を使用して特定の状況で役立つ単一のインデックスを特定することで、クエリのパフォーマンスを50％向上させたことに注意してください。また、これらのデータベースを意図された通りに使用しない限り、任意のリレーショナルデータベースは明確なテキスト検索よりも優れているわけではないことも示しました。