Results of benchmarking performance of CUBRID and MySQL before and after using solid state drives (SSD)
- Transfer
Good day to all!
Finally Habr has earned, and now I can post a translation of an article published in English on the official website of the CUBRID project, which you requested in the comments to the previous habratopik .
In the following performance analysis of database systems, CUBRID and MySQL are tested to determine their performance in two different situations:
It is generally accepted that data storage is the main task of any database system. A hard drive is a popular medium used by enterprises to store large amounts of data. However, it is known that hard disk (I / O) performance decreases under workloads limited by I / O speed (I / O Bound). Therefore, it is often necessary to find a more efficient storage medium. In this article, we present the results of the application and testing of a new solid-state drive (SSD), used as the main storage medium for data, which demonstrates increased database performance.
To run the test, each database system (CUBRID and MySQL) was installed on two separate servers: one with a hard drive and the other with a solid state drive. The performance improvement in transactions per second was continuously recorded throughout the experiment.
The following are the specifications of computers equipped with a hard drive and a solid state drive. To accurately determine the difference in database performance when using a hard drive and an SSD, computers must be the same. Despite the fact that for internal purposes the use of identical computers was not a priority, equipment with very similar characteristics was still used for this test.
CUBRID and MySQL database systems were installed on computers with a hard drive and an SSD. During testing, the following database versions were used.
The following are the default configurations for the CUBRID and MySQL database systems. Both database servers were configured with a 4 GB data buffer. Other test setups were used by default.
CUBRID Configurations (cubrid.conf) MySQL Configurations (my.cnf)
To measure performance results, 40 tbl_200 ~ tbl_239 tables were created with the diagram below.
Using the above table diagram, necessary to create test tables, machines equipped with a hard drive and a solid state drive passed three types of tests:
All of the above loads were created in 40 threads. One INSERT load consists of one INSERT query, while the SELECT load consists of three SELECT queries with a primary key , a unique index and a non-unique index in each.
After creating a database with 40 tables, each of which should contain approximately 625,000 data records (a total of 25 million), both computers (with a hard disk and an SSD) were subjected to performance testing with a FULL INSERT load for 30 minutes. The following table shows the results of performance testing.
The following diagram shows the change in the number of transactions per second.
Based on the results of the test described above with the “FULL INSERT” load, the following conclusions can be drawn.
After creating a database with 40 tables, each of which should contain approximately 1,600,000 data records (a total of 64 million), both computers (with a hard disk and an SSD) were subjected to performance testing with a load limited by the CPU capacity for 10 minutes . In this load with SELECT queries, the query search area should be narrowed in order to completely place the necessary page in the memory buffer and maintain the desired 100% buffer performance value. Since I / O operations are not performed under this load, the performance difference between computers with a hard drive and an SSD is measured for all components except I / O. The following table shows the results of performance testing.
The following diagram shows the change in the number of transactions per second.
With no I / O operations, the performance of CUBRIDs using solid state media drops by about 17% compared to hard drives, and the performance of MySQL increases by about 6%. The difference in performance of all components except I / O on both computers is indicated above.
After creating a database with 40 tables, each of which should contain approximately 1,600,000 data records (a total of 64 million), both computers (with a hard disk and an SSD) were subjected to performance testing with a load limited by I / O speed during 10 minutes. In order not to place the necessary page in the memory buffer completely and to prevent frequent page replacement, the search area of the SELECT query in this load should be expanded. The number of I / O operations is increasing because the workload is very intense. The following table shows the performance test results for both systems.
The following diagram shows the change in the number of transactions per second.
According to the results of the above test with the load "SELECT", limited by the speed of input-output, we can draw the following conclusions.
Thus, under conditions of limited speed and I / O, the performance of both database systems increases with the use of solid state drives.
The following table summarizes the results of the two tests discussed above.
In the diagram below, the results of the test, limited by CPU capabilities , are shown in the left column, and the results of the test, limited by I / O speed- in the second. In all cases, the performance level (in transactions per second) during testing limited by CPU capabilities is higher than during testing limited by I / O speed. Thus, I / O operations can be considered to be the main reason for the decrease in the performance of database systems. The most interesting characteristic found during the experiment is a slight difference in CUBRID performance when performing operations limited by CPU capabilities and operations limited by I / O speed on a computer equipped with a solid state drive. In other words, CUBRID probably takes full advantage of working on a computer with an SSD. ( Random access speed the solid state drive used in this test is considered very high).
This experiment confirms that the performance levels of CUBRID and MySQL database systems increase on computers equipped with a solid state drive. Under load limited by I / O speed, CUBRID performance increases by 4.2 times, and MySQL performance by 2.8 times. CUBRID and MySQL database systems were not configured on SSD computers for this experiment. Therefore, in this experiment we do not discuss the suitability of computers with a solid state drive for a specific database system. Nevertheless, since both CUBRID and MySQL worked on computers equipped with a solid state drive, we can conclude that it is possible to further increase the performance of operations limited by I / O speed. In the future you can get more interesting results,
THIS TESTING WAS PERFORMED ONLY FOR INTERNAL USETO DETERMINE difference in performance when using solid-state drives as the main media, THEREFORE IN NO EVENT SHALL THE COMPANY, conduct research, BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, PUNITIVE OR CONSEQUENTIAL DAMAGES, INCLUDING LOSS OF DATA OR INCOME OR INTERRUPTION OF BUSINESS. THE RESULTS OF THIS TEST DO NOT MEAN THE EXCELLENCE OF ONE DATABASE OVER THE OTHER. TO EXACTLY DETERMINE THE DIFFERENCE IN THE DATABASE PRODUCTIVITY WHEN USING A HARD DISK AND A SOLID DRIVE, COMPUTERS SHOULD BE THE SAME. Despite the fact that for internal purposes the use of identical computers was not a priority, for this test the equipment was used with very similar characteristics (see.TEST COMPUTER ENVIRONMENT ). THEREFORE, THE RESULTS OF THIS TEST SHOULD BE USED ONLY FOR GENERAL EDUCATIONAL PURPOSES.
Finally Habr has earned, and now I can post a translation of an article published in English on the official website of the CUBRID project, which you requested in the comments to the previous habratopik .
1. About the test
In the following performance analysis of database systems, CUBRID and MySQL are tested to determine their performance in two different situations:
- when the systems are running on a server equipped with a hard drive;
- when systems are running on a server equipped with a solid state drive.
1.1. Short description
It is generally accepted that data storage is the main task of any database system. A hard drive is a popular medium used by enterprises to store large amounts of data. However, it is known that hard disk (I / O) performance decreases under workloads limited by I / O speed (I / O Bound). Therefore, it is often necessary to find a more efficient storage medium. In this article, we present the results of the application and testing of a new solid-state drive (SSD), used as the main storage medium for data, which demonstrates increased database performance.
1.2. Test methods
To run the test, each database system (CUBRID and MySQL) was installed on two separate servers: one with a hard drive and the other with a solid state drive. The performance improvement in transactions per second was continuously recorded throughout the experiment.
1.3. Test computer environment
The following are the specifications of computers equipped with a hard drive and a solid state drive. To accurately determine the difference in database performance when using a hard drive and an SSD, computers must be the same. Despite the fact that for internal purposes the use of identical computers was not a priority, equipment with very similar characteristics was still used for this test.
CUBRID and MySQL database systems were installed on computers with a hard drive and an SSD. During testing, the following database versions were used.
- CUBRID 2008 R3.0
- MySQL 5.1.47 (innoDB)
The following are the default configurations for the CUBRID and MySQL database systems. Both database servers were configured with a 4 GB data buffer. Other test setups were used by default.
CUBRID Configurations (cubrid.conf) MySQL Configurations (my.cnf)
[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 = 11.4. Test script
1.4.1. Table layout used in testing
To measure performance results, 40 tbl_200 ~ tbl_239 tables were created with the diagram below.
CREATE TABLE tbl_200;
ALTER TABLE tbl_200 ADD COLUMN
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 TABLE "tbl_200" ADD PRIMARY KEY ("id", "seq");
CREATE UNIQUE INDEX "iuk_tbl" ON "tbl_200" ("id", "col3", "col4", "col5");
CREATE INDEX "ink1_tbl" ON "tbl_200" ("id", "col9" DESC, "col14");Using the above table diagram, necessary to create test tables, machines equipped with a hard drive and a solid state drive passed three types of tests:
- After creating a database to insert 25 million data records in 40 tables, performance is measured with an “INSERT FULL” load for 30 minutes.
- After creating a database to insert 64 million data records into 40 tables, performance is measured with a “SELECT” load limited by the CPU Bound.
- After creating a database to insert 64 million data records into 40 tables, performance is measured with a “SELECT” load limited by I / O Bound.
All of the above loads were created in 40 threads. One INSERT load consists of one INSERT query, while the SELECT load consists of three SELECT queries with a primary key , a unique index and a non-unique index in each.
2. Review of test results
2.1. Test with Insert workload limited by I / O speed
After creating a database with 40 tables, each of which should contain approximately 625,000 data records (a total of 25 million), both computers (with a hard disk and an SSD) were subjected to performance testing with a FULL INSERT load for 30 minutes. The following table shows the results of performance testing.
The following diagram shows the change in the number of transactions per second.
Based on the results of the test described above with the “FULL INSERT” load, the following conclusions can be drawn.
- The performance of the CUBRID database system on a computer with an SSD is about five times higher than on a computer with a hard drive.
- The performance of the MySQL database system on a computer with a solid-state drive is approximately 2.5 times higher than on a computer with a hard drive. ( Note: On a computer with a solid state drive, MySQL does not load resources at 100%, so you can further improve performance).
2.2. Test with SELECT Workload Limited by CPU Capabilities
After creating a database with 40 tables, each of which should contain approximately 1,600,000 data records (a total of 64 million), both computers (with a hard disk and an SSD) were subjected to performance testing with a load limited by the CPU capacity for 10 minutes . In this load with SELECT queries, the query search area should be narrowed in order to completely place the necessary page in the memory buffer and maintain the desired 100% buffer performance value. Since I / O operations are not performed under this load, the performance difference between computers with a hard drive and an SSD is measured for all components except I / O. The following table shows the results of performance testing.
The following diagram shows the change in the number of transactions per second.
With no I / O operations, the performance of CUBRIDs using solid state media drops by about 17% compared to hard drives, and the performance of MySQL increases by about 6%. The difference in performance of all components except I / O on both computers is indicated above.
2.3. Test with SELECT Workload Limited by I / O Rate
After creating a database with 40 tables, each of which should contain approximately 1,600,000 data records (a total of 64 million), both computers (with a hard disk and an SSD) were subjected to performance testing with a load limited by I / O speed during 10 minutes. In order not to place the necessary page in the memory buffer completely and to prevent frequent page replacement, the search area of the SELECT query in this load should be expanded. The number of I / O operations is increasing because the workload is very intense. The following table shows the performance test results for both systems.
The following diagram shows the change in the number of transactions per second.
According to the results of the above test with the load "SELECT", limited by the speed of input-output, we can draw the following conclusions.
- The performance of CUBRID (in transactions per second) on a computer with an SSD increases by about 4.2 times compared to a computer with a hard drive.
- The performance of MySQL in transactions per second on a computer with a solid-state drive increases by about 2.8 times compared to a computer with a hard drive.
Thus, under conditions of limited speed and I / O, the performance of both database systems increases with the use of solid state drives.
2.4. Systematization of the SELECT test results
The following table summarizes the results of the two tests discussed above.
In the diagram below, the results of the test, limited by CPU capabilities , are shown in the left column, and the results of the test, limited by I / O speed- in the second. In all cases, the performance level (in transactions per second) during testing limited by CPU capabilities is higher than during testing limited by I / O speed. Thus, I / O operations can be considered to be the main reason for the decrease in the performance of database systems. The most interesting characteristic found during the experiment is a slight difference in CUBRID performance when performing operations limited by CPU capabilities and operations limited by I / O speed on a computer equipped with a solid state drive. In other words, CUBRID probably takes full advantage of working on a computer with an SSD. ( Random access speed the solid state drive used in this test is considered very high).
3. Conclusion
This experiment confirms that the performance levels of CUBRID and MySQL database systems increase on computers equipped with a solid state drive. Under load limited by I / O speed, CUBRID performance increases by 4.2 times, and MySQL performance by 2.8 times. CUBRID and MySQL database systems were not configured on SSD computers for this experiment. Therefore, in this experiment we do not discuss the suitability of computers with a solid state drive for a specific database system. Nevertheless, since both CUBRID and MySQL worked on computers equipped with a solid state drive, we can conclude that it is possible to further increase the performance of operations limited by I / O speed. In the future you can get more interesting results,
4. Explanatory Note
THIS TESTING WAS PERFORMED ONLY FOR INTERNAL USETO DETERMINE difference in performance when using solid-state drives as the main media, THEREFORE IN NO EVENT SHALL THE COMPANY, conduct research, BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, PUNITIVE OR CONSEQUENTIAL DAMAGES, INCLUDING LOSS OF DATA OR INCOME OR INTERRUPTION OF BUSINESS. THE RESULTS OF THIS TEST DO NOT MEAN THE EXCELLENCE OF ONE DATABASE OVER THE OTHER. TO EXACTLY DETERMINE THE DIFFERENCE IN THE DATABASE PRODUCTIVITY WHEN USING A HARD DISK AND A SOLID DRIVE, COMPUTERS SHOULD BE THE SAME. Despite the fact that for internal purposes the use of identical computers was not a priority, for this test the equipment was used with very similar characteristics (see.TEST COMPUTER ENVIRONMENT ). THEREFORE, THE RESULTS OF THIS TEST SHOULD BE USED ONLY FOR GENERAL EDUCATIONAL PURPOSES.
