]]>
]]>

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 with KEY xid (ID) , instead of PRIMARY KEY (ID) - taken from Mark Callaghan’s sysbench tree). This option will make, for example, the innodb 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 special distribution 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, offoff
–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, offoff
–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
Except where otherwise noted, content on this wiki is licensed under the following license:CC Attribution-Noncommercial-Share Alike 3.0 Unported
Contact Us 24 Hours A Day
SupportContact us 24×7
Emergency? Contact us for help now!
Sales North America(888) 316-9775 or
(208) 473-2904
Sales
Europe
+44-208-133-0309 (UK)
0-800-051-8984 (UK)
0-800-181-0665 (GER)
Training(855) 55TRAIN or
(925) 271-5054

 

Share This
]]> ]]>