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.
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:
Most people don't really care. “localhost” is as good as a UNIX socket …
Well - it is not!
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
[hs@zenbook 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
[hs@zenbook 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 skyrockets from 0.126 ms to 0.211 milliseconds. At the same time TPS drop from 79.000 to 47.300.
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 Facebook or LinkedIn.
During the last days I've read an interesting post, published by Uber. It has caught our attention here at CYBERTEC. Here you can read it by yourself.
The idea behind geo-fencing is to provide information about an area to users. Somebody might want to find a taxi near a certain location, or somebody might simply want to order a Pizza from a nearby restaurant.
According to the blog post Uber has solved this problem using some hand-made GO code. Uber's implementation: 95% <5ms. Another Blog also had an eye on it: https://medium.com/@buckhx/unwinding-uber-s-most-efficient-service-406413c5871d#.vdsg0fhoi
Of course, to a team of PostgreSQL professionals, 5 ms is quite a lot so we tried to do better than that.
The first thing we need to compete is some test data. Some nice data can be found here:
https://github.com/buckhx/gofence-profiling/tree/master/nyc
Then the data can be loaded with psql nicely:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
set content `cat nyc_census_2010_tracts.geojson` CREATE TEMPORARY TABLE geojson (data jsonb); INSERT INTO geojson VALUES (:'content'); CREATE TABLE census_tracts ( id serial primary key, ntaname text, boroname text, shape geometry); INSERT INTO census_tracts (ntaname, boroname, shape) SELECT geom->'properties'->>'NTAName', geom->'properties'->>'BoroName', st_geomfromgeojson(geom->>'geometry') FROM geojson, LATERAL jsonb_array_elements(data->'features') geom; |
The data can already be queried. The following query does what Uber tries to achieve:
|
1 2 3 4 5 |
SELECT * FROM census_tracts WHERE ST_Contains(shape, ST_Point(-73.9590, 40.7830)); |
However, to be fair:
The sample set is not big enough yet. To increase the amount of data roughly to the size of Uber's database, the following SQL can do the job:
|
1 2 3 4 5 6 7 |
CREATE TABLE tracts_100k (LIKE census_tracts INCLUDING ALL); INSERT INTO tracts_100k (ntaname, boroname, shape) SELECT ntaname || ' ' || offs::text, boroname || ' ' || offs::text, ST_Translate(shape, offs, 0) FROM census_tracts, LATERAL generate_series(0,360,360/50) offs; |
After applying some nice indexing we can already see, how well our query behaves:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
CREATE EXTENSION file_fdw; CREATE SERVER files FOREIGN DATA WRAPPER file_fdw; CREATE FOREIGN TABLE geobench ( client int, tx_no int, time int, file_no int, time_epoch int, time_us int ) SERVER files OPTIONS (filename '/home/user/code/gis-build/gofence-profiling/nyc/pgbench_log.7665', format 'text', delimiter ' '); SELECT percentile_disc(ARRAY[0.5,0.95,0.99]) WITHIN GROUP (ORDER BY ROUND(time/1000.,3)) latency_ms FROM geobench; |
The results are very promising. Our version of the geo-fencing query is around 40 times faster than the Uber one. Clearly, Uber should consider using PostgreSQL instead of custom code. Given the fact that we invested around 30 minutes to get this done, even developing the business logic is faster with PostgreSQL.
Foto Copyright: Uber
