[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
Re: [PERFORM] TCP Overhead on Local Loopback
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 of...@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
Re: [PERFORM] TCP Overhead on Local Loopback
On Sun, Apr 1, 2012 at 1:24 PM, Ofer Israeli of...@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 wult...@gmail.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] TCP Overhead on Local Loopback
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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] TCP Overhead on Local Loopback
On Sun, Apr 1, 2012 at 8:54 PM, Andrew Dunstan and...@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. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] TCP Overhead on Local Loopback
On 04/01/2012 08:29 PM, Claudio Freire wrote: On Sun, Apr 1, 2012 at 8:54 PM, Andrew Dunstanand...@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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
