Testing systems in TPC-C - fast and easy

The first step in working with new computing resources, no matter whether we build them ourselves or rent them in the cloud, is an assessment of performance. To this end, a series of tests is carried out that determine the performance of the new stand with respect to the existing one.

In an ideal world, such a test is carried out “live”: the system is copied to a new stand and the real load is emulated. But this way is too laborious, so synthetic tests are used in the real world.
')
It has always been interesting to me to measure the performance of our systems and look at their place in the world rankings. In addition, from time to time there are tasks to load the server with a real load and see how the hardware behaves.

And today I’ll tell you how to measure bench performance using the TPC-C test and get the result in standard transactions per second.

To begin, remember what synthetic tests are. For processors suitable 7-Zip Benchmark, for drives - CrystalDiskMark. With their help, we can very quickly see how fast our booth works on the algorithms embedded in these (!) Tests. The thing is that in our system for which the stand is intended, other algorithms will definitely be used. And with this we have to somehow live.

For more accurate disk test results, there is SQLIO or FIO. Be sure to read Amarao 's two historical articles about testing disks - one and two , and a recent article by a colleague . In these articles, you will learn how to properly apply this complex test.

So why did I choose TPC testing for performance evaluation? TPC has been developing data-processing tests since 1988. These tests have long become the industry standard, they are used by almost all hardware vendors and publish the results they achieve on various samples of hardware and software.

Since most business systems for which performance is critical are relational databases, the TPC-C test is of most interest to us. This is a comprehensive test that generates a multi-user OLTP load from various transactions. For its passage in the database, a set of data is generated, which is typical for business systems related to sales or production of goods and services. TPC-C can be supplemented with a TPC-H test to emulate the load on an OLAP system.

Some may say that the tests are over 10 years old. But the principle of data processing embodied in them is used in almost every business system. And that is why they are suitable for simulating a real load.

For the TPC-C test we will use the HammerDB open source project.

Virtual machine options for testing:

• 4 vgps 3GHz,
  
• 64 GB of RAM,
  
• Disk on shared storage with SSD disks.
  

Why such parameters? A smaller number of processors on servers with a DBMS is rare, memory for a DBMS does not happen much, well, putting it on hard disks is more expensive.

Installed on the machine software:

• Microsoft Windows 2016 x64,
  
• Microsoft SQL Server 2017 (not Express edition; or follow the maximum database size),
  
• SQL Server Management Studio,
  
• and actually HammerDB.
  

Of course, HammerDB and SQL server can be distributed to different VMs / servers to isolate user load from server load, which is quite correct. But another time.

And now we start the test.

1. The first step with the help of SQL Server Management Studio is to create a database for tests and place it on a separate disk - this is how you will have higher karma.
2. Next, run HammerDB.
   
   1. Choose MS SQL, TPC-C.
   2. Fill in the parameters for connecting to MS SQL:
      
      • number of warehouses: you can start with ten, but for real tests you need, for example, 2000,
      • number of virtual users: double the number of our processors.
   3. Click OK and wait.
   
   
   
   
3. Our database is filled with data, growing. For 2000 warehouses the size of the database on disk is about 250 GB.
   
   
   
   
4. After the database is full (this may take several hours), we need to generate a driver script - a test script.
   
   1. Set the conditions:
      
      • test time limit is 20 minutes
      • time of "acceleration" of the test - 5 minutes. This restriction is necessary not to "put" the system a sharp start.
      
      
      
      
   2. Click Ok and go to Load.
   
   
   Our test script is ready.
5. Go to the Virtual Users and set the number of users.
   
   
   In our case - 200 users, but in general it is recommended to set up 10 times less users than warehouses.
   
   Here we select Show Output so that the test results are visible at the time of its operation, and the Log Output to Temp to generate a text file with the test results.
   
   Click Create, Run!
   
   
   Our Virtual Users have changed status.
6. We turn to the Transaction Counter and see if our test is launched.
   Hooray, it's very simple - and the test works!
   
   This is how the test results are displayed after launch.
7. We are waiting for 5 minutes. The load comes to plan; performance, in my case, improved.
   
8. Now let's look at the results from virtualization. And that's what we see.
   
   • VM processors during the test were loaded almost 100%:
   • The memory was used quite actively:
   • In disk operations prevailed reading, delays at the expected level:
     

So, everything is quite simple and now we have a VM with a reference test. By moving it around the various stands, we can evaluate their relative performance.

And finally, useful advice. During the test, try to remove all possible performance graphs of components of your system. From them you will see what became the “bottleneck” when passing the test. Whether you loaded the system enough, or unnecessarily. It may be worth returning to the test setup step and changing the values.

Someone this text may seem very simple. But I have not yet met in Russian an answer to the question: “How can I measure the performance of our system in TPC-C?” Enjoy! :-)