21. ベンチマーク環境
? HP DL360p G8v2 (ベンチマークサーバ)
? CPU Intel Xeon E5-2643 v2 @ 3.50GHz x 2(1CPU =
6Core)
? MEM 8GB x 8 = 64GB
? ioDrive2 785GB
? Driver version: 3.2.6 build 1212, Firmware v7.1.15, rev 110356 Public
? NIC Intel I350
? OS CentOS 6.6(2.6.32-504.12.2.el6.x86_64)
22. ? HP DL360 G7(ベンチマーククライアント)
? CPU Intel Xeon L5640 @ 2.27GHz (2CPU = 6Core x 2)
? MEM 4GB x 12 = 48GB
? NIC Broadcom NetXtreme II BCM5709
? OS CentOS 5.9 (2.6.18-348.16.1.el5)
31. ? ./storage/innobase/srv/srv0mon.cc からコメント抜粋
? purge_del_mark_records
? Number of delete-marked rows purged
? purge_invoked
? Number of times purge was invoked
? purge_undo_log_pages
? Number of undo log pages handled by the purge
? purge_upd_exist_or_extern_records
? Number of purges on updates of existing records and
updates on delete marked record with externally stored
field
? buffer_flush_batch_num_scan
? Number of times buffer flush list flush is called
? buffer_LRU_search_scanned
? Total pages scanned as part of LRU search
38. ? ./storage/perfschema/pfs_instr.hより
/**
@def WAIT_STACK_LOGICAL_SIZE
Maximum number of nested waits.
Some waits, such as:
- "wait/io/table/sql/handler"
- "wait/lock/table/sql/handler"
are implemented by calling code in a storage engine,
that can cause nested waits (file io, mutex, ...)
Because of partitioned tables, a table io event (on the whole table)
can contain a nested table io event (on a partition).
Because of additional debug instrumentation,
waiting on what looks like a "mutex" (safe_mutex, innodb sync0sync, ...)
can cause nested waits to be recorded.
For example, a wait on innodb mutexes can lead to:
- wait/sync/mutex/innobase/some_mutex
- wait/sync/mutex/innobase/sync0sync
- wait/sync/mutex/innobase/os0sync
The max depth of the event stack must be sufficient
for these low level details to be visible.
*/
? WaitedTime/secにおいてwait/io/table/sql/handlerが性能劣化
時から増加していることからテーブルの操作(INSERTや
UPDATE)に時間がかかるようになっていると考えられます
42. sxlock is 何？
? https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_rw_lock よ
り
? An sx-lock provides write access to a common resource
while permitting inconsistent reads by other threads. sx-
locks were introduced in MySQL 5.7 to optimize
concurrency and improve scalability for read-write workloads.
![? 上位10件のみ表示するため Select TOP-[10]にチェック
? 絞り込みたいパターンとして wait/synch/* を入力し
Use Select Pattern にチェック](https://image.slidesharecdn.com/myna-201508dimstat-ppt-150830031103-lva1-app6892/85/dimSTAT-13-320.jpg)
![fio + perf + Flame Graphs
? 実行コマンド
? perf record -a -g -F100000 /usr/bin/fio --name=‘test’
--readwrite=randwrite --blocksize=4k --size=32m
--filename=/var/lib/mysql/fio/fiotest --direct=1 --numjobs=16
--group_reporting
? 1つのファイルに対して16プロセスでそれぞれ32MBの書き込み
をO_DIRECTで行います
? Flame Graphsの作成
? perf script > perf_data.txt
? stackcollapse-perf.pl perf_data.txt | flamegraph.pl --title “[タイ
トル名]” > [ファイル名].svg
? 参考: perf + Flame Graphs で Linux カーネル内のボトルネックを特定する
(http://d.hatena.ne.jp/yohei-a/20150706/1436208007)](https://image.slidesharecdn.com/myna-201508dimstat-ppt-150830031103-lva1-app6892/85/dimSTAT-58-320.jpg)
