Comparison

Introduction

This document is written for the purpose of measuring performance of CUBRID 2008 R1.1. The information about performing an experiment on the NBD Bench and its results are provided in this document. We compare CUBRID with three different DBMSs, which are widely used, to show the CUBRID performance objectively.

History

Version	Date	Changes made	Author
2008R1.1	2008.11.20	First draft written	Wongsae Choi (wschoi@nhncorp.com)

Copyright

Copyright 2009 Search Solution Corporation. All Rights Reserved.

This document is an intellectual property of Search Solution Corporation; unauthorized reproduction or distribution of this document, or any portion of it is prohibited by law.

This document is provided for information purpose only. Search Solution Corporation has endeavored to verify the completeness and accuracy of information contained in this document, but it does not take the responsibility for possible errors or omissions in this document. Therefore, the responsibility for the usage of this document or the results of the usage falls entirely upon the user, and Search Solution Corporation does not make any explicit or implicit guarantee regarding this.

Software products or merchandises mentioned in this document, including relevant URL information, conform to the copyright laws of their respective owners. The user is solely responsible for any results occurred by not complying with applicable laws.

Search Solution Corporation may modify the details of this document without prior notice.

Table of Contents

 

• 1. Purpose
• 2. Test Scenario
  • 2.1 Configuration of Test Databases
  • 2.2 Schema
  • 2.3 Workload
  • 2.4 Test Method
• 3. Test Environment
  • 3.1 Test Environment
• 4. Analysis and Conclusions
  • 4.1 Performance Analysis of Each DBMS
  • 4.2 Relationship between Database Size and Performance
• 5. Conclusions
• Attachment 1: NBD Benchmark Specifications Summary
• Attachment 2: Benchmark Test Results

 

Table & Pictures

 

List of Tables

 

Table 1 Configuration of Test Database

Table 2 Index Configuration

Table 3 Workload Summary

Table 4 Parameter Configuration of CUBRID

Table 5 Parameter Configuration of Commercial DBMS D1

Table 6 Parameter Configuration of OSS DBMS D1

Table 7 Parameter Configuration of Commercial DBMS D2

Table 8 Configuration Unit and Expansion Rules of NBD

Table 9 Features of Each Workload Type

Table 10 How to Measure NBD Results

Table 11 Test Configuration and Results

Resource Usage of Each Test

 

List of Figures

 

Figure 1 Schema Integration Model

Figure 2 Schema Separation Model

Figure 3 Test Environment

Figure 4 Performance of Each DBMS

Figure 5 CPU Usage of Each DBMS

Figure 6 Performance by Database Size

Figure 7 Logical E-R Diagram of NBD

1. Purpose

The purpose of this test is to verify the performance of CUBRID 2008 R1.1 Database Management System (hereinafter "DBMS") by using the NHN Internet Bulletin Board Application Database Benchmark (hereinafter "NBD Benchmark"). And then, it compares its performance with that of OSS DBMS D1, commercial DBMS DS, and commercial DBMS D2.

For more information about the NBD Benchmark, see the NDB specifications; for its summary, see Attachment 1: NBD Benchmark Specifications Summary.

2. Test scenario

2.1. Configuration of Test Databases

For this test, small and medium-sized databases are used. The characteristics of each database are described in the Table 1 Configuration of Test Database below.

Table 1 Configuration of Test Database

 

Size	Number of Bulletin Boards		Total Number of Rows (Tolerance: 10%)	Application
	Small	Medium
Small (Level 1)	1	1	101,000	Notification
Medium (Level 2)	2	6	602,000	Medium-sized UCC bulletin board

 

2.2. Schema

The schema used in this test is the same as the one given in Attachment 1: NBD Benchmark Specifications Summary; however, we also perform a test for the case in which the field that indicates the click count of a post is separated to tune the performance of OSS DBMS D1. For OSS DBMS D1, the performance deviation is relatively high depending on whether or not the Query Result Cache feature is used.

However, if the click count is managed in the post information table as defined in the current NBD specifications, the Query Result Cache feature of OSS DBMS D1 will not work properly because the click count keeps changing. It is unavoidable to process the result set whenever the click count changes. Therefore, this test compares the schema in which the click count field is separated as an independent table and the schema in which the field is integrated in the post information as defined in the NBD specifications, respectively.

