pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedTCP Overhead on Local Loopback
13+ messages / 8 participants
[nested] [flat]
* TCP Overhead on Local Loopback
@ 2012-04-01 20:24 Ofer Israeli <oferi@checkpoint.com>
0 siblings, 3 replies; 13+ messages in thread
From: Ofer Israeli @ 2012-04-01 20:24 UTC (permalink / raw)
To: pgsql-performance
Hi all,
We are running performance tests using PG 8.3 on a Windows 2008 R2 machine connecting locally over TCP.
In our tests, we have found that it takes ~3ms to update a table with ~25 columns and 60K records, with one column indexed.
We have reached this number after many tweaks of the database configuraiton and one of the changes we made was to perform the updates in batches of 5K as opposed to the pervious transaction per event. Note that our use of batches is to have only one transaction, but still each of the 5K events is independently SELECTing and UPDATEing records, i.e. it is not all contained in a stored procedure or such.
Still these times are too high for us and we are looking to lower them and I am wondering about the TCP/IP overhead of passing the information back and forth. Does anyone have any numbers in what the TCP could cost in the configuration mentioned above or pointers on how to test it?
Many thanks,
Ofer
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: TCP Overhead on Local Loopback
@ 2012-04-01 22:01 Andy <angelflow@yahoo.com>
parent: Ofer Israeli <oferi@checkpoint.com>
2 siblings, 1 reply; 13+ messages in thread
From: Andy @ 2012-04-01 22:01 UTC (permalink / raw)
To: Ofer Israeli <oferi@checkpoint.com>; pgsql-performance
You could try using Unix domain socket and see if the performance improves. A relevant link:
http://stackoverflow.com/questions/257433/postgresql-unix-domain-sockets-vs-tcp-sockets
________________________________
From: Ofer Israeli <oferi@checkpoint.com>
To: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Sent: Sunday, April 1, 2012 4:24 PM
Subject: [PERFORM] TCP Overhead on Local Loopback
Hi
all,
We are running
performance tests using PG 8.3 on a Windows 2008 R2 machine connecting locally
over TCP.
In our tests, we
have found that it takes ~3ms to update a table with ~25 columns and 60K
records, with one column indexed.
We have reached this
number after many tweaks of the database configuraiton and one of the changes we
made was to perform the updates in batches of 5K as opposed to the pervious
transaction per event. Note that our use of batches is to have only one
transaction, but still each of the 5K events is independently SELECTing and
UPDATEing records, i.e. it is not all contained in a stored procedure or
such.
Still these times
are too high for us and we are looking to lower them and I am wondering about
the TCP/IP overhead of passing the information back and forth. Does anyone
have any numbers in what the TCP could cost in the configuration mentioned above
or pointers on how to test it?
Many
thanks,
Ofer
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: TCP Overhead on Local Loopback
@ 2012-04-01 23:48 Rob Wultsch <wultsch@gmail.com>
parent: Ofer Israeli <oferi@checkpoint.com>
2 siblings, 0 replies; 13+ messages in thread
From: Rob Wultsch @ 2012-04-01 23:48 UTC (permalink / raw)
To: Ofer Israeli <oferi@checkpoint.com>; +Cc: pgsql-performance
On Sun, Apr 1, 2012 at 1:24 PM, Ofer Israeli <oferi@checkpoint.com> wrote:
> Hi all,
>
> We are running performance tests using PG 8.3 on a Windows 2008 R2 machine
> connecting locally over TCP.
8.3 will be not supported in under a year. Time to start testing upgrades.
http://www.postgresql.org/support/versioning/
--
Rob Wultsch
wultsch@gmail.com
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: TCP Overhead on Local Loopback
@ 2012-04-01 23:54 Andrew Dunstan <andrew@dunslane.net>
parent: Andy <angelflow@yahoo.com>
0 siblings, 1 reply; 13+ messages in thread
From: Andrew Dunstan @ 2012-04-01 23:54 UTC (permalink / raw)
To: Andy <angelflow@yahoo.com>; +Cc: Ofer Israeli <oferi@checkpoint.com>; pgsql-performance
On 04/01/2012 06:01 PM, Andy wrote:
> You could try using Unix domain socket and see if the performance
> improves. A relevant link:
He said Windows. There are no Unix domain sockets on Windows. (And
please don't top-post)
cheers
andrew
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: TCP Overhead on Local Loopback
@ 2012-04-02 00:29 Claudio Freire <klaussfreire@gmail.com>
parent: Andrew Dunstan <andrew@dunslane.net>
0 siblings, 1 reply; 13+ messages in thread
From: Claudio Freire @ 2012-04-02 00:29 UTC (permalink / raw)
To: Andrew Dunstan <andrew@dunslane.net>; +Cc: Andy <angelflow@yahoo.com>; Ofer Israeli <oferi@checkpoint.com>; pgsql-performance
On Sun, Apr 1, 2012 at 8:54 PM, Andrew Dunstan <andrew@dunslane.net> wrote:
>> You could try using Unix domain socket and see if the performance
>> improves. A relevant link:
>
>
> He said Windows. There are no Unix domain sockets on Windows. (And please
> don't top-post)
Windows supports named pipes, which are functionally similar, but I
don't think pg supports them.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: TCP Overhead on Local Loopback
@ 2012-04-02 01:11 Andrew Dunstan <andrew@dunslane.net>
parent: Claudio Freire <klaussfreire@gmail.com>
0 siblings, 2 replies; 13+ messages in thread
From: Andrew Dunstan @ 2012-04-02 01:11 UTC (permalink / raw)
To: Claudio Freire <klaussfreire@gmail.com>; +Cc: Andy <angelflow@yahoo.com>; Ofer Israeli <oferi@checkpoint.com>; pgsql-performance
On 04/01/2012 08:29 PM, Claudio Freire wrote:
> On Sun, Apr 1, 2012 at 8:54 PM, Andrew Dunstan<andrew@dunslane.net> wrote:
>>> You could try using Unix domain socket and see if the performance
>>> improves. A relevant link:
>>
>> He said Windows. There are no Unix domain sockets on Windows. (And please
>> don't top-post)
> Windows supports named pipes, which are functionally similar, but I
> don't think pg supports them.
>
Correct, so telling the OP to have a look at them isn't at all helpful.
And they are not supported on all Windows platforms we support either
(specifically not on XP, AIUI).
cheers
andrew
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: TCP Overhead on Local Loopback
@ 2012-04-02 08:25 Samuel Gendler <sgendler@ideasculptor.com>
parent: Andrew Dunstan <andrew@dunslane.net>
1 sibling, 1 reply; 13+ messages in thread
From: Samuel Gendler @ 2012-04-02 08:25 UTC (permalink / raw)
To: Andrew Dunstan <andrew@dunslane.net>; +Cc: Claudio Freire <klaussfreire@gmail.com>; Andy <angelflow@yahoo.com>; Ofer Israeli <oferi@checkpoint.com>; pgsql-performance
On Sun, Apr 1, 2012 at 6:11 PM, Andrew Dunstan <andrew@dunslane.net> wrote:
>
>
> On 04/01/2012 08:29 PM, Claudio Freire wrote:
>
>> On Sun, Apr 1, 2012 at 8:54 PM, Andrew Dunstan<andrew@dunslane.net>
>> wrote:
>>
>>> You could try using Unix domain socket and see if the performance
>>>> improves. A relevant link:
>>>>
>>>
>>> He said Windows. There are no Unix domain sockets on Windows. (And please
>>> don't top-post)
>>>
>> Windows supports named pipes, which are functionally similar, but I
>> don't think pg supports them.
>>
>>
> Correct, so telling the OP to have a look at them isn't at all helpful.
> And they are not supported on all Windows platforms we support either
> (specifically not on XP, AIUI).
>
But suggesting moving away from TCP/IP with no actual evidence that it is
network overhead that is the problem is a little premature, regardless.
What, exactly, are the set of operations that each update is performing
and is there any way to batch them into fewer statements within the
transaction. For example, could you insert all 60,000 records into a
temporary table via COPY, then run just a couple of queries to do bulk
inserts and bulk updates into the destination tble via joins to the temp
table? 60,000 rows updated with 25 columns, 1 indexed in 3ms is not
exactly slow. That's a not insignificant quantity of data which must be
transferred from client to server, parsed, and then written to disk,
regardless of TCP overhead. That is happening via at least 60,000
individual SQL statements that are not even prepared statements. I don't
imagine that TCP overhead is really the problem here. Regardless, you can
reduce both statement parse time and TCP overhead by doing bulk inserts
(COPY) followed by multi-row selects/updates into the final table. I don't
know how much below 3ms you are going to get, but that's going to be as
fast as you can possibly do it on your hardware, assuming the rest of your
configuration is as efficient as possible.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: TCP Overhead on Local Loopback
@ 2012-04-02 11:34 Andrew Dunstan <andrew@dunslane.net>
parent: Andrew Dunstan <andrew@dunslane.net>
1 sibling, 0 replies; 13+ messages in thread
From: Andrew Dunstan @ 2012-04-02 11:34 UTC (permalink / raw)
To: Claudio Freire <klaussfreire@gmail.com>; +Cc: Andy <angelflow@yahoo.com>; Ofer Israeli <oferi@checkpoint.com>; pgsql-performance
On 04/01/2012 09:11 PM, Andrew Dunstan wrote:
>
>
> On 04/01/2012 08:29 PM, Claudio Freire wrote:
>> On Sun, Apr 1, 2012 at 8:54 PM, Andrew Dunstan<andrew@dunslane.net>
>> wrote:
>>>> You could try using Unix domain socket and see if the performance
>>>> improves. A relevant link:
>>>
>>> He said Windows. There are no Unix domain sockets on Windows. (And
>>> please
>>> don't top-post)
>> Windows supports named pipes, which are functionally similar, but I
>> don't think pg supports them.
>>
>
> Correct, so telling the OP to have a look at them isn't at all
> helpful. And they are not supported on all Windows platforms we
> support either (specifically not on XP, AIUI).
>
>
Apparently I was mistaken about the availability. However, my initial
point remains. Since all our client/server comms on Windows are over
TCP, telling the OP to look at Unix domain sockets is unhelpful.
cheers
andrew
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: TCP Overhead on Local Loopback
@ 2012-04-02 16:51 Jeff Janes <jeff.janes@gmail.com>
parent: Ofer Israeli <oferi@checkpoint.com>
2 siblings, 0 replies; 13+ messages in thread
From: Jeff Janes @ 2012-04-02 16:51 UTC (permalink / raw)
To: Ofer Israeli <oferi@checkpoint.com>; +Cc: pgsql-performance
On Sun, Apr 1, 2012 at 1:24 PM, Ofer Israeli <oferi@checkpoint.com> wrote:
> Hi all,
>
> We are running performance tests using PG 8.3 on a Windows 2008 R2 machine
> connecting locally over TCP.
> In our tests, we have found that it takes ~3ms to update a table with ~25
> columns and 60K records, with one column indexed.
I assume you mean 3ms per row, as per 3ms per 60,000 rows (or per
5,000 rows?) seems improbably fast.
> We have reached this number after many tweaks of the database configuraiton
> and one of the changes we made was to perform the updates in batches of 5K
> as opposed to the pervious transaction per event. Note that our use of
> batches is to have only one transaction, but still each of the 5K events is
> independently SELECTing and UPDATEing records, i.e. it is not all contained
> in a stored procedure or such.
>
> Still these times are too high for us and we are looking to lower them and I
> am wondering about the TCP/IP overhead of passing the information back and
> forth. Does anyone have any numbers in what the TCP could cost in the
> configuration mentioned above or pointers on how to test it?
Change all your updates to selects, with the same where clause. If
doing that makes it much faster, TCP must not have been your
bottleneck.
Cheers,
Jeff
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: TCP Overhead on Local Loopback
@ 2012-04-03 15:24 Ofer Israeli <oferi@checkpoint.com>
parent: Samuel Gendler <sgendler@ideasculptor.com>
0 siblings, 1 reply; 13+ messages in thread
From: Ofer Israeli @ 2012-04-03 15:24 UTC (permalink / raw)
To: Samuel Gendler <sgendler@ideasculptor.com>; Andrew Dunstan <andrew@dunslane.net>; +Cc: Claudio Freire <klaussfreire@gmail.com>; Andy <angelflow@yahoo.com>; pgsql-performance
On Sun, Apr 2, 2012 at 11:25 AM, Samuel Gendler < sgendler@ideasculptor.com > wrote:
> But suggesting moving away from TCP/IP with no actual evidence that it is network overhead that is the problem is a little premature, regardless.
Agreed, that's why I'd like to understand what tools / methodologies are available in order to test whether TCP is the issue.
> What, exactly, are the set of operations that each update is performing and is there any way to batch them into fewer statements
> within the transaction. For example, could you insert all 60,000 records into a temporary table via COPY, then run just a couple of queries to do
> bulk inserts and bulk updates into the destination tble via joins to the temp table?
I don't see how a COPY can be faster here as I would need to both run the COPY into the temp table and then UPDATE all the columns in the real table.
Are you referring to saving the time where all the UPDATEs would be performed via a stored procedure strictly in the db domain without networking back and forth?
> 60,000 rows updated with 25 columns, 1 indexed in 3ms is not exactly slow. That's a not insignificant quantity of data which must be transferred from client to server,
> parsed, and then written to disk, regardless of TCP overhead. That is happening via at least 60,000 individual SQL statements that are not even prepared statements. I don't
> imagine that TCP overhead is really the problem here. Regardless, you can reduce both statement parse time and TCP overhead by doing bulk inserts
> (COPY) followed by multi-row selects/updates into the final table. I don't know how much below 3ms you are going to get, but that's going to be as fast
> as you can possibly do it on your hardware, assuming the rest of your configuration is as efficient as possible.
The 3ms is per each event processing, not the whole 60K batch. Each event processing includes:
5 SELECTs
1 DELETE
2 UPDATEs
where each query performed involves TCP connections, that is, the queries are not grouped in a stored procedure or such.
For all these queries does 3ms sound like a reasonable time? If so, do you have an estimation of how long the network portion would be here?
Thanks,
Ofer
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: TCP Overhead on Local Loopback
@ 2012-04-03 15:38 Claudio Freire <klaussfreire@gmail.com>
parent: Ofer Israeli <oferi@checkpoint.com>
0 siblings, 1 reply; 13+ messages in thread
From: Claudio Freire @ 2012-04-03 15:38 UTC (permalink / raw)
To: Ofer Israeli <oferi@checkpoint.com>; +Cc: Samuel Gendler <sgendler@ideasculptor.com>; Andrew Dunstan <andrew@dunslane.net>; Andy <angelflow@yahoo.com>; pgsql-performance
On Tue, Apr 3, 2012 at 12:24 PM, Ofer Israeli <oferi@checkpoint.com> wrote:
> On Sun, Apr 2, 2012 at 11:25 AM, Samuel Gendler < sgendler@ideasculptor.com > wrote:
>> But suggesting moving away from TCP/IP with no actual evidence that it is network overhead that is the problem is a little premature, regardless.
>
> Agreed, that's why I'd like to understand what tools / methodologies are available in order to test whether TCP is the issue.
As it was pointed out already, if you perform 60.000 x (5+1+2) "select
1" queries you'll effectively measure TCP overhead, as planning and
execution will be down to negligible times.
>> What, exactly, are the set of operations that each update is performing and is there any way to batch them into fewer statements
>> within the transaction. For example, could you insert all 60,000 records into a temporary table via COPY, then run just a couple of queries to do
>> bulk inserts and bulk updates into the destination tble via joins to the temp table?
>
> I don't see how a COPY can be faster here as I would need to both run the COPY into the temp table and then UPDATE all the columns in the real table.
> Are you referring to saving the time where all the UPDATEs would be performed via a stored procedure strictly in the db domain without networking back and forth?
You'll be saving a lot of planning and parsing time, as COPY is
significantly simpler to plan and parse, and the complex UPDATEs and
INSERTs required to move data from the temp table will only incur a
one-time planning cost. In general, doing it that way is significantly
faster than 480.000 separate queries. But it does depend on the
operations themselves.
>> 60,000 rows updated with 25 columns, 1 indexed in 3ms is not exactly slow. That's a not insignificant quantity of data which must be transferred from client to server,
>> parsed, and then written to disk, regardless of TCP overhead. That is happening via at least 60,000 individual SQL statements that are not even prepared statements. I don't
>> imagine that TCP overhead is really the problem here. Regardless, you can reduce both statement parse time and TCP overhead by doing bulk inserts
>> (COPY) followed by multi-row selects/updates into the final table. I don't know how much below 3ms you are going to get, but that's going to be as fast
>> as you can possibly do it on your hardware, assuming the rest of your configuration is as efficient as possible.
>
> The 3ms is per each event processing, not the whole 60K batch. Each event processing includes:
> 5 SELECTs
> 1 DELETE
> 2 UPDATEs
> where each query performed involves TCP connections, that is, the queries are not grouped in a stored procedure or such.
If you run the 480.000 queries on a single transaction, you use a
single connection already. So you only have transmission overhead,
without the TCP handshake. You still might gain a bit by disabling
Nagle's algorithm (if that's possible in windows), which is the main
source of latency for TCP. But that's very low-level tinkering.
> For all these queries does 3ms sound like a reasonable time? If so, do you have an estimation of how long the network portion would be here?
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).
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: TCP Overhead on Local Loopback
@ 2012-04-03 16:04 Dave Crooke <dcrooke@gmail.com>
parent: Claudio Freire <klaussfreire@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Dave Crooke @ 2012-04-03 16:04 UTC (permalink / raw)
To: Claudio Freire <klaussfreire@gmail.com>; +Cc: Ofer Israeli <oferi@checkpoint.com>; Samuel Gendler <sgendler@ideasculptor.com>; Andrew Dunstan <andrew@dunslane.net>; Andy <angelflow@yahoo.com>; pgsql-performance
On Tue, Apr 3, 2012 at 10:38 AM, Claudio Freire <klaussfreire@gmail.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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: TCP Overhead on Local Loopback
@ 2012-04-05 09:39 Ofer Israeli <oferi@checkpoint.com>
parent: Dave Crooke <dcrooke@gmail.com>
0 siblings, 0 replies; 13+ messages in thread
From: Ofer Israeli @ 2012-04-05 09:39 UTC (permalink / raw)
To: Dave Crooke <dcrooke@gmail.com>; Claudio Freire <klaussfreire@gmail.com>; +Cc: Samuel Gendler <sgendler@ideasculptor.com>; Andrew Dunstan <andrew@dunslane.net>; Andy <angelflow@yahoo.com>; pgsql-performance
On Tue, Apr 3, 2012 at 7:04 PM, Dave Crooke <dcrooke@gmail.com> wrote:
>On Tue, Apr 3, 2012 at 10:38 AM, Claudio Freire <klaussfreire@gmail.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
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2012-04-05 09:39 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-04-01 20:24 TCP Overhead on Local Loopback Ofer Israeli <oferi@checkpoint.com>
2012-04-01 22:01 ` Andy <angelflow@yahoo.com>
2012-04-01 23:54 ` Andrew Dunstan <andrew@dunslane.net>
2012-04-02 00:29 ` Claudio Freire <klaussfreire@gmail.com>
2012-04-02 01:11 ` Andrew Dunstan <andrew@dunslane.net>
2012-04-02 08:25 ` Samuel Gendler <sgendler@ideasculptor.com>
2012-04-03 15:24 ` Ofer Israeli <oferi@checkpoint.com>
2012-04-03 15:38 ` Claudio Freire <klaussfreire@gmail.com>
2012-04-03 16:04 ` Dave Crooke <dcrooke@gmail.com>
2012-04-05 09:39 ` Ofer Israeli <oferi@checkpoint.com>
2012-04-02 11:34 ` Andrew Dunstan <andrew@dunslane.net>
2012-04-01 23:48 ` Rob Wultsch <wultsch@gmail.com>
2012-04-02 16:51 ` Jeff Janes <jeff.janes@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox