![\"\"](\"https:\/\/www.hammerdb.com\/blog\/wp-content\/uploads\/2021\/07\/mariadbgui-1.png\")
<\/a><\/p>\nAnd 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.<\/p>\n
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<\/a> 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.<\/p>\n Also install HammerDB v4.2 if you have not done so already and run the librarycheck command from the CLI.\u00a0 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.<\/p>\n 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.<\/p>\n If we look in the lib directory of the MariaDB installation, we can find this file<\/p>\n So lets add it to the LIBRARY_PATH and retrace our steps.<\/p>\n Now using ldd we can see that the library can be found right in the directory we added to the LIBRARY_PATH.<\/p>\n and now librarycheck works as it knows where this file is, meaning HammerDB is ready to start testing MariaDB.<\/p>\n As noted previously, we’ve added a my.cnf file to the MariaDB directory and tuned this for performance rather than resilience.\u00a0 (A future blog post on performance profiles will explain why this is a good first test on which to baseline other changes)<\/p>\n Next we will install the database and start MariaDB<\/p>\n Finally, we will set the root password<\/p>\n 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.<\/p>\n Save the script as mysqlbuild.tcl and run as follows:<\/p>\n Wait for the schema build to complete. In the example below it took just over 14 minutes to create 800 warehouses.<\/p>\n 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.<\/p>\n The script is run as follows:<\/p>\n 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.<\/p>\n Add these results to your favourite spreadsheet and you have your first performance profile of MariaDB on your system.<\/p>\n\/opt\/mariadb-10.2.34-linux-x86_64$ ls\r\nbin EXCEPTIONS-CLIENT man README-wsrep support-files\r\nCOPYING include my.cnf scripts THIRDPARTY\r\nCREDITS INSTALL-BINARY mysql-test share\r\ndata lib README.md sql-bench<\/pre>\n
hammerdb>librarycheck\r\n....\r\nChecking database library for MariaDB\r\nError: 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\r\nEnsure that MariaDB client libraries are installed and the location in the LD_LIBRARY_PATH environment variable<\/pre>\n
~\/HammerDB-4.2\/lib\/mariatcl0.1$ ldd libmariatcl0.1.so\r\nlinux-vdso.so.1 (0x00007ffd3edff000)\r\nlibmariadb.so.3 => not found\r\n...<\/pre>\n
\/opt\/mariadb-10.2.34-linux-x86_64\/lib$ ls\r\ngalera libmariadb.so.3 libmysqlclient.so libmysqlservices.a\r\nlibgalera_smm.so libmysqlclient.a libmysqld.a pkgconfig\r\nlibmariadbclient.a libmysqlclient_r.a libmysqld.so plugin\r\nlibmariadb.so libmysqlclient_r.so libmysqld.so.19<\/pre>\n
$ export LD_LIBRARY_PATH=\/opt\/mariadb-10.2.34-linux-x86_64\/lib:$LD_LIBRARY_PATH<\/pre>\n
~\/HammerDB-4.2\/lib\/mariatcl0.1$ ldd libmariatcl0.1.so\r\nlinux-vdso.so.1 (0x00007fff613f3000)\r\nlibmariadb.so.3 => \/opt\/mariadb-10.2.34-linux-x86_64\/lib\/libmariadb.so.3 (0x00007fa177941000)\r\n...<\/pre>\n
~\/HammerDB-4.2$ .\/hammerdbcli\r\nHammerDB CLI v4.2\r\nCopyright (C) 2003-2021 Steve Shaw\r\nType \"help\" for a list of commands\r\nThe xml is well-formed, applying configuration\r\nhammerdb>librarycheck\r\n...\r\nChecking database library for MariaDB\r\nSuccess ... loaded library mariatcl for MariaDB<\/pre>\n
$ more my.cnf\r\n[mysqld]\r\nskip-log-bin\r\nlc_messages_dir=\/opt\/mariadb-10.2.34-linux-x86_64\/share\/english\r\ndatadir=\/opt\/mariadb-10.2.34-linux-x86_64\/data\r\ndefault_authentication_plugin=mysql_native_password\r\nsocket=\/tmp\/mariadb10234.sock\r\nport=3306\r\nlog-error=\/tmp\/mariadb10234.log\r\nbind_address=localhost\r\n# general\r\nmax_connections=1000\r\ntable_open_cache=2000\r\ntable_open_cache_instances=16\r\nback_log=1500\r\n#default_password_lifetime=0\r\nssl=0\r\nperformance_schema=OFF\r\nmax_prepared_stmt_count=12800\r\nskip_log_bin=1\r\ncharacter_set_server=latin1\r\ncollation_server=latin1_swedish_ci\r\ntransaction_isolation=REPEATABLE-READ\r\n# files\r\n#innodb_file_per_table\r\ninnodb_log_file_size=16384M\r\ninnodb_open_files=1000\r\n# buffers\r\ninnodb_buffer_pool_size=64000M\r\ninnodb_buffer_pool_instances=16\r\ninnodb_log_buffer_size=64M\r\n# tune\r\ninnodb_doublewrite=0\r\ninnodb_thread_concurrency=0\r\ninnodb_flush_log_at_trx_commit=0\r\ninnodb_max_dirty_pages_pct=90\r\ninnodb_max_dirty_pages_pct_lwm=10\r\njoin_buffer_size=32K\r\nsort_buffer_size=32K\r\ninnodb_use_native_aio=1\r\ninnodb_stats_persistent=1\r\ninnodb_spin_wait_delay=6\r\ninnodb_max_purge_lag_delay=300000\r\ninnodb_max_purge_lag=0\r\ninnodb_flush_method=O_DIRECT_NO_FSYNC\r\n#innodb_checksum_algorithm=none\r\ninnodb_io_capacity=4000\r\ninnodb_io_capacity_max=20000\r\ninnodb_lru_scan_depth=9000\r\ninnodb_change_buffering=none\r\ninnodb_read_only=0\r\n#innodb_page_cleaners=2\r\n#innodb_undo_log_truncate=off\r\n# perf special\r\ninnodb_adaptive_flushing=1\r\ninnodb_flush_neighbors=0\r\ninnodb_read_io_threads=16\r\ninnodb_write_io_threads=16\r\ninnodb_purge_threads=4\r\ninnodb_adaptive_hash_index=0\r\n# monitoring\r\ninnodb_monitor_enable='%'<\/pre>\n
.\/scripts\/mysql_install_db --defaults-file=\/opt\/mariadb-10.6.3-linux-x86_64\/my.cnf --user=mysql\r\n.\/bin\/mysqld --defaults-file=.\/my.cnf<\/pre>\n
.\/bin\/mysql -uroot -S\/tmp\/mariadb10234.sock\r\n...\r\nMySQL [(none)]> SET PASSWORD FOR 'root'@localhost = PASSWORD(\"mysql\");<\/pre>\n
dbset db maria\r\ndbset bm TPC-C\r\ndiset connection maria_socket \/tmp\/mariadb10234.sock\r\ndiset tpcc maria_count_ware 800\r\ndiset tpcc maria_num_vu 64\r\ndiset tpcc maria_partition true\r\nbuildschema\r\nwaittocomplete\r\nquit<\/pre>\n
.\/hammerdbcli auto mysqlbuild.tcl<\/pre>\n
Hammerdb Log @ Wed Jul 07 07:33:23 PDT 2021\r\n+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-\r\nVuser 1:Monitor Thread\r\nVuser 1:CREATING TPCC SCHEMA\r\nVuser 1:CREATING DATABASE tpcc\r\nVuser 1:CREATING TPCC TABLES\r\nVuser 1:Loading Item\r\nVuser 2:Worker Thread\r\nVuser 2:Waiting for Monitor Thread...\r\nVuser 2:Loading 13 Warehouses start:1 end:13\r\nVuser 2:Start:Wed Jul 07 07:33:24 PDT 2021\r\nVuser 2:Loading Warehouse\r\nVuser 2:Loading Stock Wid=1\r\n...\r\nVuser 30:Orders Done\r\nVuser 30:Loading Orders for D=10 W=377\r\nVuser 30:...1000\r\nVuser 30:...2000\r\nVuser 30:...3000\r\nVuser 30:Orders Done\r\nVuser 30:End:Wed Jul 14 07:17:36 PDT 2021\r\nVuser 1:Workers: 0 Active 64 Done\r\nVuser 1:CREATING TPCC STORED PROCEDURES\r\nVuser 1:GATHERING SCHEMA STATISTICS\r\nVuser 1:TPCC SCHEMA COMPLETE\r\nVuser 1:Workers: 0 Active 64 Done\r\nVuser 1:CREATING TPCC STORED PROCEDURES\r\nVuser 1:GATHERING SCHEMA STATISTICS<\/pre>\n
puts \"MariaDB 10.2.34 Test Started\"\r\ndbset db maria\r\ndbset bm TPC-C\r\ndiset connection maria_socket \/tmp\/mariadb10234.sock\r\ndiset tpcc maria_driver timed\r\ndiset tpcc maria_rampup 2\r\ndiset tpcc maria_duration 5\r\nvuset logtotemp 1\r\nvuset unique 1\r\nloadscript\r\nforeach 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} {\r\nputs \"$z VU test\"\r\nvuset vu $z\r\nvucreate\r\nvurun\r\nruntimer 480\r\nvudestroy\r\n}\r\nputs \"MariaDB 10.2.34 Test Complete\"<\/pre>\n.\/hammerdbcli auto mysqlrun.tcl<\/pre>\n
Hammerdb Log @ Mon Jul 05 04:36:59 PDT 2021\r\n+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-\r\nVuser 1:Beginning rampup time of 2 minutes\r\nVuser 2:Processing 10000000 transactions with output suppressed...\r\nVuser 3:Processing 10000000 transactions with output suppressed...\r\nVuser 4:Processing 10000000 transactions with output suppressed...\r\nVuser 65:Processing 10000000 transactions with output suppressed...\r\nVuser 1:Rampup 1 minutes complete ...\r\nVuser 1:Rampup 2 minutes complete ...\r\nVuser 1:Rampup complete, Taking start Transaction Count.\r\nVuser 1:Timing test period of 5 in minutes\r\nVuser 1:1 ...,\r\nVuser 1:2 ...,\r\nVuser 1:3 ...,\r\nVuser 1:4 ...,\r\nVuser 1:5 ...,\r\nVuser 1:Test complete, Taking end Transaction Count.\r\nVuser 1:64 Active Virtual Users configured\r\nVuser 1:TEST RESULT : System achieved 669086 NOPM from 2026420 MariaDB TPM<\/pre>\n