OB数据库压力测试

最新推荐文章于 2025-10-03 07:40:55 发布

原创最新推荐文章于 2025-10-03 07:40:55 发布 · 789 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #database #运维

本文档介绍了如何使用sysbench工具对Oceanbase数据库进行压力测试。首先，详细阐述了安装sysbench的步骤，然后在Oceanbase和MySQL集群中创建测试数据库和用户。接着，针对Oceanbase进行了读写测试，并在遇到错误时调整了相关参数以解决超时问题。最后，执行了压测并展示了部分测试结果，包括事务每秒（tps）和查询每秒（qps）等指标。

安装压测工具sysbench

RHEL/CentOS:

wget https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh

$ cat /etc/os-release |grep ID

ID="centos"

ID_LIKE="rhel fedora"

VERSION_ID="7"

chmod a+x script.rpm.sh

os=centos dist=7 ./script.rpm.sh

yum -y install sysbench

[root@master opt]# rpm -ql sysbench

/usr/bin/sysbench

/usr/share/doc/sysbench-1.0.20

/usr/share/doc/sysbench-1.0.20/COPYING

/usr/share/doc/sysbench-1.0.20/ChangeLog

/usr/share/doc/sysbench-1.0.20/README.md

/usr/share/licenses/sysbench-1.0.20

/usr/share/licenses/sysbench-1.0.20/COPYING

/usr/share/sysbench

/usr/share/sysbench/bulk_insert.lua

/usr/share/sysbench/oltp_common.lua

/usr/share/sysbench/oltp_delete.lua

/usr/share/sysbench/oltp_insert.lua

/usr/share/sysbench/oltp_point_select.lua

/usr/share/sysbench/oltp_read_only.lua

/usr/share/sysbench/oltp_read_write.lua

/usr/share/sysbench/oltp_update_index.lua

/usr/share/sysbench/oltp_update_non_index.lua

/usr/share/sysbench/oltp_write_only.lua

......省略一些输出

分别在Oceanbase和mysql集群中创建测试数据库和用户：

Oceanbase上执行：

obclient -uroot@obmysql -proot -P2883 -h192.168.18.28 -e "create database sbtest;"

obclient -uroot@obmysql -proot -P2883 -h192.168.18.28 -e "grant all on sbtest.* to sbtest@'%' identified by 'sbtest';"

mysql集群上执行：

mysql -uroot -p123456 -P8066 -h127.0.0.1 -e "create database sbtest;"

mysql -uroot -p123456 -P8066 -h127.0.0.1 -e "grant all on sbtest.* to sbtest@'%' identified by 'sbtest';"

压测

1 对Oceanbase的读写测试：

–准备数据

./oltp_read_write.lua --mysql-host=10.0.14.22 --mysql-port=2883 --mysql-user=root@obcp_t1 --mysql-password='' --mysql-db=sbtest --tables=15 --table-size=100000 --report-interval=2 --time=60 --threads=15 --auto_inc=off --mysql-ignore-errors --db-ps-mode=disable prepare

发现在执行该脚本时，会报错：

sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Initializing worker threads...

Creating table 'sbtest7'...

Creating table 'sbtest1'...

Creating table 'sbtest2'...

Creating table 'sbtest4'...

Creating table 'sbtest6'...

Creating table 'sbtest15'...

Creating table 'sbtest8'...

Creating table 'sbtest9'...

Creating table 'sbtest12'...

Creating table 'sbtest11'...

Creating table 'sbtest14'...

Creating table 'sbtest13'...

Creating table 'sbtest3'...

Creating table 'sbtest16'...

Creating table 'sbtest10'...

Creating table 'sbtest5'...

Inserting 100000 records into 'sbtest7'

Inserting 100000 records into 'sbtest1'

Inserting 100000 records into 'sbtest2'

Inserting 100000 records into 'sbtest4'

Inserting 100000 records into 'sbtest6'

Inserting 100000 records into 'sbtest8'

Inserting 100000 records into 'sbtest15'

