oltp.lua

本文转载自：http://www.percona.com/docs/wiki/benchmark:sysbench:olpt.lua

 

History:  »  oltp.lua
Path:  Percona Software Documentation »  Benchmark Pages »  sysbench »  oltp.lua

oltp.lua

This test mode was written to improve performance's benchmarking of database servers by providing a more 'realistic' scenario than 'oltp'. This mode, implemented in a set of LUA scripts, provides workloads against multiple tables (in contrast to the single table scenario in 'oltp') and parallel initialization of the tests.

oltp.lua should understand most of the options that are available for regular 'oltp' mode.

oltp.lua is part of sysbench-db-scripts. You can download it or contribute at Launchpad.

PREREQUISITE: Accessing to the server

Every time you execute the script, whether for preparation or running the benchmarking tests, you should specify how to access the server if your setup differs from the defaults values.

You must specify the option –mysql-host if it differs from localhost, –mysql-port if it differs from 3306 or –mysql-socket if you are not using TCP/IP.

Your shell user will be assumed to be your database user. If it is not the case, specify it with the option –mysql-user and provide the password with –mysql-password if applicable.

The database used by default will be sbtest. It will not be created by Sysbench. You should create it and your user must have the appropriate privileges on it. You choose the database with the option –mysql-db.

Option	Description	Default value
–mysql-host	MySQL server host. Starting from version 0.4.5 you may specify a list of hosts separated by commas. In this case SysBench will distribute connections between specified MySQL hosts on a round-robin basis. Note that all connection ports and passwords must be the same on all hosts. Also, databases and tables must be prepared explicitely on each host before executing the benchmark.	localhost
–mysql-port	MySQL server port (in case TCP/IP connection should be used)	3306
–mysql-socket	Unix socket file to communicate with the MySQL server
–mysql-user	MySQL user	user
–mysql-password	MySQL password
–mysql-db	MySQL database name.	sbtest
–mysql-ssl	Use SSL connections.	no

FIRST STAGE: ''prepare''

The first stage, known as prepare, is creating the tables with its records where the queries will be performed. You can perform this stage in parallel (parallel_prepare.lua) instead of sequential (oltp.lua) to speed up the process.

Assuming that you have access to the server, you have to provide the driver, the engine and its relevant parameters, the number of tables, –oltp-tables-count, the indexes on them and the amount of records that each table will store.

Script	Description	Command	Extra Options
oltp.lua	Prepares the database with multiple tables	prepare
parallel_prepare	Prepares the database with multiple tables in parallel. Note that the number of threads should be a multiplier of the number of tables	run	–num-threads=8

Database driver

The type of the server is specified by the –db-driver option, mysql is its default value but drizzle is supported also. If you choose mysql, you can set the engine with the variable –mysql-table-engine. If you choose myisam, you can set the MAX ROWS with–myisam-max-rows.

Option	Description	Default value
–db-driver	Type of the server. Possible values: mysql	mysql
–mysql-table-engine	Type of the test table. Possible values: myisam, innodb, heap, ndbcluster, bdb, maria, falcon, pbxt	innodb
–myisam-max-rows	MAX_ROWS option for MyISAM tables (required for big tables)	1000000

Table creation

The number of tables, –oltp-tables-count, is a feature of oltp.lua.

The default table name is sbtest (STATIC), a suffix with the table's number will be added to its name (eg. sbtest1, sbtest2, ..sbtest[oltp-tables-count]).

A secondary index on each table instead of the primary is supported with the option –oltp-secondary (tables will be created withKEY xid (ID) , instead of PRIMARY KEY (ID) - taken from Mark Callaghan’s sysbench tree). This option will make, for example, theinnodb engine to create an internal unique 6-byte primary index for each tables.

You can also set 'id' as an auto-incremental field with the opton –oltp-auto-inc.

Option	Description	Default value
–oltp-table-count	Number of tables in the test database	1
–oltp-table-size	Number of rows in the test table	10000
–oltp-secondary	Creates a secondary index on 'id' instead of a primary
–oltp-auto-inc	Sets the id field as auto-incremental. Possible values: on, off	on

Content of tables

A variant of this query - with the appropriate indexes - will be executed for creating, for example the 101st table:

	  CREATE TABLE `sbtest101` (
	  `id` int(10) unsigned NOT NULL auto_increment,
	  `k` int(10) unsigned NOT NULL default '0',
	  `c` char(120) NOT NULL default '',
	  `pad` char(60) NOT NULL default '',
	  PRIMARY KEY  (`id`),
	  KEY `k` (`k`));

Once the tables are created, the tables will be 'populated' with the amount of records defined with the option –oltp-table-size(10000 by default). Each record will contain random strings in the fields c and pad and random integers between 1 and oltp-table-size in the field k.

Examples

An example statement of the prepare stage done in parallel is:

./sysbench --test=tests/db/parallel_prepare.lua --mysql-user=USER --mysql-password=SECRET --oltp-tables-count=64 --num-threads=8 run

Note that the number of tables should be multiplier of num-threads.

An example statement of the prepare stage done in a sequential way is:

./sysbench --test=tests/db/oltp.lua --mysql-user=USER --mysql-password=SECRET --mysql-table-engine=myisam --oltp-table-size=1000000 --oltp-tables-count=64 --mysql-socket=/tmp/mysql.sock prepare

''cleanup''

You can revert the preparation stage by executing a cleanup. You must provide the options to access the servers and the quantity of tables created.

An example statement of a cleanup is:

./sysbench --test=tests/db/oltp.lua --mysql-user=USER --mysql-password=SECRET --oltp-tables-count=64 cleanup

SECOND STAGE: ''run''

Once the environment has been prepared, oltp.lua will run the specified tests in each thread. The number of concurrent threads is set with the –num-threads option.

Each thread will choose at random a table by sampling an integer lesser or equal than the quantity of tables (oltp-tables-count).

The random sampling distribution can be set with the option –oltp-dist-type, which is defaulted to special. The specialdistribution needs two more options: –oltp-dist-pct, the percentage of the records to be treated as 'special', and –oltp-dist-res, the probability assigned to them. For example, given 1000 rows, 1000 queries, and values –oltp-dist-pct=1 and –oltp-dist-res=50. This would select, on average, the first 10 records (1% of 1000) on 500 of the queries, 50 times each. The other half of the queries will sample uniformly from the other 990 records.

Once the table is chosen, the appropriate tests will be run according to the options passed by the user. All of them will be wrapped in a transaction to the server (unless myisam engine was specified, the tables will be locked instead). If not options are passed, the default values will be assumed.

You can also run only a set of tests using the appropriate script (oltp_simple.lua, select.lua, insert.lua, delete.lua,update_index.lua, update_non_index.lua) in a threaded way.

Option	Description	Default value
–num-threads	Number of threads to run in parallel	1
–oltp-tables-count	Number of tables in the test database	1
–oltp-read-only	Performs only SELECT tests. Possible Values: on, off	off
–oltp-dist-type	Distribution of random numbers. Possible values: uniform (uniform distribution), gauss (gaussian distribution) and special. With special distribution a specified percent of numbers is generated in a specified percent of cases (see options below).	special
–oltp-dist-pct	Percentage of values to be treated as 'special' (for special distribution)	1
–oltp-dist-res	Percentage of cases when 'special' values are generated (for special distribution)	75

SELECT tests

This set of tests are divided in Point select queries and Range queries.

Option	Description	Default value
–oltp-point-selects	Number of point select queries in a single transaction	10
–oltp-range-size	Range size for range queries	100
–oltp-simple-ranges	Number of simple range queries in a single transaction	1
–oltp-sum-ranges	Number of SUM range queries in a single transaction	1
–oltp-order-ranges	Number of ORDER range queries in a single transaction	1
–oltp-distinct-ranges	Number of DISTINCT range queries in a single transaction	1

Point Select tests

The amount of queries ran by this test is set by the option oltp-point-selects (10 by default).

Each time an integer N less or equal than the number of rows (oltp-table-size) is sampled with the specified distribution, then it executes the following query

SELECT c FROM sbtestXXX WHERE id=N

Ranges tests

All of the tests in this section need the size of the range to execute. It can be set by the option oltp-range-size (100 by default). Note that the range size should be smaller than oltp-table-size.

Simple ranges

The amount of queries ran by this test is set by the option oltp-simple-ranges (1 by default).

Each time an integer N less or equal than the number of rows (oltp-table-size) is sampled with the specified distribution, then sets M accordingly to the oltp-range-size and executes the following query

SELECT c FROM sbtest WHERE id BETWEEN N AND M 

Sum in ranges

The amount of queries ran by this test is set by the option oltp_sum_ranges (1 by default).

Each time an integer N less or equal than the number of rows (oltp-table-size) is sampled with the specified distribution, then sets M accordingly to the oltp-range-size and executes the following query

SELECT SUM(K) FROM sbtest WHERE id BETWEEN N and M

Order in ranges

The amount of queries ran by this test is set by the option oltp_order_ranges (1 by default).

Each time an integer N less or equal than the number of rows (oltp-table-size) is sampled with the specified distribution, then sets M accordingly to the oltp-range-size and executes the following query

SELECT c FROM sbtest WHERE id between N and M ORDER BY c

Distincts in ranges

The amount of queries ran by this test is set by the option oltp-distinct-ranges (1 by default).

