EEUM researchers aim to improve database performance

In our daily lives, we barely notice the structures that support sectors like commerce, banking, or others, as well as high transactional data loads. In general, we only become aware of them during disruptions, which end up influencing the normal functioning of said services.

In order to improve the performance of transactional databases, Nuno Faria, a researcher at High-Assurance Laboratory (HASLab), developed a flexible and transparent solution for users: the Multi-Record Values (MRVs), which aim to significantly improve the performance of the systems. Transactional databases are often disrupted by the challenges of upgrade hotspots, situations that lead to conflicts and larger waiting times, time loss, and performance degradation.

The solution, described in the article MRVs: Enforcing Numeric Invariants in Parallel Updates to Hotspots with Randomizes Splitting, and co-authored by José Pereira, a researcher and lecturer at the School of Engineering, was presented at the 2023 ACM SIGMOD/PODS, an A* level conference held in June in Seattle, USA, which is considered one of the most important in the field of data management. The event brought together more than a thousand participants from academia and industry – with sessions dedicated to both.

The solution proposed by Nuno Faria focuses on dividing the data into several records — rows of a database —using randomisation, to reduce the probability of conflicts to arbitrarily low levels. In this sense, the only coordination needed is the underlying transactional system, responsible for isolation guarantees – through which two or more simultaneous transactions occur without any conflict. In these cases, isolation could lead, for instance, to the cancelation of one of the transactions to ensure the system’s consistency.

So far, the solution has been tested on five different systems, ranging from DBx1000 to MySQL GR, but also on a cloud native NewSQL system, with a positive performance and reduced conflict rates (when compared to available alternatives).

Nuno Faria, researcher at High-Assurance Laboratory (HASLab), explained that high-throughput transactional systems rely on parallelism on processor cores and distributed nodes to perform multiple simultaneous operations. However, transactional isolation introduces the need to avoid invalid results, which translates into a reduction in the transactional execution rate. This issue becomes clearer in sectors with high transactional data loads, like commerce or banking, due to the large number of concurrent accesses, e.g., when two transactions try to modify the stock of a product at the same time.

The MRVs technique addresses these points by allowing competing transactions to have access to split shares of a disputed value (a value with many competing accesses). Randomisation manages each client operation, starting at a random position and scrolling through an index for one or more records as needed. The transactional isolation tool provides the necessary coordination between concurrent customers — for example, when two individuals purchase the same product, from the same stock, and at the same time — and maintenance tasks, avoiding the need for additional explicit distributed interaction.

The results of the tests carried out using the MRVs solution were promising, with the implementation on different transactional database management systems: PostgreSQL, DBx1000, MongoDB and MySQL GR.

Nuno Faria also pointed out that one of the greatest advantages of the MRVs technique is that it can be implemented on existing systems, without requiring changes to the database mechanism. This way, its adoption on closed source clouds is facilitated, which allows combining it with other high-performance synchronisation methods.

“It greatly improves performance, when we compare it to the most common version; it’s also easier to implement/manage and more efficient than the alternatives currently available”, stated Nuno Faria. Another benefit is the automatic adaptation “to the load, towards reducing the overhead of time and space”, he concluded.

The researcher mentioned in this news piece is associated with INESC TEC and UMinho.