APRS Tier2 Network, T2FUKUOKA, KetaiTracker
2007年（17年前）にKetaiTracker用にデータベースの運用を始めました。 当時からのデータを蓄積していますが、全データ記録テーブル（永年保存のテーブル）は2023年2月時点で355,707,148件を越えました。 既に、ビックデータといえる状況です。
開始当時は何の考えもなく蓄積していましたが、さすがにデータ数が大きくなりすぎ、レスポンスの問題が数年前から顕著になってきました。 ハードウェアの割り当てを増やすなどして、力技でその場しのぎをしてきましたが... INSERT時のインデックスの更新に時間を要するようになり、さすがに限界です。
データベースを運用しているサーバのOSをFreeBSD 13に変更しました。現行マシンのUpgradeではなく、新規サーバ（仮想環境/ CPUやメモリー割り当ては変更無）を用意しインストールし、ファイルシステムをZFSにしました（これまではUFS）。
PostgreSQLを15に更新しました（これまでは、PostgreSQL 12）。 しかし、これは根本的な解決ではありません。
PostgreSQLの過去のバージョンからテーブルのパーティショニングをサポートするようになり、これを使えば、今回のような時系列のビックデータの性能向上が見込めますが、一度、データをバックアップしてから、テーブルの再定義、リストアーの作業という数日の仕事になるため、躊躇してました。 それでも、今回は思い切ってパーティショニングに挑戦しました。
既存のテーブルを後付けでパーティショニングにできないか調べて見ましたが、その情報は見つからず......
パーティショニングの方法はいくつかありますが、今回はデータの性質上、データの日付で分割することにしました。 問題は、１年毎にするか、月毎にするか..... で、最終的に月毎にパーティショニングすることにしました。その分、テーブルの数は増えましたが.... これにより、一つのテーブルのデータ数は 1,501,000件ほどになりました。
public | aprstrack_forever | partitioned table | yahiro public | aprstrack_forever_2007_01 | table | yahiro public | aprstrack_forever_2007_02 | table | yahiro public | aprstrack_forever_2007_03 | table | yahiro public | aprstrack_forever_2007_04 | table | yahiro public | aprstrack_forever_2007_05 | table | yahiro public | aprstrack_forever_2007_06 | table | yahiro public | aprstrack_forever_2007_07 | table | yahiro public | aprstrack_forever_2007_08 | table | yahiro public | aprstrack_forever_2007_09 | table | yahiro public | aprstrack_forever_2007_10 | table | yahiro public | aprstrack_forever_2007_11 | table | yahiro public | aprstrack_forever_2007_12 | table | yahiro public | aprstrack_forever_2008_01 | table | yahiro public | aprstrack_forever_2008_02 | table | yahiro public | aprstrack_forever_2008_03 | table | yahiro ... ... ... public | aprstrack_forever_2049_04 | table | yahiro public | aprstrack_forever_2049_05 | table | yahiro public | aprstrack_forever_2049_06 | table | yahiro public | aprstrack_forever_2049_07 | table | yahiro public | aprstrack_forever_2049_08 | table | yahiro public | aprstrack_forever_2049_09 | table | yahiro public | aprstrack_forever_2049_10 | table | yahiro public | aprstrack_forever_2049_11 | table | yahiro public | aprstrack_forever_2049_12 | table | yahiro
PHPでCREATE TABLE IF NOT EXIXTS aprstrack_foever_YYYY_MM ....を毎回実行するのもレスポンスが悪くなりそうなので、事前に作成することにしました。
作成するテーブルの数がありますので、テーブルを定義するスクリプトを作成して一気に作成しました。 とりあえず、2049年分まで作成。26年後まで放置できます。
と安心していたら、
EXPLAIN SELECT * FROM aprstrack_forever WHERE lastshow >= '2023-01-01';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..0.38 rows=10 width=119)
-> Append (cost=0.00..101614.33 rows=2655552 width=119)
-> Seq Scan on aprstrack_forever_2023_01 aprstrack_forever_1 (cost=0.00..48045.57 rows=1501326 width=118)
Filter: (lastshow >= '2023-01-01 00:00:00'::timestamp without time zone)
-> Seq Scan on aprstrack_forever_2023_02 aprstrack_forever_2 (cost=0.00..36306.25 rows=1133940 width=118)
Filter: (lastshow >= '2023-01-01 00:00:00'::timestamp without time zone)
-> Seq Scan on aprstrack_forever_2023_03 aprstrack_forever_3 (cost=0.00..12.38 rows=63 width=400)
Filter: (lastshow >= '2023-01-01 00:00:00'::timestamp without time zone)
-> Seq Scan on aprstrack_forever_2023_04 aprstrack_forever_4 (cost=0.00..12.38 rows=63 width=400)
Filter: (lastshow >= '2023-01-01 00:00:00'::timestamp without time zone)
-> Seq Scan on aprstrack_forever_2023_05 aprstrack_forever_5 (cost=0.00..12.38 rows=63 width=400)
Filter: (lastshow >= '2023-01-01 00:00:00'::timestamp without time zone)
-> Seq Scan on aprstrack_forever_2023_06 aprstrack_forever_6 (cost=0.00..12.38 rows=63 width=400)
Filter: (lastshow >= '2023-01-01 00:00:00'::timestamp without time zone)
-> Seq Scan on aprstrack_forever_2023_07 aprstrack_forever_7 (cost=0.00..12.38 rows=63 width=400)
Filter: (lastshow >= '2023-01-01 00:00:00'::timestamp without time zone)
-> Seq Scan on aprstrack_forever_2023_08 aprstrack_forever_8 (cost=0.00..12.38 rows=63 width=400)
Filter: (lastshow >= '2023-01-01 00:00:00'::timestamp without time zone)
-> Seq Scan on aprstrack_forever_2023_09 aprstrack_forever_9 (cost=0.00..12.38 rows=63 width=400)
Filter: (lastshow >= '2023-01-01 00:00:00'::timestamp without time zone)
-> Seq Scan on aprstrack_forever_2023_10 aprstrack_forever_10 (cost=0.00..12.38 rows=63 width=400)
Filter: (lastshow >= '2023-01-01 00:00:00'::timestamp without time zone)
-> Seq Scan on aprstrack_forever_2023_11 aprstrack_forever_11 (cost=0.00..12.38 rows=63 width=400)
Filter: (lastshow >= '2023-01-01 00:00:00'::timestamp without time zone)
....
....
....
....
....
orz! 余計にテーブルを準備している分、なめるテーブルが増えているため、レスポンスに悪影響を与えています。
EXPLAIN SELECT * FROM aprstrack_forever WHERE lastshow >= '2023-01-01' AND lastshow <= current_timestamp;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..0.43 rows=10 width=118)
-> Append (cost=0.00..113336.03 rows=2635588 width=118)
Subplans Removed: 322
-> Seq Scan on aprstrack_forever_2023_01 aprstrack_forever_1 (cost=0.00..55552.21 rows=1501326 width=118)
Filter: ((lastshow >= '2023-01-01 00:00:00'::timestamp without time zone) AND (lastshow <= CURRENT_TIMESTAMP))
-> Seq Scan on aprstrack_forever_2023_02 aprstrack_forever_2 (cost=0.00..41975.95 rows=1133940 width=118)
Filter: ((lastshow >= '2023-01-01 00:00:00'::timestamp without time zone) AND (lastshow <= CURRENT_TIMESTAMP))
(7 rows)
なめるテーブルを減らすため、WHERE句の条件を増やしても、それなりの処理が加わるため最適解ではないようです。 結局のところ、月末に crontab で翌月分のテーブルを作成した方がよさそうです。
それでもパーティショニングの効果は絶大で、INSERT時の処理はかなり速くなりました。