Horizontal scaling of database servers for OLTP systems, or what is on the market
As a rule, in large and medium-sized companies there are high-loaded transactional information systems, which are the most important component of the business, they are called OLTP-systems. With the growth of business, the load increases very quickly, so the task of increasing the performance of existing resources for database servers is very acute. Often, to solve the problem of increasing the performance of database servers, more powerful equipment is acquired (the so-called “vertical” scaling), but this method has a very significant disadvantage: the company will sooner or later buy a maximum performance database server at an affordable price, and what to do next? Further business prospects may not be so bright - in many cases we are talking about the deterioration of the company's reputation, the inability to serve customers in times of high demand, a significant loss of profits.
To exclude such situations and ensure the health of the OLTP systems, many companies follow the path of "horizontal" scaling of database servers. In contrast to increasing the performance of the main server ("vertical" scaling), when "horizontal" scaling servers are combined into a cluster (set), and the load on the database servers is distributed between them. This approach is more technological, since in addition to the obvious advantages in the form of the possibility of increasing productivity by adding new servers, the problem of achieving failover and disaster tolerance is solved.
Many IT companies in Russia and the world are developing such solutions, below I will try to tell about them in more detail.
The first solution - Oracle RAC (Real Application Cluster - appeared back in 2001 in version 9i to increase availability and performance in high-load systems based on Oracle DBMS. It allows you to distribute the load on a high-loaded database between database servers and thereby increase the capabilities of the OLTP system for problem-free growth of information flow. For more information you can refer to the documentation or the Oracle Press books publishing. Therefore, I focus on a few points of interest from the point of view of the principle of work.
')
Since Since Oracle RAC implements the Shared-everything architecture (with all its inherent advantages and disadvantages), then for each server, Oracle RAC has its own cache, which receives SQL query data executed on it. There is also a global cluster cache, implemented using Cache Fusion technology, which synchronizes with local server caches for data. A special role in cluster resource coordination and cache integration is played by the Global Resource Directory data structure, which records on which server, which data and which objects are relevant; what mode of locks for object on a copy. All this information helps to decide which server in terms of performance is better to send an SQL query, because if the wrong decision is made, the SQL query time will increase due to the time it takes to synchronize data between the caches.
An important feature of this approach to load sharing between database servers is the need to take into account the “diversity” of SQL traffic from an OLTP system. In cases where SQL queries retrieve data from many tables at the same time, and the rate of change in these tables is large, you may lose time to synchronize cache data between different servers in the cluster (for this reason, you need fast and reliable interconnect between servers). This, in turn, can lead to a deterioration in the response of the OLTP system, and the benefits of using Oracle RAC can be completely leveled.
Pros:
Minuses:
The second solution, Citrix NetScaler , implements the horizontal scaling of database servers for OLTP systems based on MS SQL Server and MySQL differently from Oracle RAC. Technical features can be found by following the link .
If in Oracle RAC, database servers are synchronized automatically, then Citrix NetScaler should use third-party technologies for synchronization: AlwaysOn from Microsoft, MySQL replication. The Citrix NetScaler solution itself is a proxy server between the application layer (application server, web server) and database servers, so all SQL queries to the database server go through it.
According to the specification, the solution can recognize the SQL query signature (for reading or writing data) and redirect them to the necessary (defined by the settings) servers in the cluster. The delay in processing the SQL query by the proxy server is minimal, so the response of the OLTP system should not deteriorate after implementation. Despite this advantage, the possibilities for balancing the load from SQL queries also depend on the peculiarities of the traffic of the OLTP system. In many OLTP systems, the modified data in the transaction is immediately read by the next SQL query for further work. Considering the features of such a technology, such as MS AlwaysOn, data on additional servers lags behind the main one for some time (in synchronous and asynchronous mode). Without taking into account this fact, the application and the user can get a situation in which the added data will be missing in the selection of the next SQL query. As a rule, Citrix NetScaler technology is recommended to be used not in automatic mode, but in manual mode; therefore, its scope is limited to simple database queries in web applications.
The third technology, Softpoint Data Cluster, is a Russian development that is similar to the two previous ones, while at some points it is more applicable to practical tasks on “horizontal” scaling of database servers for OLTP systems. More information about the product can be found on the vendor's website.
At first glance, the technology is similar to Citrix NetScaler, since it is a proxy server between the application level and the database level, and is tightly integrated with database synchronization technologies (for example, MS AlwaysOn), but unlike Citrix NetScaler it tracks database out-of-sync cluster and fully guarantees the consistency of the data in the samples, no matter where the SQL query is executed on the servers. This feature allows automatic load balancing without adaptation to application traffic.
The technology also provides synchronization of temporary tables between servers in a cluster, which is very important for better balancing, including SQL queries using temporary tables. An important advantage of using Softpoint Data Cluster is the opportunity to familiarize yourself with examples of implementations for high-loaded OLTP systems .
It is quite difficult to compare the three technologies, given the differences in approaches and implementation, but another thing is important: the horizontal scaling technologies of database servers are constantly evolving and find practical use in the tasks of ensuring the required level of performance for high-loaded OLTP systems.
This article does not pretend to a comprehensive description of the details of implementation and use, its purpose is to review the basic technologies of horizontal scaling of database servers.
Authors: Sergey Zimin, Sergey Samoilov, Alexander Turkovsky, Pavel Barketov.
To exclude such situations and ensure the health of the OLTP systems, many companies follow the path of "horizontal" scaling of database servers. In contrast to increasing the performance of the main server ("vertical" scaling), when "horizontal" scaling servers are combined into a cluster (set), and the load on the database servers is distributed between them. This approach is more technological, since in addition to the obvious advantages in the form of the possibility of increasing productivity by adding new servers, the problem of achieving failover and disaster tolerance is solved.
Many IT companies in Russia and the world are developing such solutions, below I will try to tell about them in more detail.
The first solution - Oracle RAC (Real Application Cluster - appeared back in 2001 in version 9i to increase availability and performance in high-load systems based on Oracle DBMS. It allows you to distribute the load on a high-loaded database between database servers and thereby increase the capabilities of the OLTP system for problem-free growth of information flow. For more information you can refer to the documentation or the Oracle Press books publishing. Therefore, I focus on a few points of interest from the point of view of the principle of work.
')
Since Since Oracle RAC implements the Shared-everything architecture (with all its inherent advantages and disadvantages), then for each server, Oracle RAC has its own cache, which receives SQL query data executed on it. There is also a global cluster cache, implemented using Cache Fusion technology, which synchronizes with local server caches for data. A special role in cluster resource coordination and cache integration is played by the Global Resource Directory data structure, which records on which server, which data and which objects are relevant; what mode of locks for object on a copy. All this information helps to decide which server in terms of performance is better to send an SQL query, because if the wrong decision is made, the SQL query time will increase due to the time it takes to synchronize data between the caches.
An important feature of this approach to load sharing between database servers is the need to take into account the “diversity” of SQL traffic from an OLTP system. In cases where SQL queries retrieve data from many tables at the same time, and the rate of change in these tables is large, you may lose time to synchronize cache data between different servers in the cluster (for this reason, you need fast and reliable interconnect between servers). This, in turn, can lead to a deterioration in the response of the OLTP system, and the benefits of using Oracle RAC can be completely leveled.
Pros:
- Active / Active cluster
- Load balancing
- Scaling with increased performance, but also increased availability
- Almost linear increase in performance when adding new nodes to the cluster
- Transparent application scaling
Minuses:
- Works only with Oracle DBMS
- High-performance low-latency interconnect is desirable for operation.
- Storage can be a single point of failure. To ensure a high level of fault tolerance, RAC must be combined with standby or mirroring storage.
The second solution, Citrix NetScaler , implements the horizontal scaling of database servers for OLTP systems based on MS SQL Server and MySQL differently from Oracle RAC. Technical features can be found by following the link .
If in Oracle RAC, database servers are synchronized automatically, then Citrix NetScaler should use third-party technologies for synchronization: AlwaysOn from Microsoft, MySQL replication. The Citrix NetScaler solution itself is a proxy server between the application layer (application server, web server) and database servers, so all SQL queries to the database server go through it.
According to the specification, the solution can recognize the SQL query signature (for reading or writing data) and redirect them to the necessary (defined by the settings) servers in the cluster. The delay in processing the SQL query by the proxy server is minimal, so the response of the OLTP system should not deteriorate after implementation. Despite this advantage, the possibilities for balancing the load from SQL queries also depend on the peculiarities of the traffic of the OLTP system. In many OLTP systems, the modified data in the transaction is immediately read by the next SQL query for further work. Considering the features of such a technology, such as MS AlwaysOn, data on additional servers lags behind the main one for some time (in synchronous and asynchronous mode). Without taking into account this fact, the application and the user can get a situation in which the added data will be missing in the selection of the next SQL query. As a rule, Citrix NetScaler technology is recommended to be used not in automatic mode, but in manual mode; therefore, its scope is limited to simple database queries in web applications.
The third technology, Softpoint Data Cluster, is a Russian development that is similar to the two previous ones, while at some points it is more applicable to practical tasks on “horizontal” scaling of database servers for OLTP systems. More information about the product can be found on the vendor's website.
At first glance, the technology is similar to Citrix NetScaler, since it is a proxy server between the application level and the database level, and is tightly integrated with database synchronization technologies (for example, MS AlwaysOn), but unlike Citrix NetScaler it tracks database out-of-sync cluster and fully guarantees the consistency of the data in the samples, no matter where the SQL query is executed on the servers. This feature allows automatic load balancing without adaptation to application traffic.
The technology also provides synchronization of temporary tables between servers in a cluster, which is very important for better balancing, including SQL queries using temporary tables. An important advantage of using Softpoint Data Cluster is the opportunity to familiarize yourself with examples of implementations for high-loaded OLTP systems .
It is quite difficult to compare the three technologies, given the differences in approaches and implementation, but another thing is important: the horizontal scaling technologies of database servers are constantly evolving and find practical use in the tasks of ensuring the required level of performance for high-loaded OLTP systems.
This article does not pretend to a comprehensive description of the details of implementation and use, its purpose is to review the basic technologies of horizontal scaling of database servers.
Authors: Sergey Zimin, Sergey Samoilov, Alexander Turkovsky, Pavel Barketov.
Source: https://habr.com/ru/post/262073/
All Articles