MariaDB_Performance_Tuning.pdf

Transcript

1. 実践 MariaDBパフォーマ ンスチューニング JPMUG 上妻 武
2. TPC-Cをチューニングしてみた 「"MariaDB(MySQL)" + "チューニング" で調べるといくつかの定番 パラメータの情報が検索されますが、実際に設定してみた結果は あまり書かれていません。そこでHammerDB TPC-Cを用いて、 innodbのパラメータを実践してみました。」
3. 環境 OS CentOS Linux release 7.3.1611 (Core) CPU Intel(R) Core(TM) i7-6700K CPU @ 4.00GHz (8 core) Memory 32GB SSD SC300 HFS256G32MND-3312A (メーカー:SK hynix) ×１ 2015年8月発売当時 ツクモで9240円 容量:256GB シーケンシャルRead:530MB/s、Write:380MB/s MariaDB 10.2.12-MariaDB
4. TPC-C Benchmark TPC(トランザクション処理性能評議会)によって策定されたベン チマーク。 http://www.tpc.org/tpcc/
5. TPC-C Benchmark トランザクション処理性能評議会 コンピュータ関連の有力企業が集まり、データベー スのトランザクションに関して実際のシステムに近 い性能指標を作成する目的で設立された非営利団体。
6. TPC-C Benchmark HammerDB HammerDB 2.23 を使用 http://www.hammerdb.com
7. TPC-C Benchmark テーブル構成 • テーブル構成 table name 内容 rows data size (MB) index size (MB) stock 在庫 11,774,349 4,178 0 order_line 注文明細 35,850,016 2,956 0 customer 顧客 3,462,264 2,258 264 orders 注文 3,549,909 119 142 history 支払履歴 3,6351,00 310 0 new_order 新規注文 1,087,545 39 0 item 商品マスタ 99,385 9 0 district 地区 1,200 0.28 0 warehouse 倉庫 120 0.02 0 合計約10GB
8. TPC-C Benchmark 処理 (44%)新規注文 new order (1注文あたり5～15個購入する) (1) 18秒スリープ (2) insert: 注文、新規注文、注文明細テーブル (3) update: 地区、在庫テーブル (4) 0～12秒スリープ (4%)注文状況確認 order status (1) 2秒スリープ (2) select: 顧客、新規注文、注文テーブル (3) 0～5秒スリープ (44%)支払い payment (未精算の注文に支払いする) (1) 3秒スリープ (2) insert: 注文履歴テーブル (3) update: 倉庫、地区、顧客テーブル (4) 0～12秒スリープ (4%)在庫確認 stock level (1) 2秒スリープ (2) select: 地区、新規注文テーブル (3) 0～5秒スリープ (4%)配送 delivery (10注文配送する) (1) 2秒スリープ (2) delete: 新規注文テーブル (3) update: 注文、注文明細、顧客テーブル (4) 0～10秒スリープ
9. HammerDB注意点 WAREHOUSEカラムサイズ拡張 WAREHOUSE件数を増やすとエラーが発生する。 「mysqlexec/db server: Out of range value for column 'w_ytd' at row 1」 WAREHOUSE テーブルの w_ytd (年間売上)カラムを拡張拡張する。 /usr/local/HammerDB-2.23/hdb-components/hdb_tpcc.tcl (8838行) set sql(9) "CREATE TABLE `warehouse` ( `w_id` INT(4) NOT NULL, `w_ytd` DECIMAL(36, 2) NULL, `w_tax` DECIMAL(4, 4) NULL,
10. HammerDB注意点 Transaction Per Minutes の間違い show global status where Variable_name = 'Handler_commit' or Variable_name = 'Handler_rollback' → MySQL, MariaDBにバグがある。 handler_commit is incremented for InnoDB SELECT queries https://bugs.mysql.com/bug.php?id=52453
11. HammerDB注意点 Handler_commitのバグ MariaDB [tpcc]> show global status where Variable_name = 'Handler_commit' or Variable_name = 'Handler_rollback' or Variable_name = 'Com_commit'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | Com_commit | 0 | | Handler_commit | 0 | | Handler_rollback | 0 | +------------------+-------+ MariaDB [tpcc]> select count(*) from warehouse; MariaDB [tpcc]> show global status where Variable_name = 'Handler_commit' or Variable_name = 'Handler_rollback' or Variable_name = 'Com_commit'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | Com_commit | 0 | | Handler_commit | 1 | | Handler_rollback | 0 | +------------------+-------+ MariaDB [tpcc]> update warehouse set w_id =120 where w_id=120; MariaDB [tpcc]> commit; MariaDB [tpcc]> show global status where Variable_name = 'Handler_commit' or Variable_name = 'Handler_rollback' or Variable_name = 'Com_commit'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | Com_commit | 1 | | Handler_commit | 3 | | Handler_rollback | 0 |
12. HammerDB注意点 Handler_commitをCom_commitに変更 /usr/local/HammerDB-2.23/hdb-components/hdb_tpcc.tcl (4か所: 9640、9644、9664、9668行) show global status where Variable_name = 'Com_commit' or Variable_name = 'Handler_rollback' • Handler_commit 内部 COMMIT ステートメントの数。 • Com_commit COMMITステートメントが実行された回数。
13. とりあえずデフォルト my.cnf で • my.cnfに、datadirとmax_connectionsのみ設定 • Virtual Users = 10 でピーク 22,000 TPM
14. Innodb定番 • DBバッファ • innodb_buffer_pool_size=24G • REDOログ • innodb_log_file_size=10G • innodb_log_files_in_group=2 • innodb_log_buffer_size=1G • ファイルへのフラッシュ方法 • innodb_flush_method=O_DIRECT • トランザクション分離レベル • transaction-isolation = READ-COMMITTED
15. Innodb定番の結果 • Virtual Users = 200くらいでピーク 32万TPM
16. 何が一番効いたのか？ 10ユーザ 200ユーザ
17. innodb_buffer_pool_size 10ユーザ 200ユーザ 11,233 TPM 42,494 TPM
18. innodb_buffer_pool_size メモリ ＳＳＤ InnoDB Buffer Pool mysqld Log Buffer ibdata* / *.ibd ib_logfile* 12GiB/s 20GiB/s 315MiB/s 400MiB/s 物理メモリサイズの80% innodb_buffer_pool_size=128MB(default) → 24GB
19. innodb_buffer_pool_size • innodb_buffer_pool_sizeを1GB以上にするとデフォルトが変わる パラメータ • innodb_buffer_pool_instances 1 → 8 • バッファプールを８つに分割して競合を解消する。 • innodb_page_cleaners 1 → 4 • ページクリーナーを4スレッドで動かす。durtyページがマルチスレッドでflushさ れる。
20. innodb_buffer_pool_sizeの効果 MariaDB [(none)]> show global status; Innodb_buffer_pool_pages_flushed = 9,985 → 621,150 Innodb_row_lock_time = 7,545,612 → 3,694,147 Innodb_row_lock_time_avg = 1,285 → 204 Innodb_row_lock_waits = 5,871 → 19,098 Innodb_buffer_pool_bytes_data = 132,841,472 → 9,151,987,712 Innodb_buffer_pool_pages_free = 0 → 1,004,177 (16,066,832,000 bytes) → innodb_buffer_pool_size=25GBは多すぎ Innodb_buffer_pool_bytes_dirty = 46,137,344 → 1,296,809,984
21. innodb_buffer_pool_sizeの効果 MariaDB [(none)]> show engine innodb status; ---------------------- BUFFER POOL AND MEMORY ---------------------- ... Pages made young 2889, not young 934461 ... Buffer pool hit rate 861 / 1000, young-making rate 1 / 1000 not 306 / 1000 ↓ ---------------------- BUFFER POOL AND MEMORY ---------------------- ... Pages made young 53, not young 0 ... Buffer pool hit rate 996 / 1000, young-making rate 0 / 1000 not 0 / 1000 → 再利用されない追い出し候補のバッファ
22. REDOログ(innodb_log_*) 11,233 TPM 12,421 TPM 効果なし 22,199 22,812
23. REDOログ(innodb_log_*) メモリ ＳＳＤ InnoDB Buffer Pool mysqld Log Buffer ibdata* / *.ibd ib_logfile* 12GiB/s 20GiB/s 315MiB/s 400MiB/s
24. REDOログ(innodb_log_*) 設定値の根拠 innodb_log_file_size = 50MB（default） → 10GB MariaDB [(none)]> show global status より、 Innodb_os_log_written = 3,279,874,560 (3GB) ログスイッチしないのが一番パフォーマンスが良いので、 TPMが上がることも考えて、余裕をもって10GB。 innodb_log_files_in_group = 2 （default） → 2 1回のTPC-Cで書ききれない（ログスイッチしない）ので、2個。 innodb_log_buffer_size = 8MB（default） → 1GB TPC-C一回の総redoログ書き込み量=3,279,874,560 bytes で、実行時間約4分なので、 1分あたりのredoログ書き込み量は、 3,279,874,560/4 = 819,968,640=781MB。 余裕を見て1GB。
25. REDOログ(innodb_log_*)の効果 show global status; show engine innodb status; → REDOログの前にDBバッファがボトルネックでTPMが上がらないので、 REDOログ対策だけしても効果は無い？
26. DBバッファ(innodb_buffer_pool_size) 、REDOログ(innodb_log_*)の合わせ技
27. DBバッファ(innodb_buffer_pool_size) 、REDOログ(innodb_log_*)の合わせ技 MariaDB [performance_schema]> select event_name, count_star, sum_timer_wait -> from events_waits_summary_by_account_by_event_name -> where count_star <> 0 and event_name <> 'idle' and user = 'root' order by sum_timer_wait desc limit 5; +--------------------------------------+------------+-------------------+ | event_name | count_star | sum_timer_wait | +--------------------------------------+------------+-------------------+ | wait/io/table/sql/handler | 3408766 | 42510835632968250 | | wait/io/file/innodb/innodb_data_file | 1810463 | 597258834945000 | | wait/io/file/innodb/innodb_log_file | 34680 | 153149057016500 | | wait/lock/table/sql/handler | 1184974 | 1170412270250 | | wait/io/file/myisam/dfile | 4183 | 7905654000 | +--------------------------------------+------------+-------------------+ デフォルト my.cnf の Wait Event → innodb_buffer_pool_size = 24GBに設定すると
28. DBバッファ(innodb_buffer_pool_size) 、REDOログ(innodb_log_*)の合わせ技 MariaDB [performance_schema]> select event_name, count_star, sum_timer_wait -> from events_waits_summary_by_account_by_event_name -> where count_star <> 0 and event_name <> 'idle' and user = 'root' order by sum_timer_wait desc limit 5; +--------------------------------------+------------+-------------------+ | event_name | count_star | sum_timer_wait | +--------------------------------------+------------+-------------------+ | wait/io/table/sql/handler | 11707250 | 43313073565340750 | | wait/io/file/innodb/innodb_log_file | 28559 | 118337615488000 | | wait/io/file/innodb/innodb_data_file | 372455 | 13451588753250 | | wait/lock/table/sql/handler | 4281043 | 3189581059750 | | wait/io/file/myisam/dfile | 4182 | 13438506500 | +--------------------------------------+------------+-------------------+ innodb_buffer_pool_size = 24GB の Wait Event → REDOログ回りの設定を変更すると
29. DBバッファ(innodb_buffer_pool_size) 、REDOログ(innodb_log_*)の合わせ技 MariaDB [performance_schema]> select event_name, count_star, sum_timer_wait -> from events_waits_summary_by_account_by_event_name -> where count_star <> 0 and event_name <> 'idle' and user = 'root' order by sum_timer_wait desc limit 5; +--------------------------------------+------------+-------------------+ | event_name | count_star | sum_timer_wait | +--------------------------------------+------------+-------------------+ | wait/io/table/sql/handler | 68217960 | 13681480839471750 | | wait/io/file/innodb/innodb_log_file | 58201 | 235886274592000 | | wait/lock/table/sql/handler | 25430189 | 10595424424750 | | wait/io/file/innodb/innodb_data_file | 106501 | 4538950475000 | | wait/io/file/myisam/dfile | 4181 | 16364870500 | +--------------------------------------+------------+-------------------+ DBバッファとREDOログ回りを両方設定した後の Wait Event
30. O_DIRECT 11,233 TPM 9372 TPM 悪化した 22,199 13,018 innodb_flush_method = fsync (default) → O_DIRECT
31. O_DIRECTの効果 • OSのファイルキャッシュを使わない設定 • Innodbは自分でバッファを持ってるので、 ファイルキャッシュを有効にすると二重に キャッシュすることになり、オーバーヘッドがかかる。 • また、サーバーが落ちるとファイルキャッシュが失われ、デー タロストしないため、必ずすべき設定。
32. O_DIRECTでなぜ悪化したか DBバッファが128MBしかない。 ↓ ファイルキャッシュがDBバッファ代わりになっていた。 ↓ ファイルキャッシュを無くしたため性能が下がった。 ↓ DBバッファと一緒に増やさないと意味がない。
33. トランザクション分離レベル 分離レベル ダーティーリード ファジーリード ファントムリード READ UNCOMMITTED 可能性あり 可能性あり 可能性あり READ COMMITED (一般的なRDBMS) 発生しない 可能性あり 可能性あり REPEATABLE READ (innodb デフォルト) 発生しない 発生しない innodbでは発生しない SERIALIZABLE 発生しない 発生しない 発生しない 概要 パフォーマンスの利点 ダーティーリード コミット前の更新が別トランザクションから見える 更新前のデータにロックがかからない ファジーリード トランザクション1がselectした結果を、 トランザクション2が更新・コミットした後、 トランザクション1が再度selectすると更新される。 他トランザクションがselectしたレコード の更新前のデータを保持しなくてよい。 ファントムリード トランザクション1がselectした範囲にあるレコードを、 トランザクション2が挿入・削除・コミットした後、 トランザクション1が再度selectすると更新される。 他トランザクションがselectした範囲のレ コードの更新前のデータを保持しなくて よい。 パフォーマンス 良 悪 transaction-isolation = REPEATABLE-READ（default） → READ-COMMITTED
34. トランザクション分離レベルの効果 11,233 TPM 11,514 TPM 22,199 21,807 効果なし
35. トランザクション分離レベルの効果 • READ-COMMITEDにすると、ファントムリードを許容する。 • ファントムリードのためのロック(gap lock、next-key lock)がか かっている場合、パフォーマンスアップが期待できる。 id data 1 aaa 10 bbb 20 ccc Transaction1 select data from TBL1 where id > 1 and id < 10; TBL1 Transaction1 が id 1 ～ 10 のレコードに ロックをかける → gap lock
36. トランザクション分離レベルの効果 • しかし、本環境ではgap lock、next-key lock がかかっていないので、 効果なし。 MariaDB [(none)]> show engine innodb status; ------------ TRANSACTIONS ------------ ... RECORD LOCKS space id 9 page no 42731 n bits 328 index PRIMARY of table `tpcc`.`orders` trx id 16602294 lock_mode X locks rec but not gap ...
37. Innodb SSDの定番 • ページサイズ • innodb_page_size=4K • IO効率化 • innodb_lo_capacity=2000 • innodb_flush_neighbors=0 • マウントオプション • nobarrier • IOスケジューラ • deadline
38. Innodb SSDの定番 • 最大 32万 → 38万TPM • Virtual Usersのピークが60 Virtual Usersに下がった。
39. 何が一番効いたのか？ マウントオプション（nobarrier）だけが効いた。
40. 書き込みバリア無効（nobarrier） • ストレージの電源供給が失われても、順序を守った書き込みを 維持するカーネルの機能 • デフォルト有効 • fsync()の前後にストレージの書き込みキャッシュをフラッシュ することで実現 • fsync()を多用するアプリケーションではパフォーマンス低下を 招く • UPSなどで電力を保障していれば無効にしても良い
41. ページサイズ変更の効果 • ページサイズをSSDに合わせて4kに変更 • innodb_page_size=16K（Default） → 4K 下がった
42. ページサイズ変更の効果 # iostat -t -x 1 (ページサイズ 16KB) Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %util sda 0.00 977.00 981.00 8309.00 3924.00 79176.00 17.89 3.67 0.39 0.35 0.40 0.04 36.40 ↓ (ページサイズ 4KB) Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %util sda 0.00 2016.00 2253.00 1676.00 9012.00 35680.00 22.75 4.97 1.27 1.32 1.20 0.25 97.90 → SSDがリクエスト数をさばききれなくなり、パフォーマンス低下？
43. innodb_flush_neighbors • innodb_flush_neighbors=1（Default） → 0 下がった
44. innodb_flush_neighbors • innodb_flush_neighbors=1（Default） → 0 １：バッファプールから同じエクステント内の 連続するダーティーページがフラッシュされる。 ０：その他のダーティーページはフラッシュされない。 HDDのディスクシークのオーバーヘッドを短縮するために、隣接 ページをまとめてフラッシュする。 SSDでは不要。 → なぜパフォーマンスが低下するかわからない
45. innodb_io_capacity 効果なし • innodb_io_capacity=200（Default） → 2000
46. innodb_io_capacity • innodb_io_capacity=200（Default） → 2000 InnoDB バックグラウンドタスクで実行される I/O アクティビティー (バッファープールからのペー ジのフラッシュや挿入バッファーからのデータのマージなど) に上限を設定します。 ストレージのiopsに合わせて設定する。 Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %util sda 0.00 1819.00 253.00 1523.00 4048.00 88776.00 104.53 2.57 1.45 1.53 1.43 0.53 94.00 2000は妥当。 → なぜ効果が無いかわからない
47. IOスケジューラ deadlineの効果 • /sys/block/sda/queue/scheduler cfq（Default） → deadline 効果なし
48. IOスケジューラ deadlineの効果 • IOスケジューラ deadline I/Oのキューのうち、保持期限(Deadline)を超えているリクエストを優先 Deadlineを超えているキューが無ければ、セクタ順にソートして処理する。 Deadlineがあることで、レイテンシーが一定に保たれる。 → なぜ効果が無いかわからない
49. 今回の検証結果 まとめ DBバッファサイズ拡張(innodb_buffer_pool_size) ◦効果あり REDOログ回り拡張(innodb_log_*) △効果なし DBバッファ ＋ REDOログ 合わせ技 ◎効果あり O_DIRECT ×悪化 トランザクション分離レベル △効果なし ページサイズをストレージに合わせる ×悪化 IOスケジューラ Deadline △効果なし マウントオプション nobarrier ◎効果あり innodb_flush_neighbors ×悪化 innodb_io_capacity △効果なし 全部 ◎効果あり！！
51. Thank you so, so much!