- 目录
- 工具安装
- 执行压测
- 针对优化
- 示例结果
数据库指标
对于mysql压力测试，大多用sysbench/tpcc-mysql工具。主要针对数据库OLTP（在线事务处理）指标。
1 | qps : query per second 查询量 |
工具安装
1 | yum install sysbench -y |
在sysbench压测时会调用内置lua脚本, 这些脚本为于/etc/share/sysbench目录下1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16/usr/share/sysbench
[root@mysql sysbench]# ll
total 64
-rwxr-xr-x 1 root root 1452 Sep 18 12:29 bulk_insert.lua
-rw-r--r-- 1 root root 13762 Sep 18 12:29 oltp_common.lua
-rwxr-xr-x 1 root root 1290 Sep 18 12:29 oltp_delete.lua
-rwxr-xr-x 1 root root 2018 Sep 18 12:29 oltp_insert.lua
-rwxr-xr-x 1 root root 1265 Sep 18 12:29 oltp_point_select.lua
-rwxr-xr-x 1 root root 1649 Sep 18 12:29 oltp_read_only.lua
-rwxr-xr-x 1 root root 1824 Sep 18 12:29 oltp_read_write.lua
-rwxr-xr-x 1 root root 1118 Sep 18 12:29 oltp_update_index.lua
-rwxr-xr-x 1 root root 1127 Sep 18 12:29 oltp_update_non_index.lua
-rwxr-xr-x 1 root root 1440 Sep 18 12:29 oltp_write_only.lua
-rwxr-xr-x 1 root root 1919 Sep 18 12:29 select_random_points.lua
-rwxr-xr-x 1 root root 2118 Sep 18 12:29 select_random_ranges.lua
drwxr-xr-x 4 root root 4096 Nov 10 02:57 tests
执行压测
方便压测时找到内置lua脚本，以下命令都在/etc/share/sysbench目录下执行
在远端连接数据库时，授权1
grant all on test.* to root@'%' identified by '123';
生成压测数据1
2cd /usr/share/sysbench/
sysbench oltp_read_write.lua --mysql-host=127.0.0.1 --mysql-port=3306 --db-driver=mysql --mysql-db=test --mysql-user=root --mysql-password=MyNewPass4! --table_size=50000 --tables=10 --threads=300 --time=60 --report-interval=10 prepare
执行压测1
sysbench oltp_read_write.lua --mysql-host=127.0.0.1 --mysql-port=3306 --db-driver=mysql --mysql-db=test --mysql-user=root --mysql-password=MyNewPass4! --table_size=50000 --tables=10 --threads=300 --time=60 --report-interval=10 run
清理压测数据1
sysbench oltp_read_write.lua --mysql-host=127.0.0.1 --mysql-port=3306 --db-driver=mysql --mysql-db=test --mysql-user=root --mysql-password=MyNewPass4! --table_size=50000 --tables=10 --threads=300 --time=60 --report-interval=10 cleanup
优化数据库
在压测过程中可能出现一些错误，连接数不够，max_prepared_stmt_count不够，可优化/etc/my.cnf 做适当调整1
2
3
4
5
6
7
8
9
10
11
12max_connections = 4096
max_prepared_stmt_count=100000 # 默认16384压测时需调大
query_cache_type = 1
query_cache_size = 10M
wsrep_slave_threads = 4
#wsrep_notify_cmd = /usr/local/bin/wsrep-notify.sh
max_connections = 10000
key_buffer_size = 64M
max_heap_table_size = 64M
tmp_table_size = 64M
innodb_buffer_pool_size = 8192M
示例结果
1 | Initializing worker threads... |
