欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

paip.mysql性能测试by mysqlslap_MySQL

程序员文章站 2024-02-09 14:34:46
...
bitsCN.com

paip.mysql性能测试by mysqlslap

///////////模式

usr/local/mysql/bin/mysqlslap --defaults-file=/etc/my.cnf --concurrency=200 --iterations=1 --number-int-cols=1 --auto-generate-sql --auto-generate-sql-load-type=write --engine=myisam,innodb --number-of-queries=200 -S/tmp/mysql.sock --debug-info -uroot -p123

concurrency 客户端数量

--auto-generate-sql-load-type 代表要测试的是读还是写还是两者混合的(read,write,update,mixed)

--debug-info 代表要额外输出CPU以及内存的相关信息

///////////////////我总结的模式

"C:/MySQL Server 5.6/bin/mysqlslap.exe" --defaults-file="C:/MySQL Server 5.6/my-default.ini" --concurrency=1 --auto-generate-sql --auto-generate-sql-load-type=write --engine=innodb --number-of-queries=100 --port=3307 -uroot -p

两个测试auto-generate-sql-load-type=write --number-of-queries=100

测试读能力 read ,5000

/////////获得help

/mysqlslap.exe" --help

C:/Users/Administrator>"C:/MySQL Server 5.6/bin/mysqlslap.exe" --defaults-file

="C:/MySQL Server 5.6/my-default.ini" --concurrency=1000 --auto-generate-sql --

auto-generate-sql-load-type=write --engine=innodb --number-of-queries=1000

--port=3307 -uroot -p

Enter password:

Benchmark

Running for engine innodb

Average number of seconds to run all queries: 2.856 seconds

Minimum number of seconds to run all queries: 2.856 seconds

Maximum number of seconds to run all queries: 2.856 seconds

Number of clients running queries: 1000

Average number of queries per client: 1

Running for engine innodb

Average number of seconds to run all queries: 3.841 seconds

Minimum number of seconds to run all queries: 3.841 seconds

Maximum number of seconds to run all queries: 3.841 seconds

Number of clients running queries: 1000

Average number of queries per client: 1

Benchmark

Running for engine innodb

Average number of seconds to run all queries: 3.560 seconds

Minimum number of seconds to run all queries: 3.560 seconds

Maximum number of seconds to run all queries: 3.560 seconds

Number of clients running queries: 1000

Average number of queries per client: 1

"C:/MySQL Server 5.6/bin/mysqlslap.exe" --defaults-file="C:/MySQL Server 5.6/my-default.ini" --concurrency=100 --auto-generate-sql --auto-generate-sql-load-type=write --engine=innodb --number-of-queries=1000 --port=3307 -uroot -p

Benchmark

Running for engine innodb

Average number of seconds to run all queries: 2.310 seconds

Minimum number of seconds to run all queries: 2.310 seconds

Maximum number of seconds to run all queries: 2.310 seconds

Number of clients running queries: 100

Average number of queries per client: 10

"C:/MySQL Server 5.6/bin/mysqlslap.exe" --defaults-file="C:/MySQL Server 5.6/my-default.ini" --concurrency=1 --auto-generate-sql --auto-generate-sql-load-type=write --engine=innodb --number-of-queries=1000 --port=3307 -uroot -p

Running for engine innodb

Average number of seconds to run all queries: 50.210 seconds

Minimum number of seconds to run all queries: 50.210 seconds

Maximum number of seconds to run all queries: 50.210 seconds

Number of clients running queries: 1

Average number of queries per client: 1000

-------write 60

"C:/MySQL Server 5.6/bin/mysqlslap.exe" --defaults-file="C:/MySQL Server 5.6/my-default.ini" --concurrency=1 --auto-generate-sql --auto-generate-sql-load-type=write --engine=innodb --number-of-queries=60 --port=3307 -uroot -p

Benchmark

Running for engine innodb

Average number of seconds to run all queries: 2.498 seconds

Minimum number of seconds to run all queries: 2.498 seconds

Maximum number of seconds to run all queries: 2.498 seconds

Number of clients running queries: 1

Average number of queries per client: 60