Figure 1 Schema Integration Model below is the schema model defined in the NBD specifications; that is, the click count field is managed as an attribute of the post information table (NBD_ARTICLE_INFO) in this model. Figure 2 Schema Separation Model has the click count as a separate table to improve the performance of OSS DBMS D1. This allows queries that only access the post information table (NBD_ARTICLE_INFO) to use the Query Result Cache feature.

Figure 1 Schema Integration Model

Figure 2 Schema Separation Model

The index information for each table is shown in Table 2 Index Configuration. The index is configured to accomplish the best performance for each DBMS, and DBAs of the CHINA data analysis team has configured the index for commercial DBMS D1, commercial DBMS D2, and OSS DBMS D1 while the team tunes the performance.

 

Table 2 Index Configuration

 

Table Name	CUBRID, Commercial DBMS D1, D2 DB		OSS DBMS D1
	Index Type	Test Column	Index Type	Test Column
NBD_BOARD_INFO	PK	BOARD_ID	PK	BOARD_ID
NBD_CATEGORY	PK	CATEGORY_ID	PK	CATEGORY_ID
NBD_ARTICLE_INFO	PK	ARTICLE_ID	PK	ARTICLE_ID
	UNIQUE	BOARDID ARTICLE_CATEGORY ARTICLE_NO REPLY_DEPTH REPLY_NO_SEQ POSTED_TIME	UNIQUE	BOARDID ARTICLE_CATEGORY ARTICLE_NO REPLY_DEPTH REPLY_NO_SEQ POSTED_TIME
	General	BOARD_ID ARTICLE_CATEGORY	General	BOARD_ID ARTICLE_CATEGORY
	General	BOARD_ID ARTICLE_CATEGORY ARTICLE_NO	General	BOARD_ID ARTICLE_CATEGORY ARTICLE_STATUSIS_PRIVATE ARTICLE_NO REPLY_DEPTH REPLY_NO_SEQ POSTED_TIME
	REVERSE	BOARD_ID ARTICLE_CATEGORY ARTICLE_NO
NBD_CONTENT	PK	ARTICLE_ID	PK	ARTICLE_ID
NBD_ATTACHMENT	PK	ARTICLE_ID, ATTACHMENT_NO	PK	ARTICLE_ID, ATTACHMENT_NO
NBD_COMMENT	PK	ARTICLE_ID, COMMENT_NO	PK	ARTICLE_ID, COMMENT_NO
	General	ARTICLE_ID POSTED_TIME	General	ARTICLE_ID COMMENT_STATUS POSTED_TIME

 

2.3. Workload

Of the two workloads defined in the NBD Benchmark, we test the HOTSPOT READ type. The HOTSPOT READ is the type of modeling which concentrates workload onto a specific post. In case of a post published on a portal main page, it can be exposed to intensive user clicks; therefore, DBMS must simultaneously read the post and increase the number of clicks on it. At this moment, updates on increasing click count cause an intensive workload to DBMS, leading to a performance bottleneck. This test simulates this situation and compares CUBRID with the commercial DBMS D1, commercial DBMS D2 and OSS DBMS D1, in terms of characteristics and performances of system.

The Table 3 Workload Summary below shows the summary of the workload transaction.

Table 3 Workload Summary

 

Transaction Mix	Summary	Unit Task	Waiting Time	Ratio
MIX1	Reads a post	Views the post information Increases the click count Views the content Prints the attachment Prints the previous and next posts Prints the reply	5 ms	70%
MIX2	Reads a post and prints a list	Views the post information Increases the click count Views the content Prints the attachment Prints the previous and next posts Prints the reply Prints a list including a post	5 ms	20%
MIX3	Views a post list	Prints a list	50 ms	10%

 

There are 6 HOTSPOT posts. (The average number of posts exposed on the main page for each Naver service is 5~6.)

Table 4 Parameter Configuration of CUBRID

 

Item	Value	Remark
Version	CUBRID 2008 R1.1
Connections	max_clients 200
Isolation Level	isolation_level="TRAN_REP_CLASS_UNCOMMIT_INSTANCE"	Dirty read allowed
Memory Buffer	data_buffer_pages=2000
Lock Timeout	lock_timeout_in_secs=-1
Logging	log_buffer_pages=100 async_commit=on group_commit_interval_in_msecs=5 checkpoint_evey_npages=10000 checkpoint_interval_in_mins=30	Asynchronous Commit, Group Commit,   Log diff & compression
Thread
Query processing	sort_buffer_pages=32 temp_file_memory_size_in_pages=4 index scan_in_oid_order=off max_plan_cache_entries=200 query_cache_mode=2	Query Plan Cache, Query Result Cache
Broker	SQL_LOG=off JDBC_CACHE=ON

 

