How does a backend developer know that an SQL query will work well in production? In large or fast-growing companies, access to production is not available to everyone. And even with access, not all queries can be safely checked, and creating a database copy often takes hours. To address these issues, we created an artificial DBA—Joe. He has already been successfully implemented in several companies and helps more than a dozen developers.
Video:
Hello everyone! My name is Anatoly Stansler. I work at . We focus on speeding up the development process by removing delays associated with working with Postgres for developers, DBAs, and QA.
We have great clients, and today part of the presentation will be dedicated to case studies we've encountered while working with them. I will share how we helped them solve some serious issues.
When we are developing and performing complex heavy migrations, we ask ourselves the question: 'Will this migration succeed?'. We rely on reviews and the knowledge of more experienced colleagues, DBA experts. They can tell us whether it will succeed or not.
But perhaps it would be better if we could test it ourselves on full-sized copies. Today, we'll discuss the current approaches to testing, how to do it better, and what tools to use. We'll also talk about the pros and cons of these approaches and what we can improve here.
Has anyone ever created indexes or made changes directly in production? Quite a few. And who has experienced data loss or downtime because of it? Then you're familiar with that pain. Thank goodness for backups.
The first approach is testing in production. Or when a developer sits at their local machine, has test data, and a limited sample. We deploy it to production and find ourselves in this situation.
It's painful, it's expensive. Probably, that's not the best way to go.
So how should we do it?
Let's take staging and designate a portion of production for it. Or, better yet, take real production with all the data. And after developing locally, we should verify it additionally on staging.
This will allow us to eliminate some errors, i.e., to prevent them in production.
What problems exist?
- The problem is that we share this staging environment with colleagues. It often happens that you make some change, and bam – no data, all that work is wasted. The staging was multiterabyte. It takes a long time for it to spin back up again. So we decide to work on this tomorrow. That's it, our development is stalled.
- And of course, many colleagues and teams are working there. It's necessary to coordinate manually. And that's inconvenient.
It's worth noting that we only have one attempt, one shot, if we want to make any changes to the database, touch the data, change the structure. And if something goes wrong, if there’s an error in the migration, we won't roll back quickly.
This is better than the previous approach, but there’s still a high probability that some error might make it to production.
What prevents us from giving each developer a test environment with a full-sized copy? I think it's clear what the obstacles are.
Who has a database larger than a terabyte? More than half the room.
And it’s obvious that maintaining machines for each developer when there’s such large production is very expensive, and it also takes a long time.
We have clients who understand that it's very important to test all changes on full-sized copies, but their databases are under a terabyte, and they don't have the resources to provide a test environment for each developer. Thus, they have to download dumps locally onto their machines and test that way. It takes a lot of time.
Even if you do this within the infrastructure, downloading one terabyte of data in an hour is already very good. But they use logical dumps; they download locally from the cloud. For them, the speed is around 200 gigabytes per hour. And it also takes time to deploy from the logical dump, apply indexes, etc.
But they use this approach because it helps keep production reliable.
What can we do here? Let’s make test environments inexpensive and provide each developer with their own test environment.
And that's possible.
In this approach, when we create thin clones for each developer, we can share this on one machine. For example, if you have a four terabyte database and you want to give it to 10 developers, you don't need to have 10 times four terabyte databases. One machine is enough to create thin isolated copies for each developer using just one machine. I will explain how this works a bit later.
A real example:
DB – 4.5 terabytes.
We can get independent copies in 30 seconds.
You don't have to wait for a test environment and depend on its size. You can get it in seconds. These will be fully isolated environments, but they will share data with each other.
It's cool. Here we're talking about magic and a parallel universe.
In our case, it works with the OpenZFS system.
OpenZFS is a copy-on-write file system that natively supports snapshots and clones. It's reliable and scalable. It's very easy to manage. You can set it up literally with two commands.
There are other options:
LVM,
Storage Arrays (for example, Pure Storage).
The Database Lab that I’m discussing is modular. It can be implemented using such options. But for now, we focused on OpenZFS because there were specific issues with LVM.
How does it work? Instead of rewriting data every time we change it, we save it by simply marking that these new data correspond to a new point in time, a new snapshot.
Then, when we want to roll back or create a new clone from an older version, we simply say: 'Okay, give us those blocks of data that are marked like this.'
And this user will work with this set of data. They will gradually modify it, creating their own snapshots.
And we will have branching. Each developer in our case will have the opportunity to have their own clone that they edit, while the shared data will be shared among all.
To deploy such a system, two problems need to be solved:
The first is the data source from which you will derive them. You can set up replication with production. You might be able to use already configured backups, I hope. WAL-E, WAL-G, or Barman. And even if you are using some Cloud solution like RDS or Cloud SQL, you can utilize logical dumps. However, we still advise you to use backups because this approach will also preserve the physical structure of the files, allowing you to get as close as possible to the metrics you would see in production to catch any existing issues.
The second is where you want to host the Database Lab. This can be in the Cloud or on-premise. It's important to mention that ZFS supports data compression. And it does this quite effectively.
Imagine that each of these clones will accumulate some dev space based on the operations we perform with the database. This dev space will also need to be allocated. However, since we took a 4.5 terabyte database, ZFS will compress it down to 3.5 terabytes. Depending on the settings, this can vary. We will still have space left for dev.
Such a system can be used for various cases.
These are developers and DBAs for query testing and optimization.
This can be used in QA testing to verify a specific migration before we roll it out in production. We can also create special environments for QA with real data, where they can test new functionality. This will take seconds instead of waiting for hours or perhaps even days in other scenarios where thin clones are not used.
And another separate case. If the company does not have an analytics system set up, we can allocate a thin clone of the production database and dedicate it to long queries or special indexes that can be used in analytics.
With this approach:
There is a low probability of errors in production, as we have tested all changes on full-sized data.
A testing culture develops because there’s no need to wait for hours for your own stand.
And there are no barriers, no waiting time between tests. You can really go and check. It will be better as we speed up development.
There will be less refactoring. Fewer bugs will enter production. We will refactor them less later.
We can make irreversible changes. This is not present in standard approaches.
- It's beneficial because we share the resources of testing environments.
That's already good, but what else could we speed up?
Thanks to this system, we can significantly lower the entry barrier to this type of testing.
Currently, there is a vicious circle where a developer must become an expert to gain access to real full-sized data. They must be trusted with such access.
But how to grow if it's not available? What if you only have a very small set of test data? Then, gaining real experience is not possible.
How to break out of this loop? As a first interface convenient for developers of any level, we chose a Slack bot. But it could be any other interface.
What does it allow us to do? You can take a specific request and send it to a special channel for the database. We will automatically deploy a thin clone in seconds. We'll run this request, collect metrics and recommendations, and show visualizations. This clone will remain for the optimization of that request, to add indexes, etc.
And Slack also provides us with collaboration opportunities out of the box. Since it's just a channel, you can start discussing that request right there in a thread, pinging your colleagues and DBAs who are inside the company.
But there are, of course, problems. Since this is the real world and we use a server where we host many clones simultaneously, we have to limit the amount of memory and processing power available to the clones.
But to make these tests plausible, we need to find a way to address this issue.
It's clear that having identical data is crucial. But we already have that. We want to achieve the same configuration, and we can provide an almost identical configuration.
It would be great to have the same hardware as in production, but it may differ.
Let's remember how Postgres works with memory. We have two caches: one from the file system and the other, Postgres's own, i.e., the Shared Buffer Cache.
It is important to note that the Shared Buffer Cache is allocated at the start of Postgres based on the size you set in the configuration.
And the second cache uses all the available space.
When we create several clones on one machine, we gradually fill up the memory. Ideally, the Shared Buffer Cache should be 25% of the total memory available on the machine.
If we do not change this parameter, we will only be able to run 4 instances on one machine, which means only 4 of these thin clones. This is certainly not ideal because we want to have many more.
On the other hand, the Buffer Cache is used for executing queries and for indexes, which means the plan depends on the size of our caches. If we just decrease this parameter, our plans may change significantly.
For example, if we have a large cache in production, Postgres will prefer to use an index. If not, then it will use SeqScan. What would be the point if our plans did not match?
However, we come to the conclusion that the plan in Postgres does not depend on the specific size set in the Shared Buffer; it depends on the effective_cache_size.
Effective_cache_size is the estimated cache size available to us, which is the sum of the Buffer Cache and the filesystem cache. This is set in the configuration, and this memory is not allocated.
With this parameter, we can essentially trick Postgres into thinking that a lot of data is available to us, even if we do not have that data. Thus, the plans will fully align with production.
But this may affect the timing. We optimize queries based on timing, but it's important to note that timing depends on many factors:
It depends on the current load on production.
It depends on the characteristics of the machine itself.
This is an indirect parameter, but in reality, we can optimize based on the amount of data that this query will read to produce a result.
If we want the timing to be close to what we will see in production, we need to use hardware that is as similar as possible, and possibly even more, so that all clones fit. But this is a compromise, meaning you will receive similar plans, see how much data a specific query reads, and be able to conclude whether this query is good (or migration) or bad, and if it needs further optimization.
Let's break down how the optimization process works with Joe.
Let's take a request from a real system. In this case, the database is 1 terabyte. We want to count the number of recent posts that have more than 10 likes.
We send a message to the channel, a clone is spun up for us. We will see that such a request will take 2.5 minutes. This is the first thing we will notice.
B Joe will show automatic recommendations based on the plan and metrics.
We will see that the request processes too much data to obtain a relatively small number of rows. A specialized index is needed, as we noticed that there are too many filtered rows in the query.
Let's take a closer look at what happened. Indeed, we see that we read almost one and a half gigabytes of data from the file cache or even from the disk. This is not good, as we only retrieved 142 rows.
And, seemingly, we have an index scan that should have executed quickly, but because we filtered too many rows (we had to count them), the request processed slowly.
This happened in the plan because the conditions in the query and in the index partially do not match.
Let's try to make the index more precise and see how the execution of the query changes afterward.
Creating the index took quite a lot of time, but now we check the query and see that the time has changed from 2.5 minutes to just 156 milliseconds, which is quite good. And we are only reading 6 megabytes of data.
And now we are using index-only scan.
Another important point is that we want to present the plan in a more understandable way. We have implemented visualization using Flame Graphs.
This is another request, more content-rich. We build Flame Graphs based on two parameters: the amount of data that a specific node in the plan read and the timing, i.e., the execution time of the node.
Here we can specifically compare nodes with each other. It will be clear which one occupies more or less space, which is usually difficult to do with other visualization methods.
Of course, everyone knows explain.depesz.com. A good feature of this visualization is that we save the text plan and also extract some key parameters into a table so that we can sort them.
And developers who have not delved into this topic yet also use explain.depesz.com because it helps them understand which metrics are important and which are not.
There is a new approach to visualization – explain.dalibo.com. They provide a tree-like visualization, but it is very difficult to compare nodes with each other here. You can understand the structure well, though if there’s a large query, you'll need to scroll back and forth, but it's also an option.
Collaboration
And as I mentioned, Slack gives us the opportunity for collaboration. For example, if we encounter a complex query that's unclear how to optimize, we can clarify this issue with our colleagues in a thread in Slack.
We believe it’s important to test on full-sized data. For this, we created the Update Database Lab tool, which is available in open source. You can also use bot Joe. You can take it right now and implement it yourself. All guides are available there.
It’s also important to note that the solution itself is not revolutionary, as there’s Delphix, but that’s an enterprise solution. It's completely closed and very expensive. We specifically specialize in Postgres. All our products are open source. Join us!
That concludes my talk. Thank you!
Questions
Hello! Thank you for the presentation! It was very interesting to me, especially because I tackled a similar task some time ago. So I have a whole series of questions. I hope I can at least ask some of them.
I’m curious, how do you estimate the space for this environment? The technology implies that under certain circumstances your clones can grow to their maximum size. Roughly speaking, if you have a ten-terabyte database and 10 clones, it’s easy to model a situation where each clone holds 10 unique data points. How do you calculate that space, i.e., the delta you mentioned, where these clones will reside?
That's a good question. It's important to keep an eye on specific clones. If a clone undergoes a significant change and starts to grow, we can either issue a warning to the user about this or stop the clone immediately to avoid a fail situation.
Yes, I have a follow-up question. How do you ensure the lifecycle of these modules? This is an issue for us and a whole separate story. How does it work?
Each clone has a certain TTL. In principle, we have a fixed TTL.
What is it, if you don't mind me asking?
One hour, meaning idle – one hour. If it's not used, we terminate it. But there's nothing surprising about this, as we can spin up a clone in seconds. And if it's needed again, it's ready.
I'm also interested in your choice of technologies because we, for instance, use several methods simultaneously for various reasons. Why specifically ZFS? Why didn’t you use LVM? You mentioned you had issues with LVM. What were those problems? In my opinion, the option with an external storage system is the most optimal in terms of performance.
What is the main issue with ZFS? The fact that you have to run it on a single host, meaning all instances will operate under one OS. In the case of external storage, you can connect different hardware. The bottleneck is only those blocks that are on the external storage. The question of technology choice is interesting too. Why not LVM?
We can discuss LVM specifically at the meetup. As for external storage, it's simply expensive. We can implement the ZFS system anywhere. You can deploy it on your own machine. You can just download the repository and set it up. ZFS can be installed almost everywhere if we're talking about Linux. So we get a very flexible solution. ZFS out of the box offers a lot. You can load an unlimited amount of data, connect a large number of disks, and it has snapshots. And as I already mentioned, it's easy to administer. It seems very pleasant to use. It’s proven, has been around for many years, and has a large community that is growing. ZFS is a very reliable solution.
Nikolai Samokhvalov: May I add a comment? My name is Nikolai, and I work with Anatoly. I agree that external storage is great. Some of our clients have Pure Storage and so on.
Anatoly correctly noted that we are focused on modularity. In the future, we can implement one interface – create a snapshot, make a clone, destroy the clone. It's all quite straightforward. And the storage system is great, if available.
But ZFS is available to everyone. Enough with Delphix; they have 300 clients. Out of these, 50 are in the Fortune 100, which means they are targeting NASA, etc. It's time to get this technology to everyone. That's why we have an open-source core. We do have part of the interface that is not open-source. This is the platform we will show. But we want it to be accessible to everyone. We want to revolutionize the process so that all testers stop guessing on their laptops. We need to write SELECT and instantly see if it’s slow. No more waiting for the DBA to tell us. That's the main goal. And I think we will reach it. This solution we are developing should be available to all. Hence ZFS, because it will be accessible everywhere. Thanks to the community for solving these problems and for the open-source license, etc.
Greetings! Thank you for the presentation! My name is Maxim. We faced similar issues. We dealt with them ourselves. How do you allocate resources between these clones? Each clone can be engaged in its own tasks at any given time: one is testing one thing, another is testing something else, one is building an index, another is running a heavy job. If CPU allocation can still be managed, how do you handle IO? That’s my first question.
And my second question is about the differences in setups. Suppose I have ZFS here and everything is great, but the client in production has ext4 instead of ZFS, for example. How do you handle that?
Those are very good questions. I briefly mentioned the issue of resource allocation. The solution is as follows. Imagine you are testing in staging. You might also encounter a situation where one person is adding one load while another is adding another. As a result, you see unclear metrics. The same problem can arise in production. When you want to check a query and notice that there’s an issue with it – it’s running slowly – the actual problem isn’t with the query, but with some parallel load that’s occurring.
And that's why it's important to focus on what the plan will be, the steps we'll take in the plan, and how much data we will gather for this. The fact that our disks, for example, will be loaded with something will specifically affect the timing. But we can estimate how heavy this request is based on the amount of data. It's not so important that there will also be some execution alongside it.
I have two questions. This is really cool. Have there been cases where data in production is critically important, such as credit card numbers? Is there something ready available or is this a separate task? And the second question – is there something like this for MySQL?
Regarding the data. We will be doing obfuscation, until we do this. But if you are running Joe specifically, if you do not grant access to developers, then there is no access to the data. Why? Because Joe does not show the data. He only shows metrics, plans, and everything. This was done deliberately, as it is one of our client's requirements. They wanted to have the ability to optimize, but at the same time, not give access to everyone.
Regarding MySQL. This system can be used for anything that stores state on disk. And since we are dealing with Postgres, we are currently prioritizing full automation for Postgres. We want to automate data retrieval from backups. We are correctly configuring Postgres. We know how to ensure that the plans match, etc.
But since the system is extensible, it can also be used for MySQL. And there are such examples. A similar thing exists at Yandex, but they don't publish it anywhere. They use it internally in Yandex.Metrics. And that's precisely about MySQL. But the technologies are the same, ZFS.
Thank you for the presentation! I also have a couple of questions. You mentioned that cloning can be used for analytics, for example, to build additional indexes there. Can you explain a bit more about how this works?
And I will immediately ask the second question regarding the uniformity of stands, and the uniformity of plans. The plan depends in part on the statistics collected by Postgres. How do you solve this problem?
There is no specific case analysis available because we haven't utilized it in that way yet, but such an option exists. If we talk about indexes, imagine running a query against a table with hundreds of millions of records, specifically on a column that is usually not indexed in production. If we want to compute some data there, running this query on production could cause downtime, as it may take a minute to execute.
Okay, let's create a thin clone that can safely be stopped for a few minutes. To make it easier to analyze the data, we'll add indexes on the columns that interest us.
Will the index be created each time?
We can handle the data, make snapshots, and then recover from that snapshot to run new queries. In other words, we can create new clones with the indexes already in place.
Regarding the question about statistics, if we recover from a backup, or if we perform replication, the statistics will be exactly the same. This is because we will bring over the complete physical data structure, meaning the data with all statistics metrics.
There's another issue here. If you're using a cloud solution, only logical dumps are available because Google and Amazon do not allow physical copies to be taken. That will be a problem.
Thank you for the presentation. There were two good questions here about MySQL and resource partitioning. However, essentially, it all comes down to the fact that this topic is not specific to particular DBMS but rather to the file system as a whole. Accordingly, the questions of resource partitioning should also be addressed from that perspective, not just at the end about Postgres, but at the file system level. server, in the instance.
My question is slightly different. It's more related to the layered architecture of the database, where there are multiple layers. For example, we have set up a ten-terabyte image update, and replication is taking place. We specifically use this solution for databases. Replication is ongoing, and data updates are happening. Meanwhile, 100 employees are working in parallel, constantly running various snapshots. What should we do? How can we ensure there are no conflicts where one person launches something, and then the file system changes, causing all the snapshots to fail?
They won't go because that's how ZFS works. We can keep file system changes separately in one stream, which come through replication. And we can keep clones of older data versions that developers use. And this works for us, everything is fine with that.
So, the update will occur as an additional layer, and all new snapshots will be based on that layer, right?
From the previous layers that came from earlier replications.
The previous layers will fall off, but they will reference the old layer, and the new images will be taken from the last layer obtained during the update?
In general, yes.
Then as a consequence, we will have a lot of layers. And over time we will need to compress them?
Yes, that's right. There is a certain window. We keep weekly snapshots. It depends on what resources you have. If you have the ability to store a lot of data, you can keep snapshots for a long time. They won’t delete themselves. There will be no data corruption. If the snapshots become outdated, as we see it, that is, it depends on the company's policy, we can simply delete them and free up space.
Hello, thank you for the presentation! Regarding Joe's question. You mentioned that the client didn't want to give everyone access to the data. Strictly speaking, if a person has the result from Explain Analyze, they can peek at the data.
That's right. For example, we can write: 'SELECT FROM WHERE email = someone'. That is, we won't see the actual data, but we can observe some indirect signs. This is important to understand. But on the other hand, it's all visible. We have audit logs, we have oversight from other colleagues who also see what developers are doing. And if someone tries to do this, security will come to them and address the issue.
Good afternoon! Thank you for the presentation! I have a quick question. If Slack is not used in the company, is there currently any integration with it, or can instances be deployed for developers to connect a test application to the databases?
Currently, there is a binding to Slack, meaning there are no other messengers available, but we really want to add support for other messengers as well. What can you do? You can deploy the DB Lab without Joe, use the REST API, or our platform to create clones and connect them using PSQL. However, this is only possible if you are willing to give your developers access to the data, as there won't be any screen in place here.
I don't need this layer; I need that capability.
Then, yes, it can be done.
Source: habr.com