"C:/MySQL Server 5.6/bin/mysqlslap.exe" --defaults-file="C:/MySQL Server 5.6/my-default.ini" --concurrency=1 --auto-generate-sql --auto-generate-sql-load-type=read --engine=innodb --number-of-queries=4000 --port=3307 -uroot -p

------------read 4000

C:/Users/Administrator> "C:/MySQL Server 5.6/bin/mysqlslap.exe" --defaults-f

le="C:/MySQL Server 5.6/my-default.ini" --concurrency=1 --auto-generate-sql --

uto-generate-sql-load-type=read --engine=innodb --number-of-queries=4000 -

port=3307 -uroot -p

Enter password:

Benchmark

Running for engine innodb

Average number of seconds to run all queries: 2.014 seconds

Minimum number of seconds to run all queries: 2.014 seconds

Maximum number of seconds to run all queries: 2.014 seconds

Number of clients running queries: 1

Average number of queries per client: 4000

----------read 2800

C:/Users/Administrator> "C:/MySQL Server 5.6/bin/mysqlslap.exe" --defaults-f

le="C:/MySQL Server 5.6/my-default.ini" --concurrency=1 --auto-generate-sql --

uto-generate-sql-load-type=read --engine=innodb --number-of-queries=2800 -

port=3307 -uroot -p

Enter password:

Benchmark

Running for engine innodb

Average number of seconds to run all queries: 1.780 seconds

Minimum number of seconds to run all queries: 1.780 seconds

Maximum number of seconds to run all queries: 1.780 seconds

Number of clients running queries: 1

Average number of queries per client: 2800

/////////////// Error when connecting to server: 1040 Too many connections

show VARIABLES like '%max_connections%'

mysql默认的连接为151个,最大10万...

///////////tf

"f:/MySQL Server 5.6sec/bin/mysqld.exe" --defaults-file="c:/MySQL Server 5.6/my-default.ini"

C:/Users/Administrator> "C:/MySQL Server 5.6/bin/mysqlslap.exe" --defaults-f

le="C:/MySQL Server 5.6/my-default.ini" --concurrency=1 --auto-generate-sql --

uto-generate-sql-load-type=read --engine=innodb --number-of-queries=2800 -

port=3307 -uroot -p

Enter password:

Benchmark

Running for engine innodb

Average number of seconds to run all queries: 1.780 seconds

Minimum number of seconds to run all queries: 1.780 seconds

Maximum number of seconds to run all queries: 1.780 seconds

Number of clients running queries: 1

Average number of queries per client: 2800

--------read 4000

C:/Users/Administrator> "C:/MySQL Server 5.6/bin/mysqlslap.exe" --defaults-f

le="C:/MySQL Server 5.6/my-default.ini" --concurrency=1 --auto-generate-sql --

uto-generate-sql-load-type=read --engine=innodb --number-of-queries=4000 -

port=3307 -uroot -p

Enter password:

Benchmark

Running for engine innodb

Average number of seconds to run all queries: 1.936 seconds

Minimum number of seconds to run all queries: 1.936 seconds

Maximum number of seconds to run all queries: 1.936 seconds

Number of clients running queries: 1

Average number of queries per client: 4000

-------write 60

"C:/MySQL Server 5.6/bin/mysqlslap.exe" --defaults-file="C:/MySQL Server 5.6/my-default.ini" --concurrency=1 --auto-generate-sql --auto-generate-sql-load-type=write --engine=innodb --number-of-queries=60 --port=3307 -uroot -p

C:/Users/Administrator> "C:/MySQL Server 5.6/bin/mysqlslap.exe" --defaults

e="C:/MySQL Server 5.6/my-default.ini" --concurrency=1 --auto-generate-sql

to-generate-sql-load-type=write --engine=innodb --number-of-queries=60

rt=3307 -uroot -p

Enter password:

Benchmark

Running for engine innodb

Average number of seconds to run all queries: 6.136 seconds

Minimum number of seconds to run all queries: 6.136 seconds

Maximum number of seconds to run all queries: 6.136 seconds

Number of clients running queries: 1

Average number of queries per client: 60

bitsCN.com