Table 5 Parameter Configuration of Commercial DBMS D1

 

Item	Value	Remark
Version	-
Connections	processes =500
Isolation Level	transaction_isolation= READ-COMMITTED	Default isolation level, cannot be changed
Memory Buffer	10 g:      db_buffer : 2.5 G sga_target: 4 G 11 g:      db_buffer : 2.5 G memory_target : 4 G
Lock Timeout	default
Logging	logging
Result_cache	result_cache_mode=manual
Other parameter	Commit_logging=batch commit_write=nowait

 

Table 6 Parameter Configuration of OSS DBMS D1

 

Item	Setting Value	Remark
Version	-
Connections	max_connections 500
Isolation Level	transaction_isolation= READ-UNCOMMITTED	Dirty Read allowed
Memory Buffer	innodb_buffer_pool_size = 4 G innodb_additional_mem_pool_size = 16 M	About 50% of the actual available memory
Lock Timeout	innodb_lock_wait_timeout = 60
Logging	innodb_log_buffer_size = 8 M innodb_log_file_size = 512 M innodb_log_files_in_group = 3	Recorded into 3 log files.
Thread	thread_cache_size=128 innodb_thread_concurrency = 4
Query processing	table_cache = 1024 sort_buffer_size = 16 M join_buffer_size = 16 M read_buffer_size = 16 M read_rnd_buffer_size = 16 M query_cache_size = 64 M query_cache_limit = 2M	The Query Result Cache is used, and its size must be large enough to prevent sort or join from performing a disk operation.

 

Table 7 Parameter Configuration of Commercial DBMS D2

 

Item	Setting Value	Remark
Version	-
Connections	max_connections = 0	No limit
Isolation Level	transaction_isolation= READ-UNCOMMITTED	Specified in the stored procedures
Memory Buffer	min server memory = 1638 M max server memory = 3072 M
Lock Timeout	query wait = -1	Keep waiting
Logging
Thread	max worker threads = 255
Query processing

 

2.4. Test Method

Perform the test three times consecutively that generates workload for 10 minutes through on the SQLMAP interface and select the intermediate value. The following conditions must be satisfied according to Measuring Rules defined in Section 4.4 of the document regarding the NBD specifications.

Performance is measured during Steady State Period, and Expected Response Time must be within 0.3 seconds.

The CPU usage of a DB server must be below 85% during the test.

Workload amount may be adjusted to achieve the optimum performance of each DBMS system.

If one transaction out of a transaction mix has failed, the next transaction will not be executed and the corresponding service is handled as a failure.

Transaction failure rate must be below 10%.

Measured values must be collected at least every 10 seconds.

Warm-up time may be excluded from the measurement time.

The configuration of SQLMAP interface is common to all the DBMSs as followings:

<settings cacheModelsEnabled="true" enhancementEnabled="true" lazyLoadingEnabled="true" maxRequests="512" maxSessions="128" maxTransactions="128" useStatementNamespaces="false" defaultStatementTimeout="0" /> <transactionManager type="JDBC"> <dataSource type="DBCP"> <property name="maxActive" value="100" /> <property name="minIdle" value="10" /> <property name="maxIdle" value="-1" /> <property name="maxWait" value="60000" /> <property name="poolPreparedStatements" value="true" /> </dataSource> </transactionManager>

3. Test Environment

This chapter briefly describes the configuration of the test bed used during performing this test. For more information about the features and specifications for each device for the test bed, see Section 2.5 Hardware Configuration.

3.1. Test Environment

The test environment includes only DB servers, without configuring high availability (HA) functionality. In addition, to maximize workload to a DB server, four servers are configured, and its tolerant limit is 40 users at the same time; that is, 160 simultaneous users are allowed to all DB servers. Each workload server runs two workload generation programs, and each program generates 20 threads to load work on a DBMS. Since CUBRID supports the separation of DB server and broker, it is assumed that broker is separated from the DB server (that is, located on Standby server) in replication state.

Figure 3 Test Environment

4. Analysis and Conclusions

