数据库优化-基准测试（三）

短命女 2022-08-02 05:49 227阅读 0赞

## 基准测试工具 ##

### 基准测试工具：mysqlslap ###

是MySQL官方提供的性能基准测试工具，通过客户端模拟工作负载。  
其执行包括三个阶段：  
1. 创建表结构和加载数据  
2. 运行测试  
3. 清理数据

#例子1：--only-print 只输出SQL语句并打印 --auto-generate-sql 代表用系统自己生成的SQL脚本来测试
    $ mysqlslap --only-print --auto-generate-sql
    DROP SCHEMA IF EXISTS `mysqlslap`;
    CREATE SCHEMA `mysqlslap`;
    use mysqlslap;
    CREATE TABLE `t1` (intcol1 INT(32) ,charcol1 VARCHAR(128));
    INSERT INTO t1 VALUES (...);
    INSERT INTO t1 VALUES (...);
    ....
    SELECT intcol1,charcol1 FROM t1;
    INSERT INTO t1 VALUES (...);
    SELECT intcol1,charcol1 FROM t1;
    INSERT INTO t1 VALUES (...);
    ...
    DROP SCHEMA IF EXISTS `mysqlslap`;

#例子2：只输出SQL语句，对命令计数并打印
    $ mysqlslap --only-print --auto-generate-sql | awk '{print $1" "$2}' | sort | uniq -c | sort -n -r
    105 INSERT INTO 5 SELECT intcol1,charcol1 2 DROP SCHEMA 1 use mysqlslap;
    1 CREATE TABLE 1 CREATE SCHEMA

说明：

*  默认，创建和删除数据库`mysqlslap`
 *  可通过–create-schema=value指定一个不同的数据库名称
 *  在数据加载阶段每个线程执行100个INSERT语句（使用–auto-generate-sql-write-number）
 *  在测试阶段每个线程执行许多INSERT和SELECT语句（使用–auto-generate-sql-loadtype）

#例子3：计数INSERT/SELECT语句，--number-of-queries 代表总共要运行多少次查询 $ mysqlslap --only-print --auto-generate-sql --number-of-queries=100 | awk '{print $1}' | egrep '(INSERT|SELECT)' | sort | uniq -c | sort -n -r 154 INSERT INTO 45 SELECT intcol1,charcol1

#例子4：计数INSERT/SELECT语句，--concurrency 代表并发数量 $ mysqlslap --only-print --auto-generate-sql --concurrency=5 --number-of-queries=100 | awk '{print $1}' | egrep '(INSERT| SELECT)' | sort | uniq -c | sort -n -r 154 INSERT INTO 45 SELECT intcol1,charcol1

#例子5：计数INSERT/SELECT语句，--iterations 代表要运行这些测试多少次 $ mysqlslap --only-print --auto-generate-sql --iteration=5 --number-of-queries=100 | awk '{print $1}' | egrep '(INSERT| SELECT)' | sort | uniq -c | sort -n -r 770 INSERT INTO 225 SELECT intcol1,charcol1

#例子6：计数INSERT/SELECT语句，--auto-generate-sql-write-number 每个线程生成write sql语句数量 $ mysqlslap --only-print --auto-generate-sql --number-of-queries=100 --auto-generate-sql-write-number=10000 | awk '{print $1}' | egrep '(INSERT|SELECT)' | sort | uniq -c | sort -n -r 10054 INSERT 45 SELECT

#例子7：计数INSERT/SELECT语句，--auto-generate-sql-load-type=read 代表要测试的是读还是写还是两者混合的 $ mysqlslap --only-print --auto-generate-sql --number-of-queries=100 –auto-generate-sql-write-number=10000 --auto-generate-sql-load-type=read | awk '{print $1}' | egrep '(INSERT|SELECT)' | sort | uniq -c | sort -n -r 9999 INSERT 100 SELECT

