MariaDB:Benchmarks and long running tests

From Askmonty.org

Jump to: navigation, search
MariaDB:Development >
Plans | Replication | Backup | Benchmarks | BuildBot:Todo | BuildBot:vm-setup

Contents

Benchmarks

Here you will find details about our automated benchmark runs and long running tests. Feel free to suggest other benchmarks and tests. You can also send us your findings about benchmarks and tests, which you have run.

Where to get the helper scripts

You can all helper and wrapper scripts, which we use in this section via lp:mariadb-tools (https://launchpad.net/mariadb-tools)

sql-bench

You can find sql-bench in the MariaDB sources.

How to run

We run the automated sql-bench currently on our host "work".

You can find the automation related scripts and results at /home/hakan, which I will reference as $BASE_DIR. The sql-bench automation is not finished yet and therefore we have no regular results. Once we finish the coding of the helper scripts, we will publish our findings on a regular basis.

The wrapper script for configuring, compiling, and running sql-bench is $BASE_DIR/bin/run-sql-bench.sh.

The configuration scripts used for the different runs you can find at $BASE_DIR/sql-bench-configurations.

  • Branch a MariaDB or MySQL tree
  • Optionally do some code changes in that tree and commit your changes
  • Optionally add a configuration to $BASE_DIR/sql-bench-configurations
  • Run $BASE_DIR/bin/run-sql-bench.sh 
 $BASE_DIR/bin/run-sql-bench.sh [/path/to/bzr/repository] [name_without_spaces]
 [name_without_spaces] is used as identifier in the result file (--suffix)

The results

The results you can find at $BASE_DIR/sql-bench-results. The results are organized in sub directories with following schema 

   $BASE_DIR/sql-bench-results/$HOSTNAME/YYYY-MM-DD

How to configure

The minimum configuration you can find in the file $BASE_DIR/sql-bench-configurations/sql-bench-default-config.inc 

 #
 # Configure line for compiling MariaDB.
 #
 export MARIADB_CONFIG="--enable-thread-safe-client \
   --enable-local-infile"
 
 #
 # Options for starting mysqld.
 #
 # Attention: Do not set --datadir, --tmpdir, and --socket.
 #
 export MARIADB_OPTIONS="--no-defaults \
   --skip-grant-tables \
   --language=./sql/share/english"
 
 #
 # Options for sql-bench.
 #
 # Attention: Do not set --socket.
 #
 export SQLBENCH_OPTIONS="--log \
   --fast \
   --server=mysql \
   --small-test \
   --user=root"

To add a new configuration you copy sql-bench-default-config.inc and add or change your configuration for compiling, starting, and running sql-bench. Please make sure that you have no newline as the last line for the variables.

Good: 

 export MARIADB_OPTIONS="--no-defaults \
   --skip-grant-tables \
   --language=./sql/share/english"

NOT Good: 

 export MARIADB_OPTIONS="--no-defaults \
   --skip-grant-tables \
   --language=./sql/share/english \
   --a_new_line
  "

Future considerations

  • Run mysql_install_db before starting sql-bench. Done
  • Add a pre- and post-test mysql client hook. Pre-test hook is added. Post-test hook is postponed.
  • Crash and error detection and reporting.
  • One should be able to specify a test name for each file (run-all-tests --suffix='_xxxx')

Benchmarks runs to do

Different compiler options

The default options we use are: (From ./BUILD/compile-pentium-max --print)

CC="gcc" CFLAGS="-Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings -Wunused-function -Wunused-label -Wunused-value -Wunused-variable -Wunused-parameter -O3 -fno-omit-frame-pointer " CXX="gcc" CXXFLAGS="-Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings -Wunused-function -Wunused-label -Wunused-value -Wunused-variable -Wreorder -Wctor-dtor-privacy -Wnon-virtual-dtor -felide-constructors -fno-exceptions -fno-rtti -O3 -fno-omit-frame-pointer " CXXLDFLAGS="" ./configure --prefix=/usr/local/mysql --enable-assembler --with-extra-charsets=complex --enable-thread-safe-client --with-big-tables --with-plugin-maria --with-maria-tmp-tables --without-plugin-innodb_plugin --with-mysqld-ldflags=-static --with-client-ldflags=-static --with-readline --with-ssl --with-plugins=max-no-ndb --with-embedded-server --with-libevent --enable-local-infile

The different builds we should run are:

  • As above
  • -O3 -> -O2
  • -fno-omit-frame-pointer -> -fomit-frame-pointer
  • --with-debug
  • --with-debug=full
  • --with-mysqld-ldflags=-dynamic

Different mysqld startup options

If nothing else is said, we are using the base build above.

Default run is done with 

 --skip-grant-tables
User status

Effect of user status:

mysqld --userstat=1

SHOW PROFILE

Start test with 'set profile=1'

Having safemalloc and safemutex compiled in but not enabled

Compile with --with-debug=full

mysqld --skip-safemalloc --skip-mutex-deadlock-detector

Different engines

This need to be extend to check how things works with different startup options.

  • --default-storage-engine=myisam --key-buffer-size=256M
  • --default-storage-engine=heap
  • --default-storage-engine=maria --maria-pagecache-buffer-size=256M 
This needs to be run with following --create-options
transactional=1 page_checksum=1
transactional=1 page_checksum=0
transactional=0
  • --default-storage-engine=innodb --innodb-buffer-pool-size=256M
  • --default-storage-engine=pbxt --pbxt-index-cache-size=64M --pbxt-record-cache-size=name=196M
Effect of GRANT

Note that default run is done with --skip-grant

Run with user test under the following setups:

  • GRANT ALL PRIVILEGES to *.* to 'test@localhost'
  • GRANT ALL PRIVILEGES on test.* to 'test@localhost'
  • GRANT ALL PRIVILEGES on test.* to 'test@localhost' ; CREATE table test.foo (a int); GRANT SELECT (a) on test.t1 to 'test@localhost';

This is to see if there is an effect of having column privleges active.

Different run-all-tests options

TCP connection

The following will force the test to use TCPIP. For this to work, the MYSQL_TCP_PORT needs to be set by the test system

  • --connect_options='host=LOCALHOST'

sysbench

We are using sysbench from lp:sysbench

Automated version

You can find the automation wrapper scripts for running sysbench at lp:mariadb-tools

Current general parameters

 table_open_cache = 512
 thread_cache = 512
 query_cache_size = 0
 query_cache_type = 0

Current InnoDB parameters

 innodb_data_home_dir = /data/mysql/
 innodb_data_file_path = ibdata1:128M:autoextend
 innodb_log_group_home_dir = /data/mysql/
 innodb_buffer_pool_size = 1024M
 innodb_additional_mem_pool_size = 32M
 innodb_log_file_size = 256M
 innodb_log_buffer_size = 16M
 innodb_flush_log_at_trx_commit = 1
 innodb_lock_wait_timeout = 50
 innodb_doublewrite = 0
 innodb_flush_method = O_DIRECT
 innodb_thread_concurrency = 0
 innodb_max_dirty_pages_pct = 80

Compile

Install MariaDB or MySQL to /usr/local and make a symlink to /usr/local/mysql. Please use non-debug version! On Mac OS X we currently need automake1.10. 

 ./autogen.sh
 ./configure \
   --without-drizzle \
   --with-mysql-includes=/usr/local/mysql/include/mysql \
   --with-mysql-libs=/usr/local/mysql/lib/mysql
 make
 optionally: make install

Start and prepare database to use

 mysqladmin -uroot drop sbtest
 mysqladmin -uroot create sbtest

Tests

We use the latest sysbench with LUA scripting support. Therefore the test names differ from sysbench <= 0.4. To get reasonable results we use a run time of 5 minutes.

We run the tests with 1, 4, 8, 16, 32, 64, and 128 threads. 

 NUM_THREADS="1 4 8 16 32 64 128"
 SYSBENCH_TESTS="delete.lua \
   insert.lua \
   oltp_complex_ro.lua \
   oltp_complex_rw.lua \
   oltp_simple.lua \
   select.lua \
   update_index.lua \
   update_non_index.lua"

 NUM_THREADS=1
 TEST_DIR=${HOME}/work/monty_program/sysbench/sysbench/tests/db

 ./sysbench \
   --test=${TEST_DIR}/oltp_simple.lua \
   --oltp-table-size=2000000 \
   --max-time=300 \
   --max-requests=0 \
   --mysql-table-engine=InnoDB \
   --mysql-user=root \
   --mysql-engine-trx=yes \
   --num-threads=$NUM_THREADS \
   prepare

 ./sysbench \
   --test=${TEST_DIR}/oltp_simple.lua \
   --oltp-table-size=2000000 \
   --max-time=300 \
   --max-requests=0 \
   --mysql-table-engine=InnoDB \
   --mysql-user=root \
   --mysql-engine-trx=yes \
   --num-threads=$NUM_THREADS \
   run

Custom added tests

select_random_ranges.lua

select_random_ranges.lua is in the latest SysBench v0.5 repository now.

select_random_points.lua

select_random_points.lua is in the latest SysBench v0.5 repository now.

DBT3

Get it and compile

  • Get DBT3 from http://osdldbt.sourceforge.net/
  • Make sure that you have pg_ctl and createdb of PostgreSQL installed. On Ubuntu look for postgresql and postgresql-client and make sure that pg_ctl is in your PATH, for instance: 
 PATH=/usr/lib/postgresql/8.4/bin:$PATH
  • Compile DBT3 
 gunzip -c dbt3-1.9.tar.gz | tar xvf -
 cd dbt3-1.9
 ./configure
 make
 cd src/dbgen
 make

Generate and load data

  • Generate data 
 ./dbgen -s30

DDL

Substitute $ENGINE by the storage engine you want to use 

 CREATE TABLE supplier (
 	s_suppkey  INTEGER PRIMARY KEY,
 	s_name CHAR(25),
 	s_address VARCHAR(40),
 	s_nationkey INTEGER,
 	s_phone CHAR(15),
 	s_acctbal REAL,
 	s_comment VARCHAR(101)) Engine $ENGINE;
 
 CREATE TABLE part (
 	p_partkey INTEGER PRIMARY KEY,
 	p_name VARCHAR(55),
 	p_mfgr CHAR(25),
 	p_brand CHAR(10),
 	p_type VARCHAR(25),
 	p_size INTEGER,
 	p_container CHAR(10),
 	p_retailprice REAL,
 	p_comment VARCHAR(23)) Engine $ENGINE;
 
 CREATE TABLE partsupp (
 	ps_partkey INTEGER,
 	ps_suppkey INTEGER,
 	ps_availqty INTEGER,
 	ps_supplycost REAL,
 	ps_comment VARCHAR(199),
 	PRIMARY KEY (ps_partkey, ps_suppkey)) Engine $ENGINE;
 
 CREATE TABLE customer (
 	c_custkey INTEGER primary key,
 	c_name VARCHAR(25),
 	c_address VARCHAR(40),
 	c_nationkey INTEGER,
 	c_phone CHAR(15),
 	c_acctbal REAL,
 	c_mktsegment CHAR(10),
 	c_comment VARCHAR(117))  Engine $ENGINE;
 
 CREATE TABLE orders (
 	o_orderkey INTEGER primary key,
 	o_custkey INTEGER,
 	o_orderstatus CHAR(1),
 	o_totalprice REAL,
 	o_orderDATE DATE,
 	o_orderpriority CHAR(15),
 	o_clerk CHAR(15),
 	o_shippriority INTEGER,
 	o_comment VARCHAR(79))  Engine $ENGINE;
 
 CREATE TABLE lineitem (
 	l_orderkey INTEGER,
 	l_partkey INTEGER,
 	l_suppkey INTEGER,
 	l_linenumber INTEGER,
 	l_quantity REAL,
 	l_extendedprice REAL,
 	l_discount REAL,
 	l_tax REAL,
 	l_returnflag CHAR(1),
 	l_linestatus CHAR(1),
 	l_shipDATE DATE,
 	l_commitDATE DATE,
 	l_receiptDATE DATE,
 	l_shipinstruct CHAR(25),
 	l_shipmode CHAR(10),
 	l_comment VARCHAR(44),
 	PRIMARY KEY (l_orderkey, l_linenumber)) Engine $ENGINE;
 
 CREATE TABLE nation (
 	n_nationkey INTEGER primary key,
 	n_name CHAR(25),
 	n_regionkey INTEGER,
 	n_comment VARCHAR(152)) Engine $ENGINE;
 
 CREATE TABLE region (
 	r_regionkey INTEGER primary key,
 	r_name CHAR(25),
 	r_comment VARCHAR(152)) Engine $ENGINE;
 
 CREATE TABLE time_statistics (
 	task_name VARCHAR(40),
 	s_time TIMESTAMP,
 	e_time TIMESTAMP,
 	int_time INTEGER)  Engine $ENGINE;

Load data

Substitute $DATA_DIR with the path to your generated data. 

 LOAD DATA LOCAL INFILE '$DATA_DIR/supplier.tbl' into table supplier fields terminated by '|';
 LOAD DATA LOCAL INFILE '$DATA_DIR/part.tbl' into table part fields terminated by '|';
 LOAD DATA LOCAL INFILE '$DATA_DIR/partsupp.tbl' into table partsupp fields terminated by '|';
 LOAD DATA LOCAL INFILE '$DATA_DIR/customer.tbl' into table customer fields terminated by '|';
 LOAD DATA LOCAL INFILE '$DATA_DIR/orders.tbl' into table orders fields terminated by '|';
 LOAD DATA LOCAL INFILE '$DATA_DIR/lineitem.tbl' into table lineitem fields terminated by '|';
 LOAD DATA LOCAL INFILE '$DATA_DIR/nation.tbl' into table nation fields terminated by '|';
 LOAD DATA LOCAL INFILE '$DATA_DIR/region.tbl' into table region fields terminated by '|';

Indexes we need

 ALTER TABLE lineitem 
   ADD INDEX i_l_shipdate(l_shipdate),
   ADD INDEX i_l_suppkey_partkey (l_partkey, l_suppkey),
   ADD INDEX i_l_partkey (l_partkey),
   ADD INDEX i_l_suppkey (l_suppkey),
   ADD INDEX i_l_receiptdate (l_receiptdate),
   ADD INDEX i_l_orderkey (l_orderkey),
   ADD INDEX i_l_orderkey_quantity (l_orderkey, l_quantity),
   ADD INDEX i_l_commitdate (l_commitdate);

 CREATE INDEX i_c_nationkey ON customer (c_nationkey);

 ALTER TABLE orders
   ADD INDEX i_o_orderdate (o_orderdate),
   ADD INDEX i_o_custkey (o_custkey);
 CREATE INDEX i_s_nationkey ON supplier (s_nationkey);

 ALTER TABLE partsupp 
   ADD INDEX i_ps_partkey (ps_partkey),
   ADD INDEX i_ps_suppkey (ps_suppkey);
 CREATE INDEX i_n_regionkey ON nation (n_regionkey);

Analyze tables

 ANALYZE TABLE supplier;
 ANALYZE TABLE part;
 ANALYZE TABLE partsupp;
 ANALYZE TABLE customer;
 ANALYZE TABLE orders;
 ANALYZE TABLE lineitem;
 ANALYZE TABLE nation;
 ANALYZE TABLE region;

Long running tests

Random Query Generator

The documentation for the Random Query Generator is available [1]. The list of automatic tests is available from the buildbot.conf file in the lp:maria-tools repository.

Benchmark results

sysbench results

perro: Linux openSUSE 11.1 (x86_64), single socket dual-core Intel 3.2GHz. with 1MB L2 cache, 2GB RAM, data_dir on 2 disk software RAID 0

work: Linux openSUSE 11.1 (x86_64), daul socket quad-core Intel 3.0GHz. with 6MB L2 cache, 8 GB RAM, data_dir on single disk.

sysbench v0.5 results

sysbench v0.4 results

Retrieved from "http://askmonty.org/wiki/MariaDB:Benchmarks_and_long_running_tests"
Personal tools