Oracle & esProc Performance Comparison Testing on Single Machine

1. Testing purposes

Test esProc and Oracle on the same hardware to compare their single machine performances in carrying out a single big task and small concurrent tasks.

2. Testing contents and methods

Data volume:

Small data volume: Single fact table around 10G. To prevent the testing result from being affected by the operating system cache, different fact tables are accessed with multiple concurrent requests.

Large data volume: Single fact table around 100G.

Algorithms:

Several typical SQL algorithms, including data scan, join, data grouping with small result set and with large result set. The use of these simple algorithms is to make the comparison more intuitively and easier to understand, not because esProc and SQL are identical to each other. In fact, the two of them focus on different things. esProc is good at procedure computation for tasks with complicated business logic, while SQL is suitable for computations of average complexity. A complicated algorithm that can be realized with different SQL execution plans makes execution paths uncontrollable and lacks comparability. So the testing doesn’t include one.

Use Cases:

Multiple use cases are designed for testing each algorithm according to table widths and data types.

Among them, the purpose of the use cases of data grouping with large result set is to test the performances of Oracle and esProc in handling the scenarios where the result sets of the group operations are too large to be entirely accommodated by the memory. So this report tests the parallel processing of a task with an amount of 100G data, not the concurrent processing of small tasks.

Note: An appendix of esProc Oracle Single Machine Performance Comparison Process  is added to this report. Please refer to it for details such as data structures, test code, and test reproduction.

3. Testing Environment

Testing Machine：Dell Power Edge T610

CPU：Intel Xeon E5620*2

RAM：20G

HDD：Raid5 1T

Operating System：CentOS 6.4

JDK：1.6

Oracle Version：11g

esProc Version:3.1

4. Data Description

The volume of the data is decided by the amount allowed by the exported text file.

For the single machine test, esProc uses the proprietary file format.

As the purpose is mainly to test the performance of big data computation and full table scan performance, no primary key or index is built for any table in the database. The primary key field described in data structure isn’t a physical key but a logical one defining that data repetition isn’t allowed.

4.1Data tables and table association

Fact Tables T1, T11, T12, and T13

They are wide data tables that each has 100 fields to simulate the real-world fact tables with a large number of fields. The four tables have identical structures. T11, T12, T13 are used for small concurrent accesses to different tables to avoid system cache.

Fact Tables T2, T21, T22, and T23

These are narrow data tables having identical structures and 11 fields for simulating the real-world facts tables with a small number of fields. T21, T22, T23 are used for small concurrent accesses to different tables to avoid system cache.

Fact tables are the main data source for this test, and are used in data scan, data grouping, and join operations. Tests are done for both large and small data volume, depending on the numbers of records inserted to the tables.

Dimension tables DL2, DL6, DD2, DD6, DC2, and DC6

The dimension tables are only used in the use cases of the join (and multi-join) operations where dimension tables and fact tables are joined or the dimension tables are joined between themselves. These tables have fixed data volume.

4.2. Data volume

The following statistics are the numbers of records in the testing tables, not the numbers of bytes that they occupy.

Note: The big-task test on a single machine is intended to measure the performance when data requires an amount of memory that is far larger than the physical memory. The standard used by the testing is that a fact table is approximately 5 times larger than the physical memory and a dimension table is far smaller than the physical memory. The actual number of records in a table can be adjusted according to the machine configuration.

The test of small concurrent tasks on a single machine is intended to measure the performance when the amount of memory occupied by data is less than the physical memory, and the computation could be done completely in the memory. The standard used by the testing is that a fact table is approximately 50% of the size of the physical memory and a dimension table is far smaller than the physical memory. The actual number of records in a table can be adjusted according to the machine configuration. T11/T12/T13 and T21/T22/T23 are only used for the test of the concurrent computation of small tasks.

To make sure that esProc and Oracle are computing exactly the same data, the data generated in Oracle is exported to a file with the esProc-defined binary format, which is esProc’s data source for the test.

5. Use Cases Description

For better understanding, all the logics in the test are described in SQL.

During the test Oracle executes SQL statements directly, whereas functionally equivalent esProc programs are written to complete the same computation.

5.1. Use Case for big-data scan

This use case is about big-task single machine test. It’s designed to test the computational performance of full table scan for a large volume of data. Performances for full table counting, integer sum, float sum, value sum, integer filter, number filter, character filter, date filter are considered respectively.

5.2. Use Case for big-data grouping with small result set

This use case is about big-task single machine test. It’s designed to test the performance of grouping computation involving large data volume, where the number of records in each group is small, smaller than the physical memory. Performances for integer grouping, number grouping, character grouping and date grouping are considered respectively.

5.3. Use Case for big-data grouping with large result set

This use case is about big-task single machine test. It’s designed to test the performance of grouping computation involving large data volume, where the number of records in each group is large, larger than the physical memory, and thus the computation cannot be entirely completed within the memory.

5.4. Use Case for big-data joins

This use case is about big-task single machine test. It’s designed to test the performance of join computations involving large data volume. Performances for integer, number, character joins, as well as single-level and multi-level joins, are considered respectively.

5.5. Use cases for performing small concurrent tasks

These use cases test the performance of carrying out small concurrent tasks that access various physical tables or files to avoid OS cache. Tests are done for three types operations – data scanning, data grouping and joins – using the above use cases 5.1, 5.2 and 5.4 where the data volume is set as small (See the section of Data Volume).

Scenarios of single task, dual-task concurrency, and four-task concurrency are tested respectively.

6. Use Cases for Testing

6.1. Small concurrent scans

This use case tests Oracle and esProc for scanning performance against small tables (files). The test is done with a multi-task concurrent access mode. Each task is accessing a different table (file). Oracle executes 16-threads parallel processing, while esProc executes 4 tasks in parallel, because it is proved through the test that those are the very number of parallel tasks for highest performance.

Testing statistics:

Note: In the following table, the time unit is second. 2 Concurrencies indicates 2 SQL statements are executed simultaneously; and the same with 4 concurrencies.

Statistics interpretation:

1. Data with blue background is the peak values obtained by this use case. Both Oracle and esProc reach peak performance with 1 concurrency, but Oracle’s performance is several times higher than esProc.

2. Performance degrades significantly both for Oracle and esProc when the number of concurrencies increases to 2. Oracle still has advantage but much of the gap has been closed.

3. esProc handles each task with equal performance, while Oracle is extremely unstable.

6.2. Big-data scan

This use case tests Oracle and esProc for their performance in scanning big tables (files). Scenarios of 1, 2 and 4 parallel tasks are tested respectively.

Testing statistics:

Statistics interpretation:

1. Oracle is at peak performance with 1 thread, but performance starts to degrade with 2 parallels. However, esProc’s performance with 4 parallel tasks is generally higher than with 1 task, sometimes even several times higher, excepting for a few instances.

2. esProc demonstrates an overall advantage over Oracle in this use case test. This is particularly obvious with narrow tables and computation-intensive cases, such as 106, 110 and 114. Use cases 129 and 119 are exceptions, however, where Oracle performs slightly better than esProc.

3. esProc is observed to be capable of increasing the computational performance several times with the increase of parallel number, as in the use cases 106, 110, 114, 118, 122, 126 and 130. These are all about narrow table access, and with more computations.

4. In some cases esProc’s performance also degrade, as in the use cases 103, 107, 111, 115, 119, 119, 123 and 127. These are all about wide table access with fewer computations.

6.3. Small concurrent grouping tasks

This use case tests Oracle and esProc for grouping computation performance against small tables (files). The test is done in a multi-task concurrent access mode. Each task accesses different tables (files).

Testing statistics:

Note: According to the data volume, esProc can choose to do pure in-memory computation or mixed-mode-of-in-memory-and-external-memory computation. This use case uses in-memory computation, while the grouping use case with large data volume in later part of this report is handled with a mixed mode. Pure in-memory computation has the risk of memory overflow. Thus use case uses 1, 2 and 4 concurrencies to avoid that. Data in green is for 1 concurrency, data in red for 2 concurrencies and data in black for 4 concurrencies. Green character reflects parallel 1, red character is for parallel 2, and black for parallel 4. Oracle forces a mixed mode computation to avoid memory overflow. Each task is performed with a fixed number of 16 parallel threads to achieve the best performance.

Statistics interpretation:

1. Oracle performs better than esProc in general, especially with 1 concurrency when the performance of Oracle can be several times better.

2. Oracle is extremely unstable in the performances of concurrent tasks, usually several times faster or slower between one and another. esProc’s performance, however, is generally stable.

3. All date grouping operations are two-level grouping, which, as we can see, Oracle performs better.

6.4. Big-data grouping

This use case tests Oracle and esProc for grouping computation performance against large tables (files). The test is done in a non-concurrent mode using parallel threads of 1, 2 and 4.

Testing statistics:

Statistics interpretation:

1. Oracle is at peak performance with 1 parallel, and performance starts to decrease with 2. However, esProc’s performance with 4 parallel tasks is normally higher than with 1 task, even several times higher in some cases.

2. esProc’s performance is several times higher than Oracle in this use case test.

6.5. Small concurrent joins

This use case tests Oracle and esProc for performance of joining small (files). The test is done in a multi-task concurrent access mode. Each task accesses different tables (files). Parallel processing won’t be used for Oracle because no performance improvement can be achieved with it. esProc uses 4-thread parallel processing because more threads won’t increase the performance.

Testing statistics:

Statistics interpretation:

1. esProc is one time or several times faster than Oracle.

2. esProc’s performance variation among concurrent tasks is very small. Oracle’s performance variation remains big, but less than the variations in other use cases.

3. Parallel mode yields no performance gain for Oracle, while esProc reaches peak performance with 4 parallel tasks.

6.6 Big-data joins

This use case tests Oracle and esProc for their performance in joining large tables (files) . The test is done in a single-task, non-concurrent mode. Oracle reaches peak performance with 1 parallel task, while esProc reaches its own with 4 parallel tasks.

Testing statistics:

Statistics interpretation:

1. esProc performs better than Oracle.

2. With multi-level join, performance degradation for both is little.

6.7. Big-data grouping with big result set

This use case tests Oracle and esProc for grouping computation performance against large tables (files), with the grouping results are too big to be all stored in memory. The test is done in a single-task non-concurrent mode using 1, 2 and 4 parallel threads respectively.

Testing statistics:

Statistics interpretation:

1. esProc performs better than Oracle.

2. Oracle is at peak performance with 2 parallel tasks. esProc is at peak performance with 4 parallel tasks.

7. Summary Analysis

From all use case tests we can generally reach the following conclusions about the testing statistics:

1. Oracle normally performs better than esProc with small concurrent tasks, even several times higher in some cases. But, there’s the exception of joins where esProc’s performance surpasses Oracle’s.

2. For big-data, single-task computation, esProc’s performance is always higher than Oracle’s, several times higher sometimes.

3. Performance degrades significantly both for Oracle and esProc when concurrent number changes from 1 to 2. Oracle still has advantage but the difference between the two is small enough.

4. For small concurrent tasks, esProc shows stable performance with each task completed using approximately the same time, while Oracle demonstrates great variation among different tasks (normally the variation is up to ten times). However, there are exceptions. Oracle has a very small performance variation among different tasks during join operations, and parallel processing won’t improve the performance in such situations.

Causes of the above conclusions could be as follows:

1. esProc is JAVA-coded and interpreted for execution, and thus has a lower efficiency than Oracle,  which is coded in native C language. The latter has a sophisticated multi-tier cache mechanism which enables a much higher performance in in-memory computations. As small concurrent computations can normally be cached, Oracle generally performs better than esProc in such use cases.

2. For big-data, single-task computation, esProc’s performance is always higher than Oracle. This is because a large volume of data cannot be cached, and Oracle’s main method for performance improvement is inapplicable. But in such a situation, esProc has an obvious advantage with its more efficient function-based algorithm.

3. With the concurrent computation with small data volume, overall performance of both degrades significantly with the parallel number changed from 1 to 2, and then to 4. This is a signal of incompetence of the processing power of CPU. Under this circumstance, Oracle still has the advantage, but only slightly. This is because Oracle doesn’t have the time to read the cache, and the cache mechanism almost stops working though the only advantage of native code remains. From this we can see that, basically, the performance-enhancing method for Oracle is effectively leveraging cache.

4. Since Oracle’s cache mechanism and execution plan cannot be managed manually, thus with small concurrent tasks Oracle has a significant performance variation, which sometimes is up to ten times.

5. Small-data joins are an exception. In those cases, esProc’s performance is one or several times higher than Oracle’s and the Oracle performance variation among concurrent tasks is small with parallel processing being invalid. Probably this is caused by the same reason that Oracle’s cache mechanism and execution plan cannot be managed manually, making a join automatically treated as an external-memory computation. The external-memory computation involves competitions for hard disk resources, which leads to the lack of performance improvement with parallel processing. Parallel processing is effective in enhancing esProc’s performance because esProc can load the small dimension tables into the memory, and thus leverage the multi-core CPU for computation. The small variation among concurrent tasks is possibly also because that Oracle automatically treats a join as external-memory computation, which does not require cache, and that resource is equally distributed among concurrent processes. With join computations, Oracle’s advantage of using cache disappears, but the performance enhancement with native code is limited, whereas esProc gains an advantage with efficient function-based algorithm and thus achieves a higher performance.

Final conclusion: Oracle performs better in handling computational tasks with small data or with fewer computations and simple algorithms. esProc performs better in handling big-data tasks, computation-intensive tasks, or those with complicated logics.

Pleast Click here to download the entire document.