初心者級SQLチューニング（DB環境はPostgreSQL）

11月から昨日まで、職場でSQLの速度改善のためにpg_stat(PostgreSQLの統計情報)とニラメッコしまくりだった。
昔からSQLにだけは深入りしたくないと思ってたけど、いよいよやる羽目になったのでしんどかったｗ

要点を簡潔にまとめると、下記のとおり。

【使う技（３種）】

・統計情報をリセットするSQL文
select pg_stat_reset();
※要postgres権限
・色々ある統計情報のうち、とりあえずTABLEスキャンとINDEXスキャンの情報を見るSQL文
SELECT
  *
FROM
  pg_stat_user_tables
ORDER BY
  seq_tup_read DESC
  , idx_tup_fetch DESC;
・クエリの実行プラン(の解析情報)を見るSQL文
EXPLAIN ANALYZE
※これに続けて、実行プランを解析したい対象のSQL文を書いて実行するだけ。

 

 【上記を使ってやる作業】

  1. スロークエリだと判っているとあるSQL文について、開発対象のシステムの機能から実行して、その時のSQLログを取り出す。
  2. 対象SQLの取り扱っているテーブルに対して、できるだけ抽出対象のレコードと余計なレコードとを織り交ぜて登録しておく。
  3. 対象SQLをEXPLAIN ANALYZEして、実行プランのrowsが余計なレコードも含んでそうな値になってたら、それの対策を打つ。(SQLの組み換えとか組み直しとか)
  4. さらに。統計情報をクリアしてEXPLAIN ANALYZEし、直後のpg_stat_user_tablesを見る。そこでseq_scanやseq_tup_readが余計なレコードに対してもかかっているようなら、うまいことINDEXを使うようにSQLを工夫する。(追加のINDEXを検討したり、絞込みだけするSELECTを用意してその結果をASで別名にしてその後のJOINで使ったり)
  5. 納得いくまで上記を繰り返す。
 以上。

作業内容を上記に絞り込むまでには色々とWeb漁って用語や動作原理的なところの下調べをやったんだけど、ややこしいことをとっぱらっていったら、これだけになったw

------------------------------------------------
調査の過程で特に参考にしたページを、下記に羅列しておきます。

インデックスを作成して，SQLの速度をチューニングする手順　（PostgreSQLで，EXPLAIN文とCREATE INDEX文によるパフォーマンス改善）
http://d.hatena.ne.jp/language_and_engineering/20110121/p1

稼動統計情報を活用しよう(3)
http://lets.postgresql.jp/documents/technical/statistics/3

スロークエリの改善
http://lets.postgresql.jp/documents/technical/query_tuning/

PostgreSQLを遅くしている犯人はどこだ？ (2/3)
http://www.atmarkit.co.jp/ait/articles/0806/09/news125_2.html

[PostgreSQL]Explain Analyzeで、多重JOINしたSQLをチューニングする
http://dqn.sakusakutto.jp/2011/11/postgresqlexplain_analyzejoins_1.html

confのチューニング
http://sweng.web.fc2.com/ja/database/postgresql/tuning.html
http://www.desknets.com/neo/faq/result/1337/
http://old.postgresql.jp/wg/shikumi/doc/kataoka_check_your_config.pdf

本日は以上です。

コメント

コメントを投稿

このブログの人気の投稿

LaravelのTestCaseを継承したテストにて use RefreshDatabase を使って artisan db:seed --class='TestSeeder' まで実行させるやり方

