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 进行观察
压测数据库
-压测MySQL
-- 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 /usr/share/sysbench/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
-压测PostgreSQL
sysbench --table-size=10000000 --tables=10 --db-driver=pgsql --pgsql-user=postgres --pgsql-password=postgres --mysql-host=10.100.172.12 --pgsql-port=9999 --pgsql-db=sbtest --threads=32 --max-requests=0 --time=30 --report-interval=3 /usr/share/sysbench/oltp_read_write.lua prepare
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
