CUBRID vs. MySQL performance test results before and after the SSD usage
1. About the Test
This article is also available in Russian.
Situation
In the following performance analysis the CUBRID and MySQL database systems are tested to reveal their performance results in two different situations:
- when the databases run on the HDD (Hard Disk Drive) equipped server
- and, when the databases run on the SSD (Solid State Disk) equipped server
Outline
It is widely accepted that storing the data is the primary mission of any database system. The HDD (Hard Disk Drive) is a popular medium used by enterprises to store the large amount of data. However, the HDD (I/O) performance is known to decline during the I/O Bound workloads. Therefore, it is often vital to find the more efficient medium to use to store the data. This article introduces the usage and the test results of a new SSD (Solid State Disk) used as the primary data storage medium which illustrates the improved database performance.
Testing Methods
To run the test, each database system (CUBRID and MySQL) has been installed on two separate server machines: one equipped with the HDD, the other with the SSD. The performance improvements in TPS (Transactions Per Second) have been constantly recorded throughout the experiment.
Testing Machine Environment
The machines equipped with the HDD and SSD have the following characteristics. In order to accurately determine the difference between the database performances when the HDD is used vs. when the SSD is used, two machines should have been identical. However, for the internal purpose using the identical machines were not the top priority, though, very similar hardware characteristics were used for this test.
Each HDD and SSD equipped machine had CUBRID and MySQL database systems installed. The database versions used in the test are as following.
- CUBRID 2008 R3.0
- MySQL 5.1.47 (innoDB)
The followings are the default configurations used in CUBRID and MySQL database systems. Both database servers have been configured with 4G data buffer size. In this experiment none of the databases were tuned. The default settings were used for the test.
[service] service=server,broker,manager [common] data_buffer_pages=25000 sort_buffer_pages=16 log_buffer_pages=50 lock_escalation=100000 lock_timeout_in_secs=-1 deadlock_detection_interval_in_secs=1 checkpoint_interval_in_mins=720 isolation_level="TRAN_REP_CLASS_UNCOMMIT_INSTANCE" cubrid_port_id=15097 max_clients=50 auto_restart_server=yes replication=no java_stored_procedure=no checkpoint_every_npages=100000000 data_buffer_pages=262144 error_log_level=notification communication_histogram=yes num_LRU_chains=200 async_commit=yes group_commit_interval_in_msecs=1000
[client] socket = /home1/mysql/mysql/tmp/mysql.sock [mysqld] user = mysql port = 3306 basedir = /home1/mysql/mysql datadir = /home1/mysql/mysql/data tmpdir = /home1/mysql/mysql/tmp socket = /home1/mysql/mysql/tmp/mysql.sock default-character-set = utf8 default_table_type = InnoDB skip_name_resolve back_log = 100 max_connections = 500 max_connect_errors = 999999 max_allowed_packet = 16M max_heap_table_size = 64M tmp_table_size = 64M binlog_cache_size = 1M thread_cache_size = 128 table_cache = 1024 sort_buffer_size = 8M join_buffer_size = 8M read_buffer_size = 2M read_rnd_buffer_size = 16M query_cache_size = 64M query_cache_limit = 2M # MyISAM options key_buffer_size = 32M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G myisam_max_extra_sort_file_size = 10G myisam_repair_threads = 1 myisam_recover ft_min_word_len = 4 # INNODB options innodb_buffer_pool_size = 4G # 50 ~ 70% of main memory innodb_log_buffer_size = 8M innodb_additional_mem_pool_size = 16M innodb_data_file_path = ibdata1:100M:autoextend innodb_file_per_table innodb_log_file_size = 256M innodb_log_files_in_group = 3 innodb_support_xa=0 innodb_thread_concurrency = 16 innodb_lock_wait_timeout = 60 innodb_flush_log_at_trx_commit = 0 # 0 for slave, 1 for master # Loging Configuration log-bin=mysql-bin expire_logs_days=5 log_warnings log_slow_queries log_slow_admin_statements long_query_time = 2 log_long_format # Replication setting server-id = 1
Test Scenario
Table Schema used in the test
There were 40 tables tbl_200 ~ tbl_239 created with the following table schema in order to measure the performance results.
CREATE TABLE tbl_200; ALTER CLASS tbl_200 ADD ATTRIBUTE id character varying(20) NOT NULL, seq integer NOT NULL, col3 character varying(16) NOT NULL, col4 character varying(5) NOT NULL, col5 character varying(50) NOT NULL, col6 character varying(1000), col7 character varying(300) NOT NULL, col8 character varying(150), col9 timestamp NOT NULL, col10 smallint DEFAULT 0 NOT NULL, col11 timestamp NOT NULL, col12 character varying(15) NOT NULL, col13 character(1) NOT NULL, col14 character(1) NOT NULL, col15 timestamp DEFAULT timestamp '04:25:44 PM 07/30/2010' NOT NULL; ALTER CLASS tbl_200 ADD ATTRIBUTE CONSTRAINT "iuk_tbl" UNIQUE(id, col3, col4, col5), CONSTRAINT "ipk_tbl" PRIMARY KEY(id, seq); CREATE INDEX ink1_tbl ON tbl_200 (id, col9 DESC, col14);
Using the above table schema necessary to create the test tables, HDD/SSD equipped machines ran over the following three kinds of tests.
- After creating a database to insert 25 million records of data to 40 tables, measure the performance by exposing to the INSERT FULL load for 30 minutes.
- After creating a database to insert 64 million records of data to 40 tables, measure the performance by exposing to the CPU Bound SELECT load.
- After creating a database to insert 64 million records of data to 40 tables, measure the performance by exposing to the I/O Bound SELECT load.
All of the above loads have been generated in 40 threads. A single INSERT load is composed of one INSERT query, while a SELECT load is composed of three SELECT queries each with the primary key, unique index, and non-unique index.
2. Test Insight and Results
I/O Bound Insert workload test
After creating a database with 40 tables each of which would contain approximately 625,000 records of data (25 million in total), both HDD/SSD equipped machines were exposed to a performance test with INSERT FULL load for 30 minutes. The following table illustrates the performance results.
The following chart shows the change in TPS.
According to the results of the above INSERT FULL load test:
- The performance of CUBRID database system on the SSD equipped machine is approximately 5 times higher than on the HDD equipped machine.
- MySQL database system shows about 2.5 time higher performance on the SSD equipped machine than on the HDD equipped machine. (Note: on SSD equipped machine MySQL does not have 100% UTIL, therefore an additional room for higher performance can be expected.)
CPU Bound Select workload test
After creating a database with 40 tables each of which would contain approximately 1,600,000 records of data (64 million in total), both HDD/SSD equipped machines were exposed to a performance test with CPU Bound load for 10 minutes. In this workload when the SELECT query is executed, in order to allocate the necessary Page entirely in the Memory Buffer and maintain the desired 100% Buffer Hit Ratio, the search scope of the query request should be narrowed. In this workload since I/O does not occur, the performance difference between HDD and SSD equipped machines is measured for the parts except I/O. The following table illustrates the performance results.
The following chart shows the change in TPS.
In case when I/O does not occur, CUBRID's performance drops for approximately 17%, while MySQL's performance increases for about 6%. The performance difference of other parts except I/O on both machines are as stated above.
I/O Bound Select workload test
After creating a database with 40 tables each of which would contain approximately 1,600,000 records of data (64 million in total), both HDD/SSD equipped machines were exposed to a performance test with I/O Bound load for 10 minutes. In this workload when the SELECT query is executed, in order not to allocate the necessary Page entirely in the Memory Buffer and prevent frequent Page replacements, the search scope of the query request should be expanded. Therefore, the I/O operations increase as the workload is very intensive. The following table illustrates the performance results.
The following chart shows the change in TPS.
According to the results of the above I/O Bound SELECT load test:
- CUBRID's TPS increases approximately 4.2 times on the SSD equipped machine than on HDD equipped machine.
- MySQL's TPS increases about 2.8 times on the SSD equipped machine than on HDD equipped machine.
Thus, in case SSD is used, both database systems are known to have the increased performance.
Arranging the Select test results
The following table illustrates the combined results of the two tests discussed above.
In the following chart the CPU Bound test results are illustrated on the left column, while I/O Bound - on the right. In all cases TPS level at CPU Bound is higher than at I/O Bound. Thus, I/O operations are considered to be the major cause of the performance decline for the database systems. The most interesting point revealed after this experiment (as shown in the image below) is that CUBRID's performance does not change much between CPU Bound and I/O Bound operations on SSD equipped machine. In other words, CUBRID seems to use the full advantage from running on SSD equipped machine. (The random access speed of the SSD equipped machine used in this test is considered to be very fast).
3. Conclusion
The current experiment confirms that TPS levels of CUBRID and MySQL database systems increase on SSD equipped machines. During the I/O Bound workload CUBRID had 4.2 times increase in TPS, while MySQL had 2.8 fold improvement. CUBRID and MySQL database systems have not been tuned on SSD equipped machines for this experiment. Therefore, this setting does not discuss whether the SSD equipped machines is suitable for a particular database system. However, since both CUBRID and MySQL ran on the SSD equipped machines, it is possible to conclude that there is a potential for the increase of I/O Bound operations performance. In the future, it is possible to obtain more interesting results by conducting various tests with the same hardware specifications and the Operating System (installing other HDD and SSD storage mediums), but having both CUBRID and MySQL database systems more optimally configured.
4. Disclaimer
THIS TEST HAS BEEN CONDUCTED FOR THE INTERNAL USAGE ONLY TO REVEAL THE PERFORMANCE DIFFERENCE WHEN THE SSD IS USED AS THE PRIMARY STORAGE MEDIUM, THEREFORE, IN NO EVENT SHALL THE TEST CONDUCTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES, INCLUDING THE LOSS OF DATA, PROFIT, OR BUSINESS INTERRUPTION. THESE TEST RESULTS DO NOT INDICATE THAT ONE DATABASE IS BETTER THAN THE OTHER. IN ORDER TO ACCURATELY DETERMINE THE DIFFERENCE BETWEEN THE DATABASE PERFORMANCES WHEN THE HDD IS USED VS. WHEN THE SSD IS USED, TWO MACHINES SHOULD HAVE BEEN IDENTICAL. HOWEVER, FOR THE INTERNAL PURPOSE USING THE IDENTICAL MACHINES WERE NOT THE TOP PRIORITY, THOUGH, VERY SIMILAR HARDWARE CHARACTERISTICS WERE USED FOR THIS TEST (SEE TESTING MACHINE ENVIRONMENT SECTION ABOVE). THEREFORE, THE RESULTS OF THIS TEST SHOULD BE USED FOR THE GENERAL EDUCATION PURPOSE ONLY.