Each time an integer N less or equal than the number of rows (oltp-table-size) is sampled with the specified distribution, then sets M accordingly to the oltp-range-size and executes the following query

SELECT DISTINCT c FROM sbtest WHERE id BETWEEN N and M ORDER BY c

UPDATE tests

This set of tests will be executed unless the option oltp-read-only=on is passed.

Option	Description	Default value
–oltp-index-updates	Number of index UPDATE queries in a single transaction	1
–oltp-non-index-updates	Number of non-index UPDATE queries in a single transaction	1

Index updates

The amount of queries ran by this test is set by the option oltp_index_updates (1 by default).

Each time an integer N less or equal than the number of rows (oltp-table-size) is sampled with the specified distribution and executes the following query

UPDATE sbtest SET k=k+1 WHERE id=N 

Non-Index updates

The amount of queries ran by this test is set by the option oltp-non-index-updates (1 by default).

Each time an integer N less or equal than the number of rows (oltp-table-size) is sampled with the specified distribution, then a random string is generated, C, and executes the query

UPDATE sbtest SET c=C WHERE id=N 

DELETE test

This test will be executed once unless the option oltp-read-only=on is passed.

An integer N less or equal than the number of rows (oltp-table-size) is sampled with the specified distribution and executes the query

DELETE FROM sbtest WHERE id=N 

INSERT test

This test will be executed once unless the option oltp-read-only=on is passed.

Two integers, N and K, lesser or equal than the number of rows (oltp-table-size) are sampled with the specified distribution, two random strings, C and P, are generated and executes the query

INSERT INTO sbtest (id, k, c, pad) VALUES N, K, C, PAD 

Examples

A simple example, with 5 threads performing the default tests on 25 tables is:

./sysbench --mysql-user=USER --mysql-password=SECRET --test=tests/db/oltp.lua --oltp-tables-count=25 --num-threads=5 run

Another simple example, with 10 threads performing only the select tests on 100 tables, with 100 point selects and a range of 1000 in the range tests is:

./sysbench --mysql-user=USER --mysql-password=SECRET --test=tests/db/select.lua --oltp-tables-count=100 --num-threads=10 --oltp-point-selects=100 --oltp-range-size=1000 run

List of all available options

Option	Description	Default value
–mysql-host	MySQL server host. Starting from version 0.4.5 you may specify a list of hosts separated by commas. In this case SysBench will distribute connections between specified MySQL hosts on a round-robin basis. Note that all connection ports and passwords must be the same on all hosts. Also, databases and tables must be prepared explicitely on each host before executing the benchmark.	localhost
–mysql-port	MySQL server port (in case TCP/IP connection should be used)	3306
–mysql-socket	Unix socket file to communicate with the MySQL server
–mysql-user	MySQL user	user
–mysql-password	MySQL password
–mysql-db	MySQL database name.	sbtest
–mysql-ssl	Use SSL connections.	no
–db-driver	Type of the server. Possible values: mysql	mysql
–mysql-table-engine	Type of the test table. Possible values: myisam, innodb, heap, ndbcluster, bdb, maria, falcon, pbxt	innodb
–myisam-max-rows	MAX_ROWS option for MyISAM tables (required for big tables)	1000000
–num-threads	Number of threads to run in parallel	1
–oltp-tables-count	Number of tables in the test database	sbtest
–oltp-read-only	Performs only SELECT tests. Possible Values: on, off	off
–oltp-dist-type	Distribution of random numbers. Possible values: uniform (uniform distribution), gauss (gaussian distribution) and special. With special distribution a specified percent of numbers is generated in a specified percent of cases (see options below).	special
–oltp-dist-pct	Percentage of values to be treated as 'special' (for special distribution)	1
–oltp-dist-res	Percentage of cases when 'special' values are generated (for special distribution)	75
–oltp-point-selects	Number of point select queries in a single transaction	10
–oltp-range-size	Range size for range queries	100
–oltp-simple-ranges	Number of simple range queries in a single transaction	1
–oltp-sum-ranges	Number of SUM range queries in a single transaction	1
–oltp-order-ranges	Number of ORDER range queries in a single transaction	1
–oltp-distinct-ranges	Number of DISTINCT range queries in a single transaction	1
–oltp-index-updates	Number of index UPDATE queries in a single transaction	1
–oltp-non-index-updates	Number of non-index UPDATE queries in a single transaction	1

benchmark/sysbench/olpt.lua.txt · Last modified: 2012/05/31 15:17 by hrvojem

注：近期参加MySQL运维学习，老师推荐该文章作为学习和技术提高的扩展阅读，先记录到自己的博客中，随后慢慢消化、学习、提高。本文与MySQL数据库 “压力测试”主题有关。