数据库性能测试工具

Sysbench是一款基于LuaJIT的,模块化多线程基准测试工具,常用于数据库基准测试。通过内置的数据库测试模型,采用多线程并发操作来评估数据库的性能。了解Sysbench更多详情,请访问https://github.com/akopytov/sysbench

本次测试使用的Sysbench版本为1.0.12,具体的安装命令如下:

# wget -c https://github.com/akopytov/sysbench/archive/1.0.12.zip

# yum install autoconf libtool mysql mysql-devel vim unzip

# unzip 1.0.12.zip

# cd sysbench-1.0.12

# ./autogen.sh

# ./configure

# make

# make install

测试步骤

请根据实际信息,替换数据库、连接IP与用户密码。

  1. 导入数据。
    1. 使用mysql命令登录数据库,并创建测试数据库“loadtest”mysql -u root -P 3306 -h <host> -p -e “create database loadtest”
    2. 使用sysbench命令导入测试背景数据到“loadtest”数据库。sysbench –test=/usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua –db-driver=mysql –mysql-db=loadtest –mysql-user=root –mysql-password=<password> –mysql-port=3306 –mysql-host=<host> –oltp-tables-count=64 –oltp-table-size=10000000 –num-threads=20 prepare
  2. 压测数据。sysbench –test=/usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua –db-driver=mysql –mysql-db=loadtest –mysql-user=root –mysql-password=<password> –mysql-port=3306 –mysql-host=<host> –oltp-tables-count=64 –oltp-table-size=10000000 –max-time=3600 –max-requests=0 –num-threads=200 –report-interval=3 –forced-shutdown=1 run
  3. 清理数据。sysbench –test=/usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua –db-driver=mysql –mysql-db=loadtest –mysql-user=root –mysql-password=<password> –mysql-port=3306 –mysql-host=<host> –oltp-tables-count=64 –oltp-table-size=10000000 –max-time=3600 –max-requests=0 –num-threads=200 cleanup

测试模型

  1. 表结构CREATE TABLE `sbtest` (`id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,`k` INTEGER UNSIGNED DEFAULT ‘0’ NOT NULL,`c` CHAR(120) DEFAULT ” NOT NULL,`pad` CHAR(60) DEFAULT ” NOT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB
  2. 读写比Sysbench默认提交的事务中包含18条SQL语句,具体执行语句和条数如下:
    • 主键SELECT语句,10条:SELECT c FROM ${rand_table_name} where id=${rand_id};
    • 范围SELECT语句,4条:SELECT c FROM ${rand_table_name} WHERE id BETWEEN ${rand_id_start} AND ${rand_id_end};SELECT SUM(K) FROM ${rand_table_name} WHERE id BETWEEN ${rand_id_start} AND ${rand_id_end};SELECT c FROM ${rand_table_name} WHERE id BETWEEN ${rand_id_start} AND ${rand_id_end} ORDER BY c;SELECT DISTINCT c FROM ${rand_table_name} WHERE id BETWEEN ${rand_id_start} AND ${rand_id_end} ORDER BY c;
    • UPDATE语句,2条:UPDATE ${rand_table_name} SET k=k+1 WHERE id=${rand_id}UPDATE ${rand_table_name} SET c=${rand_str} WHERE id=${rand_id}
    • DELETE语句,1条:DELETE FROM ${rand_table_name} WHERE id=${rand_id}
    • INSERT语句,1条:INSERT INTO ${rand_table_name} (id, k, c, pad) VALUES (${rand_id},${rand_k},${rand_str_c},${rand_str_pad})

测试指标

  • TPS:Transaction Per Second,数据库每秒执行的事务数,每个事务中包含18条SQL语句。
  • QPS:Query Per Second,数据库每秒执行的SQL数,包含insert、select、update、delete等。

发表评论