SlideShare a Scribd company logo

Linux/DB Tuning (DevSumi2010, Japanese)

•Download as PPT, PDF•
•
Y
Yoshinori Matsunobu
1 of 44
Downloaded 392 times
高性能・安定運用のための Linux/DB システム構築 / 運用技術 松信 嘉範 (MATSUNOBU Yoshinori) サン・マイクロシステムズ株式会社 プリンシパル MySQL コンサルタント
プロフィール 2006 年 9 月から MySQL->Sun(-> オラクル ) で MySQL コンサルタントとして勤務 主な著書 Linux-DB システム構築 / 運用入門 現場で使える MySQL Java データアクセス実践講座 Twitter: matsunobu Blog: http://opendatabaselife.blogspot.com * 今回の資料は公開します * MySQL 用語があちこちに出ますが、 経験の無い方にも分かるように配慮していくつもりです
安定稼働と高性能を支える要素 アプリケーション層に関する技術 テーブル設計、インデックス設計、 SQL 文など RDBMS 製品に関する技術 OS ( Linux ) に関する技術 ハードウェア に関する技術 ストレージ、 RAID 、メモリ、ネットワーク、 CPU 潜在的なリスクを予知し、未然に防ぐ力 運用経験を積めば積むほどその力は上がる 愚者は経験に学び、賢者は歴史に学ぶ 頭の隅に置いておきたい考え方もいくつかある
問題の大半はディスクI/O メモリアクセスとディスクアクセスでは、 実際のスループットが 2 桁変わる メモリアクセスのクエリは秒間 1,000-10,000 のオーダー ディスクアクセスのクエリは秒間 10-100 のオーダー ディスクアクセスをいかに減らすかが大切 メモリを増やす データサイズを減らす クエリを書き換える 高速なストレージ (SSD) を導入する 世の中のベンチマークは、実際の用途と かけ離れているものが多いので注意 ディスク I/O がまったく発生しないベンチマークとか
製品の品質に対する目を向ける 品質に対する定量的な評価は難しい 相当使い倒したり、ソースコードレベルにまで踏み込まないと分からないことも多い GA( 安定 ) 版の定義は製品によってまちまち A 社の製品 A の機能は○が 4 個で × が 2 個、 B 社の製品 B の機能は○が 5 個で × が 1 個、なので B を使おう 技術の評価はそれほど単純なものではない A 社の GA と B 社の GA は同じ意味ではない GA が出てから半年経てば安全 ? 誰も使っていない機能は、半年経ってもやっぱり使われない 普及している ≒ それだけテストされている ≒ 品質がある程度担保されている 海外で普及している方が品質は高くなる 日本での普及度が低い場合は日本語機能などに多大なバグが残ってることがある 設計上も優れた機能なのか、マーケティング上行き当たりばったりで作られた機能なのかによっても品質は大きく変わる 後者の方が後々エンバグを引き起こしやすい
一般解を求めすぎない 「社内標準のインフラ設定」を決めたがる会社は多い 汎用性を高くすればするほど、効率は悪くなる UTF-8 はシフト JIS や日本語 EUC には扱えない文字も処理できるが、 1.2-1.5 倍くらい消費する 文字列型は数値型には扱えない文字も処理できるが、消費バイト数が数倍になる InnoDB はほとんどの場面で最適だが、 MyISAM や NDB が勝る場面もある アプリケーションの性質によって インフラ設定を変えることも考える 耐障害性が求められなければコミット時同期書き込みをやめる (innodb_flush_log_at_trx_commit=2)
良いところよりも悪いところを見る 悪いクエリが 1 個でもあると、すべてが止まることがある MySQL では… UPDATE や DELETE でフルテーブルスキャン 内部的にはアクセスしたレコードに排他ロック 誰も INSERT できなくなる mysqldump デフォルトのオプションはテーブルに共有ロック 誰も INSERT できなくなる 毎秒 100 アクセスが来るアプリケーションで 10 秒止まると、接続数は 1000 になる 同時接続数 500 あれば大丈夫だろうなんて言えない
良いところよりも悪いところを見る(2) ボトルネックになっていないところを どれだけ最適化しても、 全体のパフォーマンスはたいして変わらない Theory of Constraints 100 回のリクエストを処理するにあたり、 処理時間が memcached で 0.001 秒、 MySQL で 0.1 秒で、合計 0.101 秒とすると -> memcached 層をいくら速くしても 　何も変わらない
データベース以外にも目を向ける ハードウェアがボトルネックになることは日常茶飯事 監視ツールのエージェントがボトルネックになることがある クラスタソフトがボトルネックになることがある アプリケーションが…
アプリケーションの性質を ある程度知っておく O/Rマッパーを使うことが多くなった RDBMSが想定していない(最適化していない)クエリの乱発に気を付ける DESCRIBEコマンドを大量に発行するORM ユーザー数が2倍ならCPU使用率は 2倍程度だろうという考えは一般的に誤り データ量の増加に伴なうディスクI/Oの多発、 インデックス検索でスキャンする範囲の増加など
OSSを責任転嫁の 道具にしてはいけない 「 DB サーバの性能が悪いのは、オープンソース だからです」は、答えになっていない 有償 / 無償に関わらず、明らかな問題は解決しなければいけない 「無料だから仕方ないよね」、どうやって問題を解決できるかを考えなければいけない 問題の答えを知るためにお金を使うのは悪いことではない 時間をお金で買うのは常套手段 問題解決に時間がかかりすぎると機会損失になる
Linuxのチューニングと安定運用 メモリ管理とスワップ制御 I/Oスケジューラとファイルシステム 使いこなしたいコマンド類 ネットワークとデータベース接続 カーネルパニックとHA構成
メモリ領域 DBサーバでは最も重要な要素 32GB – 64GBを搭載することも多くなった ダイレクトI/Oを使う
メモリを十分に取り、ダイレクト I/O を活用する innodb_flush_method=O_DIRECT ファイルの I/O 単位が 512 バイトの倍数で無いとダイレクト I/O はできないため、 サポートしていない RDBMS も多い InnoDB ログファイル、 MyISAM 、 PostgreSQL など ファイルシステムキャッシュ InnoDB バッファプール 実メモリ InnoDB データファイル InnoDB バッファプール 実メモリ InnoDB データファイル
スワップ制御とOOM Killer 実メモリを使い切ると、何が起こるのか ファイルシステムキャッシュの中身を捨てて空き領域を確保する プロセスの一部をスワップ空間に追い出して空き領域を確保する スワップの効率は悪い プロセス空間がディスクに追いやられる ( スワップアウト ) もう一度その領域にアクセスするとディスクから読まれる ( スワップイン ) I/O 多発の上シングルスレッドであり非効率。スワップはできるだけ発生させない 実メモリとスワップの両方を使い切ると、 OOM Killer が発動し、任意のプロセスが 強制終了 されうる 空き領域が無いと何もできないので、これは仕方ない 基本的にはメモリ使用量の多いプロセス (mysqld) が殺される mysqld の前に DRBD とか Heartbeat とかが問答無用で殺されることもある OOM Killer が発動してからプロセスが終了するまでの間には、 数分 – 数時間を要することがある。 この間は操作をまるで受け付けてくれない スワップを発生させるべきではないが、 スワップを使い切るような設計はもっとやってはいけない
スワップサイズをゼロにしては いけない 1 個の CPU コアが %system を 100% 使いきってしまい、それがしばらく続く この環境は 4CPU コアなので 1 個がほぼ 100% SSH すら受け付けてくれず、画面も固まる top - 01:01:29 up 5:53, 3 users, load average: 0.66, 0.17, 0.06 Tasks: 170 total, 3 running, 167 sleeping, 0 stopped, 0 zombie Cpu(s): 0.0%us, 24.9%sy , 0.0%ni,75.0%id,0.2%wa,0.0%hi, 0.0%si,0.0%st Mem: 32967008k total, 32815800k used, 151208k free, 8448k buffers Swap: 0k total, 0k used, 0k free, 376880k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 26988 mysql 25 0 30g 30g 1452 R 98.5 97.7 0:42.18 mysqld
スワップの調整 安定稼働のためにスワップは必要 でも RDBMS 本体プロセスはスワップされたくない メモリを使う状況は、大きく分けて 2 種類 RDBMS 本体プロセス 主に実メモリを占有 (InnoDB) ファイルシステムキャッシュを使うものもある (MyISAM) 管理操作 ( バックアップなど ) 主にファイルシステムキャッシュを使う vm.swappiness = 0 実メモリを使い切ったときに、ファイルシステムキャッシュを優先的に捨てて空きメモリを確保する プロセスサイズが実メモリを超えたら、プロセスがスワップされる デフォルトは 60 巨大なファイルをコピーしただけで mysqld がスワップされる
メモリ領域の割り当て メモリ割り当てには malloc() が使われる tcmalloc() など、高速なメモリアロケータの利用も検討したい InnoDB Plugin ではメモリアロケータの変更が可能 Innodb_use_sys_malloc = 1 に加え LD_LIBRARY_PATH や LD_PRELOAD で tcmalloc などのアロケータを指定 必要以上にメモリ割り当てをしない 2MB の領域確保にかかる時間は、 128KB の確保よりもずっと長い 512KB 未満は brk() によって確保し、それ以上は mmap() によって確保される セッション変数のメモリ領域を大きく取りすぎると、 逆効果になることがある SELECT * FROM huge_myisam_table LIMIT 1; SET read_buffer_size = 256*1024; -> 10,000 回の実行に 0.68 秒 SET read_buffer_size = 2048*1024; -> 10,000 回の実行に 18.81 秒
I/Oスケジューラ 比喩表現「エレベータ」 巨大エレベータで一気に運ぶか、 快速で一気に運ぶか、など。。 「スケジューラの種類」と「キューのサイズ」が重要 noop, anticipatory, deadline, cfq デフォルトは cfq だが、 noop, deadline の方が良い echo noop > /sys/block/sda/queue/scheduler キューサイズ 自分で I/O を最適化する RDBMS にとってはあまり関係なく、 I/O を OS に任せる RDBMS にとってはとても重要 InnoDB では重要ではなく、 MyISAM では重要
I/O スケジューラの性能差 (InnoDB) ・ I/O スケジューラの性能差は ミドルウェアにも依存する ・ MySQL5.4 は I/O スレッドが 計 16 本あるが、 5.1 では 2 本 ・簡易的なベンチマークでも cfq は I/O スレッド本数が 少ないときは良い性能が 出なかった ・ anticipatory はどんなときにも 悪い性能しか出なかった
I/O スケジューラのキューサイズ (MyISAM) MyISAM は、 InnoDB のような I/O 処理最適化のメカニズムが無い OS とストレージに大きく依存する Linux の I/O スケジューラには、「 I/O キュー」というものがある キュー内の I/O リクエストをソートし、最適になるように並べ替える # echo 100000 > /sys/block/sdX/queue/nr_requests
ファイルシステム 原則：ルートパーティションと データファイル用パーティションは分ける データファイルが一杯になったときに その影響が OS 全体に及ぶのを避ける ext3 色々言われているが、圧倒的に使われている 巨大ファイルの削除に時間がかかる innodb_file_per_table を使うような場合に注意 MySQL ではファイル削除に長時間を要すると テーブルをオープンするスレッドが全部待たされてしまう 同じファイルに対して並列に書き込むことができない 1 個の巨大なファイルより、複数個のファイルを用意した方が速いことがよくある 大量の数のファイルを扱う場合、 dir_index オプションで作成して探索を高速化する ジャーナリングモード journal 、 ordered( デフォルト ) 、 writeback から選択可能 一般論として、データを上書きしている途中にクラッシュすると、 対象のブロックが中途半端な状態になる可能性がある OS に任せる場合、 journal 以外ではデータの復旧ができない RDBMS では、こうした障害を防ぐためにブロックを二度書きする仕組みを持っている。 このためどのジャーナリングモードでも安全 InnoDB の Doublewrite Buffer PostgreSQL の full_page_writes InnoDB ブロック (16KB) 変更前 変更後 ストレージの I/O 単位 (512B 等 )
ファイルシステム (続き) xfs ファイルの削除が高速 ダイレクト I/O を使えば 1 個のファイルに対して並列に書き込みが可能 RHEL/CentOS では標準では使えない デフォルトで「ライトバリア」が有効になっているので「 nobarrier 」で消すこと ext2 ジャーナリングが無いため高速 fsck に非常に時間がかかる 冗長化構成を組んでいる場合、あえて ext2 にして高速化を狙う ことがある btrfs ( 開発中 ) コピーオンライト型のファイルシステム トランザクション対応なので、中途半端な状態で更新されることが無い スナップショット・バックアップをオーバーヘッド無しで取れる
ファイルI/Oと同期書き込み (1) ストレージを有効に使う RAID コントローラに「バッテリーバックアップつきライトキャッシュ」を搭載し、 書き込み性能を上げる ドライブ自身のキャッシュは無効にすること disk Write cache disk seek & rotation time seek & rotation time
ファイルI/Oと同期書き込み (2) 「上書き ( ファイルサイズ固定 ) 」と「追記 ( ファイルサイズ増加 ) 」の違いに注意 追記の方がずっと遅い ファイルメタデータの更新も必要なので 上書きは秒間 10,000 強、追記は 3,000 程度 追記の性能はファイルシステムに大きく依存するが、 ext3, xfs, reiserfs ではどれもこの程度。 Solaris ZFS のようなコピーオンライト型では 7,000 強程度の良い値が出る ほとんどの RDBMS では上書き。 MySQL では バイナリログを sync-binlog=1 にしたときだけ追記の同期書き込みになる sync-binlog=1 必須なら、 Solaris ZFS を検討 ファイルを頻繁に拡張しないようにする innodb-autoextend-increment = 20 (default 8)
使いこなしたいコマンド類 iostat mpstat oprofile gdb SystemTap (stap)
iostat デバイスごとに I/O 統計を取るためのコマンド RDBMS はほとんど I/O ボトルネックになるため 分析のために欠かせない iostat -x RDBMS では転送量よりも I/O 回数 (IOPS) の方が重要 r/s, w/s, svctm, %util に注目 デバイスあたり、常に %util = (r/s + w/s) * svctm %util が 100% に達していても、 そこからさらにスループットが上がることもある # iostat -xm 10 avg-cpu: %user %nice %system %iowait %steal %idle 21.16 0.00 6.14 29.77 0.00 42.93 Device: rqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await svctm %util sdb 2.60 389.01 283.12 47.35 4.86 2.19 43.67 4.89 14.76 3.02 99.83 (283.12+47.35) * 3.02(ms)/1000 = 0.9980 = 100% util
iostat の例 (DBT-2) 同じストレージで、 %util はどちらも 100% 近いのに、 r/s や w/s は全然違う %util があまりあてにならないという理由がこれ ストレージを監視するときは、 %util よりも svctm を見た方が良い ピーク時 1000IOPS を期待するなら、 svctm は 1.00 (ms) 以下になるはずなので、 一定時間 1ms を超えたらアラートを上げるなど # iostat -xm 10 avg-cpu: %user %nice %system %iowait %steal %idle 21.16 0.00 6.14 29.77 0.00 42.93 Device: rqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await svctm %util sdb 2.60 389.01 283.12 47.35 4.86 2.19 43.67 4.89 14.76 3.02 99.83 (283.12+47.35) * 3.02(ms)/1000 = 0.9980 = 100% util # iostat -xm 10 avg-cpu: %user %nice %system %iowait %steal %idle 40.03 0.00 16.51 16.52 0.00 26.94 Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await svctm %util sdb 6.39 368.53 543.06 490.41 6.71 3.90 21.02 3.29 3.20 0.90 92.66 (543.06+490.41) * 0.90(ms)/1000 = 0.9301 = 93% util
mpstat CPU コア単位で統計を出力 vmstat は平均値 1 個の CPU コアだけ 100% で、 残りの CPU コアは 1 桁 % ということがよくある vmstat だけ見ていると見落としてしまうので注意
vmstat と mpstat を併用する vmstat は平均値。平均 12% に見えても実は 1 個が 100% ということはよくある話 # vmstat 1 procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------ r b swpd free buff cache si so bi bo in cs us sy id wa st 　 0 1 2096472 1645132 18648 19292 0 0 4848 0 1223 517 0 0 88 12 0 0 1 2096472 1645132 18648 19292 0 0 4176 0 1287 623 0 0 87 12 0 0 1 2096472 1645132 18648 19292 0 0 4320 0 1202 470 0 0 88 12 0 0 1 2096472 1645132 18648 19292 0 0 3872 0 1289 627 0 0 87 12 0 # mpstat -P ALL 1 ... 11:04:37 AM CPU %user %nice %sys %iowait %irq %soft %steal %idle intr/s 11:04:38 AM all 0.00 0.00 0.12 12.33 0.00 0.00 0.00 87.55 1201.98 11:04:38 AM 0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00 990.10 11:04:38 AM 1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00 0.00 11:04:38 AM 2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00 0.00 11:04:38 AM 3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00 0.00 11:04:38 AM 4 0.99 0.00 0.99 98.02 0.00 0.00 0.00 0.00 206.93 11:04:38 AM 5 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00 0.00 11:04:38 AM 6 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00 4.95 11:04:38 AM 7 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00 0.00
Oprofile プロファイリングツール 実行中のプロセスの負荷計測が可能 どの関数が CPU リソースを最も消費したかを簡単に特定できる ユーザー空間、カーネル空間両方のプロファイリングが可能 特定の関数が CPU リソースを占有している場合、 それを避けるようにアプリケーション側で対処できることがある CPU 使用率が低い場合には役に立たない I/O ネックやロック競合など 使い方 opcontrol --start --no-vmlinux 負荷をかける opcontrol --dump opcontrol --shutdown opreport -l /usr/local/bin/mysqld
Oprofile の利用例 文字コード変換周りでかなりの CPU リソースを消費していることが分かる 変換を防止することで改善 (Oprofile からは分からないが、スループットが約 20% 改善 ) # opreport –l /usr/local/bin/mysqld samples % symbol name 83003 8.8858 String::copy (char const*, unsigned int, charset_info_st*, charset_info_st*, unsigned int*) 79125 8.4706 MYSQLparse(void*) 68253 7.3067 my_wc_mb_latin1 55410 5.9318 my_pthread_fastmutex_lock 34677 3.7123 my_utf8_uni 18359 1.9654 MYSQLlex(void*, void*) 12044 1.2894 _ZL15get_hash_symbolPKcjb 11425 1.2231 _ZL20make_join_statisticsP4JOINP10TABLE_LISTP4ItemP16st_dynamic_array samples % symbol name 83107 10.6202 MYSQLparse(void*) 68680 8.7765 my_pthread_fastmutex_lock 20469 2.6157 MYSQLlex(void*, void*) 13083 1.6719 _ZL15get_hash_symbolPKcjb 12148 1.5524 JOIN::optimize() 11529 1.4733 _ZL20make_join_statisticsP4JOINP10TABLE_LISTP4ItemP16st_dynamic_array
gdb 本来はデバッグのためのツール gdb には、実行中のプロセスにおける、 全スレッドのスタックトレースを 取る機能がある (Solaris の truss に相当 ) 突然ハングアップしたような場合、その原因がどこにあるのかを突き止めるのに役立つ MySQL のようなオープンソースの RDBMS を使っている場合に便利 対象プログラムにデバッグシンボルが必要 MySQL は標準バイナリに DWARF デバッグシンボルが ついているので、そのまま恩恵を受けることができる 自分でビルドするなら – g をビルドオプションに付与 デバッグシンボルの有無はパフォーマンスにほとんど影響を与えない
ケーススタディ あるとき、すべてのクエリが止まってしまった MySQL のスロークエリログを見る どれもシンプルなクエリばかり 5 秒以上かかるのは異常 CPU 使用率 (%us, %sy) はほぼゼロ MySQL 提供のどのコマンドを使っても原因がよく分からない こういうときは OS のツールに頼る mysql> SELECT query_time, start_time, sql_text -> FROM mysql.slow_log WHERE start_time -> BETWEEN '2010-02-05 23:00:00' AND '2010-02-05 01:00:00' -> ORDER BY query_time DESC LIMIT 10; +------------+---------------------+----------+ | query_time | start_time | sql_text | +------------+---------------------+----------+ | 00:00:11 | 2010-02-05 23:09:55 | begin | | 00:00:09 | 2010-02-05 23:09:55 | Prepare | | 00:00:08 | 2010-02-05 23:09:55 | Prepare | | 00:00:08 | 2010-02-05 23:09:55 | Init DB | | 00:00:08 | 2010-02-05 23:09:55 | Init DB | | 00:00:07 | 2010-02-05 23:09:55 | Prepare | | 00:00:07 | 2010-02-05 23:09:55 | Init DB | | 00:00:07 | 2010-02-05 23:09:55 | Init DB | | 00:00:07 | 2010-02-05 23:09:55 | Init DB | | 00:00:06 | 2010-02-05 23:09:55 | Prepare | +------------+---------------------+----------+ 10 rows in set (0.02 sec)
gdb によるスタックダンプ 動作中の MySQL プロセスにアタッチして、全スレッドのダンプを取得 数秒おきに取得 毎回同じところで止まっている、同じ LWP 番号のスレッドがあれば、それが怪しい 負荷がかかるので、特定の条件 ( 接続数が跳ね上がったときなど ) でのみ実行するのが良い gdbtrace() { … PID=`cat /var/lib/mysql/mysql.pid` STACKDUMP=/tmp/stackdump.$$ echo ' thread apply all bt ' > $STACKDUMP echo ' detach ' >> $STACKDUMP echo ' quit ' >> $STACKDUMP gdb --batch --pid=$PID -x $STACKDUMP } while loop do CONN=`netstat -an | grep 3306 | grep ESTABLISHED | wc | awk '{print $1}'` if [ $CONN -gt 40 ]; then gdbtrace() done sleep 3 done
Thread 1 (LWP 15380) が毎回同じところで止まっていた pthread_create() から呼ばれる clone() システムコールが怪しい とりあえず、これを MySQL に呼ばせないような処理をさせよう ..... Thread 3 (Thread 0x46c1d950 (LWP 28494)): #0 0x00007ffda5474384 in __lll_lock_wait () from /lib/libpthread.so.0 #1 0x00007ffda546fc5c in _L_lock_1054 () from /lib/libpthread.so.0 #2 0x00007ffda546fb30 in pthread_mutex_lock () from /lib/libpthread.so.0 #3 0x0000000000a0f67d in my_pthread_fastmutex_lock (mp=0xf46d30) at thr_mutex.c:487 #4 0x000000000060cbe4 in dispatch_command (command=16018736, thd=0x80, packet=0x65 <Address 0x65 out of bounds>, packet_length=4294967295) at sql_parse.cc:969 #5 0x000000000060cb56 in do_command (thd=0xf46d30) at sql_parse.cc:854 #6 0x0000000000607f0c in handle_one_connection (arg=0xf46d30) at sql_connect.cc:1127 #7 0x00007ffda546dfc7 in start_thread () from /lib/libpthread.so.0 #8 0x00007ffda46305ad in clone () from /lib/libc.so.6 #9 0x0000000000000000 in ?? () Thread 1 (Thread 0x7ffda58936e0 ( LWP 15380 )): #0 0x00007ffda4630571 in clone () from /lib/libc.so.6 #1 0x00007ffda546d396 in do_clone () from /lib/libpthread.so.0 #2 0x00007ffda546db48 in pthread_create@@GLIBC_2.2.5 () from /lib/libpthread.so.0 #3 0x0000000000600a66 in create_thread_to_handle_connection (thd=0x3d0f00) at mysqld.cc:4811 #4 0x00000000005ff65a in handle_connections_sockets (arg=0x3d0f00) at mysqld.cc:5134 #5 0x00000000005fe6fd in main (argc=4001536, argv=0x4578c260) at mysqld.cc:4471 #0 0x00007ffda4630571 in clone () from /lib/libc.so.6
SystemTap Solaris の DTrace に匹敵する 高機能なプロファイリング機能を提供 DWARF デバッグシンボルのあるプログラムなら、 ユーザー関数のプロファイリングも可能 MySQL 標準バイナリで使える 監視対象プロセスがクラッシュしたという報告が随所にある
ケーススタディ MySQL が内部で行うソートアルゴリズムには 2 種類ある 旧型方式：　ソート対象列と行 ID だけをソートバッファに 読み込んでソートして、その後に行 ID から残りの列を取りに行く 新型方式：　アクセス対象列全部をソートバッファに 読み込んでソートして、それを返す LIMIT 句を使う場合など、結構な性能差が出る場合もあるので、 どちらが使われるかは重要 現在の MySQL の統計情報からは、どちらの アルゴリズムが実際に使用されたのかを確認する方法は無い 返されるレコード 1 個につき、旧型ソートでは rr_from_pointers() 、 新型ソートでは rr_unpack_from_buffer() が呼ばれることを知っていれば、 それぞれが何回呼ばれるかを数える System Tap スクリプトを書けば良い mysql> EXPLAIN SELECT user_id, post_date, title -> FROM diary ORDER BY rating DESC limit 100 ********* select_type: SIMPLE table: diary type: ALL key: NULL rows: 1163 Extra: Using filesort post_date 2010-03-29 2010-03-30 2010-03-31 2010-04-01 user_id 100 2 3 10 rating 4.71 3.32 4.10 4.50 title UEFA CL: Inter vs Chelsea Denmark vs Japan, 3-0 MySQL Administration Linux tuning post_date 2009-03-29 2009-03-30 2009-03-31 2009-04-01 user_id 100 2 3 10 rating 4.71 3.32 4.10 4.50 title UEFA CL: Inter vs Chelsea Denmark vs Japan, 3-0 MySQL Administration Linux tuning 1) Load into sort buffer 3) Fetch the rest columns 2) Sort 1) Load all columns into sort buffer 旧型方式 新型方式 rating 4.71 3.32 4.10 4.50 RowID 1 2 3 4 2) Sort rating 4.71 4.50 4.10 3.32 RowID 1 4 3 3
SystemTap を使う global oldsort=0; global newsort=0; probe process(&quot;/usr/local/bin/mysqld&quot;).function(&quot;*rr_from_pointers*&quot;).return { oldsort++; } probe process(&quot;/usr/local/bin/mysqld&quot;).function(&quot;*rr_unpack_from_buffer*&quot;).return { newsort++; } probe end { printf(&quot;# of returned rows sorted by old algorithm: %d &quot;, oldsort); printf(&quot;# of returned rows sorted by new algorithm: %d &quot;, newsort); } ----- [root #] stap sort.stp # of returned rows sorted by old algorithm: 0 # of returned rows sorted by new algorithm: 100
ネットワークとデータベース接続 ディスク I/O よりずっと速いので、ディスク I/O ネックの場合はどちらでも大差無い パーシステント・コネクションの方が効率的 エラー発生時に、その影響を後続のコネクションに 引き継がないように細心の注意が必要 プリペアードステートメントキャッシュの利用も検討したい 通常のコネクションの場合には、余計な初期化 / 終了処理を可能な限り減らす skip-name-resolve によって DNS サーバへのルックアップを無くす thread-cache-size を max-connections と同じ値にして、 pthread_create() を呼ばさせない 接続時に操作対象のデータベース名を指定して、ドライバに USE 文を実行させない ( できるドライバとできないドライバがある ) skip-client-character-set-handshake によって、ドライバに SET NAMES 文を実行させない Persistent Connection/ Connection Pool Non-Persistent Connection Connect USE db SET NAMES utf8 SQL statement Disconnect SQL statement - Establish TCP/IP conn - Name Resolve to DNS - pthread_create() (Rollback) Web/App Server DB Server Web/App Server DB Server
カーネルパニックとHA構成 カーネルパニックが起きて RDBMS の処理が続けられなくなった だが、 heartbeat のパケットは返ってくる フェイルオーバーしてくれない 強制的に再起動し、フェイルオーバーさせる kernel.panic_on_oops = 1 kernel.panic = 1
Linuxの設定まとめ sar, mpstat, iostat, oprofile, gdb, SystemTap くらいは入れておく 実メモリの半分程度のスワップ領域を用意しておく vm.swappiness = 0 /sys/block/sdX/queue/scheduler = deadline または noop /etc/security/limits.conf soft nofile 8192 hard nofile 8192 ルートパーティションとデータベース用パーティションを分ける ファイルシステムのチューニング relatime (noatime) ext3: tune2fs –O dir_index -c –l –i 0 xfs: nobarrier HA 構成の場合、カーネルパニック時に再起動させる kernel.panic_on_oops = 1 kernel.panic = 1
ありがとうございました 今回のセッション内容は、 書籍「 Linux-DB システム構築 / 運用入門」 がベースになっています。 2F にて 1 割引で販売中 サイン会 本日 14:00 – 14:20 お問い合わせ [email_address] Twitter で @matsunobu をつけて何か言ってください
Q & A

More Related Content

Linux/DB Tuning (DevSumi2010, Japanese)

  • 1. 高性能・安定運用のための Linux/DB システム構築 / 運用技術 松信 嘉範 (MATSUNOBU Yoshinori) サン・マイクロシステムズ株式会社 プリンシパル MySQL コンサルタント
  • 2. プロフィール 2006 年 9 月から MySQL->Sun(-> オラクル ) で MySQL コンサルタントとして勤務 主な著書 Linux-DB システム構築 / 運用入門 現場で使える MySQL Java データアクセス実践講座 Twitter: matsunobu Blog: http://opendatabaselife.blogspot.com * 今回の資料は公開します * MySQL 用語があちこちに出ますが、 経験の無い方にも分かるように配慮していくつもりです
  • 3. 安定稼働と高性能を支える要素 アプリケーション層に関する技術 テーブル設計、インデックス設計、 SQL 文など RDBMS 製品に関する技術 OS ( Linux ) に関する技術 ハードウェア に関する技術 ストレージ、 RAID 、メモリ、ネットワーク、 CPU 潜在的なリスクを予知し、未然に防ぐ力 運用経験を積めば積むほどその力は上がる 愚者は経験に学び、賢者は歴史に学ぶ 頭の隅に置いておきたい考え方もいくつかある
  • 4. 問題の大半はディスクI/O メモリアクセスとディスクアクセスでは、 実際のスループットが 2 桁変わる メモリアクセスのクエリは秒間 1,000-10,000 のオーダー ディスクアクセスのクエリは秒間 10-100 のオーダー ディスクアクセスをいかに減らすかが大切 メモリを増やす データサイズを減らす クエリを書き換える 高速なストレージ (SSD) を導入する 世の中のベンチマークは、実際の用途と かけ離れているものが多いので注意 ディスク I/O がまったく発生しないベンチマークとか
  • 5. 製品の品質に対する目を向ける 品質に対する定量的な評価は難しい 相当使い倒したり、ソースコードレベルにまで踏み込まないと分からないことも多い GA( 安定 ) 版の定義は製品によってまちまち A 社の製品 A の機能は○が 4 個で × が 2 個、 B 社の製品 B の機能は○が 5 個で × が 1 個、なので B を使おう 技術の評価はそれほど単純なものではない A 社の GA と B 社の GA は同じ意味ではない GA が出てから半年経てば安全 ? 誰も使っていない機能は、半年経ってもやっぱり使われない 普及している ≒ それだけテストされている ≒ 品質がある程度担保されている 海外で普及している方が品質は高くなる 日本での普及度が低い場合は日本語機能などに多大なバグが残ってることがある 設計上も優れた機能なのか、マーケティング上行き当たりばったりで作られた機能なのかによっても品質は大きく変わる 後者の方が後々エンバグを引き起こしやすい
  • 6. 一般解を求めすぎない 「社内標準のインフラ設定」を決めたがる会社は多い 汎用性を高くすればするほど、効率は悪くなる UTF-8 はシフト JIS や日本語 EUC には扱えない文字も処理できるが、 1.2-1.5 倍くらい消費する 文字列型は数値型には扱えない文字も処理できるが、消費バイト数が数倍になる InnoDB はほとんどの場面で最適だが、 MyISAM や NDB が勝る場面もある アプリケーションの性質によって インフラ設定を変えることも考える 耐障害性が求められなければコミット時同期書き込みをやめる (innodb_flush_log_at_trx_commit=2)
  • 7. 良いところよりも悪いところを見る 悪いクエリが 1 個でもあると、すべてが止まることがある MySQL では… UPDATE や DELETE でフルテーブルスキャン 内部的にはアクセスしたレコードに排他ロック 誰も INSERT できなくなる mysqldump デフォルトのオプションはテーブルに共有ロック 誰も INSERT できなくなる 毎秒 100 アクセスが来るアプリケーションで 10 秒止まると、接続数は 1000 になる 同時接続数 500 あれば大丈夫だろうなんて言えない
  • 8. 良いところよりも悪いところを見る(2) ボトルネックになっていないところを どれだけ最適化しても、 全体のパフォーマンスはたいして変わらない Theory of Constraints 100 回のリクエストを処理するにあたり、 処理時間が memcached で 0.001 秒、 MySQL で 0.1 秒で、合計 0.101 秒とすると -> memcached 層をいくら速くしても 　何も変わらない
  • 10. アプリケーションの性質を ある程度知っておく O/Rマッパーを使うことが多くなった RDBMSが想定していない(最適化していない)クエリの乱発に気を付ける DESCRIBEコマンドを大量に発行するORM ユーザー数が2倍ならCPU使用率は 2倍程度だろうという考えは一般的に誤り データ量の増加に伴なうディスクI/Oの多発、 インデックス検索でスキャンする範囲の増加など
  • 11. OSSを責任転嫁の 道具にしてはいけない 「 DB サーバの性能が悪いのは、オープンソース だからです」は、答えになっていない 有償 / 無償に関わらず、明らかな問題は解決しなければいけない 「無料だから仕方ないよね」、どうやって問題を解決できるかを考えなければいけない 問題の答えを知るためにお金を使うのは悪いことではない 時間をお金で買うのは常套手段 問題解決に時間がかかりすぎると機会損失になる
  • 12. Linuxのチューニングと安定運用 メモリ管理とスワップ制御 I/Oスケジューラとファイルシステム 使いこなしたいコマンド類 ネットワークとデータベース接続 カーネルパニックとHA構成
  • 13. メモリ領域 DBサーバでは最も重要な要素 32GB – 64GBを搭載することも多くなった ダイレクトI/Oを使う
  • 14. メモリを十分に取り、ダイレクト I/O を活用する innodb_flush_method=O_DIRECT ファイルの I/O 単位が 512 バイトの倍数で無いとダイレクト I/O はできないため、 サポートしていない RDBMS も多い InnoDB ログファイル、 MyISAM 、 PostgreSQL など ファイルシステムキャッシュ InnoDB バッファプール 実メモリ InnoDB データファイル InnoDB バッファプール 実メモリ InnoDB データファイル
  • 15. スワップ制御とOOM Killer 実メモリを使い切ると、何が起こるのか ファイルシステムキャッシュの中身を捨てて空き領域を確保する プロセスの一部をスワップ空間に追い出して空き領域を確保する スワップの効率は悪い プロセス空間がディスクに追いやられる ( スワップアウト ) もう一度その領域にアクセスするとディスクから読まれる ( スワップイン ) I/O 多発の上シングルスレッドであり非効率。スワップはできるだけ発生させない 実メモリとスワップの両方を使い切ると、 OOM Killer が発動し、任意のプロセスが 強制終了 されうる 空き領域が無いと何もできないので、これは仕方ない 基本的にはメモリ使用量の多いプロセス (mysqld) が殺される mysqld の前に DRBD とか Heartbeat とかが問答無用で殺されることもある OOM Killer が発動してからプロセスが終了するまでの間には、 数分 – 数時間を要することがある。 この間は操作をまるで受け付けてくれない スワップを発生させるべきではないが、 スワップを使い切るような設計はもっとやってはいけない
  • 16. スワップサイズをゼロにしては いけない 1 個の CPU コアが %system を 100% 使いきってしまい、それがしばらく続く この環境は 4CPU コアなので 1 個がほぼ 100% SSH すら受け付けてくれず、画面も固まる top - 01:01:29 up 5:53, 3 users, load average: 0.66, 0.17, 0.06 Tasks: 170 total, 3 running, 167 sleeping, 0 stopped, 0 zombie Cpu(s): 0.0%us, 24.9%sy , 0.0%ni,75.0%id,0.2%wa,0.0%hi, 0.0%si,0.0%st Mem: 32967008k total, 32815800k used, 151208k free, 8448k buffers Swap: 0k total, 0k used, 0k free, 376880k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 26988 mysql 25 0 30g 30g 1452 R 98.5 97.7 0:42.18 mysqld
  • 17. スワップの調整 安定稼働のためにスワップは必要 でも RDBMS 本体プロセスはスワップされたくない メモリを使う状況は、大きく分けて 2 種類 RDBMS 本体プロセス 主に実メモリを占有 (InnoDB) ファイルシステムキャッシュを使うものもある (MyISAM) 管理操作 ( バックアップなど ) 主にファイルシステムキャッシュを使う vm.swappiness = 0 実メモリを使い切ったときに、ファイルシステムキャッシュを優先的に捨てて空きメモリを確保する プロセスサイズが実メモリを超えたら、プロセスがスワップされる デフォルトは 60 巨大なファイルをコピーしただけで mysqld がスワップされる
  • 18. メモリ領域の割り当て メモリ割り当てには malloc() が使われる tcmalloc() など、高速なメモリアロケータの利用も検討したい InnoDB Plugin ではメモリアロケータの変更が可能 Innodb_use_sys_malloc = 1 に加え LD_LIBRARY_PATH や LD_PRELOAD で tcmalloc などのアロケータを指定 必要以上にメモリ割り当てをしない 2MB の領域確保にかかる時間は、 128KB の確保よりもずっと長い 512KB 未満は brk() によって確保し、それ以上は mmap() によって確保される セッション変数のメモリ領域を大きく取りすぎると、 逆効果になることがある SELECT * FROM huge_myisam_table LIMIT 1; SET read_buffer_size = 256*1024; -> 10,000 回の実行に 0.68 秒 SET read_buffer_size = 2048*1024; -> 10,000 回の実行に 18.81 秒
  • 19. I/Oスケジューラ 比喩表現「エレベータ」 巨大エレベータで一気に運ぶか、 快速で一気に運ぶか、など。。 「スケジューラの種類」と「キューのサイズ」が重要 noop, anticipatory, deadline, cfq デフォルトは cfq だが、 noop, deadline の方が良い echo noop > /sys/block/sda/queue/scheduler キューサイズ 自分で I/O を最適化する RDBMS にとってはあまり関係なく、 I/O を OS に任せる RDBMS にとってはとても重要 InnoDB では重要ではなく、 MyISAM では重要
  • 20. I/O スケジューラの性能差 (InnoDB) ・ I/O スケジューラの性能差は ミドルウェアにも依存する ・ MySQL5.4 は I/O スレッドが 計 16 本あるが、 5.1 では 2 本 ・簡易的なベンチマークでも cfq は I/O スレッド本数が 少ないときは良い性能が 出なかった ・ anticipatory はどんなときにも 悪い性能しか出なかった
  • 21. I/O スケジューラのキューサイズ (MyISAM) MyISAM は、 InnoDB のような I/O 処理最適化のメカニズムが無い OS とストレージに大きく依存する Linux の I/O スケジューラには、「 I/O キュー」というものがある キュー内の I/O リクエストをソートし、最適になるように並べ替える # echo 100000 > /sys/block/sdX/queue/nr_requests
  • 22. ファイルシステム 原則：ルートパーティションと データファイル用パーティションは分ける データファイルが一杯になったときに その影響が OS 全体に及ぶのを避ける ext3 色々言われているが、圧倒的に使われている 巨大ファイルの削除に時間がかかる innodb_file_per_table を使うような場合に注意 MySQL ではファイル削除に長時間を要すると テーブルをオープンするスレッドが全部待たされてしまう 同じファイルに対して並列に書き込むことができない 1 個の巨大なファイルより、複数個のファイルを用意した方が速いことがよくある 大量の数のファイルを扱う場合、 dir_index オプションで作成して探索を高速化する ジャーナリングモード journal 、 ordered( デフォルト ) 、 writeback から選択可能 一般論として、データを上書きしている途中にクラッシュすると、 対象のブロックが中途半端な状態になる可能性がある OS に任せる場合、 journal 以外ではデータの復旧ができない RDBMS では、こうした障害を防ぐためにブロックを二度書きする仕組みを持っている。 このためどのジャーナリングモードでも安全 InnoDB の Doublewrite Buffer PostgreSQL の full_page_writes InnoDB ブロック (16KB) 変更前 変更後 ストレージの I/O 単位 (512B 等 )
  • 23. ファイルシステム (続き) xfs ファイルの削除が高速 ダイレクト I/O を使えば 1 個のファイルに対して並列に書き込みが可能 RHEL/CentOS では標準では使えない デフォルトで「ライトバリア」が有効になっているので「 nobarrier 」で消すこと ext2 ジャーナリングが無いため高速 fsck に非常に時間がかかる 冗長化構成を組んでいる場合、あえて ext2 にして高速化を狙う ことがある btrfs ( 開発中 ) コピーオンライト型のファイルシステム トランザクション対応なので、中途半端な状態で更新されることが無い スナップショット・バックアップをオーバーヘッド無しで取れる
  • 24. ファイルI/Oと同期書き込み (1) ストレージを有効に使う RAID コントローラに「バッテリーバックアップつきライトキャッシュ」を搭載し、 書き込み性能を上げる ドライブ自身のキャッシュは無効にすること disk Write cache disk seek & rotation time seek & rotation time
  • 25. ファイルI/Oと同期書き込み (2) 「上書き ( ファイルサイズ固定 ) 」と「追記 ( ファイルサイズ増加 ) 」の違いに注意 追記の方がずっと遅い ファイルメタデータの更新も必要なので 上書きは秒間 10,000 強、追記は 3,000 程度 追記の性能はファイルシステムに大きく依存するが、 ext3, xfs, reiserfs ではどれもこの程度。 Solaris ZFS のようなコピーオンライト型では 7,000 強程度の良い値が出る ほとんどの RDBMS では上書き。 MySQL では バイナリログを sync-binlog=1 にしたときだけ追記の同期書き込みになる sync-binlog=1 必須なら、 Solaris ZFS を検討 ファイルを頻繁に拡張しないようにする innodb-autoextend-increment = 20 (default 8)
  • 26. 使いこなしたいコマンド類 iostat mpstat oprofile gdb SystemTap (stap)
  • 27. iostat デバイスごとに I/O 統計を取るためのコマンド RDBMS はほとんど I/O ボトルネックになるため 分析のために欠かせない iostat -x RDBMS では転送量よりも I/O 回数 (IOPS) の方が重要 r/s, w/s, svctm, %util に注目 デバイスあたり、常に %util = (r/s + w/s) * svctm %util が 100% に達していても、 そこからさらにスループットが上がることもある # iostat -xm 10 avg-cpu: %user %nice %system %iowait %steal %idle 21.16 0.00 6.14 29.77 0.00 42.93 Device: rqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await svctm %util sdb 2.60 389.01 283.12 47.35 4.86 2.19 43.67 4.89 14.76 3.02 99.83 (283.12+47.35) * 3.02(ms)/1000 = 0.9980 = 100% util
  • 28. iostat の例 (DBT-2) 同じストレージで、 %util はどちらも 100% 近いのに、 r/s や w/s は全然違う %util があまりあてにならないという理由がこれ ストレージを監視するときは、 %util よりも svctm を見た方が良い ピーク時 1000IOPS を期待するなら、 svctm は 1.00 (ms) 以下になるはずなので、 一定時間 1ms を超えたらアラートを上げるなど # iostat -xm 10 avg-cpu: %user %nice %system %iowait %steal %idle 21.16 0.00 6.14 29.77 0.00 42.93 Device: rqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await svctm %util sdb 2.60 389.01 283.12 47.35 4.86 2.19 43.67 4.89 14.76 3.02 99.83 (283.12+47.35) * 3.02(ms)/1000 = 0.9980 = 100% util # iostat -xm 10 avg-cpu: %user %nice %system %iowait %steal %idle 40.03 0.00 16.51 16.52 0.00 26.94 Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await svctm %util sdb 6.39 368.53 543.06 490.41 6.71 3.90 21.02 3.29 3.20 0.90 92.66 (543.06+490.41) * 0.90(ms)/1000 = 0.9301 = 93% util
  • 29. mpstat CPU コア単位で統計を出力 vmstat は平均値 1 個の CPU コアだけ 100% で、 残りの CPU コアは 1 桁 % ということがよくある vmstat だけ見ていると見落としてしまうので注意
  • 30. vmstat と mpstat を併用する vmstat は平均値。平均 12% に見えても実は 1 個が 100% ということはよくある話 # vmstat 1 procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------ r b swpd free buff cache si so bi bo in cs us sy id wa st 　 0 1 2096472 1645132 18648 19292 0 0 4848 0 1223 517 0 0 88 12 0 0 1 2096472 1645132 18648 19292 0 0 4176 0 1287 623 0 0 87 12 0 0 1 2096472 1645132 18648 19292 0 0 4320 0 1202 470 0 0 88 12 0 0 1 2096472 1645132 18648 19292 0 0 3872 0 1289 627 0 0 87 12 0 # mpstat -P ALL 1 ... 11:04:37 AM CPU %user %nice %sys %iowait %irq %soft %steal %idle intr/s 11:04:38 AM all 0.00 0.00 0.12 12.33 0.00 0.00 0.00 87.55 1201.98 11:04:38 AM 0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00 990.10 11:04:38 AM 1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00 0.00 11:04:38 AM 2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00 0.00 11:04:38 AM 3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00 0.00 11:04:38 AM 4 0.99 0.00 0.99 98.02 0.00 0.00 0.00 0.00 206.93 11:04:38 AM 5 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00 0.00 11:04:38 AM 6 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00 4.95 11:04:38 AM 7 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00 0.00
  • 31. Oprofile プロファイリングツール 実行中のプロセスの負荷計測が可能 どの関数が CPU リソースを最も消費したかを簡単に特定できる ユーザー空間、カーネル空間両方のプロファイリングが可能 特定の関数が CPU リソースを占有している場合、 それを避けるようにアプリケーション側で対処できることがある CPU 使用率が低い場合には役に立たない I/O ネックやロック競合など 使い方 opcontrol --start --no-vmlinux 負荷をかける opcontrol --dump opcontrol --shutdown opreport -l /usr/local/bin/mysqld
  • 32. Oprofile の利用例 文字コード変換周りでかなりの CPU リソースを消費していることが分かる 変換を防止することで改善 (Oprofile からは分からないが、スループットが約 20% 改善 ) # opreport –l /usr/local/bin/mysqld samples % symbol name 83003 8.8858 String::copy (char const*, unsigned int, charset_info_st*, charset_info_st*, unsigned int*) 79125 8.4706 MYSQLparse(void*) 68253 7.3067 my_wc_mb_latin1 55410 5.9318 my_pthread_fastmutex_lock 34677 3.7123 my_utf8_uni 18359 1.9654 MYSQLlex(void*, void*) 12044 1.2894 _ZL15get_hash_symbolPKcjb 11425 1.2231 _ZL20make_join_statisticsP4JOINP10TABLE_LISTP4ItemP16st_dynamic_array samples % symbol name 83107 10.6202 MYSQLparse(void*) 68680 8.7765 my_pthread_fastmutex_lock 20469 2.6157 MYSQLlex(void*, void*) 13083 1.6719 _ZL15get_hash_symbolPKcjb 12148 1.5524 JOIN::optimize() 11529 1.4733 _ZL20make_join_statisticsP4JOINP10TABLE_LISTP4ItemP16st_dynamic_array
  • 33. gdb 本来はデバッグのためのツール gdb には、実行中のプロセスにおける、 全スレッドのスタックトレースを 取る機能がある (Solaris の truss に相当 ) 突然ハングアップしたような場合、その原因がどこにあるのかを突き止めるのに役立つ MySQL のようなオープンソースの RDBMS を使っている場合に便利 対象プログラムにデバッグシンボルが必要 MySQL は標準バイナリに DWARF デバッグシンボルが ついているので、そのまま恩恵を受けることができる 自分でビルドするなら – g をビルドオプションに付与 デバッグシンボルの有無はパフォーマンスにほとんど影響を与えない
  • 34. ケーススタディ あるとき、すべてのクエリが止まってしまった MySQL のスロークエリログを見る どれもシンプルなクエリばかり 5 秒以上かかるのは異常 CPU 使用率 (%us, %sy) はほぼゼロ MySQL 提供のどのコマンドを使っても原因がよく分からない こういうときは OS のツールに頼る mysql> SELECT query_time, start_time, sql_text -> FROM mysql.slow_log WHERE start_time -> BETWEEN '2010-02-05 23:00:00' AND '2010-02-05 01:00:00' -> ORDER BY query_time DESC LIMIT 10; +------------+---------------------+----------+ | query_time | start_time | sql_text | +------------+---------------------+----------+ | 00:00:11 | 2010-02-05 23:09:55 | begin | | 00:00:09 | 2010-02-05 23:09:55 | Prepare | | 00:00:08 | 2010-02-05 23:09:55 | Prepare | | 00:00:08 | 2010-02-05 23:09:55 | Init DB | | 00:00:08 | 2010-02-05 23:09:55 | Init DB | | 00:00:07 | 2010-02-05 23:09:55 | Prepare | | 00:00:07 | 2010-02-05 23:09:55 | Init DB | | 00:00:07 | 2010-02-05 23:09:55 | Init DB | | 00:00:07 | 2010-02-05 23:09:55 | Init DB | | 00:00:06 | 2010-02-05 23:09:55 | Prepare | +------------+---------------------+----------+ 10 rows in set (0.02 sec)
  • 35. gdb によるスタックダンプ 動作中の MySQL プロセスにアタッチして、全スレッドのダンプを取得 数秒おきに取得 毎回同じところで止まっている、同じ LWP 番号のスレッドがあれば、それが怪しい 負荷がかかるので、特定の条件 ( 接続数が跳ね上がったときなど ) でのみ実行するのが良い gdbtrace() { … PID=`cat /var/lib/mysql/mysql.pid` STACKDUMP=/tmp/stackdump.$$ echo ' thread apply all bt ' > $STACKDUMP echo ' detach ' >> $STACKDUMP echo ' quit ' >> $STACKDUMP gdb --batch --pid=$PID -x $STACKDUMP } while loop do CONN=`netstat -an | grep 3306 | grep ESTABLISHED | wc | awk '{print $1}'` if [ $CONN -gt 40 ]; then gdbtrace() done sleep 3 done
  • 36. Thread 1 (LWP 15380) が毎回同じところで止まっていた pthread_create() から呼ばれる clone() システムコールが怪しい とりあえず、これを MySQL に呼ばせないような処理をさせよう ..... Thread 3 (Thread 0x46c1d950 (LWP 28494)): #0 0x00007ffda5474384 in __lll_lock_wait () from /lib/libpthread.so.0 #1 0x00007ffda546fc5c in _L_lock_1054 () from /lib/libpthread.so.0 #2 0x00007ffda546fb30 in pthread_mutex_lock () from /lib/libpthread.so.0 #3 0x0000000000a0f67d in my_pthread_fastmutex_lock (mp=0xf46d30) at thr_mutex.c:487 #4 0x000000000060cbe4 in dispatch_command (command=16018736, thd=0x80, packet=0x65 <Address 0x65 out of bounds>, packet_length=4294967295) at sql_parse.cc:969 #5 0x000000000060cb56 in do_command (thd=0xf46d30) at sql_parse.cc:854 #6 0x0000000000607f0c in handle_one_connection (arg=0xf46d30) at sql_connect.cc:1127 #7 0x00007ffda546dfc7 in start_thread () from /lib/libpthread.so.0 #8 0x00007ffda46305ad in clone () from /lib/libc.so.6 #9 0x0000000000000000 in ?? () Thread 1 (Thread 0x7ffda58936e0 ( LWP 15380 )): #0 0x00007ffda4630571 in clone () from /lib/libc.so.6 #1 0x00007ffda546d396 in do_clone () from /lib/libpthread.so.0 #2 0x00007ffda546db48 in pthread_create@@GLIBC_2.2.5 () from /lib/libpthread.so.0 #3 0x0000000000600a66 in create_thread_to_handle_connection (thd=0x3d0f00) at mysqld.cc:4811 #4 0x00000000005ff65a in handle_connections_sockets (arg=0x3d0f00) at mysqld.cc:5134 #5 0x00000000005fe6fd in main (argc=4001536, argv=0x4578c260) at mysqld.cc:4471 #0 0x00007ffda4630571 in clone () from /lib/libc.so.6
  • 37. SystemTap Solaris の DTrace に匹敵する 高機能なプロファイリング機能を提供 DWARF デバッグシンボルのあるプログラムなら、 ユーザー関数のプロファイリングも可能 MySQL 標準バイナリで使える 監視対象プロセスがクラッシュしたという報告が随所にある
  • 38. ケーススタディ MySQL が内部で行うソートアルゴリズムには 2 種類ある 旧型方式：　ソート対象列と行 ID だけをソートバッファに 読み込んでソートして、その後に行 ID から残りの列を取りに行く 新型方式：　アクセス対象列全部をソートバッファに 読み込んでソートして、それを返す LIMIT 句を使う場合など、結構な性能差が出る場合もあるので、 どちらが使われるかは重要 現在の MySQL の統計情報からは、どちらの アルゴリズムが実際に使用されたのかを確認する方法は無い 返されるレコード 1 個につき、旧型ソートでは rr_from_pointers() 、 新型ソートでは rr_unpack_from_buffer() が呼ばれることを知っていれば、 それぞれが何回呼ばれるかを数える System Tap スクリプトを書けば良い mysql> EXPLAIN SELECT user_id, post_date, title -> FROM diary ORDER BY rating DESC limit 100 ********* select_type: SIMPLE table: diary type: ALL key: NULL rows: 1163 Extra: Using filesort post_date 2010-03-29 2010-03-30 2010-03-31 2010-04-01 user_id 100 2 3 10 rating 4.71 3.32 4.10 4.50 title UEFA CL: Inter vs Chelsea Denmark vs Japan, 3-0 MySQL Administration Linux tuning post_date 2009-03-29 2009-03-30 2009-03-31 2009-04-01 user_id 100 2 3 10 rating 4.71 3.32 4.10 4.50 title UEFA CL: Inter vs Chelsea Denmark vs Japan, 3-0 MySQL Administration Linux tuning 1) Load into sort buffer 3) Fetch the rest columns 2) Sort 1) Load all columns into sort buffer 旧型方式 新型方式 rating 4.71 3.32 4.10 4.50 RowID 1 2 3 4 2) Sort rating 4.71 4.50 4.10 3.32 RowID 1 4 3 3
  • 39. SystemTap を使う global oldsort=0; global newsort=0; probe process(&quot;/usr/local/bin/mysqld&quot;).function(&quot;*rr_from_pointers*&quot;).return { oldsort++; } probe process(&quot;/usr/local/bin/mysqld&quot;).function(&quot;*rr_unpack_from_buffer*&quot;).return { newsort++; } probe end { printf(&quot;# of returned rows sorted by old algorithm: %d &quot;, oldsort); printf(&quot;# of returned rows sorted by new algorithm: %d &quot;, newsort); } ----- [root #] stap sort.stp # of returned rows sorted by old algorithm: 0 # of returned rows sorted by new algorithm: 100
  • 40. ネットワークとデータベース接続 ディスク I/O よりずっと速いので、ディスク I/O ネックの場合はどちらでも大差無い パーシステント・コネクションの方が効率的 エラー発生時に、その影響を後続のコネクションに 引き継がないように細心の注意が必要 プリペアードステートメントキャッシュの利用も検討したい 通常のコネクションの場合には、余計な初期化 / 終了処理を可能な限り減らす skip-name-resolve によって DNS サーバへのルックアップを無くす thread-cache-size を max-connections と同じ値にして、 pthread_create() を呼ばさせない 接続時に操作対象のデータベース名を指定して、ドライバに USE 文を実行させない ( できるドライバとできないドライバがある ) skip-client-character-set-handshake によって、ドライバに SET NAMES 文を実行させない Persistent Connection/ Connection Pool Non-Persistent Connection Connect USE db SET NAMES utf8 SQL statement Disconnect SQL statement - Establish TCP/IP conn - Name Resolve to DNS - pthread_create() (Rollback) Web/App Server DB Server Web/App Server DB Server
  • 41. カーネルパニックとHA構成 カーネルパニックが起きて RDBMS の処理が続けられなくなった だが、 heartbeat のパケットは返ってくる フェイルオーバーしてくれない 強制的に再起動し、フェイルオーバーさせる kernel.panic_on_oops = 1 kernel.panic = 1
  • 42. Linuxの設定まとめ sar, mpstat, iostat, oprofile, gdb, SystemTap くらいは入れておく 実メモリの半分程度のスワップ領域を用意しておく vm.swappiness = 0 /sys/block/sdX/queue/scheduler = deadline または noop /etc/security/limits.conf soft nofile 8192 hard nofile 8192 ルートパーティションとデータベース用パーティションを分ける ファイルシステムのチューニング relatime (noatime) ext3: tune2fs –O dir_index -c –l –i 0 xfs: nobarrier HA 構成の場合、カーネルパニック時に再起動させる kernel.panic_on_oops = 1 kernel.panic = 1
  • 43. ありがとうございました 今回のセッション内容は、 書籍「 Linux-DB システム構築 / 運用入門」 がベースになっています。 2F にて 1 割引で販売中 サイン会 本日 14:00 – 14:20 お問い合わせ [email_address] Twitter で @matsunobu をつけて何か言ってください
  • 44. Q & A