Mysql 压力测试

压力测试准备

mysql > use test100w;
mysql > source t100w.sql

 

未做优化之前的压力测试

mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 \
--iterations=1 --create-schema='test100w' \
--query="select * from test100w.t100w where k2='MN89'" \
engine=innodb --number-of-queries=2000 -uroot -p123 -verbose

#测试结果
Benchmark
    Running for engine rbose
    Average number of seconds to run all queries: 1583.619 seconds
    Minimum number of seconds to run all queries: 1583.619 seconds
    Maximum number of seconds to run all queries: 1583.619 seconds
    Number of clients running queries: 100
    Average number of queries per client: 20

创建普通索引后

mysql> alter table test100w.t100w add index index_c(k2);
mysql> desc test100w.t100w;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| id    | int(11)   | YES  |     | NULL              |                             |
| num   | int(11)   | YES  |     | NULL              |                             |
| k1    | char(2)   | YES  |     | NULL              |                             |
| k2    | char(4)   | YES  | MUL | NULL              |                             |
| dt    | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
5 rows in set (0.00 sec)

#加了普通索引后测试结果
Benchmark
    Running for engine rbose
    Average number of seconds to run all queries: 3.226 seconds
    Minimum number of seconds to run all queries: 3.226 seconds
    Maximum number of seconds to run all queries: 3.226 seconds
    Number of clients running queries: 100
    Average number of queries per client: 20