Increasing Database Performance by Query Tuning

Today CUBRID shared its latest Query Tuning techniques and test results conducted using the latest CUBRID 3.0 and the latest MySQL 5.1 (innoDB) database servers. The summary of the results are provided below.

In this experiment a specific test scenario has been created to generate the overwhelming workload to the database servers with 30 threads. Below are the 3 different queries defined for this experiment:

• MessageList: Displays the 10 most recent messages with a particular service code set by a user.
• MessageTime: Displays the most recent message with a particular service code read by a user.
• NewCount: Displays the number of the most recent messages generated by a user within the last week.

This test scenario had two constant code tables (SN_SVC, SN_CONF) and one dynamic table (SN_MSG), which continuously accumulated the large amount of data. In this test database there are 10 service codes, and 4,000 registered users. In addition, it is fixed that every week each user generates 500 messages of one type (this suggests that per week one user generates 5,000 messages in total). Thus,

• # of SN_SVC = 10 records
• # of SN_CONF = 4,000 records
• # of SN_MSG = 4,000 x 5,000 = 20,000,000 records

As a result of this test, when the Query Tuning had been applied to CUBRID Database System, the tuned results showed 67 times TPS increase generating 1,721.97 Transactions Per Second. MySQL DMBS illustrated 107 times TPS increase generating 1,114.22 Transactions Per Second.