Månadsarkiv: februari 2017

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!

The best programming language

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

A couple of weeks ago a friend of mine posted a very open (and admittedly pretty bad) question on #VXODEV, the local Slack developer community here in Växjö. The question can roughly be translated into something like ”Which [programming] language is the best?”. Naturally he and the members in the channel knew that there is no such thing as the ”best” programming language. Instead, the question was asked to get a discussion going on why people prefer one language over another. From my perspective as a novice Clojure enthusiast I had to stop and think for a moment. Why do I keep picking up Clojure for my late night hacks over other languages?

It’s not like Clojure helps me to solve problems that cannot be solved with other programming languages. Sure, it is true that a particular technology may be more performant or better suited for a particular task than others, but in most cases it doesn’t really matter.

Take for instance the Advent of Code (AoC) challenges that I’ve been having fun with lately. In these challenges you are free to choose whatever technology you want to use when solving the puzzles. Wouldn’t it then make more sense to just pick up the programming language you feel most comfortable and productive with? My main language as of now is with no doubt Java. I have been working as a Java developer for many years now, and I am pretty sure that I would solve challenges in AoC a lot faster with that language than with Clojure. Despite this I’ve not considered using Java in any of the challenges as of yet.

Perhaps the programming paradigm supported by the language has something to do with it? Clojure is a functional programming language, and the declarative style of programming that comes with that paradigm is something I’ve grown quite fond of.  The thing is that nowadays Java also has support for functional programming; since version 8 of the Java language developers can apply functional programming in their code. And while Clojure is predominantly a functional programming language, other languages that fall into a similar paradigm category, e.g. Haskell, pretty much lie in my toolbox collecting dust. Which in a way is kind of sad as Haskell is cool.

At the time of writing this blog post I looked through the history of the Slack channel to check what my answer to the ”which {programming] language is the best” question. It read something like the following: ”I haven’t come up with an answer yet, but what I do know is that I’m having a great time writing Clojure code”. And I think that ”a great time” is a pretty sound argument here. I mean, if you spend time in the evenings writing code it should be fun, right?

But what is fun for you may not be fun for me. For instance, a TV show may have some qualities that you enjoy but I don’t appreciate as much. Some of those can be quite obvious and be put into words, e.g. ”I don’t like sci-fi”. Other qualities are more or less based on feelings that are not as straightforward to identify and express. They just feel right and makes things interesting enough to make you watch the entire season of the show. It’s like, ”hey, this hammer feels nice in my hand so I choose it over the other ones in my toolbox”, but there is no single and obvious explanation why.

With the above ramblings in mind, I guess the ”best” programming language (for you) is a language that through some desired qualities makes you enjoy the creative process of programming.

But then again, there is no such thing as the best programming language (at least in the general sense). 😉

I’ll end this little text with a related blog post about how programming languages may relate to different personalities. Enjoy!

Until next time!