We have posted a lot of tuning advise on this blog already. Now we thought we would take it a step further and share some more advice and common “mistakes” that we see people making when configuring web applications. Improving web application performance is more critical than ever because most applications in use these days are browser based.
Small step for mankind – giant leap for performance
In most cases web applications run a large number of very small statements. In some cases the database is even on the same server as the application itself.
To run small statements on the same host, PostgreSQL provides two means:
- UNIX sockets
- “localhost” (= loopback device)
Most people don’t really care. “localhost” is as good a UNIX socket …
Well – it is not!
We have compiled a simple showing what can happen:
a.sql is a simple script, which just creates a random number and SELECTs it. So it is the most simplistic statement possible. There is nothing more simple than just fetching a number.
So, let us run this simple benchmark on a normal laptop:
- pgbench will run 5 threads
- 10 concurrent users
- 10 seconds
UNIX sockets provide us with 79.000 transactions per second:
[[email protected] postgresql-9.5.0]$ pgbench -f /tmp/a.sql -j 5 -c 10 -T 10 test starting vacuum...end. transaction type: Custom query scaling factor: 1 query mode: simple number of clients: 10 number of threads: 5 duration: 10 s number of transactions actually processed: 791171 latency average: 0.126 ms tps = 79096.447917 (including connections establishing) tps = 79136.229601 (excluding connections establishing)
The interesting thing is that the average latency is 0.126 milliseconds.
Let us repeat the very same test. This time “localhost” is added to the example:
[[email protected] postgresql-9.5.0]$ pgbench -f /tmp/a.sql -j 5 -c 10 -T 10 -h localhost test starting vacuum...end. transaction type: Custom query scaling factor: 1 query mode: simple number of clients: 10 number of threads: 5 duration: 10 s number of transactions actually processed: 473210 latency average: 0.211 ms tps = 47297.885523 (including connections establishing) tps = 47331.482977 (excluding connections establishing)
The latency sky rockets from 0.126 ms to 0.211 milliseconds. At the same time TPS drop from 79.000 to 47.300.
Just by adding “localhost” performance dropped to 60% of the original speed.
NOTE: In real life the drop won’t be that large because we expect users to run slightly more complicated SQL – however, the difference is real and there.
Why is that? UNIX sockets are actually a pretty simple thing. The loopback device is much more complicated and therefore the overhead relative to those simple queries is huge.