結論
CentOSにyumでインストールしたPostgreSQLはinitdbするとロケールがUTF8となります。
$ /usr/pgsql-9.3/bin/initdb -D /var/lib/pgsql/9.3/data --locale=C
と、ロケールをCに設定することをお勧めします。
いきさつ概要
PostgreSQL9.0からPostgreSQL9.3にVerUPする際、今までは
$ /usr/pgsql-9.0/bin/initdb -D /var/lib/pgsql/9.0/data --locale=C
と、ロケールにCを指定して初期化していました。
※確か、ロケールをCにしないとORDER BYしたときの並び方が感覚にあっていない…という理由だったと思います。
しかし、9.3を導入したときは
# service postgresql-9.3 initdb
としていました。
この場合、ロケールはUTF8で設定されます。
理屈は分かりませんが、ロケールをUTF-8とした場合、特に大量の日本語が格納されている列をGROUP BY句にいれると、SQLの実行が異常に遅くなる事象を確認しました。
いきさつ
PostgreSQLを9.0から9.3 にあげたらCPU負荷があがりました。
また微妙にディスクI/Oも増えました。
9.0と9.3でコストが高いSQLを実行して比較しました。
実行したSQLは以下の通りです。
select u.uid,COUNT(u.tokuten),m.hitokoto FROM myuserview m ,userreview u WHERE m.uid = u.uid GROUP BY u.uid,m.hitokoto ORDER BY COUNT(u.tokuten) DESC;
まずはEXPLAINの結果を比較します。
9.0のEXPLAINの結果は以下の通りです。
9.3のEXPLAINの結果は以下の通りです。
# EXPLAIN select u.uid,COUNT(u.tokuten),m.hitokoto FROM myuserview m ,userreview u WHERE m.uid = u.uid GROUP BY u.uid,m.hitokoto ORDER BY COUNT(u.tokuten) DESC;
CentOSにyumでインストールしたPostgreSQLはinitdbするとロケールがUTF8となります。
# SELECT name, setting, context FROM pg_settings WHERE name LIKE 'lc%'; name | setting | context -------------+-------------+----------- lc_collate | ja_JP.UTF-8 | internal lc_ctype | ja_JP.UTF-8 | internal lc_messages | ja_JP.UTF-8 | superuser lc_monetary | ja_JP.UTF-8 | user lc_numeric | ja_JP.UTF-8 | user lc_time | ja_JP.UTF-8 | user (6 行)ロケールがUTF8の場合、日本語を含む列をGROUP BYするような場合、とても遅くなります。
$ /usr/pgsql-9.3/bin/initdb -D /var/lib/pgsql/9.3/data --locale=C
と、ロケールをCに設定することをお勧めします。
# SELECT name, setting, context FROM pg_settings WHERE name LIKE 'lc%'; name | setting | context -------------+---------+----------- lc_collate | C | internal lc_ctype | C | internal lc_messages | C | superuser lc_monetary | C | user lc_numeric | C | user lc_time | C | user (6 行)
いきさつ概要
PostgreSQL9.0からPostgreSQL9.3にVerUPする際、今までは
$ /usr/pgsql-9.0/bin/initdb -D /var/lib/pgsql/9.0/data --locale=C
と、ロケールにCを指定して初期化していました。
※確か、ロケールをCにしないとORDER BYしたときの並び方が感覚にあっていない…という理由だったと思います。
しかし、9.3を導入したときは
# service postgresql-9.3 initdb
としていました。
この場合、ロケールはUTF8で設定されます。
理屈は分かりませんが、ロケールをUTF-8とした場合、特に大量の日本語が格納されている列をGROUP BY句にいれると、SQLの実行が異常に遅くなる事象を確認しました。
いきさつ
PostgreSQLを9.0から9.3 にあげたらCPU負荷があがりました。
また微妙にディスクI/Oも増えました。
9.0と9.3でコストが高いSQLを実行して比較しました。
実行したSQLは以下の通りです。
select u.uid,COUNT(u.tokuten),m.hitokoto FROM myuserview m ,userreview u WHERE m.uid = u.uid GROUP BY u.uid,m.hitokoto ORDER BY COUNT(u.tokuten) DESC;
まずはEXPLAINの結果を比較します。
9.0のEXPLAINの結果は以下の通りです。
# EXPLAIN select u.uid,COUNT(u.tokuten),m.hitokoto FROM myuserview m ,userreview u WHERE m.uid = u.uid GROUP BY u.uid,m.hitokoto ORDER BY COUNT(u.tokuten) DESC;
QUERY PLAN
----------------------------------------------------------------------------------------------
Sort (cost=796875.09..799229.12 rows=941611 width=291)
Sort Key: (count(u.tokuten))
-> GroupAggregate (cost=424784.57..445970.82 rows=941611 width=291)
-> Sort (cost=424784.57..427138.60 rows=941611 width=291)
Sort Key: u.uid, myuser.hitokoto
-> Hash Join (cost=5755.99..73880.29 rows=941611 width=291)
Hash Cond: (u.uid = myuser.uid)
-> Seq Scan on userreview u (cost=0.00..39965.11 rows=941611 width=12)
-> Hash (cost=2455.44..2455.44 rows=65244 width=288)
-> Seq Scan on myuser (cost=0.00..2455.44 rows=65244 width=288)
(10 行)
9.3のEXPLAINの結果は以下の通りです。
# EXPLAIN select u.uid,COUNT(u.tokuten),m.hitokoto FROM myuserview m ,userreview u WHERE m.uid = u.uid GROUP BY u.uid,m.hitokoto ORDER BY COUNT(u.tokuten) DESC;
QUERY PLAN
----------------------------------------------------------------------------------------------
Sort (cost=810050.34..812417.85 rows=947004 width=298)
Sort Key: (count(u.tokuten))
-> GroupAggregate (cost=431679.86..450619.94 rows=947004 width=298)
-> Sort (cost=431679.86..434047.37 rows=947004 width=298)
Sort Key: u.uid, myuser.hitokoto
-> Hash Join (cost=5805.85..72249.46 rows=947004 width=298)
Hash Cond: (u.uid = myuser.uid)
-> Seq Scan on userreview u (cost=0.00..38070.04 rows=947004 width=12)
-> Hash (cost=2438.82..2438.82 rows=65282 width=295)
-> Seq Scan on myuser (cost=0.00..2438.82 rows=65282 width=295)
(10 行)
EXPLAINの実行結果はあまりかわりません。
次に、 EXPLAIN ANALYZEの結果をみます。
まずは9.0です。
EXPLAINの実行結果はあまりかわりません。
次に、 EXPLAIN ANALYZEの結果をみます。
まずは9.0です。
# EXPLAIN ANALYZE select u.uid,COUNT(u.tokuten),m.hitokoto FROM myuserview m ,userreview u WHERE m.uid = u.uid GROUP BY u.uid,m.hitokoto ORDER BY COUNT(u.tokuten) DESC;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
-------------------------
Sort (cost=796875.09..799229.12 rows=941611 width=291) (actual time=7839.773..7842.543 rows=26204 loops=1)
Sort Key: (count(u.tokuten))
Sort Method: quicksort Memory: 3985kB
-> GroupAggregate (cost=424784.57..445970.82 rows=941611 width=291) (actual time=6589.616..7826.559 rows=26204 loop
s=1)
-> Sort (cost=424784.57..427138.60 rows=941611 width=291) (actual time=6589.603..7410.715 rows=946356 loops=1
)
Sort Key: u.uid, myuser.hitokoto
Sort Method: external merge Disk: 162392kB
-> Hash Join (cost=5755.99..73880.29 rows=941611 width=291) (actual time=291.349..2941.949 rows=946356
loops=1)
Hash Cond: (u.uid = myuser.uid)
-> Seq Scan on userreview u (cost=0.00..39965.11 rows=941611 width=12) (actual time=14.134..1748.
609 rows=946356 loops=1)
-> Hash (cost=2455.44..2455.44 rows=65244 width=288) (actual time=276.851..276.851 rows=65244 loo
ps=1)
Buckets: 2048 Batches: 8 Memory Usage: 508kB
-> Seq Scan on myuser (cost=0.00..2455.44 rows=65244 width=288) (actual time=6.345..229.775
rows=65244 loops=1)
Total runtime: 7874.282 ms
(14 行)
次に9.3です。
9.3の方がとんでもなく時間がかかっています。
ソートにディスクを使いまくっているので、9.3になったらディスクの使い方がまずくなったのか?と思いましたので、work_memを300MBに増やして再度実行してみました。
※ぐぐると、 external merge Diskの量だけwork_memに積めばOKなことが書いてあるのですが、実際はexternal merge Diskより多くのメモリを確保しないとメモリを使ってソートしてくれませんでした。今回のSQLの場合、work_memを276610kB確保する必要がありました。
9.0の実行結果は以下の通りです。
次に9.3です。
# EXPLAIN ANALYZE select u.uid,COUNT(u.tokuten),m.hitokoto FROM myuserview m ,userreview u WHERE m.uid = u.uid GROUP BY u.uid,m.hitokoto ORDER BY COUNT(u.tokuten) DESC;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
-----------------------
Sort (cost=810050.34..812417.85 rows=947004 width=298) (actual time=466359.118..466362.684 rows=26224 loops=1)
Sort Key: (count(u.tokuten))
Sort Method: quicksort Memory: 3986kB
-> GroupAggregate (cost=431679.86..450619.94 rows=947004 width=298) (actual time=388687.232..466347.076 rows=26224
loops=1)
-> Sort (cost=431679.86..434047.37 rows=947004 width=298) (actual time=388678.206..465844.530 rows=947102 loo
ps=1)
Sort Key: u.uid, myuser.hitokoto
Sort Method: external merge Disk: 162656kB
-> Hash Join (cost=5805.85..72249.46 rows=947004 width=298) (actual time=48.662..1106.059 rows=947102 l
oops=1)
Hash Cond: (u.uid = myuser.uid)
-> Seq Scan on userreview u (cost=0.00..38070.04 rows=947004 width=12) (actual time=0.045..291.16
0 rows=947102 loops=1)
-> Hash (cost=2438.82..2438.82 rows=65282 width=295) (actual time=48.411..48.411 rows=65291 loops
=1)
Buckets: 2048 Batches: 8 Memory Usage: 505kB
-> Seq Scan on myuser (cost=0.00..2438.82 rows=65282 width=295) (actual time=0.036..28.135
rows=65291 loops=1)
Total runtime: 466395.321 ms
(14 行)
9.3の方がとんでもなく時間がかかっています。
ソートにディスクを使いまくっているので、9.3になったらディスクの使い方がまずくなったのか?と思いましたので、work_memを300MBに増やして再度実行してみました。
※ぐぐると、 external merge Diskの量だけwork_memに積めばOKなことが書いてあるのですが、実際はexternal merge Diskより多くのメモリを確保しないとメモリを使ってソートしてくれませんでした。今回のSQLの場合、work_memを276610kB確保する必要がありました。
9.0の実行結果は以下の通りです。
# EXPLAIN ANALYZE select u.uid,COUNT(u.tokuten),m.hitokoto FROM myuserview m ,userreview u WHERE m.uid = u.uid GROUP BY u.uid,m.hitokoto ORDER BY COUNT(u.tokuten) DESC;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
-----------------------
Sort (cost=272469.15..274823.18 rows=941611 width=291) (actual time=3018.428..3021.111 rows=26204 loops=1)
Sort Key: (count(u.tokuten))
Sort Method: quicksort Memory: 3985kB
-> GroupAggregate (cost=157852.62..179038.87 rows=941611 width=291) (actual time=2432.474..3006.945 rows=26204 loop
s=1)
-> Sort (cost=157852.62..160206.65 rows=941611 width=291) (actual time=2432.464..2570.835 rows=946356 loops=1
)
Sort Key: u.uid, myuser.hitokoto
Sort Method: quicksort Memory: 277269kB
-> Hash Join (cost=3270.99..64422.35 rows=941611 width=291) (actual time=47.316..816.311 rows=946356 lo
ops=1)
Hash Cond: (u.uid = myuser.uid)
-> Seq Scan on userreview u (cost=0.00..39965.11 rows=941611 width=12) (actual time=0.009..202.23
3 rows=946356 loops=1)
-> Hash (cost=2455.44..2455.44 rows=65244 width=288) (actual time=47.250..47.250 rows=65244 loops
=1)
Buckets: 8192 Batches: 1 Memory Usage: 3744kB
-> Seq Scan on myuser (cost=0.00..2455.44 rows=65244 width=288) (actual time=0.020..27.486
rows=65244 loops=1)
Total runtime: 3030.406 ms
実行時間が半分以下になりました。
続いて9.3の実行結果です。
実行時間が20%になりましたが、それでも9.0での実行に遠く及びません。
この後
実行時間が半分以下になりました。
続いて9.3の実行結果です。
# EXPLAIN ANALYZE select u.uid,COUNT(u.tokuten),m.hitokoto FROM myuserview m ,userreview u WHERE m.uid = u.uid GROUP BY u.uid,m.hitokoto ORDER BY COUNT(u.tokuten) DESC;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
-----------------------
Sort (cost=269816.36..272183.87 rows=947004 width=290) (actual time=92436.600..92440.232 rows=26224 loops=1)
Sort Key: (count(u.tokuten))
Sort Method: quicksort Memory: 3986kB
-> GroupAggregate (cost=156871.88..175811.96 rows=947004 width=290) (actual time=91778.152..92427.445 rows=26224 lo
ops=1)
-> Sort (cost=156871.88..159239.39 rows=947004 width=290) (actual time=91778.083..91913.285 rows=947105 loops
=1)
Sort Key: u.uid, myuser.hitokoto
Sort Method: quicksort Memory: 276610kB
-> Hash Join (cost=3253.85..62867.48 rows=947004 width=290) (actual time=45.518..925.404 rows=947105 lo
ops=1)
Hash Cond: (u.uid = myuser.uid)
-> Seq Scan on userreview u (cost=0.00..38306.04 rows=947004 width=12) (actual time=0.012..329.67
0 rows=947105 loops=1)
-> Hash (cost=2437.82..2437.82 rows=65282 width=287) (actual time=45.460..45.460 rows=65291 loops
=1)
Buckets: 8192 Batches: 1 Memory Usage: 3746kB
-> Seq Scan on myuser (cost=0.00..2437.82 rows=65282 width=287) (actual time=0.011..27.265
rows=65291 loops=1)
Total runtime: 92451.731 ms
(14 行)
実行時間が20%になりましたが、それでも9.0での実行に遠く及びません。
この後
- 別サーバーにPostgreSQL9.0をインストールしてSQLを実行し、遅いことを確認 → PostgreSQL9.3は悪くない
- GROUP BY句を使うと遅いのか → 遅くない
- GROUP BY句にインデックスをはっていない列があると遅いのか → 遅くない
- GROUP BY句に数字だけの列だと速いか → 速い
- 日本語を含む列をGROUP BY句にいれると遅いこと
- GROUP BY句はソートが実行される
- そういえば元々はどうやってPostgreSQLをインストールしてたんだっけ?
- ロケールをCにしたのは、ソートが関係していたんだったような…あー、もしかして、UTF8だとソートが遅い?