Toad World® Forums

Speeding up TPC-C Load Tables

Hi,

I have a setup with a “large” VM running as the console with 16gig RAM and 8 cores, and 10x smaller VMs running as agents. I’m trying to build very large DBs - 100GB, 500GB, 1TB, 5TB etc. The TPC-C load time is considerable - days. This is all to test DB performance with our self-designed storage system - however as I build the DB, the IOPS we’re getting on our system are very low - in the hundreds of IOPS. We can easily deliver more than that. I reduced the thinking time option, and turned on the distributed index option.

Am I missing anything? What is the recommended setup of console and agents? How many?

I’m seeing similar behavior with SQL Server, MySQL, Oracle…

Eyal.

There are several things that can be done to speed data loading up. Here is are some of the most common one.

  • Make sure that you allocate your database space to be able to hold the entire standard benchmark and DO NOT rely on the auto-extend feature of the database. Allowing the database to auto-extend increases the data load time tremendously.

  • If you have other machines that can participate in the data load, load an Agent on them and select the “Distribute load using Agents” option and set the number of virtual users to use for the data load to be 1.5 X the number of cores you have available. Data generation is a CPU intensive task in order the get the same data set regardless of the number of users generating the data, so the more user generating and inserting, the better. (NOTE: This may be a point where adding more users doesn’t help if your database becomes the bottleneck for the inserts, so make sure your database is setup for fast IO).

  • Network can also be a limitation so if possible speed up the network or enable jumbo frames.

  • To speed up index creation you can check the option “Distribute Index Creation”, but make sure you have enough temp space on your database in order to hold all the indexes since all the indexes will be being created at the same time.

There are a variety of other database configuration changes that can be made such as

  • SQL Server - have your database Recovery Model set the Simple.

  • Oracle - Make sure you have Redo logs setup optimally for the inserts.

I hope this helps.