「MySQLを辞めるべき○個の理由」 或いは 「本当にあったMySQLの怖い話」

1. InnoDBが外れることがある
1. innodb_data_file_pathを迂闊に書きかけてそのまま再起動すると、InnoDBストレージエンジンがシレっと外れる
2. しかもCREATE TABLE ENGINE=InnoDBは、MyISAMに代替して、シレッと継続する。気づかないと、トランザクションが効かなくなって迷宮入りする。
3. InnoDBテーブルは読めなくなる。SELECT等を発行して始めて発覚する。
2. レプリケーションは止まることがある
1. http://nippondanji.blogspot.jp/2009/03/MySQL10.html
2. しかも復旧が面倒臭い
3. http://d.hatena.ne.jp/rockstar2007/20110128/1296230962
3. MyISAMは壊れることがある
1. http://dev.MySQL.com/doc/refman/4.1/ja/corrupted-myisam-tables.html
4. InnoDBは壊れることがある
1. http://dev.MySQL.com/doc/refman/5.1-olh/ja/innodb-backup.html
5. 大量のレコードを、単一トランザクションでINSERTすると、激遅い
1. http://dev.MySQL.com/doc/refman/4.1/ja/insert-speed.html　「挿入の速度をあげる方法」という項目の存在
6. 大量のレコードを、単一トランザクションでDELETEすると、激遅い
1. http://blog.livedoor.jp/sasata299/archives/51363104.html
2. http://d.hatena.ne.jp/bco/20101222/1293023066
3. http://dev.MySQL.com/doc/refman/5.1/ja/delete-speed.html
1. 行の削除に要する時間は、完全にインデックス数に比例します
1. ってインデックスの意味ねーだろ。
2. テーブル内のすべての行を削除する場合は、TRUNCATE TABLE tbl_name 
のほうがDELETE FROM tbl_nameを使用するより高速です。「TRUNCATE 構文」を参照してください。
1. ってそんなケースがどんだけあるんだゴルア


1. 新しいテーブルに必要なレコードだけCREATEしなおしてね。とかそんなのメンテ入れなきゃ出来ねえだろうがゴルア
7. ALTER TABLEは恐ろしく時間がかかることがある。
1. http://d.hatena.ne.jp/LukeSilvia/20090315/p1
8. UPDATE文のWHEREをANDと書き間違えると、全件書き換えてしまう
• UPDATE テーブル SET ナンチャラ=カンチャラ WHERE どうたら
• UPDATE テーブル SET ナンチャラ=カンチャラ AND どうたら
• 全てのレコードの、ナンチャラには0が入ります。
• http://dev.MySQL.com/doc/refman/5.1-olh/ja/type-conversion.html　「オペランドを適合化するため、型変換が起こります」頼んでねーよ。
• PHPと組むと、迷宮入りする可能性があります。
• 正直、SQLの仕様として、WHEREを必須にするべきでしょう。データベースの意味を考えれば、「全レコード置換」がどれほどの頻度なのか。
9. 一部のカラム型は、not null制約が、効果がない。
1. 例えばdatetime型は、0年０月0日が入ります。実際の処理を考えれば、nullと何が違うんだって話。もちろんおお違いです。むしろ面倒臭い。nullの判定に加えて、0年の判定をしなければならなくなる。
1. PHPと組むと、実は==0で判断できてしまう。何がしたいんだPHP+MySQL。
10. カラム長を超えるデータを、勝手に丸める。
1. 文字列は、ブツ切れる
2. 整数は、2^Xに収まる
3. って言うか、エラーだすとこだろゴルア
4. って言うか、SET SQL_MODE= TRADITIONAL ってやるといいらしい。なんでそれがデフォルトになってないのか個一時間。
11. なんかトランザクションが遅いなー、と思ったら、flush tablesをやったら早くなった気がする？？
1. http://dev.MySQL.com/doc/refman/5.1/ja/flush.html
2. 「query cacheもします」って？？
3. 「メモリ使用を向上させる為にクエリ キャッシュをデフラグ」
4. vacuum要りません。で売ってるのに、デフラグとか言っちゃだめじゃん！

MySQLは、ストレージエンジンという概念を導入していて、表面上は先進に見えますが、それ故に、細かいオーバーヘッドが点在していて、簡単にしかも大幅に性能が落ちます。そんなことに気を付けながら書かなきゃならないなら、「offset 100０以上は禁止」とかしてくれたほうが余程マシです。

強烈な型変換は、本当に何がしたいのか判らない。「データを保存する」という要職にありながら、データの改変をするって意味判りません。オフィシャル言い訳  http://dev.mysql.com/doc/refman/5.1/ja/constraint-invalid-data.html んん、やっぱりストレージエンジン駄目だな。そしてfailsafeを誤解してる感。

正直、「「象牙の塔」リレーショナルデータベースシステム」と言わざるを得ません。

※象牙の塔は、どこぞの本で読んで感銘を受けたフレーズです。
http://gogen-allguide.com/so/zougenotou.html

文句を言うなら使うなって？もちろん使いませんよ。ここ最近の案件で、小生の意思でMySQLを「選んだ」ことは一度もありません。決まっちゃってるから使ってるだけ。お客がゴネるから使ってるだけ。

MySQLの使い初めはお手軽だが、使いつづけるのはコストが掛かる

小生はもうぶっちゃけ、MySQL要らなくね？と思ってるのですが、顧客がそれを許さない場合が多々あります。「有名なものだから安心」。日本人は極めてその傾向が強い民族のようですが、それが当てはまらない場合もあることを知るべきでしょう。いや、厳密には「安心の理由」を知るべき。

メンテナンスが大変だが、情報があるからなんとかなる。そういう話です。可能か否かであって、コスト量の話はしてない。

ちょっと検索してみれば、MySQLと心中してる感じのブログが多々ありますが、

まず、後述のリンクのブログを中傷するものではないことは強調しておきます。

http://nippondanji.blogspot.jp/2010/03/innodb.html
たった3秒でInnoDBのデータローディングが快適になるライフハック

http://nippondanji.blogspot.jp/2009/03/MySQL7.html
さらにMySQLを高速化する7つの方法

http://nippondanji.blogspot.jp/2009/04/MySQL10.html
やってはいけない!!MySQLに悲鳴をあげさせる10の方法

http://nippondanji.blogspot.jp/2009/05/MySQL.html
限界までMySQLを使い尽くす!!

http://el.jibun.atmarkit.co.jp/garyotensei/2012/07/MySQL-465d.html
MySQLのサブクエリは危険？――深まる謎

MySQLを「使わされて」「困ってる」人にとっては、有用な記事ばかりです。

前述のリンク先の幾つかのTIPSは、非常に役立つものですが、「デフォルトのままでは全く使い物にならない」「標準機能では全く使い物にならない」という事を本質的に意味しています。ですので、小生は断言します。MySQLは「データベースツクール」であることを。ツクール遊びがお好きな人は使えばよろしい。

だってアナタ、大量のデータをアップロードする時には、「正規の手段」のLOAD DATAだと、遅い、というのは周知だと思いますが、その解法がまた酷い。

1. CSVストレージで空のテーブルを作成。
2. MySQLを一旦落として、CSVデータファイルをすり替える。
3. 再起動
4. ALTER TABLE ENGINE=InnoDB

こんなの完全に「裏技」の領域ですよ。これがツクールじゃないなら、何だっての？バージョンが進んで互換性が無くなると、また別の解法を探さなければならなくなる可能性があります。たまになら仕方ありませんが、定常運用でこんなことやってられない。ってかこんな危険な方法、マトモな人なら使いません。だって保証がない。理論的に正しいし、ある程度動くであろうことは認めますよ。

忘れちゃいけないのは、MySQLは、「データベースエンジンの選択肢の一つ」もっと下がってみれば「データ保存の手段の一つ」でしかありません。そのために、コレだけの手間暇を掛けるのは、本質的な苦労とは、小生は思いません。

「手段のために手段を選ぶ」のは、プログラマはやってはいけない。これはもちろん小生の持論です。アナタが従う義務は全くありません。故に、MySQL好きな方々が、「喧嘩を売られた」と思う必要は全くありません。

トランザクション＆レプリケーションがあるから、MySQLを使ってる案件は、少なくないと思われます。しかし、リストアに恐ろしく時間がかかり、しかもその回避策の切り札が、前述の裏技だとしたら、課金情報が入ってるテーブルには使いたくない筈。テストしてようがしてまいが。