This chapter analyzes the test results and makes conclusions. For more information about the measurements of the test results, see Attachment 2: Benchmark Test Results.

4.1. Performance Analysis of Each DBMS

Figure 4 Performance of Each DBMS

Figure 4 shows the performance data by DBMS when 160 simultaneous users access to each DBMS. The performance of each DBMS is very close to the CPU resource usage shown in Figure 5.

Figure 5 CPU Usage of Each DBMS

4.2. Relationship between Database Size and Performance

Figure 6 Performance by Database Size

5. Conclusions

This performance test was conducted using variables such as database size, workload increase, and cache functionality provided by each DBMS. We observed the manner in which each variable affects four DBMS, and drew some conclusions regarding the advantages and disadvantages of the four DBMS based on analysis.

In the small and medium-sized DBMS benchmarking, the commercial DBMS D1 shows the best performance.

CUBRID is in second place after the commercial DBMS D1 in small and medium-sized DBMS benchmarking. In the small DBMS benchmarking, its CPU usage is below 30% for CUBRID. Therefore, it may show higher performance when more work is loaded. However, in the medium-sized DBMS benchmarking, its CPU usage is about 80%; further analysis will be required.

DBMS D2 shows the highest CPU usage of 100% in both small and medium-sized DBMS.

For OSS DBMS D1 server, it shows 30% or lower in small and medium-sized DBMS. Even when load is less, there is no significant difference in CPU usage; further analysis will be required.

Attachment 1: NBD Benchmark Specifications Summary

The NBD Benchmark is a benchmark to measure the performance of a DBMS used for a Bulletin Board System (BBS) type of web service. There is no limitation in terms of the configuration of hardware and DBMS, and pricing is not calculated. Therefore, it is a useful benchmark for comparing the performance among DBMSs with the same hardware configuration.

The logical schema of database that must be established for the NBD Benchmark test is shown in Figure 7.

Figure 7 Logical E-R Diagram of NBD

The Table 8 below defines the configuration unit of NBD database in BBS unit.

Table 8 Configuration Unit and Expansion Rules of NBD

 

BBS Type	Small Size	Medium Size	Large Size	Remark
Number of Posts	1,000	100,000	10,000,000	± 10%
Category	None	Less than 10	Less than 10
Post Type	Text	Text, image, video, etc.	Text, image, video, etc.
Post Size	90% or more : 1K	50% : 1K or less than 1K 30% : 1K - 10K 20% : 10K - 64K	50% : 1K or less than 1K   30% : 1K - 10K 20% : 10K - 64K
Number of post writers	1 person	Average 10 posts per person	Average 10 posts per person
Number of attachments	None	50% : 5	50% : 5
Number of replies	None	50% : 10 40% : None 10% : 100	50% : 10 40% : None 10% : 100
Reply	None	10% out of all the posts are replies	10% out of all the posts are replies
Remark	Notification

 

The NBD database can be expanded by combining each unit element of Table 8. Expansion rules are as follows:

Step 1 is composed of one small BBS.

Up to Step 5, five medium-sized BBS and one small size BBS are added at every step.

From Step 6, two large BBS, five medium-sized BBS and one small BBS are added at every step.

The NDB workload is defined as type 1 and type 2. Type 1 is the HOTSPOT READ model where intensive views are concentrated on a specific post. For an Internet service, many users intensively view several pages when new posts are published in a main page, or when a list of popular posts is published on specific pages; these cases belong to type 1. Type 2 is the NAVIGATION model where access to a post is evenly distributed; Internet communities or blogs belong to type 2.

Table 9 below shows the features of each workload type.

Table 9 Features of Each Workload Type

 

