In the past 10 years, VTB has experienced a powerful increase in computational load. Every year it increased by one and a half times, and the volume of registration data - in two. The support services tried very hard, but it was not easy to keep up with these rates: the query plans drove away, the disk space was running out, the updates to the application code ate up all the resources. In this post, we’ll tell you how you solved the problem without spending a lot on another IBM System p.
In 2013, card processing, then still VTB24, was located on one of the most powerful servers of the time - IBM System p. This complemented the replicas for different reporting. On the additional equipment, there were replicas for reporting: a daily updated base for daily reporting, funds for an active Oracle Active Data Guard replica for operational reporting, and a database for reporting by the Central Bank, which we updated monthly.
We are actively customizing the functionality of the systems - most of the application code was occupied by internal improvements. At the same time, the data grew very rapidly. As a result, the query plan for the four bases regularly deteriorated. Front systems worked slowly. From a technical perspective, there was another difficulty: the OLTP-load of card transactions was mixed with the DWH / DSS-load of customized functionality and reporting.
The standard way out of this situation: dock resources and go to a steeper storage subsystem. We came up with a more interesting option - we took for the replicas of the reporting two optimized for the database hardware and software complex Oracle Exadata.
The processing complex was divided into “warm” and “hot” zones. The hot zone has not moved anywhere with IBM System p, and only its database has been preserved. The “warm” zone was a copy of the main database on Exadata. Here was all the reporting and customized functionality. Replicated data using Oracle GoldenGate.
We conducted replica tests on Exadata: on average, reporting began to form five times faster due to the architecture and features of the Oracle Exadata Storage software — smartscans, storage indexes, bloom filters, etc. The reporting time for the Central Bank has decreased tenfold and now some reports are being prepared in less than 1 hour. The main thing that had to be done to optimize queries when migrating to Exadata was to remove the hints that previously helped work on the old platform.
We carried out a feasibility study comparing options for different parameters with the usual expansion of current systems and with the involvement of two Exadata complexes.
- Performance. 40 thousand IOPS against 400 thousand IOPS in Exadata. Oracle's solution is sharpened on large amounts of data, a full table scan runs much faster.
- Customization options. In the standard solution, we cannot change the structure of objects without changes in the productive database, it is prohibited by the vendor. In Exadata, we can remove unnecessary indexes, add the necessary and improve the response of the system.
- Scaling. Exadata provides a linear increase in performance at a relatively lower cost.
- Reporting. The speed of reporting with the Exadata complex is increased by 5 times, and with the scaling of existing systems - by 1.5.
- Service. In the Oracle infrastructure, unified technical support, unified system of updates for servers, disk subsystems and network infrastructure. With the usual scaling you need to work with different vendors - and there are more downtimes and any other inconveniences.
- Cost Exadata wins here.
At first, GoldenGate replication was a weak point: in the case of long transactions at the source, it lagged behind. We solved this by updating and reworking some application processes. After that, working with Exadata gave us only advantages.
We introduced custom indexes and partitioning, which allowed us to increase the performance of custom functions. IBM does not allow this optimization.
The transfer of analytical reporting to the “warm” zone allowed us to reduce the storage depth of the historical data of the “hot” zone. This reduced the cost of expensive storage. It was possible to speed up the insertion into indexes. Deletion of data through the housekeeping module was filtered at the GoldenGate level, as a result, the replica contained fresh data and the whole story;
Exadata uses hybrid column compression (HCC), and this significantly saves disk space. Data older than one year are compressed using the “archive low” method, older than one month using the “advanced compression” method, newer data is not compressed to increase the speed.
As for the upgrade, it is most efficient to replace the entire storage cell in the Exadata with cells with more capacious disks and productive processors. But it can be used within the same system, storage cells of different versions - Oracle allows it.
Card processing reporting implemented on Oracle Exadata and Database technologies still works fine, and the new bank systems are built on the same principle.