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.