Shulou(Shulou.com)05/31 Report--

This article is about how to use mysqlslap performance testing tools in Mysql5.1. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

1. Installation

Download: http://dev.mysql.com/downloads/mysql/5.1.html

This is the tool that comes with MYSQL version 5.1. So you can use it after you install 5.1.

You may encounter errors about libmysqlclient.so.16 during installation.

See: http://steven1981.itpub.net/post/7967/493101

two。 Use

# help

Mysqlslap-help

Common options:

-- concurrency stands for the number of concurrency, multiple can be separated by commas, of course, you can also use your own separator, this time to use the-- delimiter switch.

-- engines represents the engine to be tested, and there can be multiple, separated by delimiters.

-- iterations represents how many times to run these tests.

Auto-generate-sql stands for testing with a SQL script generated by the system itself.

-- auto-generate-sql-load-type stands for testing whether to read or write or a mix of both (read,write,update,mixed)

-- number-of-queries represents the total number of queries to run. The number of queries run by each customer can be calculated as the total number of queries / concurrency. For example, the penultimate result is 2, 200, 100.

-- debug-info represents additional output of CPU and memory related information.

-- number-int-cols represents several properties of type INTEGER in the sample table.

The meaning of number-char-cols is the same as above.

-- create-schema represents a self-defined schema (in MySQL, that is, a library).

-- query represents its own SQL script.

-- only-print can use this option if you just want to print to see what the SQL statement is.

3. test

# Test environment

PowerEdge 1950 / single disk / 10G MEM

OS: RHEL 5.3

MySQL version 5.1.40

Innodb_buffer_pool_size=2G

Innodb_additional_mem_pool_size = 100m

Innodb_max_dirty_pages_pct = 10

Key_buffer_size=320M

Bulk_insert_buffer_size = 64m

Sync_binlog=0

# simple test results:

Mysqlslap-- defaults-file=/etc/my.cnf-- concurrency=50100200-- iterations=1-- auto-generate-sql-unique-query-number=1-- number-int-cols=4-- auto-generate-sql--auto-generate-sql-add-autoincrement-- auto-generate-sql-load-type=write-- engine=innodb-- number-of-queries=200000-- commit=2000-- debug-info-uroot-palisoftdb-h227.0.0.1-S / tmp/mysql.sock

Benchmark

Running for engine innodb

Average number of seconds to run all queries: 131.013 seconds

Minimum number of seconds to run all queries: 131.013 seconds

Maximum number of seconds to run all queries: 131.013 seconds

Number of clients running queries: 50

Average number of queries per client: 4000

Benchmark

Running for engine innodb

Average number of seconds to run all queries: 131.258 seconds

Minimum number of seconds to run all queries: 131.258 seconds

Maximum number of seconds to run all queries: 131.258 seconds

Number of clients running queries: 100

Average number of queries per client: 2000

Benchmark

Running for engine innodb

Average number of seconds to run all queries: 128.142 seconds

Minimum number of seconds to run all queries: 128.142 seconds

Maximum number of seconds to run all queries: 128.142 seconds

Number of clients running queries: 200

Average number of queries per client: 1000

Mysqlslap-- defaults-file=/etc/my.cnf-- concurrency=50100200-- iterations=1-- auto-generate-sql-unique-query-number=1-- number-int-cols=4-- auto-generate-sql--auto-generate-sql-add-autoincrement-- auto-generate-sql-load-type=write-- engine=myisam-- number-of-queries=200000-- debug-info-uroot-palisoftdb-h227.0.0.1-S / tmp/mysql.sock

Benchmark

Running for engine myisam

Average number of seconds to run all queries: 42.607 seconds

Minimum number of seconds to run all queries: 42.607 seconds

Maximum number of seconds to run all queries: 42.607 seconds

Number of clients running queries: 50

Average number of queries per client: 4000

Benchmark

Running for engine myisam

Average number of seconds to run all queries: 45.547 seconds

Minimum number of seconds to run all queries: 45.547 seconds

Maximum number of seconds to run all queries: 45.547 seconds

Number of clients running queries: 100

Average number of queries per client: 2000

Benchmark

Running for engine myisam

Average number of seconds to run all queries: 47.517 seconds

Minimum number of seconds to run all queries: 47.517 seconds

Maximum number of seconds to run all queries: 47.517 seconds

Number of clients running queries: 200

Average number of queries per client: 1000

For MYISAM, if you only insert it, it is equivalent to 40000 entries per second.

For INNODB, if you only insert it, it is equivalent to 15000 entries per second.

Thank you for reading! This is the end of this article on "how to use mysqlslap performance testing tools in Mysql5.1". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.