とりあえず、殴り書きレベルでメモを公開しておく。 実行環境情報 OS: Mac Sierra or Windows10 php: 7.2.7 Laravel Framework: 5.5.42 PHPUnit: 6.5.11 testsディレクトリと関連ファイルの構成 projectDir/ ├ app/ ├ bootstrap/ ├ ... ├ ～～省略～～ ├ ... ├ database/ │ ├ factories/ │ ├ migrations/ │ └ seeds/ │ └ TestSeeder.php ├ tests/ │ ├ Feature/ │ │ ├ BaseFeature.php │ │ └ Hoge.php │ ├ Unit/ │ ├ CreatesApplication.php │ └ TestCase.php/ ├ vendor/ ├ ... ├ ～～省略～～ ├ ... └ phpunit.xml  \Tests\Feature\BaseFeature.php の内容 <?php namespace Tests\Feature; use Tests\TestCase; use Illuminate\Contracts\Console\Kernel; use Illuminate\Foundation\Testing\RefreshDatabase; use Illuminate\Foundation\Testing\RefreshDatabaseState; class BaseFeature extends TestCase { use RefreshDatabase { refreshTestDatabase as originRefreshTestDatabase; } /** * 元々 trait RefreshDatabase に記述されている refreshTestDatabase() の処理内容に * artisan db:seed --class=TestSeeder * を...
続きを読む

ZF3で空のプロジェクトを作成する自分なりの手順

イメージ
ZF3で空のプロジェクトを作成する ※本稿では、Zendframework 3 のことを ZF3 とか zf3 と呼んでいます。 本稿タイトルについて、正規のやり方は知らないし、そもそも正規のやり方というのがあるかどうかも不明です。 極力最小限のコンポーネントを composer で持ってきて、 zf3標準のスケルトンアプリから最低限必要な構成物だけコピって 自作の空モジュールだけが稼働する状態を作る、 という自分なりの手順を、ここでは示します。 前提事項 記事は下記の環境を前提として書いていきます。 ドライブやパス等は使用環境に合わせて適宜読み替えてください。 おことわり 本稿で目指しているところは「zf3によるMVCのWebアプリケーションを開発する準備として必要最低限動作する構成物」です。 「空のプロジェクト」という定義が人によって若干異なる可能性があります。 私の場合は、以下の手順で作成されるプロジェクト構成物を「空である」と考えています。 作成環境: Windows10 Git for Windows をインストール済み Git Bashをコマンドライン作業環境として使う Git Bash の使い方の説明は特にやらない PHP7 を下記へインストールして環境変数PATHに設定済み /c/php7/php.exe PHP 7.0.15 (cli) (built: Jan 17 2017 13:58:38) ( ZTS ) composer.phar を取得済み Git Bash で言うところの /f/hoge/work/直下に composer.phar を配置する 本稿における作業ディレクトリは下記とする。 /f/hoge/work F:\hoge\work\ STEP1  zf3 のスケルトンアプリケーションを作業ディレクトリへ取得しておく。 途中で聞かれる「Do you want a minimal install (no optional packages)? Y/n」は、「最小構成でインストールするかい？」というプロンプト。とはいえ、それまでにInstallingとなってるパッケージ数から分かる通りそこそこ多い。 以下、Git...
続きを読む

Androidでical4jを使う

表題の件について、拙作のアプリ「Gcal Importer」での実装を、自分の頭を整理するついでにかいつまんで書き出してみたいと思います。 対象source: Gcal_Importer - src/net/crappo/android/androics/TopActivity.java src/net/crappo/android/androics/Model4Top.java src/net/crappo/android/androics/ShowEventListActivity.java src/net/crappo/android/androics/ShowEventListAsync.java src/net/crappo/android/androics/Model4EventList.java 【Topアクティビティで行っていること】 Google Calendarからエクスポートしたカレンダーデータは、ダウンロード直後はiCalendar形式のデータ(以後ICSデータと呼ぶ)を内包したzip圧縮ファイルになっています。このzipファイルを展開し、展開されたICSデータファイルをリストにして表示することで、ユーザが端末ローカルカレンダーにImportするICSデータをカレンダー単位で選択できるようにします。 【ShowEventListアクティビティで行っていること】 Topアクティビティで選択されたICSデータをical4jというライブラリを使ってあれこれ処理します。なお、ICSデータを読み込んでModelオブジェクト化する際には、Eventを開始日付時刻順にsortしておきます。 AsyncTaskでICSデータを読み込む Googleカレンダーから取得するICSデータは、通常の手段だとWebブラウザを使ってカレンダーのUIから「設定＞カレンダー＞カレンダーをエクスポート」という手順で取得できます。 このデータは例えば私の場合、ToDoを除いた３つのカレンダーがあって総イベント件数は2000件余りとなります。(メインのカレンダーにほぼすべてのイベントが詰まっている) これをAndroidのActivityで読み込む時にUIスレッドでやると読み込み終了までに時間がかかるので、AsyncTaskという仕組み...
続きを読む