Mysqlslap: tutorial stress test - Revision history

Onnowpurbo: /* Menggunakan Custom Query */

2015-06-08T02:27:45Z

Menggunakan Custom Query

← Older revision		Revision as of 02:27, 8 June 2015
Line 1:		Line 1:

	==Penggunaan Dasar==		==Penggunaan Dasar==

Line 160:		Line 159:


	Kita dapat ~~You can tell mysqlslap to create a~~ custom ~~table for your load testing by using the~~ --create ~~command-line switch with a~~ CREATE TABLE command.		Kita dapat membuat custom tabel gunakan argmen --create dengan CREATE TABLE command.
	~~Comparing table engines~~

	A cool feature of mysqlslap is the ability to specify the table engine used in the test. This provides database designers with an easy way to compare the performance of different table types under different load conditions. The --engine switch accepts any of MySQL's supported table types and creates test tables using the corresponding storage engine. Here's an example of how it could be used:

	~~shell> /usr/local/mysql/bin/mysqlslap --user~~=~~john --auto-generate-sql --concurrency~~=~~100 --number-of-queries~~=~~700 --engine~~=~~innodb~~		==Membandingkan tabel engine==

	~~Benchmark~~

	~~Running for~~ engine innodb		mysqlslap --user=root --password=123456 --auto-generate-sql --concurrency=100 --number-of-queries=700 --engine=innodb

	Average number of seconds to run all queries: 1.~~240~~ seconds		Benchmark
			Running for engine innodb
			Average number of seconds to run all queries: 0.196 seconds
			Minimum number of seconds to run all queries: 0.196 seconds
			Maximum number of seconds to run all queries: 0.196 seconds
			Number of clients running queries: 100
			Average number of queries per client: 7

	~~Minimum number of seconds to run all queries: 1.240 seconds~~

	~~Maximum~~ number of ~~seconds to run all~~ queries~~: 1.240 seconds~~		mysqlslap --user=root --password=123456 --auto-generate-sql --concurrency=100 --number-of-queries=700 --engine=myisam

	Number of clients running queries: 100		Benchmark
			Running for engine myisam
			Average number of seconds to run all queries: 0.119 seconds
			Minimum number of seconds to run all queries: 0.119 seconds
			Maximum number of seconds to run all queries: 0.119 seconds
			Number of clients running queries: 100
			Average number of queries per client: 7

	~~Average number of queries per client: 7 shell>~~ ~~/usr/local/mysql/bin/~~mysqlslap --user=~~john~~ --auto-generate-sql --concurrency=100 --number-of-queries=700 --engine=~~myisam~~		mysqlslap --user=root --password=123456 --auto-generate-sql --concurrency=100 --number-of-queries=700 --engine=memory
	~~Benchmark~~

	~~Running for engine myisam~~		Benchmark
			Running for engine memory
	~~Average number of seconds to run all queries: 0.676 seconds~~		Average number of seconds to run all queries: 0.131 seconds
			Minimum number of seconds to run all queries: 0.131 seconds
	~~Minimum number of seconds to run all queries: 0.676 seconds~~		Maximum number of seconds to run all queries: 0.131 seconds
			Number of clients running queries: 100
	~~Maximum number of seconds to run all queries: 0.676 seconds~~		Average number of queries per client: 7

	~~Number of clients running queries: 100~~

	~~Average number of queries per client: 7~~

	~~shell> /usr/local/mysql/bin/mysqlslap --user=john --auto-generate-sql --concurrency=100 --number-of-queries=700 --engine=memory~~
	Benchmark

	Running for engine memory

	Average number of seconds to run all queries: 0.~~602~~ seconds

	Minimum number of seconds to run all queries: 0.~~602~~ seconds

	Maximum number of seconds to run all queries: 0.~~602~~ seconds

	Number of clients running queries: 100

	Average number of queries per client: 7

	~~Saving reports~~

	~~You might wish to save a mysqlslap report so you can compare it to a previous or future test run; you may also want to use the report as a reference when you're configuring new systems.~~

	~~The easiest way to save a mysqlslap report is to pipe the output of a mysqlslap run to a file, as below:~~		==Menyimpan Laporan==

	~~shell> /usr/local/mysql/bin/mysqlslap --user=john --auto-generate-sql --concurrency=100 --number-of-queries=1000 --number-char-cols=4 --number-int-cols=7 >> /tmp/output.log~~

	You can force mysqlslap to generate reports in CSV format; this is often useful if you need to import the data into a spreadsheet or database to build graphical reports from it. To do this, add the --csv switch to your mysqlslap command line and specify the output filename as an argument to this switch. Here's an example:		Cara paling gampang

	~~shell> /usr/local/mysql/bin/~~mysqlslap --~~csv~~=~~/tmp/output.csv~~ --~~user~~=~~john~~ --auto-generate-sql --concurrency=100 --number-of-queries=1000 --number-char-cols=4 --number-int-cols=7		mysqlslap --user=root --password=123456 --auto-generate-sql --concurrency=100 --number-of-queries=1000 --number-char-cols=4 --number-int-cols=7 >> /tmp/output.log

	~~Here's what the CSV file would contain if you peeked inside it:~~

	~~shell> cat /tmp/output~~.~~csv~~		Kita dapat memaksa untuk menyimpan dalam CSV format.
	~~,query,1.070,1.070,1.070,100,10~~

	~~Trying~~ mysqlslap		mysqlslap --csv=/tmp/output.csv --user=root --password=123456 --auto-generate-sql --concurrency=100 --number-of-queries=1000 --number-char-cols=4 --number-int-cols=7

	~~The mysqlslap utility makes it possible to benchmark and compare MySQL performance on different hardware~~, as well as accurately quantify the effect of a change in database design. Try mysqlslap out for yourself and see how well your database server behaves under pressure from thousands of client connections.		Isi CSV file,

			cat /tmp/output.csv

			,mixed,0.638,0.638,0.638,100,10
			.

	==Referensi==		==Referensi==

	* http://www.techrepublic.com/blog/how-do-i/how-do-i-stress-test-mysql-with-mysqlslap/		* http://www.techrepublic.com/blog/how-do-i/how-do-i-stress-test-mysql-with-mysqlslap/

Onnowpurbo at 02:21, 8 June 2015

2015-06-08T02:21:53Z

Show changes

Onnowpurbo at 01:40, 8 June 2015

2015-06-08T01:40:01Z

← Older revision		Revision as of 01:40, 8 June 2015
Line 11:		Line 11:
	This simple (and unrealistic) example uses mysqlslap to test server performance assuming only one client connection:		This simple (and unrealistic) example uses mysqlslap to test server performance assuming only one client connection:

	shell> /usr/local/mysql/bin/mysqlslap --user=john --auto-generate-sql		shell> /usr/local/mysql/bin/mysqlslap --user=john --auto-generate-sql

	Benchmark		Benchmark
Line 34:		Line 34:
	The --auto-generate-sql switch creates a table, executes an INSERT query and saves dummy data to it, executes a SELECT query to retrieve the dummy data, and then drops the table. You can see behind-the-scenes action by adding the -v switch to the mysqlslap command line (adding extra 'v's increases the verbosity level):		The --auto-generate-sql switch creates a table, executes an INSERT query and saves dummy data to it, executes a SELECT query to retrieve the dummy data, and then drops the table. You can see behind-the-scenes action by adding the -v switch to the mysqlslap command line (adding extra 'v's increases the verbosity level):

	shell> /usr/local/mysql/bin/mysqlslap --user=john --auto-generate-sql -vv		shell> /usr/local/mysql/bin/mysqlslap --user=john --auto-generate-sql -vv
			DROP SCHEMA IF EXISTS `mysqlslap`;
	DROP SCHEMA IF EXISTS `mysqlslap`;		CREATE SCHEMA `mysqlslap`;
			CREATE SCHEMA `mysqlslap`;
	CREATE SCHEMA `mysqlslap`;		CREATE TABLE `t1` (intcol1 INT(32),charcol1 VARCHAR(128));
			INSERT INTO t1 VALUES (1804289383,'mxvtvmC9127qJNm06sGB8R92q2j7vTiiITRDGXM9ZLzkdekbWtmXKwZ2qG1llkRw5m9DHOFilEREk3q7oce8O3BEJC0woJsm6uzFAEynLH2xCsw1KQ1lT4zg9rdxBL');
	CREATE SCHEMA `mysqlslap`;		SELECT intcol1,charcol1 FROM t1;

	CREATE TABLE `t1` (intcol1 INT(32),charcol1 VARCHAR(128));

	INSERT INTO t1 VALUES (1804289383,'~~mxvtvmC9127qJNm06sGB8R92q2j7vTiiITRDGXM9ZLzkd~~

	~~ekbWtmXKwZ2qG1llkRw5m9DHOFilEREk3q7oce8O3BEJC0woJsm6uzFAEynLH2xCsw1KQ1lT4zg9rdxB~~

	L');

	SELECT intcol1,charcol1 FROM t1;

	Benchmark		Benchmark
Line 62:		Line 52:
	Number of clients running queries: 1		Number of clients running queries: 1

	Average number of queries per client: 0 DROP SCHEMA IF EXISTS `mysqlslap`;		Average number of queries per client: 0

			DROP SCHEMA IF EXISTS `mysqlslap`;

	It's unlikely that you'll have only a single client connecting to the MySQL server at any given time, so you'll typically also need the --concurrency switch, which lets you simulate multiple simultaneous client connections, like this:		It's unlikely that you'll have only a single client connecting to the MySQL server at any given time, so you'll typically also need the --concurrency switch, which lets you simulate multiple simultaneous client connections, like this:

	shell> /usr/local/mysql/bin/mysqlslap --user=john --auto-generate-sql --concurrency=100		shell> /usr/local/mysql/bin/mysqlslap --user=john --auto-generate-sql --concurrency=100

	Benchmark		Benchmark
Line 84:		Line 76:
	See what happens if you increase the number of concurrent connections even more:		See what happens if you increase the number of concurrent connections even more:

	shell> /usr/local/mysql/bin/mysqlslap --user=john --auto-generate-sql --concurrency=300		shell> /usr/local/mysql/bin/mysqlslap --user=john --auto-generate-sql --concurrency=300

	Benchmark		Benchmark
Line 103:		Line 95:
	You can force mysqlslap to run a particular test more than once by adding the --iterations switch to the command line. This example runs the same test five times and prints a composite result:		You can force mysqlslap to run a particular test more than once by adding the --iterations switch to the command line. This example runs the same test five times and prints a composite result:

	shell> /usr/local/mysql/bin/mysqlslap --user=john --auto-generate-sql --concurrency=100 --iterations=5		shell> /usr/local/mysql/bin/mysqlslap --user=john --auto-generate-sql --concurrency=100 --iterations=5

	Benchmark		Benchmark
Line 123:		Line 115:
	For example, with settings of 500 total queries and five concurrent clients, mysqlslap will run 500/5 = 100 queries per client. Take a look at an example:		For example, with settings of 500 total queries and five concurrent clients, mysqlslap will run 500/5 = 100 queries per client. Take a look at an example:

	shell> /usr/local/mysql/bin/mysqlslap --user=john --auto-generate-sql --concurrency=100 --number-of-queries=10000		shell> /usr/local/mysql/bin/mysqlslap --user=john --auto-generate-sql --concurrency=100 --number-of-queries=10000

	Benchmark		Benchmark
Line 141:		Line 133:
	The default behavior of mysqlslap when using the --auto-generate-sql switch is to create a two-column table with one integer column and one character column. If this isn't representative of the kind of tables you typically use, you can adjust these settings to include more integer and/or character columns, with the --number-char-cols and --number-int-cols switches. Here are examples:		The default behavior of mysqlslap when using the --auto-generate-sql switch is to create a two-column table with one integer column and one character column. If this isn't representative of the kind of tables you typically use, you can adjust these settings to include more integer and/or character columns, with the --number-char-cols and --number-int-cols switches. Here are examples:

	shell> /usr/local/mysql/bin/mysqlslap --user=john --auto-generate-sql --concurrency=100 --number-of-queries=1000 --number-char-cols=4 --number-int-cols=7		shell> /usr/local/mysql/bin/mysqlslap --user=john --auto-generate-sql --concurrency=100 --number-of-queries=1000 --number-char-cols=4 --number-int-cols=7

	Benchmark		Benchmark
Line 153:		Line 145:
	Number of clients running queries: 100		Number of clients running queries: 100

	Average number of queries per client: 10 shell> /usr/local/mysql/bin/mysqlslap --user=john --auto-generate-sql --concurrency=100 --number-char-cols=4		Average number of queries per client: 10

			shell> /usr/local/mysql/bin/mysqlslap --user=john --auto-generate-sql --concurrency=100 --number-char-cols=4
	Benchmark		Benchmark

Line 166:		Line 160:
	Average number of queries per client: 0		Average number of queries per client: 0

	shell> /usr/local/mysql/bin/mysqlslap --user=john --auto-generate-sql --concurrency=100 --number-int-cols=5		shell> /usr/local/mysql/bin/mysqlslap --user=john --auto-generate-sql --concurrency=100 --number-int-cols=5
	Benchmark		Benchmark

Line 183:		Line 177:
	While the --auto-generate-sql option is fine for general load testing, you may want to test the performance of a specific query on a database that already exists. In these situations, you can bypass the --auto-generate-sql switch and instead tell mysqlslap to use your own custom query with the --query switch. Here's the next example:		While the --auto-generate-sql option is fine for general load testing, you may want to test the performance of a specific query on a database that already exists. In these situations, you can bypass the --auto-generate-sql switch and instead tell mysqlslap to use your own custom query with the --query switch. Here's the next example:

	shell> /usr/local/mysql/bin/mysqlslap --user=john --create-schema=world --query="SELECT City.Name, City.District FROM City, Country WHERE City.CountryCode = Country.Code AND Country.Code = 'IND';" --concurrency=100 --iterations=5		shell> /usr/local/mysql/bin/mysqlslap --user=john --create-schema=world --query="SELECT City.Name, City.District FROM City, Country WHERE City.CountryCode = Country.Code AND Country.Code = 'IND';" --concurrency=100 --iterations=5

	Benchmark		Benchmark

Onnowpurbo at 01:38, 4 June 2015

2015-06-04T01:38:14Z

← Older revision		Revision as of 01:38, 4 June 2015
Line 1:		Line 1:
	The mysqlslap utility makes it possible to benchmark and compare MySQL performance on different hardware, as well as accurately quantify the effect of a change in database design. This tutorial shows how you can use mysqlslap to run tests involving multiple clients, custom queries, different table engines, and much more.		The mysqlslap utility makes it possible to benchmark and compare MySQL performance on different hardware, as well as accurately quantify the effect of a change in database design. This tutorial shows how you can use mysqlslap to run tests involving multiple clients, custom queries, different table engines, and much more.

	One of the interesting new tools in MySQL 5.1.4 is mysqlslap, a load emulator that lets you see how well a particular query set or table engine performs under high-load conditions.		One of the interesting new tools in MySQL 5.1.4 is mysqlslap, a load emulator that lets you see how well a particular query set or table engine performs under high-load conditions.

Onnowpurbo: New page: The mysqlslap utility makes it possible to benchmark and compare MySQL performance on different hardware, as well as accurately quantify the effect of a change in database design. This tu...

2015-06-04T01:38:06Z

New page: The mysqlslap utility makes it possible to benchmark and compare MySQL performance on different hardware, as well as accurately quantify the effect of a change in database design. This tu...

New page

The mysqlslap utility makes it possible to benchmark and compare MySQL performance on different hardware, as well as accurately quantify the effect of a change in database design. This tutorial shows how you can use mysqlslap to run tests involving multiple clients, custom queries, different table engines, and much more.

One of the interesting new tools in MySQL 5.1.4 is mysqlslap, a load emulator that lets you see how well a particular query set or table engine performs under high-load conditions.

A query that consumes too many database resources may be the result of designing tables incorrectly, choosing the wrong table type, or creating an inefficient query. When a query eats up a lot of database resources, it can negatively affect other application components. By using mysqlslap to stress test a server in a non-public environment, you will discover these errors sooner, allowing you to you avoid a database meltdown once your application goes live.

This tutorial shows how you can use mysqlslap to run stress tests involving multiple clients, custom queries, different table engines, and much more.

Basic usage

This simple (and unrealistic) example uses mysqlslap to test server performance assuming only one client connection:

shell> /usr/local/mysql/bin/mysqlslap --user=john --auto-generate-sql

Benchmark

Average number of seconds to run all queries: 0.006 seconds

Minimum number of seconds to run all queries: 0.006 seconds

Maximum number of seconds to run all queries: 0.006 seconds

Number of clients running queries: 1

Average number of queries per client: 0

The --auto-generate-sql switch tells mysqlslap to automatically generate and execute SQL statements, monitor how fast MySQL performs this task, and display the result. The results indicate that MySQL took 0.006 seconds to execute the SQL statements.

The --auto-generate-sql switch creates a table, executes an INSERT query and saves dummy data to it, executes a SELECT query to retrieve the dummy data, and then drops the table. You can see behind-the-scenes action by adding the -v switch to the mysqlslap command line (adding extra 'v's increases the verbosity level):

shell> /usr/local/mysql/bin/mysqlslap --user=john --auto-generate-sql -vv

DROP SCHEMA IF EXISTS `mysqlslap`;

CREATE SCHEMA `mysqlslap`;

CREATE SCHEMA `mysqlslap`;

CREATE TABLE `t1` (intcol1 INT(32),charcol1 VARCHAR(128));

INSERT INTO t1 VALUES (1804289383,'mxvtvmC9127qJNm06sGB8R92q2j7vTiiITRDGXM9ZLzkd

ekbWtmXKwZ2qG1llkRw5m9DHOFilEREk3q7oce8O3BEJC0woJsm6uzFAEynLH2xCsw1KQ1lT4zg9rdxB

L');

SELECT intcol1,charcol1 FROM t1;

Benchmark

Average number of seconds to run all queries: 0.007 seconds

Minimum number of seconds to run all queries: 0.007 seconds

Maximum number of seconds to run all queries: 0.007 seconds

Number of clients running queries: 1

Average number of queries per client: 0 DROP SCHEMA IF EXISTS `mysqlslap`;

It's unlikely that you'll have only a single client connecting to the MySQL server at any given time, so you'll typically also need the --concurrency switch, which lets you simulate multiple simultaneous client connections, like this:

shell> /usr/local/mysql/bin/mysqlslap --user=john --auto-generate-sql --concurrency=100

Benchmark

Average number of seconds to run all queries: 0.698 seconds

Minimum number of seconds to run all queries: 0.698 seconds

Maximum number of seconds to run all queries: 0.698 seconds

Number of clients running queries: 100

Average number of queries per client: 0

MySQL performance drops pretty significantly (from 0.007 seconds to 0.698 seconds) when it has to deal with 100 clients instead of just one.

See what happens if you increase the number of concurrent connections even more:

shell> /usr/local/mysql/bin/mysqlslap --user=john --auto-generate-sql --concurrency=300

Benchmark

Average number of seconds to run all queries: 47.515 seconds

Minimum number of seconds to run all queries: 47.515 seconds

Maximum number of seconds to run all queries: 47.515 seconds

Number of clients running queries: 300

Average number of queries per client: 0

Note: As you increase the number of concurrent connections, you might encounter a "Too many connections" error. You need to adjust MySQL's 'max_connections' variable, which controls the maximum number of concurrent connections allowed by the server.
Running tests more than once

You can force mysqlslap to run a particular test more than once by adding the --iterations switch to the command line. This example runs the same test five times and prints a composite result:

shell> /usr/local/mysql/bin/mysqlslap --user=john --auto-generate-sql --concurrency=100 --iterations=5

Benchmark

Average number of seconds to run all queries: 0.714 seconds

Minimum number of seconds to run all queries: 0.682 seconds

Maximum number of seconds to run all queries: 0.753 seconds

Number of clients running queries: 100

Average number of queries per client: 0

Specifying the total number of queries

It's possible to force each "client" to run a specific number of queries by adding the --number-of-queries switch to the mysqlslap command line. When mysqlslap encounters this switch, it divides the corresponding value by the number of concurrent connections and uses the result to decide how many queries each client should run.

For example, with settings of 500 total queries and five concurrent clients, mysqlslap will run 500/5 = 100 queries per client. Take a look at an example:

shell> /usr/local/mysql/bin/mysqlslap --user=john --auto-generate-sql --concurrency=100 --number-of-queries=10000

Benchmark

Average number of seconds to run all queries: 0.694 seconds

Minimum number of seconds to run all queries: 0.694 seconds

Maximum number of seconds to run all queries: 0.694 seconds

Number of clients running queries: 100

Average number of queries per client: 100

Using larger tables

The default behavior of mysqlslap when using the --auto-generate-sql switch is to create a two-column table with one integer column and one character column. If this isn't representative of the kind of tables you typically use, you can adjust these settings to include more integer and/or character columns, with the --number-char-cols and --number-int-cols switches. Here are examples:

shell> /usr/local/mysql/bin/mysqlslap --user=john --auto-generate-sql --concurrency=100 --number-of-queries=1000 --number-char-cols=4 --number-int-cols=7

Benchmark

Average number of seconds to run all queries: 1.290 seconds

Minimum number of seconds to run all queries: 1.290 seconds

Maximum number of seconds to run all queries: 1.290 seconds

Number of clients running queries: 100

Average number of queries per client: 10 shell> /usr/local/mysql/bin/mysqlslap --user=john --auto-generate-sql --concurrency=100 --number-char-cols=4
Benchmark

Average number of seconds to run all queries: 0.968 seconds

Minimum number of seconds to run all queries: 0.968 seconds

Maximum number of seconds to run all queries: 0.968 seconds

Number of clients running queries: 100

Average number of queries per client: 0

shell> /usr/local/mysql/bin/mysqlslap --user=john --auto-generate-sql --concurrency=100 --number-int-cols=5
Benchmark

Average number of seconds to run all queries: 1.076 seconds

Minimum number of seconds to run all queries: 1.076 seconds

Maximum number of seconds to run all queries: 1.076 seconds

Number of clients running queries: 100

Average number of queries per client: 0

Using custom queries

While the --auto-generate-sql option is fine for general load testing, you may want to test the performance of a specific query on a database that already exists. In these situations, you can bypass the --auto-generate-sql switch and instead tell mysqlslap to use your own custom query with the --query switch. Here's the next example:

shell> /usr/local/mysql/bin/mysqlslap --user=john --create-schema=world --query="SELECT City.Name, City.District FROM City, Country WHERE City.CountryCode = Country.Code AND Country.Code = 'IND';" --concurrency=100 --iterations=5

Benchmark

Average number of seconds to run all queries: 2.886 seconds

Minimum number of seconds to run all queries: 2.137 seconds

Maximum number of seconds to run all queries: 4.125 seconds

Number of clients running queries: 100

Average number of queries per client: 1

It's helpful to use mysqlslap in this manner when you need to analyze the effect of a change in your database structure or indexing because it allows you to immediately grasp the impact of, say, an additional index on overall performance. To illustrate, look what happens to the time needed to run the previous query when an index is added to the City table:

mysql> CREATE INDEX idx_ccode ON City(CountryCode);

Query OK, 4079 rows affected (1.06 sec)

Records: 4079 Duplicates: 0 Warnings: 0 mysql> exit
Bye
shell> /usr/local/mysql/bin/mysqlslap --user=john --create-schema=world --query="SELECT City.Name, City.District FROM City, Country WHERE City.CountryCode = Country.Code AND Country.Code = 'IND';" --concurrency=100 --iterations=5
Benchmark

Average number of seconds to run all queries: 1.682 seconds

Minimum number of seconds to run all queries: 1.396 seconds

Maximum number of seconds to run all queries: 2.109 seconds

Number of clients running queries: 100

Average number of queries per client: 1

You can tell mysqlslap to create a custom table for your load testing by using the --create command-line switch with a CREATE TABLE command.
Comparing table engines

A cool feature of mysqlslap is the ability to specify the table engine used in the test. This provides database designers with an easy way to compare the performance of different table types under different load conditions. The --engine switch accepts any of MySQL's supported table types and creates test tables using the corresponding storage engine. Here's an example of how it could be used:

shell> /usr/local/mysql/bin/mysqlslap --user=john --auto-generate-sql --concurrency=100 --number-of-queries=700 --engine=innodb

Benchmark

Running for engine innodb

Average number of seconds to run all queries: 1.240 seconds

Minimum number of seconds to run all queries: 1.240 seconds

Maximum number of seconds to run all queries: 1.240 seconds

Number of clients running queries: 100

Average number of queries per client: 7 shell> /usr/local/mysql/bin/mysqlslap --user=john --auto-generate-sql --concurrency=100 --number-of-queries=700 --engine=myisam
Benchmark

Running for engine myisam

Average number of seconds to run all queries: 0.676 seconds

Minimum number of seconds to run all queries: 0.676 seconds

Maximum number of seconds to run all queries: 0.676 seconds

Number of clients running queries: 100

Average number of queries per client: 7

shell> /usr/local/mysql/bin/mysqlslap --user=john --auto-generate-sql --concurrency=100 --number-of-queries=700 --engine=memory
Benchmark

Running for engine memory

Average number of seconds to run all queries: 0.602 seconds

Minimum number of seconds to run all queries: 0.602 seconds

Maximum number of seconds to run all queries: 0.602 seconds

Number of clients running queries: 100

Average number of queries per client: 7

Saving reports

You might wish to save a mysqlslap report so you can compare it to a previous or future test run; you may also want to use the report as a reference when you're configuring new systems.

The easiest way to save a mysqlslap report is to pipe the output of a mysqlslap run to a file, as below:

shell> /usr/local/mysql/bin/mysqlslap --user=john --auto-generate-sql --concurrency=100 --number-of-queries=1000 --number-char-cols=4 --number-int-cols=7 >> /tmp/output.log

You can force mysqlslap to generate reports in CSV format; this is often useful if you need to import the data into a spreadsheet or database to build graphical reports from it. To do this, add the --csv switch to your mysqlslap command line and specify the output filename as an argument to this switch. Here's an example:

shell> /usr/local/mysql/bin/mysqlslap --csv=/tmp/output.csv --user=john --auto-generate-sql --concurrency=100 --number-of-queries=1000 --number-char-cols=4 --number-int-cols=7

Here's what the CSV file would contain if you peeked inside it:

shell> cat /tmp/output.csv
,query,1.070,1.070,1.070,100,10

Trying mysqlslap

The mysqlslap utility makes it possible to benchmark and compare MySQL performance on different hardware, as well as accurately quantify the effect of a change in database design. Try mysqlslap out for yourself and see how well your database server behaves under pressure from thousands of client connections.

==Referensi==

* http://www.techrepublic.com/blog/how-do-i/how-do-i-stress-test-mysql-with-mysqlslap/