HammerDB v4.2 New Features Pt1: MariaDB Build and Test Example with the CLI

Prior to HammerDB v4.2 support for MariaDB has been provided by using the MySQL interface. However, as the two databases diverged from a shared common codebase, this presented a challenge in that it was not possible to modify the workload for MySQL or MariaDB without also changing the other.  Also, the additional installation of the MySQL client libraries was needed when testing MariaDB.  HammerDB v4.2 adds full support for MariaDB in both the GUI as shown:

And in the CLI as a separate database from MySQL, this post describes a setup and test for MariaDB using the CLI on Linux as an example running the TPROC-C workload.

First, download your favourite release and format of MariaDB from https://downloads.mariadb.org/. We will use the Linux generic file mariadb-10.2.34-linux-x86_64.tar.gz of release 10.2.34 and extract it into a directory such as /opt. We have added a my.cnf file to the file listing.

/opt/mariadb-10.2.34-linux-x86_64$ ls
bin EXCEPTIONS-CLIENT man README-wsrep support-files
COPYING include my.cnf scripts THIRDPARTY
CREDITS INSTALL-BINARY mysql-test share
data lib README.md sql-bench

Also install HammerDB v4.2 if you have not done so already and run the librarycheck command from the CLI.  As we have not told HammerDB where to find the MariaDB libraries, it will generate an error telling you to add the location of these libraries to your LIBRARY_PATH.

hammerdb>librarycheck
....
Checking database library for MariaDB
Error: failed to load mariatcl - couldn't load file "/home/HammerDB-4.2/lib/mariatcl0.1/libmariatcl0.1.so": libmariadb.so.3: cannot open shared object file: No such file or directory
Ensure that MariaDB client libraries are installed and the location in the LD_LIBRARY_PATH environment variable

Note that the error tells us we are missing the file libmariadb.so.3. Advanced users can check this using the ldd command on the HammerDB library file itself.

~/HammerDB-4.2/lib/mariatcl0.1$ ldd libmariatcl0.1.so
linux-vdso.so.1 (0x00007ffd3edff000)
libmariadb.so.3 => not found
...

If we look in the lib directory of the MariaDB installation, we can find this file

/opt/mariadb-10.2.34-linux-x86_64/lib$ ls
galera libmariadb.so.3 libmysqlclient.so libmysqlservices.a
libgalera_smm.so libmysqlclient.a libmysqld.a pkgconfig
libmariadbclient.a libmysqlclient_r.a libmysqld.so plugin
libmariadb.so libmysqlclient_r.so libmysqld.so.19

So lets add it to the LIBRARY_PATH and retrace our steps.

$ export LD_LIBRARY_PATH=/opt/mariadb-10.2.34-linux-x86_64/lib:$LD_LIBRARY_PATH

Now using ldd we can see that the library can be found right in the directory we added to the LIBRARY_PATH.

~/HammerDB-4.2/lib/mariatcl0.1$ ldd libmariatcl0.1.so
linux-vdso.so.1 (0x00007fff613f3000)
libmariadb.so.3 => /opt/mariadb-10.2.34-linux-x86_64/lib/libmariadb.so.3 (0x00007fa177941000)
...

and now librarycheck works as it knows where this file is, meaning HammerDB is ready to start testing MariaDB.

~/HammerDB-4.2$ ./hammerdbcli
HammerDB CLI v4.2
Copyright (C) 2003-2021 Steve Shaw
Type "help" for a list of commands
The xml is well-formed, applying configuration
hammerdb>librarycheck
...
Checking database library for MariaDB
Success ... loaded library mariatcl for MariaDB

As noted previously, we’ve added a my.cnf file to the MariaDB directory and tuned this for performance rather than resilience.  (A future blog post on performance profiles will explain why this is a good first test on which to baseline other changes)

$ more my.cnf
[mysqld]
skip-log-bin
lc_messages_dir=/opt/mariadb-10.2.34-linux-x86_64/share/english
datadir=/opt/mariadb-10.2.34-linux-x86_64/data
default_authentication_plugin=mysql_native_password
socket=/tmp/mariadb10234.sock
port=3306
log-error=/tmp/mariadb10234.log
bind_address=localhost
# general
max_connections=1000
table_open_cache=2000
table_open_cache_instances=16
back_log=1500
#default_password_lifetime=0
ssl=0
performance_schema=OFF
max_prepared_stmt_count=12800
skip_log_bin=1
character_set_server=latin1
collation_server=latin1_swedish_ci
transaction_isolation=REPEATABLE-READ
# files
#innodb_file_per_table
innodb_log_file_size=16384M
innodb_open_files=1000
# buffers
innodb_buffer_pool_size=64000M
innodb_buffer_pool_instances=16
innodb_log_buffer_size=64M
# tune
innodb_doublewrite=0
innodb_thread_concurrency=0
innodb_flush_log_at_trx_commit=0
innodb_max_dirty_pages_pct=90
innodb_max_dirty_pages_pct_lwm=10
join_buffer_size=32K
sort_buffer_size=32K
innodb_use_native_aio=1
innodb_stats_persistent=1
innodb_spin_wait_delay=6
innodb_max_purge_lag_delay=300000
innodb_max_purge_lag=0
innodb_flush_method=O_DIRECT_NO_FSYNC
#innodb_checksum_algorithm=none
innodb_io_capacity=4000
innodb_io_capacity_max=20000
innodb_lru_scan_depth=9000
innodb_change_buffering=none
innodb_read_only=0
#innodb_page_cleaners=2
#innodb_undo_log_truncate=off
# perf special
innodb_adaptive_flushing=1
innodb_flush_neighbors=0
innodb_read_io_threads=16
innodb_write_io_threads=16
innodb_purge_threads=4
innodb_adaptive_hash_index=0
# monitoring
innodb_monitor_enable='%'

