Hello, Khabrovites. Classes for the first group of the course start today. . In this regard, we want to tell you about how the open webinar for this course was held.
В talked about the challenges faced by SQL databases in the era of clouds and Kubernetes. And at the same time, we looked at how SQL databases adapt and mutate under the influence of these challenges.
Webinar hosted , Google Cloud Practice Delivery Manager at EPAM Systems.
When the trees were small...
To begin with, let's remember how the choice of a DBMS began at the end of the last century. However, this will not be difficult, because the choice of a DBMS in those days began and ended Oracle.
In the late 90s - early 2s, in fact, there was no particular choice when it comes to industrial scalable databases. Yes, there were IBM DBXNUMX, Sybase, and some other databases that came and went, but by and large they were not as noticeable against the background of Oracle. Accordingly, the skills of the engineers of those times were somehow tied to the only choice that existed.
Oracle DBA had to be able to:
- install Oracle Server from the distribution;
- configure Oracle Server:
- init.ora;
- listener.ora;
- create:
- tablespaces;
- scheme;
- users;
- perform backup and restore;
— monitor;
- deal with suboptimal requests.
At the same time, Oracle DBA was not particularly required to:
- be able to choose the optimal DBMS or other technology for storing and processing data;
- provide high availability and horizontal scalability (this was not always a DBA issue);
- good knowledge of the subject area, infrastructure, application architecture, OS;
- load and unload data, migrate data between different DBMS.
In general, if we talk about the choice in those days, then it resembles the choice in the Soviet store in the late 80s:
Our time
Since then, of course, the trees have grown, the world has changed, and it became something like this:
The DBMS market has also changed, which is clearly seen in the latest report from Gartner:
And here it should be noted that clouds have occupied their niche, the popularity of which is growing. If we read the same report from Gartner, we see the following findings:
- Many customers are on the path to moving applications to the cloud.
- New technologies first appear in the cloud and it is not certain that they will ever move to non-cloud infrastructure at all.
- The pay-as-you-go pricing model has become familiar. Everyone wants to pay only for what they use, and this is not even a trend anymore, but just a statement of fact.
What now?
Today we are all in the cloud. And the questions that we have are questions of choice. And it is huge, even if we talk only about the choice of DBMS technologies in the On-premises format. We also have managed services and SaaS. Thus, the choice every year only becomes more complicated.
Along with questions of choice, there are also limiting factors:
- price. Many technologies still cost money;
- skills. If we are talking about free software, then the question of skills arises, since free software requires sufficient competence from the people who deploy and operate it;
- functional. Not all services that are available in the cloud and are built, say, even on the basis of the same Postgres, have the same features as Postgres On-premises. This is an essential factor to know and understand. Moreover, this factor becomes more important than the knowledge of some hidden features of a single DBMS.
What is expected now from DA / DE:
- good understanding of the subject area and applied architecture;
- the ability to correctly choose the appropriate DBMS technology, taking into account the task;
- the ability to select the optimal method for implementing the chosen technology in the context of existing restrictions;
- ability to perform data transfer and migration;
- ability to implement and operate the chosen solutions.
The following example based on GCP demonstrates how the choice of one or another technology for working with data is arranged depending on their structure:
Please note that there is no PostgreSQL in the schema, but this is because it is hiding under the terminology CloudSQL. And when we get to Cloud SQL, we need to make a choice again:
It should be noted that this choice is not always clear, so application developers are often guided by intuition.
Total:
- The further, the more urgent becomes the question of choice. And even if you look only at GCP, managed services and SaaS, then some mention of the RDBMS appears only at the 4th step (and there Spanner is nearby). Plus, the choice of PostgreSQL appears in general at the 5th step, and next to it are MySQL and SQL Server, that is there are so many, but you have to choose.
- We must not forget about the restrictions against the background of temptations. Basically, everyone wants a Spanner, but it's expensive. As a result, a typical request looks something like this: “Make us, please, Spanner, but for the price of Cloud SQL, well, you are professionals!”
What to do?
Without claiming to be the ultimate truth, let's say the following:
We need to change our approach to learning.
- there is no point in teaching as DBA was taught before;
- knowledge of one product is no longer enough;
- and knowing tens at the level of one is impossible.
You need to know not only and not how much the product, but:
- use case of its application;
- different deployment methods;
- advantages and disadvantages of each method;
- similar and alternative products in order to make an informed and optimal choice and not always in favor of a familiar product.
You also need to be able to migrate data and understand the basic principles of integration with ETL.
Real case
In the recent past, I had to create a backend for a mobile application. By the time we started working on it, the backend was already developed and ready for implementation, and the development team spent about two years on this project. In doing so, the following tasks were set:
- build CI/CD;
- make an architecture review;
- put it all into operation.
The application itself was microservice, and the Python / Django code was developed from scratch and immediately in GCP. As for the target audience, it was assumed that there would be two regions - US and EU, and traffic was distributed through the Global Load balancer. All Workloads and computational load were running on Google Kubernetes Engine.
As for the data, there were 3 structures:
- cloud storage;
- datastore;
- Cloud SQL (PostgreSQL).
You might be wondering why Cloud SQL was chosen? To tell the truth, such a question in recent years causes some kind of awkward pause - there is a feeling that people have become embarrassed by relational databases, but nevertheless they continue to actively use them ;-).
As for our case, Cloud SQL was chosen for the following reasons:
- As mentioned, the application was developed using Django, and it has a model for mapping persistent data from a SQL database to Python objects (Django ORM).
- The framework itself supported a fairly finite list of DBMS:
- PostgreSQL;
- MariaDB;
- MySQL
- oracles;
- SQLite.
Accordingly, PostgreSQL was chosen from this list rather intuitively (well, it’s not Oracle to choose, in fact).
What was missing:
- the application was deployed only in 2 regions, and the 3rd one (Asia) appeared in the plans;
- The database was located in the North American region (Iowa);
- on the part of the customer, there were concerns about possible access delays from Europe and Asia and interruptions in service in case of DBMS downtime.
Despite the fact that Django itself can work with several databases in parallel and divide them by reading and writing, there were not so many entries in the application (more than 90% - reading). And in general, and in general, if it was possible to do read-replica main base in Europe and Asia, that would be a compromise solution. Well, what's so difficult about it?
And the difficulty was that the customer did not want to abandon the use of managed services and Cloud SQL. And the capabilities of Cloud SQL are currently limited. Cloud SQL supports High availability (HA) and Read Replica (RR), but the same RR is only supported in one region. Having created a database in the American region, it is impossible to make a read-replica in the European region using Cloud SQL, although postgres itself does not interfere with this. Correspondence with Google employees did not lead to anything and ended with promises in the style of "we know the problem and are working on it, someday the issue will be resolved."
If you list the possibilities of Cloud SQL thesis, it will look something like this:
1. High availability (HA):
- within one region;
- through disk replication;
- PostgreSQL mechanisms are not used;
- automatic and manual control is possible - failover / failback;
- when switching, the DBMS is unavailable for several minutes.
2. Read Replica (RR):
- within one region;
- hot standby;
- PostgreSQL streaming replication.
In addition, as is customary, when choosing a technology, you always encounter some restrictions:
- the customer did not want to produce entities and use IaaS, except through GKE;
- the customer would not like to deploy self service PostgreSQL/MySQL;
- well, in general, Google Spanner would be quite suitable if it weren’t for its price, however, Django ORM cannot work with it, but it’s a good thing.
Considering the situation, a question was received from the customer for backfilling: “Can you do something similar to be like Google Spanner, but also work with Django ORM?”
Solution #0
The first thing that came to mind:
- stay within CloudSQL;
- built-in replication between regions will not be in any form;
- try to attach a replica to an existing Cloud SQL by PostgreSQL;
- launch a PostgreSQL instance somewhere and somehow, but at least do not touch the master.
Alas, it turned out that this cannot be done, because there is no access to the host (it is generally in a different project) - pg_hba and so on, and there is still no access under superuser.
Solution #1
After another reflection and taking into account the previous circumstances, the train of thought has changed somewhat:
- we are still trying to stay within CloudSQL, but we are switching to MySQL, because Cloud SQL by MySQL has an external master, which:
- is a proxy for external MySQL;
- looks like a MySQL instance;
- designed to migrate data from other clouds or On-premises.
Since setting up MySQL replication does not require access to the host, in principle everything worked, but it was very unstable and inconvenient. And when we went further, it became completely scary, because we deployed the entire structure with terraform, and suddenly it turned out that the external master was not supported by terraform. Yes, Google has a CLI, but for some reason, everything worked here every other time - either it is created, or it is not created. Perhaps because the CLI was designed to migrate data from outside, and not for replicas.
Actually, on this it became clear that Cloud SQL does not fit the word at all. As they say, we did everything we could.
Solution #2
Since it was not possible to stay within Cloud SQL, we tried to formulate requirements for a compromise solution. The requirements were as follows:
- work in Kubernetes, maximum use of resources and capabilities of Kubernetes (DCS, ...) and GCP (LB, ...);
- lack of ballast from a bunch of unnecessary things in the cloud like HA proxy;
- the ability to run HA PostgreSQL or MySQL in the main region; in other regions - HA from the RR of the main region, plus a copy of it (for reliability);
- multi master (I did not want to contact him, but it was not very important)
.
As a result of these demands, there are finally on the horizonsuitable DBMS and binding options:
- MySQL Galera;
- CockroachDB;
- PostgreSQL tools
:
pgpool-II;
— Patroni.
MySQL Galera
MySQL Galera technology was developed by Codership and is a plugin for InnoDB. Peculiarities:
- multimaster;
- synchronous replication;
- reading from any node;
- write to any node;
- built-in HA mechanism;
- there is a Helm chart from Bitnami.
CockroachDB
According to the description, the thing is absolutely bomb and is an open source project written in Go. The main participant is Cockroach Labs (founded by people from Google). This relational DBMS was originally designed to be distributed (with horizontal scaling out of the box) and fault-tolerant. Its authors from the company outlined the goal of "combining the richness of SQL functionality with the horizontal availability familiar to NoSQL solutions."
From a pleasant bonus - support for the postgres connection protocol.
pgpool
This is an add-on for PostgreSQL, in fact, a new entity that takes over all connections and processes them. It has its own load balancer and parser, licensed under the BSD license. Provides ample opportunities, but looks somewhat intimidating, because the presence of a new entity could become a source of some additional adventures.
owners
This is the last thing that fell on the eye, and, as it turned out, not in vain. Patroni is an open source utility that is essentially a Python daemon that allows you to automatically maintain PostgreSQL clusters with different types of replication and automatic role switching. The thing turned out to be very interesting, as it integrates well with the cuber and does not carry any new entities.
What did you end up choosing?
The choice was not easy:
- CockroachDB - fire, but dumb;
- MySQL Galera - also not bad, it is used a lot, but MySQL;
- pgpool - a lot of unnecessary entities, so-so integration with the cloud and K8s;
- owners - excellent integration with K8s, no unnecessary entities, integrates well with GCP LB.
Thus, the choice fell on Patroni.
Conclusions
It's time to sum up. Yes, the world of IT infrastructure has changed a lot, and this is just the beginning. And if earlier clouds were just another type of infrastructure, now everything is different. Moreover, innovations in the clouds appear constantly, they will appear and, perhaps, they will appear only in the clouds and only then, by the efforts of startups, will they be transferred to On-premises.
As far as SQL is concerned, SQL will live. This means that you need to know PostgreSQL and MySQL and be able to work with them, but it is even more important to be able to use them correctly.
Source: habr.com