素のInnoDBは早いらしい。Key Value Storeに匹敵する速度があるらしいです。じゃあなんでデータインポートが遅いのか？

この辺りの「遅い」系の話は、フロントエンドと、ストレージエンジンバックエンドの乖離が本質的問題なのではないかと思います。フロントのSQLパーサ及び、ストレージエンジンのインデックス管理等が、データベースエンジン構造として「遠い」ために、アッサリ性能が落ちるのではないかと。

前述のALTER TABLE式は、バックエンドだけで決着が付きます。故に性能が落ちにくい。多分。

「MySQLをチューニングしなければならない」案件にMySQLを採用するのは間違ってる。と思います。チューニングコストを出せないなら尚更でしょう。

出稼ぎ先では、小生および、もう一人の推しにより、ストレージはMongoDBに移行しつつあります。これまた断言しますが、ソーシャルゲームはもとより、「MySQLで運用できてる案件のほとんどはMongoDBで運用できる」。

もちろん、そのままでは動きません。トランザクションに「頼れない」ですし、色々不都合はあるでしょう。しかしもう、特にソーシャルゲームは顕著ですが「SELECT FOR UPDATEしてりゃなんとかなる」時代ではなくなっています。悠長にレコードロックしてる場合じゃないです。

MySQLの性能を引き出したければ、SQLを使っちゃいけない。らしい。

筆者はMySQLで頑張る気がないので、情勢には疎かったのですが、
出稼ぎ先の技術者に教えてもらいました。

近頃はHandlerSocketなる代物があるらしいのです。
https://github.com/ahiguti/HandlerSocket-Plugin-for-MySQL
http://www.slideshare.net/akirahiguchi/handlersocket-plugin-for-MySQL-4664154

またMySQL自身が、memcache互換APIを内蔵するらしい。
http://dev.MySQL.com/tech-resources/articles/NoSQL-to-MySQL-with-memcached.html

いずれも、サーバ＆クライアント双方のSQLパースをバイパスするのが特徴です。
前者についていえば、性能はかなりのものらしい。InnoDB 5.1で75万クエリ/秒。これが本当であれば、裸のInnoDBは十分に高速である、と言えるでしょう。

RDBMの数々の特徴を放棄すればMySQL/InnoDBでも早くなる。ということは、「ストレージエンジン」という設計思想も、結構なオーバーヘッドなのではないか？と言う気もします。

なにしろ、InnoDBは標準でありながら、MySQLにとっては一介のストレージエンジンですから、my.cnfの記述を間違えると、InnoDBはシレっと外れてしまいます。そういう危なっかしさが、筆者がMySQLを信用できない理由の一つですね。

SQLなんてのは、そもそも複雑な検索条件を指定するための言語ですから、他に手段があればそれでも構わないわけです。ブラウザゲームでは、単一select/update/insertが殆どです。リレーションなんて要りません。

それこそがNoSQLのアドバンテージであった訳ですが、MySQL自身がNoSQLを取り込んで来るとは。流石はオラクル、商売上手です。ただどうでせなら、大量のパラメタを自動調整して欲しいんですが、それだったら天下を取れると思いますよ。なんでやらないんだろ。あ、Oracleが売れなくなるからか。

前出の技術者がやってる、別プロジェクトでは、HandlerSocketを使ってみるそうです。どうなることやら？

mysqlかpostgresqlかNoSQLか

postgresqlとmysqlとどっちを使えば良いかは、
いやっちゅーほど繰り返されてきた議論ですが、
両者とも明確なメリット＆デメリットがあり、
極論するなら、消去法で選択するしかありません。

postgresqlの場合

• insertが比較的早い
• updateを繰り返すと、性能が落ちてくる。vacuumが必要。
• vacuumするには、テーブルロックが必要。な場合がある。
• レプリケーションが未実装。
• connectは比較的遅い。

mysqlの場合

• readだけなら早い→そんな案件今時あるの？
• レプリケーションが比較的簡単＆安定→readonlyスレーブ増やして負荷分散、はよくある発想。面倒だが。
• innodb insert/updateが激遅い。
• sqlに方言がやたら多い。
• 複雑なsqlを投入すると、最適化に失敗する。と言うか最適化する気がないっぽい。
• connectは比較的早い。
• myisamは結構壊れる。(しかも普通に使っていて)
• innodbも稀に壊れる。(しかも普通に使っていて)

エンタープライズ業界なら、これの何れか、もしくはOracleでイイヤ、
って話になるでしょうけど、
ソーシャルゲームであると、データ量、時間あたりの処理量が
指数的に増大します。

テーブル分割に対する実装コストも馬鹿になりません。
であれば、NoSQLを使ってしまえ、というのは手です。

NoSQLは数有りますが、近頃実績が増えてるっぽいのが、mongodbでしょう。
http://www.infoq.com/jp/news/2010/10/4square_mongodb_outage
これらのトラブルも、過渡期であるが故です。

それにこのトラブルは、会員数300万人とかのレベルなんで、
仮にmysql/postgresqlで運用していたとしても、
別のトラブルが有ったであろう人数と言えましょう。知らないけど。

シャードの不均一化が原因だって話なんで、
最初から、細かいテーブル(コレクション)に分割してしまえば済む話かも知れません。
メモリ使用的にもその方が有利らしいし。
http://www.mongodb.org/pages/viewpage.action?pageId=18448682

こんな事が出来るのも、スキーマレス、create tableイラズだからです。

ソーシャルゲームの実装は、トモダチ1000人居るだけで大変

最近は、ソーシャルゲームが流行り？で、
作ってみようかって案件は少なくないと思いますが、
「一般的なポータルサイト」と同じように作ると、
かなり大変なことになります。

「一般的なポータルサイト」には無かった概念として、
「フレンド」が有るでしょう。
こいつのお陰で、内部の処理量が指数的に増大します。
よほど上手く作らないと、何処かがネックになります。

「ページビュー」だけで、負荷は計れない。ということです。

誰かさんがゲームを開始すると同時に、
彼のフレンドの個々の情報を取得する、
のはよくある話だと思いますが。

500人の人が、500人づつのフレンドを持っていたすると、
いきなり250000の検索をしなければならなくなります。

同時に来たら、と思うと、血の気の引く件数ですね。

どう考えても、想定してない人数です。
馬鹿正直に実装したらmysqlとかphpとか、
どっちかのメモリが足りなくなるでしょうね。

と言うわけで、ソーシャルゲームの実装の鍵は、
如何にフレンド処理をサボるか、
これに尽きるでしょう。

と言うか、「収益の出る集客」を目指すなら、
ン１０万人を最初から想定しなければならない訳で
必然、性能だけじゃなくて、テーブルのサイズの心配もしなければなりません。
テーブルパーティショニングとか、シャーディングとか、
自力で実装するには面倒臭すぎます。

バックエンドに、mysqlとかpostgresqlとかは、
ぼちぼち無理なんじゃないでしょうか。って言うか、筆者はもう勘弁してほしい。

であれば、初めから「それらの機能を内蔵したNoSQLに全部お願い」
それも手だなと。思う次第です。

大規模WEBサービスは、クラウドに倣うべき

日本国内の、主にケータイサイトの裏側は、「apacheとmysqlで構築してます」
なんて話は良く聞きますが、いずれも物理サーバを自前で置いている場合です。

最近ではamazon ec2の利用が増えているであろうと思いますが、
これを、ただ「安価なレンタルサーバ」として使うのは全面的に間違っています。

データが消えても良いことを最初から考慮して、
むしろ2分で新しいインスタンスが立ち上がることを積極的に利用するべき。

「スケーラブル」にしなければならないのに、「インスタンス1台づつ手動で立ち上げて」どうこうするのは無駄。

つまり、物理サーバとは別の運用パラダイムが必要です。
なんて話は、海外のクラウド畑ではあたりまえの話でしょう。
判ってないのは日本国内のお客さんだけ。

筆者が常用している海外のサービスは、最近ではtumblrぐらいですが、
どうも最近不安定。ポスト失敗したり。ダッシュボードが出なかったり。
リブログ主体ですから、画像の取扱いの主体が大変だろうと推測。

似て非なるtwitterは、質的にはリアルタイムチャット、
twitterのトラフィックの異常さは想像に難しくありません。

彼らの努力を見習って、同じ苦労は極力削減するべき。
この不景気、車輪の再生産をする余裕はありません。

運用面の工夫

TwitterがBitTorrentで高速にデプロイしている仕組みについて
http://www.publickey1.jp/blog/10/twitterbittorrent.html

そこでBitTorrentを使ってデプロイする「Murder」というツールを開発をした。Murderは、BitTorrentを包含して内部ネットワーク用にオプティマイズしたもの。これまで約900秒かかっていたデプロイの時間が約12秒になり、75倍も速くなった。

Twitterの大規模システム運用技術、あるいはクジラの腹の中（前編）～ログの科学的な分析と、Twitterの「ダークモード」
http://www.publickey1.jp/blog/10/twittertwitter.html

Twitterのクジラ解剖学、あるいは彼らがいかにサーバの処理能力を向上させたか
http://www.publickey1.jp/blog/10/twitter_4.html
ボトルネック調査

Twitterの大規模システム運用技術、あるいはクジラの腹の中（後編）～Twitterのサブシステム「Unicorn」「Kestrel」「Flock DB」
http://www.publickey1.jp/blog/10/twittertwitterunicornkestrelflock_db.html
ボトルネックの具体的な解消。ミドルウエアの積極的な交換。

kestrel
tiny queue system based on starling, in scala
http://github.com/robey/kestrel
タスクスケジューリングの解法(cronやatには限界があるのは周知)
memcachedの基盤を利用して、大規模分散キューを実現してる。らしい。

ストレージ面

基本的にshardingだけど、アプリケーションが個別に頑張るんじゃなくて、
RDBとの間にアダプタを挟むのが現実的。

Twitterが分散フレームワーク「Gizzard」公開！ Scalaで書かれたShardingを実現するミドルウェア
http://www.publickey1.jp/blog/10/twittergizzard_scalasharding.html

GizzardはScalaで書かれたJavaVM上で動作するミドルウェアで、PHPやRubyといったWebアプリケーションからの要求を自動的にデータベースに分散することで、大規模で可用性の高い分散データベースを容易に実現するためのものです。

なんか日本語が変。「ScalaでかかれたJavaVM」に読める。こういう書き方をする人は多いですが。

GizzardはJavaVM上で動作するミドルウェアで、Scalaで書かれてる。Webアプリケーションからの要求を、自動的にデータベースに分散することで、大規模で可用性の高い分散データベースを容易に実現するためのものです。

http://engineering.twitter.com/2010/04/introducing-gizzard-framework-for.html
http://github.com/twitter/gizzard

NoSQLは
思ったより運用実績はないみたいですね。

TwitterとDiggがNoSQLの「Cassandra」を選ぶ理由
http://www.publickey1.jp/blog/10/twitterdiggnosqlcassandra.html

Twitterが、Cassandraの本採用を断念。「いまは切り替えの時期ではない」
http://www.publickey1.jp/blog/10/twittercassandra.html

それにしても、このうちGizzardとKestrelが、Scalaで書いてるらしい。
JVMベースの、「JAVAでない言語」恐るべし。

MySQLはそれほど速くないし、PostgreSQLはそれほど遅くない in 2010

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ではこれは不可能です。

http://dev.mysql.com/doc/refman/5.0/en/subquery-errors.html

これに限らず、複雑な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を使おうね、とか

http://dev.mysql.com/doc/refman/4.1/ja/delete-speed.html

いや、運用でそんなケースそんなにあるのか？と

逆に聞きたいのですが。

手動でやってるなら、truncate使いますよそりゃあ。

postgresqlの問題点としては、vacuumを懸念している人は多いと思いますが、

実は、mysqlもvacuumをやっているのです。

しかも1レコードづつ。

いや、オフィシャルで「mysqlでvacuumやってます」

なんて記述は一つもないのですが、

遅いなあ、と思ってstraceしてみたら、

ファイルコピーしてやがった事がありました。

いや俺今delete fromしたよね？なんでファイルコピーが必要？みたいなね。

トランザクション量がもの凄い多い場合、begin〜commitが遠い場合は、

これまた性能がゴッソリ落ちます。

数10レコードづつcommitすれば劇的に改善しますが。

あと、myisamテーブルは壊れる可能性があります。

修復コマンドがあるくらいですから。

http://dev.mysql.com/doc/refman/4.1/ja/myisamchk-repair-options.html

実は筆者の経験で、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使っとけ。

もうそんな気持ちです。