A troublesome query

This blog post is also available on the Fortnox developer blog.

Last week we decided to have a look at one of our database queries that had started to become a real problem. The symptoms were super strange; occasionally during peak hours the query began to perform really, really bad, from around 200 milliseconds per query up to about 30000 milliseconds per query. Yes you read that right, we are talking about half a minute or at times even longer than normal. That’s like 150 times slower and of course not acceptable at all, so we went head in trying to figure out why. This blog post describe our findings and how we found a solution to the problem.

The first thing we did was to set up a local environment to try to recreate the problem there. We use Postgres and Java in our application, so we decided to recreate the problem in a test harness using JUnit with the same database driver as we use in production. After setting the connection pool size to 1 and repeatedly firing away the problematic query we were able to recreate the behavior quite easily. The symptoms were really weird; the first nine queries were perfectly fine performance-wise, but from the tenth query and onward the query started to take up to 150 times longer. What’s going on?

After tweaking the query, altering the prepared statement parameters back and forth and trying to understand the Postgres server logs, we finally started to grasp what was happening under the hood. The key breakthrough was probably when we came across this information concerning server-side prepared statements. It turns out that there is a special threshold that determines when server-side prepared statements should kick in. This threshold has a default value equal to 5, which means that the statement should be prepared ”often” on the server.

When we increased this threshold value a bit (to 10), the bad performance of the query instead occurred around the fifteenth time rather than on the tenth time. According to the PGStatement javadoc, setting the threshold value to 0 effectively turns off server-side prepared statements. We gave that a try and instantly the problem completely disappeared. Interestingly, the performance in our initial tests were not affected whatsoever. The query performed just as well using only client-side prepared statements. The poor performance of that query vanished completely and has not caused any problems in production since we turned server-side prepared statements off.

Server-side prepared statements might be a killer feature for some queries, but for the one we were struggling with it definitely was not. A very interesting finding we take from all this is a line of text from the PostgreSQL Extensions JDBC API documentation. It goes like this:

”You should be cautious about enabling the use of server side prepared statements globally.”

Okay, but it’s not like we enabled them globally; the default threshold value is 5, which means that they are enabled by default if you do not explicitly turn server-side prepared statements off. If the recommendation is to not enable them globally, a default value of 0 would in my mind have been more reasonable, right? Hopefully there’s a good reason for its current default value that someone more into databases than me has the answer to. In any case, as demonstrated in the API documentation referred to earlier, it’s quite easy to set the default value via the connection string, like so:

// 0 means that we do not use server-side prepared queries
String url = "jdbc:postgresql://localhost:5432/test?prepareThreshold=0";

I hope this blog post can help remedy some headache out there. Until next time!

Kommentera

E-postadressen publiceras inte. Obligatoriska fält är märkta *