トップ ソフト 雑記 日記 リンク

2015年12月8日火曜日

面倒な使い方の事例(PostgreSQL Advent Calendar 2015)

PostgreSQL Advent Calendar 2015の8日目です。

去年はAWSのRDS for PostgreSQLを使い始めたという記事とlibpqxxを使った記事を書きました。
今回は、費用と制約から発生している面倒な事例の紹介です。

拙作「ふぁぼるっく」がVPSからAWSへ移行した話は、今月1日の記事に書きました。
その構成です。
今回はPostgreSQLのアドベントカレンダーですので、PostgreSQLの部分だけ抜き出します。

家のアイコン:Liam Thynne

1つのシステムですが、PostgreSQLが3つ居ます。
まずは、RDS。これが一番のメインで、アプリケーションサーバは基本的にここへアクセスしに来ます。
PostgreSQLとほぼ同じ機能を持っているので、いままでPostgreSQLにアクセスしていたプログラムは接続先を書き換えるだけで使えるようになります。

そしてもう1台は、EC2上に構築されたPostgreSQL。
上でRDSは「ほぼ同じ機能」と書きましたが、RDSだけでは実現できないこともあります。
それがサードパティー製拡張モジュールの利用。

標準のRDS for PostgreSQL 9.4.4で使えるモジュールは以下のものです。(2015年11月。9.4.5では3つ増えています
rds=> SELECT name,default_version,comment FROM pg_available_extensions ORDER BY name;
          name          | default_version |                               comment
------------------------+-----------------+---------------------------------------------------------------------
 btree_gin              | 1.0             | support for indexing common datatypes in GIN
 btree_gist             | 1.0             | support for indexing common datatypes in GiST
 chkpass                | 1.0             | data type for auto-encrypted passwords
 citext                 | 1.0             | data type for case-insensitive character strings
 cube                   | 1.0             | data type for multidimensional cubes
 dblink                 | 1.1             | connect to other PostgreSQL databases from within a database
 dict_int               | 1.0             | text search dictionary template for integers
 dict_xsyn              | 1.0             | text search dictionary template for extended synonym processing
 earthdistance          | 1.0             | calculate great-circle distances on the surface of the Earth
 fuzzystrmatch          | 1.0             | determine similarities and distance between strings
 hstore                 | 1.3             | data type for storing sets of (key, value) pairs
 intagg                 | 1.0             | integer aggregator and enumerator (obsolete)
 intarray               | 1.0             | functions, operators, and index support for 1-D arrays of integers
 isn                    | 1.0             | data types for international product numbering standards
 ltree                  | 1.0             | data type for hierarchical tree-like structures
 pg_buffercache         | 1.0             | examine the shared buffer cache
 pg_freespacemap        | 1.0             | examine the free space map (FSM)
 pg_prewarm             | 1.0             | prewarm relation data
 pg_stat_statements     | 1.2             | track execution statistics of all SQL statements executed
 pg_trgm                | 1.1             | text similarity measurement and index searching based on trigrams
 pgcrypto               | 1.1             | cryptographic functions
 pgrowlocks             | 1.1             | show row-level locking information
 pgstattuple            | 1.2             | show tuple-level statistics
 plcoffee               | 1.4.3           | PL/CoffeeScript (v8) trusted procedural language
 plls                   | 1.4.3           | PL/LiveScript (v8) trusted procedural language
 plperl                 | 1.0             | PL/Perl procedural language
 plpgsql                | 1.0             | PL/pgSQL procedural language
 pltcl                  | 1.0             | PL/Tcl procedural language
 plv8                   | 1.4.3           | PL/JavaScript (v8) trusted procedural language
 postgis                | 2.1.5           | PostGIS geometry, geography, and raster spatial types and functions
 postgis_tiger_geocoder | 2.1.5           | PostGIS tiger geocoder and reverse geocoder
 postgis_topology       | 2.1.5           | PostGIS topology spatial types and functions
 postgres_fdw           | 1.0             | foreign-data wrapper for remote PostgreSQL servers
 sslinfo                | 1.0             | information about SSL certificates
 tablefunc              | 1.0             | functions that manipulate whole tables, including crosstab
 test_parser            | 1.0             | example of a custom parser for full-text search
 test_shm_mq            | 1.0             | Test code for shared memory message queues
 tsearch2               | 1.0             | compatibility package for pre-8.3 text search functions
 unaccent               | 1.0             | text search dictionary that removes accents
 uuid-ossp              | 1.0             | generate universally unique identifiers (UUIDs)
 worker_spi             | 1.0             | Sample background worker
 xml2                   | 1.0             | XPath querying and XSLT
(42 行)
ふぁぼるっくでは、話題になっているツイートに含まれる単語からトレンドを抽出しています。
PostgreSQLで単語での分解をするとなると、mecabを使ったtextsearch_jaモジュールですね。
しかし、上の一覧には含まれていません。PostgreSQL 9.4の標準モジュールではないからです。
そしてサードパティー製extensionのインストールもできないとなると、RDSで日本語の形態素解析はできないということです。

EC2の起動スクリプト内で、エフェメラルディスク(インスタンスが持っている揮発ディスク)にデータベースを作成し、外部テーブルでRDSを見に行く設定を作り上げます。
後は、EC2のcronで定期的に外部テーブルから文章を取り出して形態素解析し、再び外部テーブル経由でRDSにある結果用テーブルへ書き込みます。

最後の1つは、自宅サーバとして運用しているPostgreSQLです。
ふぁぼるっくのデータは2006年分から保持しています。
しかし過去のデータはなかなか参照される機会も無く、RDSに置いておいてもお金が掛かるばかりです(RDS移行初期は置いていたが死にそうになった)。

テーブルのみで、132億2336万6699タプル、1.2TB。
favlook=# SELECT SUM(reltuples) AS タプル数
favlook-#      , SUM(mbytes) AS mbytes
favlook-# FROM (
favlook(#   SELECT relname
favlook(#        , reltuples::bigint
favlook(#        , (relpages / 128) AS mbytes
favlook(#   FROM pg_class
favlook(#   WHERE relkind='r'
favlook(#   ORDER BY mbytes DESC
favlook(# ) a;
  タプル数   | mbytes
-------------+---------
 13223366699 | 1207722
(1 行)
インデックス込みで、2.1TB。
favlook=# SELECT SUM(reltuples) AS タプル数
favlook-#      , SUM(mbytes) AS mbytes
favlook-# FROM (
favlook(#   SELECT relname
favlook(#        , reltuples::bigint
favlook(#        , (relpages / 128) AS mbytes
favlook(#   FROM pg_class
favlook(#   -- WHERE relkind='r'
favlook(#   ORDER BY mbytes DESC
favlook(# ) a;
  タプル数   | mbytes
-------------+---------
 47350820338 | 2108039
(1 行)
dfコマンドでも、だいたい同じぐらいの容量になっています。
$ df
Filesystem            Size  Used Avail Use% Mounted on
/dev/md127            7.2T  2.2T  4.7T  32% /mnt

自宅サーバでは、一定期間ごとに継承テーブルを作り、その親テーブルをRDSから外部テーブルとして参照しています。
パフォーマンスの問題で継承テーブルは100以上継承しない方がいいとLet's Postgresには書かれていたのですが、1つのリレーションは100以上に継承してしまっています。
リレーションによっては1テーブルあたりのプライマリキーがメモリに乗らないサイズになってしまっているので、これ以上分割するのも厳しいし、まとめるのも厳しいと、前にも後ろにも進めないような設計になっています。

RDS側からは、自身が持つ最近のデータと、外部テーブルの古いデータが見えており、条件によってアクセスするテーブルを切り替えたりUNIONしたりして読み込んでいます。
また外部テーブルを使う際に、複雑な条件をつけるとテーブル全てを読み込んでくるようなSQLが発行されてしまうため、場合によってはアプリケーションサーバから直接自宅サーバへ接続してデータを取得し、プログラム上で取得したデータを元に再度RDSへクエリーを投げたりするような処理もあります。
外部テーブルはJOINに弱いと覚えておくと良いでしょう。

あとは、毎日自宅サーバからRDSへ古くなったデータを取りに行き、pg_bulkloadで自宅のPostgreSQLに挿入しています。


もともと自宅サーバはレプリケーションで負荷分散をしていました。しかし時々レプリケーションに失敗してスレーブの作り直しが発生し、データが多すぎるために復旧まで1週間ほどかかるという状態でした。酷い場合にはpg_basebackup後のWAL追っかけ再生が失敗して運用に載る前にスレーブが死ぬなど、辛い状況に陥ったのと、最近ではpg_bulkloadを使う関係で、マスター単体で運用しています。
サーバが一台余っていてもったいないので、はやくマルチマスタが実現すると良いですね。Postgres-XCはちょっと怖くて触っていないです(怖い=XCから戻って来れない&XCが本体に追従していくか不安だった)。

自宅サーバにもSSDを積んでいた時期があるのですが、1年ぐらいで書き込みできなくなってしまうので、最近はHDDでRAID5にしています。一生寿命を気にしなくていい高速ストレージが出ると、ストレージが原因の負荷分散もしなくて良さそうになるので、早くお手頃な値段で買えるようになるといいですね。


ということで、こんな運用もあるよ、という紹介でした。

0 件のコメント:

コメントを投稿

広告