#例子8：并发数1,2,3次执行性能比较，共执行查询100次，生成100000个INSERT语句
    $ mysqlslap --auto-generate-sql --concurrency=1,2,3 –-iteration=10 \
    --number-of-queries=100 –auto-generate-sql-write-number=100000
    Benchmark
    Average number of seconds to run all queries: 4.522 seconds
    Minimum number of seconds to run all queries: 4.216 seconds
    Maximum number of seconds to run all queries: 4.648 seconds
    Number of clients running queries: 1
    Average number of queries per client: 100
    Benchmark
    Average number of seconds to run all queries: 3.025 seconds
    Minimum number of seconds to run all queries: 2.737 seconds
    Maximum number of seconds to run all queries: 3.227 seconds
    Number of clients running queries: 2
    Average number of queries per client: 50
    Benchmark
    Average number of seconds to run all queries: 2.618 seconds
    Minimum number of seconds to run all queries: 2.338 seconds
    Maximum number of seconds to run all queries: 2.746 seconds
    Number of clients running queries: 3
    Average number of queries per client: 33

#例子9：比较myisam,innodb引擎性能，并发执行数4，共执行100次查询
    $ mysqlslap --auto-generate-sql –-concurrency=4 \
    --engine=myisam,innodb --number-of-queries=100 \
    --iteration=10 --auto-generate-sql-write-number=100000
    Benchmark
    Running for engine myisam
    Average number of seconds to run all queries: 2.232 seconds
    Minimum number of seconds to run all queries: 2.003 seconds
    Maximum number of seconds to run all queries: 2.319 seconds
    Number of clients running queries: 4
    Average number of queries per client: 25
    Benchmark
    Running for engine innodb
    Average number of seconds to run all queries: 5.332 seconds
    Minimum number of seconds to run all queries: 5.314 seconds
    Maximum number of seconds to run all queries: 5.370 seconds
    Number of clients running queries: 4
    Average number of queries per client: 25

#例子10：比较myisam,innodb引擎性能，并发执行数4，共执行1000次查询
    $ mysqlslap --auto-generate-sql --concurrency=4 \
    --engine=myisam,innodb --number-of-queries=1000 \
    --iteration=10 --auto-generate-sql-write-number=10000
    Benchmark
    Running for engine myisam
    Average number of seconds to run all queries: 2.059 seconds
    Minimum number of seconds to run all queries: 1.937 seconds
    Maximum number of seconds to run all queries: 2.169 seconds
    Number of clients running queries: 4
    Average number of queries per client: 250
    Benchmark
    Running for engine innodb
    Average number of seconds to run all queries: 5.604 seconds
    Minimum number of seconds to run all queries: 5.560 seconds
    Maximum number of seconds to run all queries: 5.659 seconds
    Number of clients running queries: 4
    Average number of queries per client: 250

#例子11：比较myisam,innodb引擎性能，并发执行数4，共执行1000次查询
    #concurrent_insert=0 无论MyISAM存储引擎的表数据文件的中间部分是否存在因为删除数据而留下的空闲空间，都不允许并发INSERT
    SET GLOBAL concurrent_insert=0;
    $ mysqlslap --auto-generate-sql –-concurrency=4 –engine=myisam,innodb \
    --number-of-queries=1000 --iteration=10 --auto-generate-sql-write-number=10000
    Benchmark
    Running for engine myisam
    Average number of seconds to run all queries: 3.256 seconds
    Minimum number of seconds to run all queries: 3.210 seconds
    Maximum number of seconds to run all queries: 3.317 seconds
    Number of clients running queries: 4
    Average number of queries per client: 250
    Benchmark
    Running for engine innodb
    Average number of seconds to run all queries: 5.615 seconds
    Minimum number of seconds to run all queries: 5.585 seconds
    Maximum number of seconds to run all queries: 5.699 seconds
    Number of clients running queries: 4
    Average number of queries per client: 250
    SET GLOBAL concurrent_insert=1;

#例子12：比较memory,myisam,innodb引擎性能，并发执行数4，共执行1000次查询
    #--auto-generate-sql-add-autoincrement 代表对生成的表自动添加auto_increment列
    mysqlslap --auto-generate-sql -–concurrency=4 \
    --engine=memory,myisam,innodb --iteration=10 \
    --number-of-queries=1000 –auto-generate-sql-write-number=10000 --auto-generate-sql-add-autoincrement
    CREATE TABLE `t1` (id serial,intcol1 INT(32) ,charcol1 VARCHAR(128));
    Running for engine memory
    Average number of seconds to run all queries: 0.035 seconds
    Running for engine myisam
    Average number of seconds to run all queries: 0.038 seconds
    Running for engine innodb
    Average number of seconds to run all queries: 0.032 seconds

InnoDB比MEMORY和MyISAM更快？

#例子13：增加测试的持续时间。比较memory,myisam,innodb引擎性能，并发执行数4，共执行10000次查询
    $ mysqlslap --auto-generate-sql --concurrency=4
    --engine=memory,myisam,innodb --iteration=10 \
    --number-of-queries=10000 --auto-generate-sql-writenumber=
    10000 --auto-generate-sql-add-autoincrement
    Running for engine memory
    Average number of seconds to run all queries: 0.430 seconds
    Running for engine myisam
    Average number of seconds to run all queries: 0.467 seconds
    Running for engine innodb
    Average number of seconds to run all queries: 0.327 seconds

结果InnoDB胜！

#例子14：增加更多字段。比较memory,myisam,innodb引擎性能，并发执行数4，共执行10000次查询
    #--auto-generate-sql, -a 自动生成测试表和数据
    #--concurrency=N, -c N 表示并发量，模拟多少个客户端同时执行select
    #-number-int-cols=N, -y N 自动生成的测试表中包含多少个数字类型的列，默认1
    #--number-char-cols=N, -x N 自动生成的测试表中包含多少个字符类型的列，默认1
    #--iterations=N, -i N 测试执行的迭代次数，代表要在不同并发环境下，各自运行测试多少次
    $ mysqlslap mysqlslap -a -c4 -y4 -x4
    --engine=memory,myisam,innodb -i10 --number-ofqueries=
    10000 --auto-generate-sql-write-number=10000
    --auto-generate-sql-add-autoincrement
    Running for engine memory
    Average number of seconds to run all queries: 0.504 seconds
    Running for engine myisam
    Average number of seconds to run all queries: 0.526 seconds
    Running for engine innodb
    Average number of seconds to run all queries: 0.468 seconds

InnoDB仍然获胜，但比上次慢了些。

#例子15：增加并发度。比较memory,myisam,innodb引擎性能，并发执行数8，共执行10000次查询
    $ mysqlslap mysqlslap -a -c8 -y4 -x4
    --engine=memory,myisam,innodb -i10 --number-ofqueries=
    10000 --auto-generate-sql-write-number=10000
    --auto-generate-sql-add-autoincrement
    Running for engine memory
    Average number of seconds to run all queries: 0.526 seconds
    Running for engine myisam
    Average number of seconds to run all queries: 0.596 seconds
    Running for engine innodb
    Average number of seconds to run all queries: 0.657 seconds

结果InnoDB最慢。

#例子16：工作负载，读取主键。比较memory,myisam,innodb引擎性能，并发执行数8，共执行10000次查询
    #--auto-generate-sql-load-type=type 测试语句的类型。代表要测试的环境是读操作还是写操作还是两者混合的。取值包括：read，key，write，update和mixed(默认)
    $ mysqlslap mysqlslap --auto-generate-sql-load-type=key -a
    -c8 -y4 -x4 --engine=memory,myisam,innodb -i10 --number-ofqueries=
    10000 --auto-generate-sql-write-number=10000 –autogenerate-
    sql-add-autoincrement
    Running for engine memory
    Average number of seconds to run all queries: 0.278 seconds
    Running for engine myisam
    Average number of seconds to run all queries: 0.328 seconds
    Running for engine innodb
    Average number of seconds to run all queries: 0.324 seconds