Skip site navigation (1) Skip section navigation (2)

Re: TCP Overhead on Local Loopback

From: Ofer Israeli <oferi(at)checkpoint(dot)com>
To: Dave Crooke <dcrooke(at)gmail(dot)com>, Claudio Freire <klaussfreire(at)gmail(dot)com>
Cc: Samuel Gendler <sgendler(at)ideasculptor(dot)com>, Andrew Dunstan<andrew(at)dunslane(dot)net>, Andy <angelflow(at)yahoo(dot)com>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: TCP Overhead on Local Loopback
Date: 2012-04-05 09:39:59
Message-ID: 217DDBC2BB1E394CA9E7446337CBDEF20102DEDB4FF4@il-ex01.ad.checkpoint.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Tue, Apr 3, 2012 at 7:04 PM, Dave Crooke <dcrooke(at)gmail(dot)com> wrote:
>On Tue, Apr 3, 2012 at 10:38 AM, Claudio Freire <klaussfreire(at)gmail(dot)com> wrote:
>> You perform 8 roundtrips minimum per event, so that's 375us per query.
>> It doesn't look like much. That's probably Nagle and task switching
>> time, I don't think you can get it much lower than that, without
>> issuing less queries (ie: using the COPY method).

> I may be missing something stated earlier, but surely there are options in between 7 individual statements and resorting to COPY and temp tables. 

> I'm thinking of a set of precompiled queries / prepared statements along the lines of "SELECT FOR UPDATE WHERE foo in (?, ?, ?, .... ?)" that handle e.g. 500-1000 records per invocation. Or what about a stored procedure that updates one record, performing the necessary 7 steps, and then calling that in bulk?

> I agree with the assessment that 375us per statement is pretty decent, and that going after the communication channel (TCP vs local pipe) is chasing pennies when there are $100 bills lying around waiting to be collected.

Thanks for the suggestions.  We ended up re-factoring the code: caching some of the data that we needed in order to eliminate some of the queries previously run and inserting data completion into update statements in the form of UPDATE SET ... (SELECT ...) which brought us down to only one SQL query as opposed to 7 and this brings the processing time down from 4.5ms (previously stated 3ms was not reproduced)  down to ~1ms which is great for us.


Many thanks for the help from all of you,
Ofer

In response to

pgsql-performance by date

Next:From: Julien CigarDate: 2012-04-05 11:47:33
Subject: bad plan
Previous:From: Tomas VondraDate: 2012-04-04 19:50:44
Subject: Re: H800 + md1200 Performance problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group