Item	Workload Type 1 (Hotspot Read)	Workload Type 2 (Navigation)
Overview	Reads a specified number of posts and views a list consecutively for a specific period of time (modeling of intensive load concentration onto a specific popular post)	Reads a post and makes recommendations consecutively while viewing a list (a popular post is periodically selected and performed)
Transaction Mix	Updates the click count, views the post content, views the attachment list, views the reply list, and views the previous & next posts by using the BBS ID and the post ID (waiting time: 5 msec.) Updates the click count, views the post content, views the attachment list, views the reply list, views the previous & next posts, and views the registration order list by using the BBS ID and the post ID (waiting time: 5 msec.) Views the posting order list by using the BBS ID and the page number 1 (waiting time: 50 msec.)	Views the posting order list - 1 page, views the posting order list - 2 pages, views the posting order list - 3 pages, views the reverse posting order list - 1 page, and views the reverse recommendation order list - 1 page by using BBS ID (waiting time: 10 msec.) Views the posting order list - 1 page and reads a post by using the BBS ID, which are defined in NBD 3.8.1 Updates a post and views a post by using the BBS ID (waiting time: 30 sec)
Parameter	Total execution time: 10 min/30 min/60 min Database size: Consisting of at least one step Number of executions per transaction mix: Number of Hotspot posts used in the entire workload: 3,6,9,12	Total execution time: 10 min/30 min/60 min Database size: Consisting of at least two steps Number of executions per transaction mix: At least 10
Requirements	Transaction mix 1, 2, and 3 are executed in a 7:2:1 ratio repeatedly for a specific time period   Hotspot posts must not be adjacent to each other, and must be neither the first nor the last post (if adjacent posts exit). A Hotspot post must have 3 or more attachments, 50 or more replies, and 1 or more responses. The Hotspot posts are distributed to Transaction mix 1 and 2 in sequence. Transaction mix 3 is executed by selecting one of the BBSs where a Hotspot post exists. (the number of posts per page is 20.)	Transactions 1 and 2 are executed repeatedly at equal ratio for a specific time period; Transaction mix 3 is executed alone for a specific time period. The number of posts per page is 20 in Transaction mix 1 or 2. Transaction mix 1 uses BBSs that are composed of at least 3 pages. Transaction mix 2 is executed selectively according to normal distribution in the medium size BBSs. In Transaction mix 2, the selection of a post and reply must not be fixed. Transaction 3 is executed for all the BBSs in sequence.

 

The general constraints for the execution of workload in the NBD Benchmark are as follows:

The ACID (Atomicity, Consistency, Isolation, and Durability) of all transactions must be guaranteed.

The increased click count when reading a post must be reflected to the DB immediately and an accurate number must be provided when it is retrieved by other transactions.

Lock Time Out or Deadlock may occur, but must be below 10% of all transactions.

The NBD Benchmark test results must include measured values for the items in Table 10 below and the following requirements must be satisfied.

Measurement must be conducted during the steady-state period.

If one transaction out of a transaction mix has failed, the next transaction will not be executed and the corresponding service is handled as a failure.

Transaction failure rate must be below 10% and max; the CPU usage must be below 85%.

Measured values must be collected at least every 10 seconds.

Warm-up time may be excluded from the measurement time.

Expected response time is below 0.3 seconds.

The final performance is determined by the page numbers per second (PV/sec) that are processed by each system.

Table 10 How to Measure NBD Results

 

Item	Measurements	Remark
Processing Capacity	Total number of processed service requests	Number of successful service requests during the steady-state period
	Total number of failed service requests	Number of failed service requests during the steady-state period
	Processed pages per second (PPS)	Processed requests per second
	Transactions per second (TPS)	Number of processed transactions without an error per second
	Processed queries per second (QPS)	Number of processed SQLs without an error while satisfying the expected response time per second
Response Time	Average/Maximum/Minimum/90% service response time	Time duration from start to end of the service
	Average/Maximum/Minimum/90% transaction response time	Time duration from transaction start to Commit/Rollback task completion
	Average/Maximum/Minimum/90% query response time	Time duration until one query statement is processed and its result is returned (Only execute is measured except for the first prepared statement.)
Resource Usage	Average CPU usage	Sum of user/system/io wait during measurement period (100 - CPU idle)
	Average memory usage	Measures the size of memory used during measurement period in GB unit
	Average IO usage	Amount of disk I/O (bytes/sec) and execution time (msec.) during measurement period

 

Attachment 2: Benchmark Test Results

Table 11 Test Configuration and Results

 

No.	DBMS	DB size	Result Cache & JDBC Cache	#Page View	CPU Usage of a DB server
1	CUBRID	Level 1	ON	1767	29.5%
2	CUBRID	Level 2	ON	2121	75%
3	Commercial DBMS D1	Level 1	ON	2630	76%
4	Commercial DBMS D1	Level 2	ON	2928	83%
5	D2 DB	Level 1	-	1184	100%
6	D2 DB	Level 2	-	1195	100%
7	OSS DBMS D1	Level 1	ON	593	29%
8	OSS DBMS D1	Level 2	ON	387	18%

 

Resource Usage of Each Test