こんにちは。三苫です。

この記事はTECHSCORE Advent Calendar 2014、1日目の記事です。

本日はPostgreSQL経験の長い私がMySQLのシステムを新規に構築、運用するときにハマった泣いたリストをチェックリストとして共有します。

PostgreSQLしかまともに使ったことがない人が準備不足でMySQLに突撃すると何が起こるか知っておきましょう。

@tmtms さんのMySQLユーザーがPostgreSQLを触ってみたメモと重なる部分も多いですが、逆から見た視点ということでご容赦を。

必ずチェックが必要

1. SQLモードをPOSTGRESQLにするのが良さそう
   • INSERT時にエラーをスルーするような挙動を避ける
   • 型の上限値を超えた数値をINSERTした時に、エラーを出さずに最大値をセットするような挙動を避ける
     （この挙動を知った当時はそれなりに衝撃でした…）
   • GROUP BYが曖昧な指定を許さないようにする。
2. 文字コード指定はutf8ではなくutf8mb4にする
   • utf8は3バイトまでのutf8文字しか対応していない。utf8mb4が4バイト文字にも対応している。
   • MySQLでは文字コード範囲外の文字が出てきた場合は以降の文字がすべて削除されてINSERTされる。（SQLモードによって違うかも）
   • 特にユーザーからどんな文字列が入力されるかわからないシステムの場合は必須。iOSなどの絵文字がアウトになるもよう？
3. 文字列はデフォルトcase sensitiveで扱うよう設定しておく
   • MySQLはcase insensitiveがデフォルト。
   • unique index も特に指定がなければこれに従う
4. 外部キー制約を必ずつける
   • innodbの場合、auto_incrementが再起動時に巻き戻ることがあるので外部キー制約をつけておかないと、同じIDが再度発行され親が削除された子レコードが別の親に紐付く場合がある。
   • 同じIDが二度と振られてはいけない場合はシーケンステーブルを作る必要がある
     ID項目がExportやURLなどで外部に露出するような設計にしている場合は特に注意。
   • railsで構築しているシステムであればforeignerというgemを使うと外部キー制約を持つマイグレートファイルが書きやすい。

意識しておけばいい

1. JOINが遅い
   • PostgreSQLのようにJOINのアルゴリズムが多彩ではないので集計時に複雑なJOINを避けたり一時表を使うなどの置き換えが必要な場合がある
   • MySQLで数時間、PostgreSQLで数分みたいなことも。
2. デフォルトのトランザクション分離レベルがREPEATABLE READ
   • PostgreSQLよりも高い分離レベルだが、思わぬところでSELECT時にロックがかかる場合がある。
     READ COMMITEDにしておけばPostgreSQLと同じ気分で開発できる。
     ※ただしREAD COMMITEDにしておいたらロックしないかどうかは検証していない。
   • next key lock なども注意しておかなければいけない。詳細は複雑過ぎて説明する気が起きないので各々で調べて欲しい。
3. バッチ処理で長大なトランザクションを避ける
   • 原因不明だがレプリケーションが正常に行われないケースがあった。
     RDSでも起きないという保証はないので避けるのが無難？
   • 長大なトランザクションとはだいたい１時間程度（で、問題が発生した）。
4. ファイルなど大きなデータをDBに格納する場合
   • max_allowed_packetを大きくしておかないとINSERTできないことがあるので気をつける。
5. Null値のソート順がPostgreSQLとMySQLで異なる

知っとけばいい

1. カラム数の上限がPostgreSQLとは違う（以下innodbの場合）
   • varcharを多用すると、早めに限界が来る。
   • innodb_file_format=Barracuda で限界を緩和することができる
   • カラムを動的に増減させるようなシステムでなければまず問題にはならない
   • ※PostgreSQLはそれはそれでカラム追加の上限が1600程度。しかもDROP COLUMNしても回復しない。などの罠がある。
     ※CREATE/DROPを繰り返し、これ以上カラムを追加できなくなったテーブルをどうしてもオンラインで再びカラムが追加できる状態に戻したい場合は、トランザクション中で新しいテーブルを作りデータを移行し、リネームしてすげ替えるという荒業もあります。
2. DDLがトランザクション内で扱えない
   • 動的なカラム追加をトランザクションで保護したいようなシステムでなければまず問題にはならない
     （が、自分は問題になった）
3. 原因不明のメモリリーク
   • 大量のテーブルがあるDBのカタログテーブルを頻繁に参照するとメモリリークが発生する可能性がある
     （原因不明, 確か当時のバージョンは5.5系）
   • もう過去の話なので気にしていない。（原因究明は諦めた）
   • ※メモリリーク系のバグはPostgreSQLも時々あります。踏みました。（これは原因究明済み）

感想

いかがでしょう。どれもMySQLで開発されている方には常識だったりする事なのかもしれないですが、やはり事前検証が甘いと思わぬ地雷を踏んでしまいますね。自分自身、振り返ると踏める地雷は軒並み踏んで行ったという自負があります。RDBMSなんて枯れたミドルウェアかるーく使うならどれも同じなんて思わずに、各RDBMSの特性を理解した上で使わないと思わぬ落とし穴が待ってます。お気をつけて！

私は今、MySQLで受けた心の傷をいやすため、PostgreSQLで元気に開発しています。MySQLのスレッドモデルによるコネクションプールいらずな軽快さはやっぱりいいなぁと思うこともありますし、PostgreSQL特有のVACUUM作業に悩まされることも多いですが、何事もトレードオフです。

一度や二度の失敗でめげずに楽しくいろいろなデータストアを検証して開発していきましょう。

PostgreSQLで踏みまくった地雷も今後お伝えできればと思います。