FATAL: mysql_drv_query() returned error 4012 (Timeout) for query 'INSERT INTO sbtest8(id, k, c, pad) VALUES(1, 50216, '37995383632-96831892448-13834617111-03288165365-98215351924-61835230531-26938366841-20852559367-97450947472-50942960202'

...省略一部分... FATAL: `sysbench.cmdline.call_command' function failed: /usr/share/sysbench/oltp_common.lua:230: db_bulk_insert_next() failed

调整OB参数：

MySQL [sbtest]> show variables like '%timeout%';

+---------------------+------------------+

| Variable_name | Value |

+---------------------+------------------+

| connect_timeout | 10 |

| interactive_timeout | 28800 |

| lock_wait_timeout | 31536000 |

| net_read_timeout | 30 |

| net_write_timeout | 60 |

| ob_pl_block_timeout | 3216672000000000 |

| ob_query_timeout | 10000000 |

| ob_trx_idle_timeout | 120000000 |

| ob_trx_lock_timeout | -1 |

| ob_trx_timeout | 100000000 |

| wait_timeout | 28800 |

+---------------------+------------------+

11 rows in set (0.003 sec)

MySQL [sbtest]> set global ob_query_timeout=100000000000;

Query OK, 0 rows affected (12.295 sec)

MySQL [sbtest]> set global ob_trx_idle_timeout=200000000;

Query OK, 0 rows affected (0.148 sec)

MySQL [sbtest]> set global ob_trx_timeout=180000000;

Query OK, 0 rows affected (0.128 sec)

先执行下面的清理数据脚本，然后再重新执行准备脚本：

sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Initializing worker threads...

Creating table 'sbtest14'...

Creating table 'sbtest8'...

Creating table 'sbtest13'...

Creating table 'sbtest15'...

Creating table 'sbtest7'...

Creating table 'sbtest5'...

Creating table 'sbtest12'...

Creating table 'sbtest4'...

Creating table 'sbtest3'...

Creating table 'sbtest10'...

Creating table 'sbtest1'...

Creating table 'sbtest11'...

Creating table 'sbtest9'...

Creating table 'sbtest6'...

Creating table 'sbtest2'...

Inserting 100000 records into 'sbtest14'

Inserting 100000 records into 'sbtest13'

Inserting 100000 records into 'sbtest15'

Inserting 100000 records into 'sbtest5'

Creating a secondary index on 'sbtest14'...

Creating a secondary index on 'sbtest13'...

Inserting 100000 records into 'sbtest8'

Inserting 100000 records into 'sbtest7'

Inserting 100000 records into 'sbtest12'

Creating a secondary index on 'sbtest5'...

Creating a secondary index on 'sbtest8'...

Inserting 100000 records into 'sbtest4'

Creating a secondary index on 'sbtest12'...

Inserting 100000 records into 'sbtest10'

Inserting 100000 records into 'sbtest11'

Inserting 100000 records into 'sbtest1'

Creating a secondary index on 'sbtest4'...

Inserting 100000 records into 'sbtest3'

Creating a secondary index on 'sbtest11'...

Creating a secondary index on 'sbtest1'...

Creating a secondary index on 'sbtest15'...

Inserting 100000 records into 'sbtest9'

Inserting 100000 records into 'sbtest6'

Inserting 100000 records into 'sbtest2'

Creating a secondary index on 'sbtest7'...

Creating a secondary index on 'sbtest9'...

Creating a secondary index on 'sbtest6'...

Creating a secondary index on 'sbtest10'...

Creating a secondary index on 'sbtest3'...

Creating a secondary index on 'sbtest2'...

查看表的主副本分布情况：

MySQL [oceanbase]> select c.tenant_name,

-> b.table_name,

-> a.zone,

-> case when a.role=1 then 'leader' when a.role=2 then 'follower' else null end role,

-> concat(a.svr_ip,':',a.svr_port) server

-> from __all_virtual_meta_table a,__all_virtual_table b,gv$tenant c

-> where a.tenant_id=b.tenant_id and a.table_id=b.table_id and a.tenant_id=c.tenant_id and a.role=1

-> order by server;

+-------------+------------+-------+--------+--------------------+

+-------------+------------+-------+--------+--------------------+

+-------------+------------+-------+--------+--------------------+

15 rows in set (0.096 sec)

在查询结果中显示15张表的主副本是平均分布到每个observer节点上的，符合预期。

–执行压测

[root@ob02 lua]# ./oltp_read_write.lua --mysql-host=10.0.14.22 --mysql-port=2883 --mysql-user=root@obcp_t1 --mysql-password='' --mysql-db=test --tables=15 --table-size=100000 --report-interval=2 --time=60 --threads=15 --auto_inc=off --mysql-ignore-errors --db-ps-mode=disable run

sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:

Number of threads: 15

Report intermediate results every 2 second(s)

Initializing random number generator from current time

Initializing worker threads...

Threads started!

[ 2s ] thds: 15 tps: 0.00 qps: 52.17 (r/w/o: 45.24/0.00/6.92) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00

[ 4s ] thds: 15 tps: 0.00 qps: 33.18 (r/w/o: 33.18/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00

[ 6s ] thds: 15 tps: 0.00 qps: 29.66 (r/w/o: 17.60/7.04/5.03) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00

[ 8s ] thds: 15 tps: 5.53 qps: 33.65 (r/w/o: 8.54/10.05/15.07) lat (ms,95%): 7754.26 err/s: 0.00 reconn/s: 0.00

[ 10s ] thds: 15 tps: 0.99 qps: 61.84 (r/w/o: 59.37/0.49/1.98) lat (ms,95%): 8333.38 err/s: 0.00 reconn/s: 0.00

[ 12s ] thds: 15 tps: 0.00 qps: 37.12 (r/w/o: 28.09/6.02/3.01) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00

[ 14s ] thds: 15 tps: 4.01 qps: 39.12 (r/w/o: 11.54/11.54/16.05) lat (ms,95%): 6476.48 err/s: 0.00 reconn/s: 0.00

[ 16s ] thds: 15 tps: 2.99 qps: 64.85 (r/w/o: 56.37/2.00/6.49) lat (ms,95%): 14827.42 err/s: 0.00 reconn/s: 0.00

[ 18s ] thds: 15 tps: 0.50 qps: 39.64 (r/w/o: 30.61/5.52/3.51) lat (ms,95%): 16224.31 err/s: 0.00 reconn/s: 0.00

[ 20s ] thds: 15 tps: 1.93 qps: 36.66 (r/w/o: 15.44/11.58/9.65) lat (ms,95%): 6360.91 err/s: 0.00 reconn/s: 0.00

[ 22s ] thds: 15 tps: 4.13 qps: 42.33 (r/w/o: 30.46/3.10/8.78) lat (ms,95%): 7615.89 err/s: 0.00 reconn/s: 0.00

[ 24s ] thds: 15 tps: 1.44 qps: 42.32 (r/w/o: 35.59/2.89/3.85) lat (ms,95%): 8638.96 err/s: 0.00 reconn/s: 0.00

[ 26s ] thds: 15 tps: 1.55 qps: 40.32 (r/w/o: 28.43/7.24/4.65) lat (ms,95%): 5312.73 err/s: 0.00 reconn/s: 0.00

[ 28s ] thds: 15 tps: 1.02 qps: 36.12 (r/w/o: 20.86/9.67/5.60) lat (ms,95%): 5709.50 err/s: 0.00 reconn/s: 0.00

[ 30s ] thds: 15 tps: 4.47 qps: 47.63 (r/w/o: 29.28/5.46/12.90) lat (ms,95%): 10343.39 err/s: 0.00 reconn/s: 0.00

[ 32s ] thds: 15 tps: 2.02 qps: 46.43 (r/w/o: 39.87/1.01/5.55) lat (ms,95%): 7895.16 err/s: 0.00 reconn/s: 0.00

[ 34s ] thds: 15 tps: 0.50 qps: 51.29 (r/w/o: 38.72/5.03/7.54) lat (ms,95%): 5709.50 err/s: 0.00 reconn/s: 0.00

[ 36s ] thds: 15 tps: 1.94 qps: 24.69 (r/w/o: 9.68/7.75/7.26) lat (ms,95%): 7895.16 err/s: 0.00 reconn/s: 0.00

[ 38s ] thds: 15 tps: 4.58 qps: 52.97 (r/w/o: 38.20/4.07/10.70) lat (ms,95%): 7754.26 err/s: 0.00 reconn/s: 0.00

[ 40s ] thds: 15 tps: 0.51 qps: 59.79 (r/w/o: 44.97/8.69/6.13) lat (ms,95%): 8955.74 err/s: 0.00 reconn/s: 0.00

[ 42s ] thds: 15 tps: 3.43 qps: 39.65 (r/w/o: 22.03/6.36/11.26) lat (ms,95%): 11732.86 err/s: 0.00 reconn/s: 0.00

[ 44s ] thds: 15 tps: 3.04 qps: 53.15 (r/w/o: 43.03/3.54/6.58) lat (ms,95%): 6360.91 err/s: 0.00 reconn/s: 0.00

[ 46s ] thds: 15 tps: 1.46 qps: 47.25 (r/w/o: 32.64/7.31/7.31) lat (ms,95%): 7615.89 err/s: 0.00 reconn/s: 0.00

[ 48s ] thds: 15 tps: 2.07 qps: 34.21 (r/w/o: 20.21/6.74/7.26) lat (ms,95%): 8638.96 err/s: 0.00 reconn/s: 0.00

[ 50s ] thds: 15 tps: 2.99 qps: 61.72 (r/w/o: 49.28/5.48/6.97) lat (ms,95%): 6835.96 err/s: 0.00 reconn/s: 0.00

[ 52s ] thds: 15 tps: 1.98 qps: 43.50 (r/w/o: 26.69/8.90/7.91) lat (ms,95%): 9624.59 err/s: 0.00 reconn/s: 0.00

[ 54s ] thds: 15 tps: 3.49 qps: 57.41 (r/w/o: 38.94/6.99/11.48) lat (ms,95%): 7754.26 err/s: 0.00 reconn/s: 0.00

[ 56s ] thds: 15 tps: 2.03 qps: 32.42 (r/w/o: 21.28/4.56/6.59) lat (ms,95%): 7086.63 err/s: 0.00 reconn/s: 0.00

[ 58s ] thds: 15 tps: 1.94 qps: 63.03 (r/w/o: 51.88/4.36/6.79) lat (ms,95%): 5507.54 err/s: 0.00 reconn/s: 0.00

[ 60s ] thds: 15 tps: 1.00 qps: 42.58 (r/w/o: 24.04/11.02/7.51) lat (ms,95%): 7754.26 err/s: 0.00 reconn/s: 0.00

[ 62s ] thds: 12 tps: 5.61 qps: 23.45 (r/w/o: 10.70/5.61/7.14) lat (ms,95%): 7754.26 err/s: 0.00 reconn/s: 0.00

SQL statistics:

queries performed:

write: 365

other: 463

total: 2760

transactions: 138 (2.17 per sec.)

queries: 2760 (43.49 per sec.)

ignored errors: 0 (0.00 per sec.)

reconnects: 0 (0.00 per sec.)

General statistics:

total time: 63.4365s

total number of events: 138

Latency (ms):

min: 3832.97

avg: 6656.86

max: 16191.84

95th percentile: 8638.96

sum: 918647.29

Threads fairness:

events (avg/stddev): 9.2000/0.83

execution time (avg/stddev): 61.2432/1.20

清除数据

./oltp_read_write.lua --mysql-host=10.0.14.22 --mysql-port=2883 --mysql-user=root@obcp_t1 --mysql-password=root --mysql-db=sbtest --tables=15 --table-size=100000 --report-interval=2 --time=60 --threads=15 --mysql-ignore-errors --db-ps-mode=disable cleanup