去年はAWSのRDS for PostgreSQLを使い始めたという記事とlibpqxxを使った記事を書きました。
今回は、費用と制約から発生している面倒な事例の紹介です。
拙作「ふぁぼるっく」がVPSからAWSへ移行した話は、今月1日の記事に書きました。
その構成です。
今回はPostgreSQLのアドベントカレンダーですので、PostgreSQLの部分だけ抜き出します。
家のアイコン：Liam Thynne
１つのシステムですが、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
パフォーマンスの問題で継承テーブルは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 件のコメント:
コメントを投稿