Search code examples
mysqldatabasehammerdb

How long it takes to build schema, or my hammerDB not working?

Accoring to this link https://blog.sqlbak.com/how-to-run-sql-server-load-tests-using-hammerdb

I run Schema build

but it got stuck at this step for around 30 minutes already, is everything ok ?

enter image description here

Solution

  • As a guide to expected completion times, below is an example of a MySQL build on a dual-core development laptop with an SSD disk that took 2 mins 30 seconds for 10 warehouses. The completion time is highly dependent on your database configuration (mysql.cnf) and hardware (CPU, memory, I/O). On a correctly configured server with adequate resources, expect 1000 warehouses to load in approximately 10-15 mins.

    Remember that HammerDB is a tool to test your database - if results are below expectations it is likely to be an issue with your database rather than the tool you are using to test it with.

    With regards to CPU HammerDB is multithreaded so the more cores you have the more virtual users you can create to load in parallel (The example below has 2 physical cores and 2 logical so we create the schema with 4 virtual users).

    By default, (there is also an alternative flat file bulk load option) HammerDB is creating the data and doing INSERT statements to load it. This means that you are generating a high volume of redo. If your redo log disk is slow or the redo logs are not correctly configured (innodb_log_file_size, innodb_log_files_in_group) your database will be slow in loading data while waiting for the redo log disk regardless of the CPU resource you are using. You should also correctly configure the buffer pool and data area disk, however the redo log disk and configuration is the best place to start.

    View your database statistics and OS statistics to diagnose where the bottleneck is that is slowing your database throughput.

    Vuser 1:Monitor Thread
Vuser 1:CREATING TPCC SCHEMA
Vuser 1:Ssl_cipher TLS_AES_256_GCM_SHA384
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:Ssl_cipher TLS_AES_256_GCM_SHA384
Vuser 2:Loading 2 Warehouses start:1 end:2
Vuser 2:Start:Thu Sep 08 10:21:38 BST 2022
.....
Vuser 5:Loading Orders for D=10 W=10
Vuser 5:...1000
Vuser 5:...2000
Vuser 5:...3000
Vuser 5:Orders Done
Vuser 5:End:Thu Sep 08 10:24:08 BST 2022
Vuser 1:Workers: 0 Active 4 Done
Vuser 1:CREATING TPCC STORED PROCEDURES
Vuser 1:GATHERING SCHEMA STATISTICS
Vuser 1:TPCC SCHEMA COMPLETE

    HammerDB MySQL build