I propose to review the transcript of Vladimir Sitnikov's report from early 2016 "PostgreSQL and JDBC: Getting the Most Out of It".
Good afternoon! My name is Vladimir Sitnikov. I've been working at NetCracker for 10 years, primarily focusing on performance. Everything related to Java and SQL is what I love.
Today, I will discuss the challenges we faced at the company when we started using PostgreSQL as a database server. We primarily work with Java, but what I will talk about today is applicable in other languages as well.
We will talk about:
- data retrieval.
- data storage.
- as well as performance.
- And the pitfalls that lie beneath.
Let's start with a simple question. We are selecting a row from a table by the primary key.
The database is on the same host. And this process takes 20 milliseconds.
Those 20 milliseconds are quite significant. If you have 100 such requests, then you are wasting time in seconds to process those requests.
We don't like that and look at what the database offers us for this. The database provides us with two options for executing queries.
The first option is a simple query. What's good about it? We just take it and send it, and nothing more.
The database also has an extended query, which is more sophisticated but more functional. You can send requests separately for parsing, execution, variable binding, etc.
Super extended queries are not the subject of our current report. We may have a wishlist for the database that is somewhat formed, i.e., things we want that are not possible right now or in the near future. So we just write them down and will keep asking the main people.
What we can do is a simple query and an extended query.
What is the peculiarity of each approach?
A simple query is good for one-time execution. Execute it once and forget it. The problem is that it does not support binary data format, making it unsuitable for high-performance systems.
Extended query helps save time on parsing. This is what we did and began to use. It has been extremely helpful for us. There's not just savings on parsing; there's also savings on data transmission. Transmitting data in binary format is much more efficient.
Let's move on to practice. This is what a typical application looks like. It could be Java, etc.
We created a statement. Executed the command. Created a close. Where's the error here? What's the problem? There are no problems. That's how it's written in all the books. That's how you should write. If you want maximum performance, write it this way.
But practice has shown that this doesn't work. Why? Because we have a method called 'close'. And when we do this, it ends up being like a smoker working with the database. We said 'PARSE EXECUTE DEALLOCATE'.
Why all these unnecessary creations and unloading of statements? They're of no use to anyone. But usually, in PreparedStatement, when we close them, they close everything in the database. That's not what we want.
We want to work with the database as healthy individuals. We prepare our statement once, and then we execute it many times. In reality, many times in this context means parsing it once for the entire application's life. We use the same statement ID for different REST calls. This is our goal.
How do we achieve this?
Very simply – we shouldn't close statements. We write it this way: 'prepare' 'execute'.
If we run something like this, it's clear that something will eventually overflow. If it's not clear, we can measure it. Let's write a benchmark with such a simple method. Create a statement. Run it on some version of the driver and see that it crashes pretty quickly due to a loss of all the memory we have.
It's clear that such errors are easy to fix. I won't talk about them. But I will say that in the new version, it works much faster. The method is meaningless, but nonetheless.
How to work correctly? What do we need to do for this?
In reality, applications always close statements. All the books say to close them, otherwise memory leaks occur.
And PostgreSQL cannot cache queries. Each session needs to create its own cache.
And we also don't want to waste time on parsing.
And as usual, we have two options.
The first option is that we take it and say, let's wrap everything in PgSQL. There is caching. It caches everything. It should turn out great. We looked at this. We have 100,500 queries. It doesn’t work. We refuse to manually turn queries into procedures. No way.
We have a second option – to take it and build it ourselves. We open the source code, start building. We build and build. It turned out that it’s not so difficult to do.
This appeared in August 2015. Now there is a more modern version. And everything is great. It works so well that we don’t change anything in the application. We even stopped considering PgSQL, that is, this was enough for us to reduce all overhead to practically zero.
Accordingly, Server-prepared statements are activated on the 5th execution to avoid wasting memory in the database on each one-time query.
You might ask – where are the numbers? What do you get? And here I won’t provide numbers because each query has its own.
Our queries were such that we spent about 20 milliseconds on parsing for OLTP queries. There was 0.5 milliseconds for execution, 20 milliseconds for parsing. The query was 10 KiB of text, 170 lines of plan. This is an OLTP query. It requests 1, 5, 10 rows, sometimes more.
But we absolutely didn’t want to spend 20 milliseconds. We reduced it to zero. Everything is great.
What can you take away from this? If you have Java, then you take the modern version of the driver and enjoy.
If you have some other language, then think – maybe you need this too? Because from the standpoint of the end language, for example, if PL 8 or you have LibPQ, it’s not obvious that you are wasting time not on execution but on parsing, and this is worth checking. How? It’s all free.
Except for the fact that there are errors, some features. And we will talk about them right now. Most of it will be about industrial archaeology, about what we found, what we stumbled upon.
If a query is generated dynamically. That happens. Someone concatenates strings, resulting in an SQL query.
What’s wrong with it? It’s bad because in the end, we get a different string each time.
This different line needs to recalculate hashCode. This is indeed a CPU task – finding a long query text in even an existing hash isn't easy. Therefore, the simple output is – don't generate queries. Keep them in a single variable. And enjoy.
The next issue. Data types are important. There are ORMs that claim any NULL is fine, just take any. If it's Int, we use setInt. But if it's NULL, let's assume it will always be VARCHAR. In the end, what's the difference with NULL? The database will understand everything on its own. But this picture doesn't work.
In practice, databases care a lot. If the first time you declared it as a number, and the second time as VARCHAR, you can't reuse Server-prepared statements. In such cases, you have to recreate our statement.
If you are executing the same query, monitor that the data types in the column do not get mixed up. You need to keep an eye on NULL. This is a common mistake we encountered after we started using PreparedStatements.
Okay, we turned it on. Perhaps we took a driver. And performance fell. Everything went bad.
How does this happen? Is it a bug or a feature? Unfortunately, it's unclear whether it's a bug or a feature. But there's a quite simple reproduction scenario for this problem. It caught us completely off guard. It involves querying literally from a single table. Of course, we had more such queries. They usually included two or three tables, but there's this specific reproduction scenario. Take any version of your database and reproduce it.
The point is that we have two columns, each indexed. In one column, there are a million rows with the value NULL. And in the other column, there are only 20 rows. When we execute without bound variables, everything works fine.
If we start executing with bound variables, i.e., we execute the sign '?' or '$1' for our query, what do we ultimately get?
The first execution - as expected. The second - a bit faster. Something got cached. The third, fourth, fifth. Then bam - and it just goes like that. And the worst part is that this happens on the sixth execution. Who knew you had to do exactly six executions to understand what the actual execution plan is?
Who is to blame? What happened? The database contains optimization. It is somewhat optimized for a generic case. And accordingly, starting from some point, it transitions to a generic plan, which, unfortunately, may turn out to be different. It may be the same or it may be different. And there is some threshold value that leads to such behavior.
What can be done about this? Here, of course, it's more complicated to make assumptions. There is a simple solution that we use. This is +0, OFFSET 0. Surely, you're familiar with such solutions. We just take and add "+0" to the query, and everything works well. I will show it later.
And there is another option – to take a closer look at the plans. The developer must not only write the query but also say "explain analyze" six times. If it's five, then it won’t do.
And there is a third option – to write a letter to pgsql-hackers. I wrote, though it's still unclear whether this is a bug or a feature.
While we think about whether it's a bug or a feature, let's fix it. We'll take our query and add "+0". Everything is fine. Just two characters, and you don’t even need to think about how it works. It's very simple. We simply forbade the database from using the index on this column. We don't have an index on the column " +0" and that's it; the database doesn't use the index, and everything works well.
This is the rule about six explain analyses. Currently, in recent versions, it needs to be done six times if you have related variables. If you don't have related variables, then we do it this way. Ultimately, this specific query fails. It's not complicated.
It may seem like it just keeps happening. There's a bug here, a bug there. In reality, there are bugs everywhere.
Let's take another look. For example, we have two schemas. Schema A with table X and Schema B with table X. The query is to select data from the table. What will happen? We will get an error. We will experience everything mentioned above. The rule is – bugs are everywhere; we will encounter all of the above.
Now the question is: "Why?". It seems there is documentation stating that if we have a schema, there is a variable called "search_path" that indicates where to look for the table. It seems the variable exists.
What is the problem? The issue is that server-prepared statements do not suspect that someone might change the search_path. This value remains somewhat constant for the database. And some parts might not pick up the new values.
Of course, it depends on the version you are testing. It depends on how significantly your tables differ. Version 9.1 will simply execute the old queries. Newer versions may detect discrepancies and indicate that there is an error.
How do we fix this? There’s a simple rule – don't do that. You shouldn't change search_path while the application is running. If you must change it, it's better to create a new connection.
We can discuss this; that is, open it up, discuss, and write more. Perhaps we can convince the database developers that when someone changes a value, the database should inform the client: 'Look, your value has changed. Maybe you need to reset the statements and recreate them?'. Right now, the database behaves quietly and doesn't inform at all that some statements have changed internally.
And I want to emphasize again – this is not typical for Java. We will see the same thing in PL/pgSQL one-to-one. But it will reproduce there.
Let’s try to select data again. We’re selecting and selecting. We have a table with a million rows. Each row is around one kilobyte. Approximately a gigabyte of data. And we have a working memory of 128 megabytes in the Java machine.
As recommended in all books, we are using streaming processing. That is, we open resultSet and read data from it gradually. Will this work? Will it run out of memory? Will it read a little bit at a time? Let’s put our trust in the database, trust in Postgres. Do we trust? We don't. Will we face OutOfMemory? Who has faced OutOfMemory? And who managed to fix it afterward? Has anyone fixed it?
If you have a million rows, you can't just select them like that. You must use OFFSET/LIMIT. Who supports this option? And who supports the idea that you should play around with autoCommit?
Here, as usual, the most unexpected option turns out to be the right one. And if you suddenly turn off autoCommit, it will help. Why is that? Science doesn’t know.
But by default, all clients connecting to the Postgres database fetch data entirely. PgJDBC is no exception; it fetches all rows.
There is a variation on the FetchSize theme; that is, you can specify at the individual statement level that it should fetch data in chunks of 10 or 50. But this doesn’t work until you turn off autoCommit. Once you turn off autoCommit, it starts working.
Walking through the code and setting setFetchSize everywhere is inconvenient. That's why we've implemented a setting that will provide a default value for the entire connection.
So we said that. We configured the parameter. And what did we achieve? If we select a small amount, such as 10 rows, the overhead is significant. Therefore, this value should be around a hundred.
Ideally, we should also learn to limit it in bytes, but the recipe is this: set defaultRowFetchSize to over a hundred and be happy.
Let's move on to data insertion. Insertion is simpler and there are different options. For example, INSERT, VALUES. That's a good option. We can also say 'INSERT SELECT.' In practice, they are the same. There is no difference in performance.
Books say to execute Batch statements, and they mention that you can perform more complex commands with multiple parentheses. And in Postgres, there is a wonderful function—you can use COPY, which makes it faster.
If we measure, we can make several interesting discoveries again. How do we want this to work? We want to avoid parsing and unnecessary commands.
In practice, TCP doesn't allow us to do that. If the client is busy sending a request, the database doesn't read requests while trying to send us replies. As a result, the client waits for the database to read the request, while the database waits for the client to read the reply.
That's why the client is forced to periodically send a synchronization packet. This results in unnecessary network interactions and wasted time.
The more we add, the worse it gets. The driver is quite pessimistic and adds them fairly often, roughly every 200 rows, depending on the size of the rows, etc.
Sometimes, adjusting just one line can speed everything up tenfold. It happens. Why? As usual, a constant was already used somewhere. And the value '128' meant not to use batching.
It's good that this didn't make it into the official version. We discovered it before we started releasing the version. All the values I mention are based on modern versions.
Let's measure. We measure the simple InsertBatch. We measure the multiple InsertBatch, that is, it's the same but with many values. It's a clever approach. Not everyone can do this, but it's a very simple trick, much easier than COPY.
You can use COPY.
And this can be done on structures. Declare User default type, pass an array, and INSERT directly into the table.
If you open the link: pgjdbc/ubenchmsrk/InsertBatch.java, you'll find this code on GitHub. You can see exactly what queries are being generated there. It's not the core issue.
We ran it. And the first thing we understood is that not using batch processing is simply not an option. All batching options are equal to zero, meaning execution time is practically zero compared to single execution.
We are inserting data. It’s quite a simple table. Three columns. And what do we see here? We see that all three options are roughly comparable. And COPY, of course, is better.
This is when we insert in chunks. When we were talking about one value VALUES, two values VALUES, three values VALUES, or we specified 10 of them separated by commas. This is exactly what is currently horizontal. 1, 2, 4, 128. It’s clear that Batch Insert, which is shown in blue, benefits greatly from this. That is, when you insert one at a time or even when you insert four at a time, it becomes twice as efficient, simply because we packed a bit more into VALUES. Fewer EXECUTE operations.
Using COPY on small volumes is extremely unpromising. I didn’t even draw on the first two. They go into the sky, that is, these green numbers are for COPY.
COPY should be used when you have at least over a hundred rows of data. The overhead for opening this connection is significant. And, to be honest, I haven't delved into that direction. I optimized batching; I didn’t optimize COPY.
What do we do next? We measured. We understand that we need to use either structures or a clever batch that combines several values.
What should we take away from today's report?
- PreparedStatement is everything to us. It greatly enhances performance. It brings a large barrel of tar.
- And we need to do EXPLAIN ANALYZE 6 times.
- And we need to dilute OFFSET 0, and use tricks like +0 to fix the remaining percentage of our problematic queries.
Source: habr.com
