GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Using MysqlSlap to test the performance of insert in mysql

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Using MysqlSlap to test the performance of insert in mysql

    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" --concurrency=8 --query="insert into t1(name) values('111')" --number-of-queries=100000 --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! )

  • #2
    All numbers indicate the total time for the test per iteration. If you increase the number of iterations, min/max/avg will differ from eachother.

    Comment

    Working...
    X