Open Source RDBMS - Seamless, Scalable, Stable and Free

English | Login |Register

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:

  1.  when the databases run on the HDD (Hard Disk Drive) equipped server
  2. 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.

HDD_SSD_environment_web.png

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.

CUBRID Configurations (cubrid.conf)
[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
MySQL Configurations (my.cnf)
[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.

io_bound_insert_workload_test_web.png

The following chart shows the change in TPS.

TPS_change_web.png

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.

cpu_bound_select_workload_test_web.png

The following chart shows the change in TPS.

TPS_change_select_cpu_full_web.png

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.

io_bound_select_workload_test_web.png

The following chart shows the change in TPS.

TPS_change_select_io_full_web.png

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.

combined_select_test_results_web.png

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).

TPS_change_combined_results_web.png

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.

See also

CUBRID 8.4.0 Key Features

The new CUBRID 8.4.0 features many significant improvements which includes Performance Improvements, Developer Productivity Improvements and HA Reliab...

CUBRID vs. MySQL Benchmark Test Results for SNS Data and Workload

As we have recently rolled out the new 8.4.0 version of the CUBRID Database, one of our CUBRID users has approached us with a proposal t...

Increasing Database Performance by Query Tuning

It is common that applications use various kinds of SQL queries when communicating with the database server. If queries are not well structured, they wi...

NBD Benchmark Result

The NBD (NHN Internet Bulletin Board Application Database) Benchmark is a benchmark to measure the performance of a DBMS used for a Bullet...

CUBRID 8.4.0 vs. 8.3.1 Volume Space Reusability Comparison

CUBRID 2008 R4.0 has brought great achievements in overall engine performance and usability of tools. In the first of these blog posts we have a...




You are either using a very old browser or a browser that is not supported.
In order to browse cubrid.org you need to have one of the following browsers:



Internet Explorer: Mozilla Firefox: Google Chrome: