15. sysbench和MySQL IO

MySQL的IO使用情况

iotop

iotop -u mysql # -u 表示监控哪个user的进程，所以前提是你的mysql服务是用mysql用户启动的

该命令只能看到MySQL的线程ID（Thread ID）

performance_schema.threads

root@mysqldb 13:42:  [performance_schema]> select name, type, PROCESSLIST_ID,  THREAD_OS_ID from threads;
+---------------------------------------------+------------+----------------+--------------+
| name                                        | type       | PROCESSLIST_ID | THREAD_OS_ID |
+---------------------------------------------+------------+----------------+--------------+
| thread/sql/main                             | BACKGROUND |           NULL |         2462 |
| thread/innodb/io_ibuf_thread                | BACKGROUND |           NULL |         2475 |
| thread/innodb/io_read_thread                | BACKGROUND |           NULL |         2476 |
| thread/innodb/io_read_thread                | BACKGROUND |           NULL |         2477 |
| thread/innodb/io_write_thread               | BACKGROUND |           NULL |         2478 |
| thread/innodb/io_write_thread               | BACKGROUND |           NULL |         2479 |
| thread/innodb/page_flush_coordinator_thread | BACKGROUND |           NULL |         2480 |
| thread/innodb/log_checkpointer_thread       | BACKGROUND |           NULL |         2481 |
| thread/innodb/log_flush_notifier_thread     | BACKGROUND |           NULL |         2482 |
| thread/innodb/log_flusher_thread            | BACKGROUND |           NULL |         2483 |
| thread/innodb/log_write_notifier_thread     | BACKGROUND |           NULL |         2484 |
| thread/innodb/log_writer_thread             | BACKGROUND |           NULL |         2485 |
| thread/innodb/log_files_governor_thread     | BACKGROUND |           NULL |         2486 |
| thread/innodb/srv_lock_timeout_thread       | BACKGROUND |           NULL |         2491 |
| thread/innodb/srv_error_monitor_thread      | BACKGROUND |           NULL |         2492 |
| thread/innodb/srv_monitor_thread            | BACKGROUND |           NULL |         2493 |
| thread/innodb/buf_resize_thread             | BACKGROUND |           NULL |         2494 |
| thread/innodb/srv_master_thread             | BACKGROUND |           NULL |         2495 |
| thread/innodb/dict_stats_thread             | BACKGROUND |           NULL |         2496 |
| thread/innodb/fts_optimize_thread           | BACKGROUND |           NULL |         2497 |
| thread/mysqlx/worker                        | BACKGROUND |           NULL |         2498 |
| thread/mysqlx/worker                        | BACKGROUND |           NULL |         2499 |
| thread/mysqlx/acceptor_network              | BACKGROUND |           NULL |         2500 |
| thread/semisync/Ack_receiver                | BACKGROUND |           NULL |         2501 |
| thread/innodb/buf_dump_thread               | BACKGROUND |           NULL |         2505 |
| thread/innodb/clone_gtid_thread             | BACKGROUND |           NULL |         2506 |
| thread/innodb/srv_purge_thread              | BACKGROUND |           NULL |         2507 |
| thread/innodb/srv_worker_thread             | BACKGROUND |           NULL |         2508 |
| thread/sql/event_scheduler                  | FOREGROUND |              5 |         2510 |
| thread/sql/signal_handler                   | BACKGROUND |           NULL |         2511 |
| thread/mysqlx/acceptor_network              | BACKGROUND |           NULL |         2512 |
| thread/sql/compress_gtid_table              | FOREGROUND |              7 |         2514 |
| thread/sql/one_connection                   | FOREGROUND |              8 |         3977 |
+---------------------------------------------+------------+----------------+--------------+
33 rows in set (0.00 sec)

root@mysqldb 13:42:  [performance_schema]> show full processlist;
+----+-----------------+-----------+--------------------+---------+------+------------------------+-----------------------+
| Id | User            | Host      | db                 | Command | Time | State                  | Info                  |
+----+-----------------+-----------+--------------------+---------+------+------------------------+-----------------------+
|  5 | event_scheduler | localhost | NULL               | Daemon  |  672 | Waiting on empty queue | NULL                  |
|  8 | root            | localhost | performance_schema | Query   |    0 | init                   | show full processlist |
+----+-----------------+-----------+--------------------+---------+------+------------------------+-----------------------+

通过threads表中的信息，结合iotop -u mysql的输出，就可以知道某个线程的io使用情况

sysbench

安装

root@ubuntu:/home/gavin# apt install sysbench
root@ubuntu:/home/gavin# sysbench --version
sysbench 1.0.20

压测磁盘

root@ubuntu:/home/gavin# sysbench --test=fileio help 
WARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options.
sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3)

fileio options:
  --file-num=N                  number of files to create [128]    创建文件数
  --file-block-size=N           block size to use in all IO operations [16384]    block size大小
  --file-total-size=SIZE        total size of files to create [2G]    文件数的大小总和
  --file-test-mode=STRING       test mode {seqwr, seqrewr, seqrd, rndrd, rndwr, rndrw}    测试模式 {seqwr, seqrewr, seqrd, rndrd, rndwr, rndrw} （顺序写，顺序读写，顺序读，随机读，随机写，随机读写）
  --file-io-mode=STRING         file operations mode {sync,async,mmap} [sync]    文件操作方式 {sync,async,mmap}
  --file-async-backlog=N        number of asynchronous operatons to queue per thread [128]
  --file-extra-flags=[LIST,...] list of additional flags to use to open files {sync,dsync,direct} []    打开文件的额外标志 {sync,dsync,direct} []
  --file-fsync-freq=N           do fsync() after this number of requests (0 - don't use fsync()) [100]    多少请求后执行fsync。默认是0，不执行
  --file-fsync-all[=on|off]     do fsync() after each write operation [off]    是否每次操作后都执行fsync
  --file-fsync-end[=on|off]     do fsync() at the end of test [on]    测完成后执行fsync，默认是on
  --file-fsync-mode=STRING      which method to use for synchronization {fsync, fdatasync} [fsync]    同步的方法 {fsync, fdatasync}默认是 [fsync]
  --file-merged-requests=N      merge at most this number of IO requests if possible (0 - don't merge) [0]    最多多少IO请求被合并，默认为0，不合并
  --file-rw-ratio=N             reads/writes ratio for combined test [1.5]    读写比例默认是 [1.5]，即 3:2


sysbench --test=fileio --file-num=4 --file-block-size=8K --file-total-size=1G --file-test-mode=rndrd --file-extra-flags=direct --max-requests=0 --time=3600 --threads=4 prepare
sysbench --test=fileio --file-num=4 --file-block-size=8K --file-total-size=1G --file-test-mode=rndrd --file-extra-flags=direct --max-requests=0 --time=3600 --threads=4 run
sysbench --test=fileio --file-num=4 --file-block-size=8K --file-total-size=1G --file-test-mode=rndrd --file-extra-flags=direct --max-requests=0 --time=3600 --threads=4 cleanup

 如果是真实的测试 runtime 设置成一周的时间，run 期间可以使用 iotop 或者 iostat 进行观察

压测数据库

-- Ubuntu
sysbench --table-size=1000000 --tables=10 --db-driver=mysql --mysql-user=root --mysql-password='<password>' --mysql-host=10.21.12.126 --mysql-db=sbtest --mysql-port=6446  --threads=32 --max-requests=0 --time=30 --report-interval=3  oltp_read_write.lua prepare

-- Centos
sysbench --oltp-table-size=1000000 --oltp-tables-count=10 --mysql-user=root --mysql-password='<password>' --mysql-host=10.20.12.158 --mysql-db=sbtest --mysql-port=3306  --threads=32 --max-requests=0 --time=30 --report-interval=3 /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua run

fio

fio -filename=/data/mysql/test.io -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=2G -numjobs=10 -runtime=10 -group_reporting -name=test