Dear all:
I want to test innodb table’s insert performance using mysqlslap!
The mysql i used is :
mysql> select version();
±-----------+
| version() |
±-----------+
| 5.1.34-log |
±-----------+
1 row in set (0.00 sec)
The only table used is:
mysql> show create table t1;
±------±-------------------------------------------------- ------------------------------------------------------------ ---------------------------------------+
| Table | Create Table |
±------±-------------------------------------------------- ------------------------------------------------------------ ---------------------------------------+
| t1 | CREATE TABLE t1 (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(50) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
±------±-------------------------------------------------- ------------------------------------------------------------ ---------------------------------------+
1 row in set (0.00 sec)
And Here is the benchmark result:
[root@sns-blog arthur]# /usr/local/mysql/bin/mysqlslap --create-schema=“test” --concurrency=8 --query=“insert into t1(name) values(‘111’)” --number-of-queries=10000 --iterations=1
Benchmark
Average number of seconds to run all queries: 0.414 seconds
Minimum number of seconds to run all queries: 0.414 seconds
Maximum number of seconds to run all queries: 0.414 seconds
Number of clients running queries: 8
Average number of queries per client: 1250
[root@sns-blog arthur]# /usr/local/mysql/bin/mysqlslap --create-schema=“test” --concurrency=4 --query=“insert into t1(name) values(‘111’)” --number-of-queries=10000 --iterations=1
Benchmark
Average number of seconds to run all queries: 0.668 seconds
Minimum number of seconds to run all queries: 0.668 seconds
Maximum number of seconds to run all queries: 0.668 seconds
Number of clients running queries: 4
Average number of queries per client: 2500
[root@sns-blog arthur]# /usr/local/mysql/bin/mysqlslap --create-schema=“test” --concurrency=1 --query=“insert into t1(name) values(‘111’)” --number-of-queries=10000 --iterations=1
Benchmark
Average number of seconds to run all queries: 1.124 seconds
Minimum number of seconds to run all queries: 1.124 seconds
Maximum number of seconds to run all queries: 1.124 seconds
Number of clients running queries: 1
Average number of queries per client: 10000
[root@sns-blog arthur]# /usr/local/mysql/bin/mysqlslap --create-schema=“test” --iterations=1
Benchmark
Average number of seconds to run all queries: 3.810 seconds
Minimum number of seconds to run all queries: 3.810 seconds
Maximum number of seconds to run all queries: 3.810 seconds
Number of clients running queries: 8
Average number of queries per client: 12500
If " Average number of seconds to run all queries" means the time used by one client to execute all queries,the four benchmark result explain the total insert rows per second are
1250/0.414=3019,3742,8896,3280 respectively and it’s obvious not the right result!
so my questions are
1.what does the " Average number of seconds to run all queries" mean here?
2.can i test the insert performance of mysql using mysqlslap this way?
3.is there any way more better to test the mysql insert performanace?
4.what does mysqlslap used for in reality?
Thanks in advance! )