MySQL 5.0時代(2011年後期現在での最新は5.5.17)と、
PostgreSQL 8.0時代(2011年後期現在での最新は9.1.1)の知識で言います。
まあ、昔話と一般論を述べてると思ってもらえれば結構です。
同時期に作成したらしいベンチマークが或るので、貼らせてもらいます。
今では更に事情が変わってる可能性があります。
同時期に作成したらしいベンチマークが或るので、貼らせてもらいます。
#大小文字の打ち分けが面倒なので、固有名詞は小文字で圧します。
端的に言えば、mysqlはマニュアル車、postgresqlはオートマチック車です。
mysqlは実は、特定状況下でのみ、高性能を発揮します。らしいです。
特定状況下は、筆者は存じません。
大量のチューニングパラメタが或るのも、特徴でしょう。
これは、悪い特徴です。適当に使うと、まったく性能は出ません。
mysqlの強力なアドバンテージは、レプリケーションの内蔵。
それに「尽き」ます。それだけしか無い。
レプリケーションの設定は、非常に簡単です。
「同期」とは言えませんが、更新のタイムラグは概ね無視できる範囲でしょう。
一方、mysqlは、不可避レベルの「苦手」が幾つかあります。
SQLの文中で、select以外では、テーブルの自己結合が出来ません。
insertの文中で、既存のレコード数を数えて、+1してinsertしたい、
って良くあると思うですが、mysqlのinnodb/myisamではこれは不可能です。
これに限らず、複雑なSQLを投入すると、
不適切なindexを使う、もしくはindexを諦める場合があります。
2段サブクエリ程度で、もう駄目な感触です。
そのための FORCE INDEX なる、SQLの独自構文があります。
#どんだけ手動なのかと
mysqlで、大量のinsert/delete/updateをすると、
性能がゴッソリ落ちます。
大量の、というのは、数十万レコード、程度ですね。
故に、遅延insertなる方言があります。
なんかindexの作成が、下手なのかなあ。という感触です。
ランダム文字列系の、ランダム順insertは、かなり遅い。と思う。
postgresqlで同程度のクエリを流したときの、
数10倍、時間が掛かることがあります。
ちなみに、postgresqlで30分で終わるinsertが、
mysqlのmyisamで3時間、innodbで12時間でした。
信じるも八卦。信じないも八卦。
また、全部deleteする場合は、trancateを使おうね、とか
いや、運用でそんなケースそんなにあるのか？と
逆に聞きたいのですが。
手動でやってるなら、truncate使いますよそりゃあ。
postgresqlの問題点としては、vacuumを懸念している人は多いと思いますが、
実は、mysqlもvacuumをやっているのです。
しかも1レコードづつ。
いや、オフィシャルで「mysqlでvacuumやってます」
なんて記述は一つもないのですが、
遅いなあ、と思ってstraceしてみたら、
ファイルコピーしてやがった事がありました。
いや俺今delete fromしたよね？なんでファイルコピーが必要？みたいなね。
トランザクション量がもの凄い多い場合、begin〜commitが遠い場合は、
これまた性能がゴッソリ落ちます。
数10レコードづつcommitすれば劇的に改善しますが。
あと、myisamテーブルは壊れる可能性があります。
修復コマンドがあるくらいですから。
実は筆者の経験で、innodbが壊れたことも一度ありました。
実験機だったので、テストデータを入れ直しましたが。
mysql自身の苦手、とは違いますが、いや筆者が苦手ってことか。
ユーザ登録をgrant構文でやるのはどうにも気持ち悪いです。
一方、postgresqlは、設定項目が非常に少なく、
起動するだけならすぐです。
しかも、結構複雑な、3段サブクエリを投入しても、
なんとか結果を返します。
適切にindexを貼れば、ちゃんと使ってくれます。
普通に使ってたら、特に「○○が苦手」という感じはありません。
postgresql-8以降、使ってないので判りませんが
(しばらくmysqlの案件が続いたので)
簡易なvacuumをマメにやるようになってるらしいので、
テーブルロックレベルのvacuumは短時間で済むようになってる。らしいです。
とは言え、大量のinsert/updateを続けると、徐々に性能が落ちてくるのは確か。
数千レコードを舐めて再構築するタイプのトリガを、書かざるを得なかった事があり、
いや、仕事で書いてね、って頼まれたんですが、
テストで何度か回しているうちに、あれ？遅くなってきた？みたいな感触はあります。
ただそれでも、2分が5分になるだけです。
倍にはなってるんだけど、元々が「待っていられる」時間です。
最悪、トランザクションスクリプトにvacuum fullを含めちゃうのは手でしょう。
総評としては、
mysqlは、SQLサポートそのものが不完全なので、SQLの記述に手間取る。
結構ありがちな状況で、ゴッソリ性能が落ちやすい感触。
postgresqlは、問題の先送りで、
更新が多いっぽいときは、トランザクションにvacumm fullと書いちまえ。
結論としては、
どうしてもレプリケーションを使いたければmysqlしかない。
その他は、postgresql使っとけ。
もうそんな気持ちです。
