CYBERTEC Logo

UNIX sockets vs. localhost: PostgreSQL Performance advice

05.2016 / Category: / Tags: |

Unix sockets vs. localhost: We posted a lot of tuning advice 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 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 as a UNIX socket …

Well - it is not!

We have compiled an example 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 simpler than just fetching a number.

So, let us run this 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:

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:

The latency skyrockets 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.

In case you need any assistance, please feel free to contact us.
 


In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.

5 2 votes
Article Rating
Subscribe
Notify of
guest
4 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Eduardo Pereira
Eduardo Pereira
8 years ago

Nice, thanks for sharing the info! 🙂

John Scott
John Scott
8 years ago

i wonder about the numbers when if PGHOST=/tmp

John Scott
John Scott
8 years ago

just to clarify, by UNIX socket you mean PGHOST=/tmp and not TCP sockets, correct?

Troels Arvin
Troels Arvin
8 years ago

This is an interesting observation, especially as some people seem to be eager to move everything to micro services which communicate via TCP instead of syscalls or domain sockets.
In this blog post, I have shared some observations about the same topic: http://troelsarvin.blogspot.dk/2010/06/separation-or-co-location-of-database.html

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf
Austria

+43 (0) 2622 93022-0
office@cybertec.at

Get the newest PostgreSQL Info & Tools


    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    ©
    2024
    CYBERTEC PostgreSQL International GmbH
    phone-handsetmagnifiercrosscross-circle
    4
    0
    Would love your thoughts, please comment.x
    ()
    x
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram