2.1

Software methods

In order to speed up data retrieval in tables, RDBMS uses various types of indexes [1]. The author’s work [8] discusses the basic indexes and the principles of their use. Partitioning involves splitting the table into partitions according to system requirements, e.g. partitioning of the microwave patterns database into partitions according to the adopted frequency range. Partitioning allows to increase the performance of queries that reference one partition instead of searching the entire table [1].

Tuning in the RDBMS system is done using the native Procedural Structured Query Language (PL / SQL). In the DBMS_PROFILER package RDBMS stores procedures, which offer a number of actions to analyze and suggest improvement of the application code. With this package, one can easily identify the most expensive operations in the code and try to optimize their operation.

2.2

Parallel processing

Each query in the database is analysed by the optimizer (parsing). If the execution plan includes parallel processing, the following steps occur [5]:

After arranging the query plan, the parallel execution coordinator - (PX coordinator) decides how the subtasks will be executed. The number of processes that can be started for one task is defined by Degree Of Parallelism (DOP). The DOP level can be defined by the user or automatically by the RDBMS system.

4.1

Test No. 1 - CentOS system

The test results are presented in Fig. 1. The data series in the graph determine the application of the server configuration with a given number of processor cores in the range from 1 to 8.

Figure 1.

Query times for different number of processors for full table scan operations (CentOS system)

Conclusions resulting from the conducted research:

It is important to notice that during hash joining of tables with grouping and sorting commands, twice as many subprocesses - PX Slaves have run than in the case of full scan. That results in much longer response times, as shown in Figure 2.

Figure 2.

Query times for different number of processors for hash joining operations (CentOS system)

Conclusions resulting from the conducted research:

Figure 3 shows the CPU usage (s) during the test. The start of testing is marked with a vertical black line. The first range of processor activity (to the right of the vertical line) covers the testing time using a single-core processor. One core was added successively. It can be seen that only for the configuration with a single-core processor, the resources have been used in 100%, which basically means that at that time any other work on the server would have been practically impossible. In other cases, the processor’s idle time has been greater than 0, so it can be assumed that the server would have been able to handle more parallel sessions.

Figure 3.

Characteristics of the processor’s work - percentage consumption during testing as part of Test No. 1 in the CentOS system

The red horizontal line in Figure 4 indicates the number of processor cores available, and above it the processes running in the queue have been marked for each configuration, which in practice means that each processor core must perform several dozen operations in series.

Figure 4.

The number of processes in the processor’s input queue during testing as part of Test No. 1 in the CentOS system

Analysing Figure 5, it can be concluded that increasing the number of processor cores does not affect the operation of disks, which must be used equally to return the desired result.

Figure 5.

Disk operation characteristics - data transfer coefficient in blocks per second in the CentOS system

4.2

Test No. 1 - Windows system

The test results are shown in Figure 6.

Figure 6.

Query times for full table scan operations (8 processor cores) and table hash joining (Windows)

Figure 6 shows query times for both the full table scan operation - the PST series and the hash join - the ZH series. Conclusions resulting from the conducted research:

4.3

Test No. 2 - CentOS system

The results of tests for test No. 2 are presented below. During the tests, the processor configuration was changed in the range of 1 to 8 cores. Query times for different sessions in most cases have been identical or have not differed significantly, so that only single times have been selected for each of the series of tests in the DOP range from 1 to 8.

With a view to run several sessions simultaneously, each with a level of parallelism above 1, it was reasonable to expect that the server would start to work much worse. However, as it can be seen in the chart in Figure 7, raising the level of parallelism in the range of 1 to 8 has brought benefits. The curves begin to overlap only from a DOP level of 5, with the number of simultaneous sessions reaching ten. This can be explained by the fact that slow resources of a fast processor can fully cope with the processing of more simultaneous processes. Only the launch of 64 concurrent sessions brought a significant decrease in performance for all DOP levels.

Figure 7.

Query times for a full table scan operation launched by multiple sessions simultaneously (CentOS system)

Only in Figure 8 one can observe the problems which appeared while the server was running. Up to the right of the black vertical line, the characteristics of CPU consumption are outlined when performing multisession tests.

Figure 8.

Processor operation characteristics - percentage of consumption (CentOS system)

The 64-parallel session test describes it on the right side of the figure. They show how the server’s idle time has decreased. For 64 simultaneous sessions, work on the server was possible, but the slowdown was strongly felt. The waiting time for any operations in the system shell has increased several times. The attempt to perform the test for 128 simultaneous sessions ended with the server to be blocked and restarted.

Figure 9 shows that only the work of 64 parallel sessions that the HDD was heavily charged. Tests carried out in the range of 1 to 10 sessions showed that disk consumption was about 4,000 operations per second.

Figure 9.

Characteristics of SSD drive - number of reads / writes (CentOS system)

4.4

Test No. 3 - CentOS system

The test has been run once in the most efficient configuration with eight processor cores. The purpose of the test was to check whether the search for a given row of the TEST_TAB1 table could be done faster by using parallel processing or by creating an index on the STR_PAD column and searching with its help.

According to Figure 10, the use of an optimal processor configuration for server cannot compete using an efficient index. The best time when using a high DOP level of 32 is 42.7 seconds, which is sometimes almost 27 times slower than searching by index. It should be noted that searching by index does not use parallel processing, which can be seen in Figure 11. Figure 11 presents a plan of the performed query along with the times of individual operations.

Figure 10.

Query times for full table scan operations relative to index time using an index (Windows)

Figure 11.

Result of trace analysis of operations performed without using the index (CentOS system)

Forcing the lack of use of the index (NO_INDEX) caused the optimizer to return to the query plan using parallel processing. As can be seen in Figure 12, there are waiting times for parallel processing operations: PX Deq: Execute Reply, PX Deq: Execution Msg. In addition, using parallel processing, the reading of data from the disk takes place after direct path read, which further introduces a delay in the use of the index.

Figure 12.

Result of trace analysis of operations performed with using the index (CentOS system)

4.5

Test 4–Windows system

Both the TEST_TAB1 full table scan operation and the hash join of two large TEST_TAB1 and TEST_TAB2 tables were tested for all selected levels of DOP parallelization. The change from test 1 was the use of one of the variants of a fast SSD. The test results are shown in Figure 13.

Figure 13.

Query times for full table scan operations and hash tables when changing the location of database files from a slower disk to a faster one (Windows system)

Using a faster SSD drive caused that the worst query time at full table scan was better than the lowest time on a powerful Linux server. It is also worth noting that in the case of such large data access times (reading at a level of over 508 MB / s), the role of the processor is limited to a minimum, and thus the level of parallelism is not of practical importance here, which can be seen in the graph above - for full table scanning, the transition from the “weakest” time 21.3 s to the “best” 20.8 s is only 0.5 seconds.

Comparing Figures 14 and 15, we can see that the use of SSD significantly increased the reading of data from the disk in a unit of time compared to the HDD, which RDBMS was able to use and translate into a short time of query execution.

Figure 14.

Characteristics of slower HDD - readings per second (Windows system)

Figure 15.

Characteristics of faster disk SSD - readings per second (Windows system)