Results of comparative performance testing of CUBRID and MySQL before and after using solid-state drives (SSD)
Good day to all!
Habr has finally earned, and now I can post a translation of an article published in English on the official CUBRID project website, which you asked for in comments to the previous habratopic .
In the following analysis of the performance of the database system, CUBRID and MySQL are tested to determine their performance in two different situations:
It is considered that data storage is the main task of any database system. Hard disk is a popular carrier used by enterprises for storing large amounts of data. However, it is known that the performance (input / output) of a hard disk decreases with workloads limited by the input / output 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, which demonstrates improved database performance.
To perform the test, each database system (CUBRID and MySQL) was installed on two separate servers: one with a hard disk and the other with a solid-state drive. The performance improvements in transactions per second were continuously recorded throughout the experiment.
')
Below are the characteristics of computers equipped with a hard disk and solid-state drive. To accurately determine the difference in database performance when using a hard disk and a solid-state drive, 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 disk and a solid-state drive. During testing, the following database versions were used.
Below are the default configurations in the CUBRID and MySQL database systems. Both database servers were configured with a 4 GB data buffer. The remaining test patterns 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 tabular scheme necessary for creating test tables, machines equipped with a hard disk 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 a single INSERT query, while a 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 a solid-state drive) were subjected to performance testing with a “FULL INSERT” load for 30 minutes. The following table shows the performance test results.
The following diagram shows the change in the number of transactions per second.
According to the results of the above test with the load “FULL INSERT”, the following conclusions can be made.
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 a solid-state drive) were subjected to performance testing with CPU-constrained load for 10 minutes . In a given load with SELECT queries, the query search area must be narrowed in order to fully place the necessary page in the memory buffer and maintain the desired 100% value of the buffer performance. Since I / O operations are not performed under this load, the difference in performance between computers with a hard disk and a solid-state drive is measured for all components except I / O. The following table shows the performance test results.
The following diagram shows the change in the number of transactions per second.
In the absence of I / O operations, the performance of CUBRID using solid-state media drops by about 17% compared to the use of hard disks, 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 a solid-state drive) were subjected to performance testing with a load limited by I / O speed for 10 minutes. In order not to place the necessary page in the memory buffer completely and prevent frequent replacement of pages, the search area of ​​the SELECT query in this load must be expanded. The number of I / O operations is increasing as the workload is very intensive. 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 input / output speed, we can draw the following conclusions.
Thus, under conditions of limited I / O speed, the performance of both database systems is improved using 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 the capabilities of the CPU , are shown in the left column, and the results of the test, limited by the speed of input-output , in the second. In all cases, the level of performance (in transactions per second) when testing, limited by the CPU, is higher than when testing, limited by the I / O speed. Thus, I / O operations can be considered to be the main reason for the decline in performance of database systems. The most interesting characteristic found during the experiment is the insignificant 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 uses all the advantages of working on a computer with a solid-state drive. (The random access speed of a computer with a solid-state drive used in this test is considered very high).
This experiment confirms that the performance levels of the CUBRID and MySQL database systems are improved on computers equipped with a solid-state drive. With a load limited by I / O speed, CUBRID performance increases by 4.2 times, and MySQL performance - by 2.8 times. The CUBRID and MySQL database systems were not configured on computers with a solid-state drive for this experiment. Therefore, in this experiment, we do not discuss the suitability of computers with a solid-state drive for a particular database system. However, since both CUBRID and MySQL worked on computers equipped with a solid-state drive, it can be concluded that it is possible to further improve the performance of operations limited by the I / O speed. In the future, you can get more interesting results by conducting various tests with the same hardware specifications and operating system (installing other hard drives and solid-state drives), but now with optimized settings for the CUBRID and MySQL database systems.
This test is carried out only for internal use To find the 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, EXEMPLARY, OR CONSEQUENTIAL DAMAGES INCLUDING LOSS OF DATA AND PROFIT OR INTERRUPTION OF BUSINESS ACTIVITIES. THE RESULTS OF THIS TEST DO NOT MEAN THE EXCELLENCE OF ONE DATABASE OVER ANOTHER. TO EXACTLY DETERMINE THE DIFFERENCE IN THE PERFORMANCE OF THE DATABASE WHEN USING A HARD DRIVE AND A HARD DRIVE, COMPUTERS SHOULD BE THE SAME. Despite the fact that, for internal purposes, the use of the same-on-one PDA-and-PDA software wasn’t PRIORITY, FOR THIS TEST, EQUIPMENT WITH VERY RELAXING CHARACTERISTICS (I ’m a key a few ) I ’ll be using the same programs and I’m already using the same programs that I’ll use ; THEREFORE, THE RESULTS OF THIS TEST MUST BE USED ONLY IN GENERAL EDUCATIONAL PURPOSES.
Habr has finally earned, and now I can post a translation of an article published in English on the official CUBRID project website, which you asked for in comments to the previous habratopic .
1. About the test
In the following analysis of the performance of the database system, CUBRID and MySQL are tested to determine their performance in two different situations:
- when systems are running on a server equipped with a hard disk;
- when systems are running on a server equipped with a solid-state drive.
1.1. Short description
It is considered that data storage is the main task of any database system. Hard disk is a popular carrier used by enterprises for storing large amounts of data. However, it is known that the performance (input / output) of a hard disk decreases with workloads limited by the input / output 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, which demonstrates improved database performance.
1.2. Testing methods
To perform the test, each database system (CUBRID and MySQL) was installed on two separate servers: one with a hard disk and the other with a solid-state drive. The performance improvements in transactions per second were continuously recorded throughout the experiment.
')
1.3. Test Computer Environment
Below are the characteristics of computers equipped with a hard disk and solid-state drive. To accurately determine the difference in database performance when using a hard disk and a solid-state drive, 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 disk and a solid-state drive. During testing, the following database versions were used.
- CUBRID 2008 R3.0
- MySQL 5.1.47 (innoDB)
Below are the default configurations in the CUBRID and MySQL database systems. Both database servers were configured with a 4 GB data buffer. The remaining test patterns were used by default.
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=1000MySQL 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 = 11.4. Test script
1.4.1. The table schema used for 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 tabular scheme necessary for creating test tables, machines equipped with a hard disk and a solid-state drive passed three types of tests:
- After creating a database for inserting 25 million data records into 40 tables, the performance is measured with a load of “INSERT FULL” (FULL INSERT) for 30 minutes.
- After creating a database for inserting 64 million data records in 40 tables, performance is measured with a “SELECT” load, limited by the CPU capabilities (CPU Bound).
- After creating a database for inserting 64 million data records into 40 tables, performance is measured under a “SELECT” load, limited by I / O speed (I / O Bound).
All of the above loads were created in 40 threads. One INSERT load consists of a single INSERT query, while a SELECT load consists of three SELECT queries with a primary key , a unique index, and a non-unique index in each.
2. Review 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 a solid-state drive) were subjected to performance testing with a “FULL INSERT” load for 30 minutes. The following table shows the performance test results.
The following diagram shows the change in the number of transactions per second.
According to the results of the above test with the load “FULL INSERT”, the following conclusions can be made.
- The performance of the CUBRID database system on a computer with a solid-state drive is about five times higher than on a computer with a hard disk.
- The performance of the MySQL database system on a computer with a solid-state drive is about 2.5 times higher than on a computer with a hard disk. ( Note. On a computer with a solid-state drive, MySQL does not load resources by 100%, so you can further improve performance).
2.2. Test with SELECT workload limited by CPU
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 a solid-state drive) were subjected to performance testing with CPU-constrained load for 10 minutes . In a given load with SELECT queries, the query search area must be narrowed in order to fully place the necessary page in the memory buffer and maintain the desired 100% value of the buffer performance. Since I / O operations are not performed under this load, the difference in performance between computers with a hard disk and a solid-state drive is measured for all components except I / O. The following table shows the performance test results.
The following diagram shows the change in the number of transactions per second.
In the absence of I / O operations, the performance of CUBRID using solid-state media drops by about 17% compared to the use of hard disks, 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 speed
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 a solid-state drive) were subjected to performance testing with a load limited by I / O speed for 10 minutes. In order not to place the necessary page in the memory buffer completely and prevent frequent replacement of pages, the search area of ​​the SELECT query in this load must be expanded. The number of I / O operations is increasing as the workload is very intensive. 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 input / output speed, we can draw the following conclusions.
- The performance of CUBRID (in transactions per second) on a computer with a solid-state drive increases about 4.2 times compared with a computer with a hard disk.
- MySQL performance in transactions per second on a computer with a solid-state drive increases about 2.8 times compared with a computer with a hard disk.
Thus, under conditions of limited I / O speed, the performance of both database systems is improved using 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 the capabilities of the CPU , are shown in the left column, and the results of the test, limited by the speed of input-output , in the second. In all cases, the level of performance (in transactions per second) when testing, limited by the CPU, is higher than when testing, limited by the I / O speed. Thus, I / O operations can be considered to be the main reason for the decline in performance of database systems. The most interesting characteristic found during the experiment is the insignificant 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 uses all the advantages of working on a computer with a solid-state drive. (The random access speed of a computer with a solid-state drive used in this test is considered very high).
3. Conclusion
This experiment confirms that the performance levels of the CUBRID and MySQL database systems are improved on computers equipped with a solid-state drive. With a load limited by I / O speed, CUBRID performance increases by 4.2 times, and MySQL performance - by 2.8 times. The CUBRID and MySQL database systems were not configured on computers with a solid-state drive for this experiment. Therefore, in this experiment, we do not discuss the suitability of computers with a solid-state drive for a particular database system. However, since both CUBRID and MySQL worked on computers equipped with a solid-state drive, it can be concluded that it is possible to further improve the performance of operations limited by the I / O speed. In the future, you can get more interesting results by conducting various tests with the same hardware specifications and operating system (installing other hard drives and solid-state drives), but now with optimized settings for the CUBRID and MySQL database systems.
4. Explanatory note
This test is carried out only for internal use To find the 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, EXEMPLARY, OR CONSEQUENTIAL DAMAGES INCLUDING LOSS OF DATA AND PROFIT OR INTERRUPTION OF BUSINESS ACTIVITIES. THE RESULTS OF THIS TEST DO NOT MEAN THE EXCELLENCE OF ONE DATABASE OVER ANOTHER. TO EXACTLY DETERMINE THE DIFFERENCE IN THE PERFORMANCE OF THE DATABASE WHEN USING A HARD DRIVE AND A HARD DRIVE, COMPUTERS SHOULD BE THE SAME. Despite the fact that, for internal purposes, the use of the same-on-one PDA-and-PDA software wasn’t PRIORITY, FOR THIS TEST, EQUIPMENT WITH VERY RELAXING CHARACTERISTICS (I ’m a key a few ) I ’ll be using the same programs and I’m already using the same programs that I’ll use ; THEREFORE, THE RESULTS OF THIS TEST MUST BE USED ONLY IN GENERAL EDUCATIONAL PURPOSES.
Source: https://habr.com/ru/post/118074/
All Articles