Next we will install the database and start MariaDB

./scripts/mysql_install_db --defaults-file=/opt/mariadb-10.6.3-linux-x86_64/my.cnf --user=mysql
./bin/mysqld --defaults-file=./my.cnf

Finally, we will set the root password

./bin/mysql -uroot -S/tmp/mariadb10234.sock
...
MySQL [(none)]> SET PASSWORD FOR 'root'@localhost = PASSWORD("mysql");

At this point, we are ready to start testing MariaDB with HammerDB so lets create 2 scripts, a build script and a run script. For the build script the example sets the socket name we have used and opts to create 800 warehouses with 64 virtual users in a partitioned schema.

dbset db maria
dbset bm TPC-C
diset connection maria_socket /tmp/mariadb10234.sock
diset tpcc maria_count_ware 800
diset tpcc maria_num_vu 64
diset tpcc maria_partition true
buildschema
waittocomplete
quit

Save the script as mysqlbuild.tcl and run as follows:

./hammerdbcli auto mysqlbuild.tcl

Wait for the schema build to complete. In the example below it took just over 14 minutes to create 800 warehouses.

Hammerdb Log @ Wed Jul 07 07:33:23 PDT 2021
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
Vuser 1:Monitor Thread
Vuser 1:CREATING TPCC SCHEMA
Vuser 1:CREATING DATABASE tpcc
Vuser 1:CREATING TPCC TABLES
Vuser 1:Loading Item
Vuser 2:Worker Thread
Vuser 2:Waiting for Monitor Thread...
Vuser 2:Loading 13 Warehouses start:1 end:13
Vuser 2:Start:Wed Jul 07 07:33:24 PDT 2021
Vuser 2:Loading Warehouse
Vuser 2:Loading Stock Wid=1
...
Vuser 30:Orders Done
Vuser 30:Loading Orders for D=10 W=377
Vuser 30:...1000
Vuser 30:...2000
Vuser 30:...3000
Vuser 30:Orders Done
Vuser 30:End:Wed Jul 14 07:17:36 PDT 2021
Vuser 1:Workers: 0 Active 64 Done
Vuser 1:CREATING TPCC STORED PROCEDURES
Vuser 1:GATHERING SCHEMA STATISTICS
Vuser 1:TPCC SCHEMA COMPLETE
Vuser 1:Workers: 0 Active 64 Done
Vuser 1:CREATING TPCC STORED PROCEDURES
Vuser 1:GATHERING SCHEMA STATISTICS

Next we are ready to run the test. In the example below, we want to collect a performance profile so will run tests without interruption from 1 to 100 Virtual Users creating a unique log file for each test.

puts "MariaDB 10.2.34 Test Started"
dbset db maria
dbset bm TPC-C
diset connection maria_socket /tmp/mariadb10234.sock
diset tpcc maria_driver timed
diset tpcc maria_rampup 2
diset tpcc maria_duration 5
vuset logtotemp 1
vuset unique 1
loadscript
foreach z {1 2 4 8 12 16 20 24 28 32 36 40 44 48 52 56 60 64 68 72 76 80 84 88 92 96 100} {
puts "$z VU test"
vuset vu $z
vucreate
vurun
runtimer 480
vudestroy
}
puts "MariaDB 10.2.34 Test Complete"

The script is run as follows:

./hammerdbcli auto mysqlrun.tcl

and can then be left without intervention to complete the test. On completion in the /tmp directory are a number of output files with the NOPM and TPM results of the test.

Hammerdb Log @ Mon Jul 05 04:36:59 PDT 2021
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
Vuser 1:Beginning rampup time of 2 minutes
Vuser 2:Processing 10000000 transactions with output suppressed...
Vuser 3:Processing 10000000 transactions with output suppressed...
Vuser 4:Processing 10000000 transactions with output suppressed...
Vuser 65:Processing 10000000 transactions with output suppressed...
Vuser 1:Rampup 1 minutes complete ...
Vuser 1:Rampup 2 minutes complete ...
Vuser 1:Rampup complete, Taking start Transaction Count.
Vuser 1:Timing test period of 5 in minutes
Vuser 1:1 ...,
Vuser 1:2 ...,
Vuser 1:3 ...,
Vuser 1:4 ...,
Vuser 1:5 ...,
Vuser 1:Test complete, Taking end Transaction Count.
Vuser 1:64 Active Virtual Users configured
Vuser 1:TEST RESULT : System achieved 669086 NOPM from 2026420 MariaDB TPM

Add these results to your favourite spreadsheet and you have your first performance profile of MariaDB on your system.

Once you have explored the TPROC-C workload you can also use HammerDB to run TPROC-H for analytic workloads and test solutions such as the MariaDB ColumnStore https://mariadb.com/kb/en/mariadb-columnstore/.

Also don’t forget that HammerDB is fully open source, so if you wish to help make HammerDB even better for testing MariaDB you can contribute both ideas and code at https://github.com/TPC-Council/HammerDB.

 

Author