headers
Bob Lunney | 1 Apr 2012 04:20
Picon
Favicon

Re: database slowdown while a lot of inserts occur

Tomas,

You are correct.  I was assuming that each insert was issued as an implicit transaction, without the benefit of an explicit BEGIN/COMMIT batching many of them together, as I've seen countless times in tight loops trying to pose as a batch insert.

Bob


From: Tomas Vondra <tv <at> fuzzy.cz>
To: pgsql-performance <at> postgresql.org
Sent: Friday, March 30, 2012 8:11 PM
Subject: Re: [PERFORM] database slowdown while a lot of inserts occur

On 29.3.2012 21:27, Bob Lunney wrote:
> Lance,
>
> May small inserts cause frequent fsyncs.  Is there any way those small
> inserts can be batched into some larger sets of inserts that use copy to
> perform the load?

Not necessarily - fsync happens at COMMIT time, not when the INSERT is
performed (unless each INSERT stands on it's own).

Tomas

--
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


Permalink | Reply | 0 comments |
headers
Ofer Israeli | 1 Apr 2012 22:24
Picon
Favicon

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 | Reply | 12 comments |
headers
Andy | 2 Apr 2012 00:01
Picon
Favicon

Re: TCP Overhead on Local Loopback

You could try using Unix domain socket and see if the performance improves. A relevant link:


From: Ofer Israeli <oferi <at> checkpoint.com>
To: "pgsql-performance <at> postgresql.org" <pgsql-performance <at> 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 | Reply | 9 comments |
headers
Rob Wultsch | 2 Apr 2012 01:48
Picon

Re: TCP Overhead on Local Loopback

On Sun, Apr 1, 2012 at 1:24 PM, Ofer Israeli <oferi <at> 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 <at> gmail.com

--

-- 
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Permalink | Reply | 0 comments |
headers
Andrew Dunstan | 2 Apr 2012 01:54
Gravatar

Re: 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 <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Permalink | Reply | 8 comments |
headers
Claudio Freire | 2 Apr 2012 02:29
Picon
Gravatar

Re: TCP Overhead on Local Loopback

On Sun, Apr 1, 2012 at 8:54 PM, Andrew Dunstan <andrew <at> 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 <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Permalink | Reply | 7 comments |
headers
Andrew Dunstan | 2 Apr 2012 03:11
Gravatar

Re: 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 Dunstan<andrew <at> 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 <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Permalink | Reply | 6 comments |
headers
Віталій Тимчишин | 2 Apr 2012 09:14
Picon

Re: database slowdown while a lot of inserts occur

Few words regarding small inserts and a lot of fsyncs:

If it is your problem, you can fix this by using battery-backed raid card. Similar effect can be  reached by turning synchronious commit off. Note that the latter may make few last commits lost in case of sudden reboot. But you can at least test if moving to BBU will help you. (Dunno if this setting can be changed with SIGHUP without restart).
Note that this may still be a lot of random writes. And in case of RAID5 - a lot of random reads too. I don't think batching will help other applications. This is the tool to help application that uses batching. If you have random writes, look at HOT updates - they may help you if you will follow requirements. 
Check your checkpoints - application writes to commit log first (sequential write), then during checkpoints data is written to tables (random writes) - longer checkpoints may make you life easier. Try to increase checkpoint_segments.
If you have alot of data written - try to move you commit logs to another drive/partition.
If you have good raid card with memory and BBU, you may try to disable read cache on it (leaving only write cache). Read cache is usually good at OS level (with much more memory) and fast writes need BBU-protected write cache.

Best regards, Vitalii Tymchyshyn

2012/3/29 Campbell, Lance <lance <at> illinois.edu>

PostgreSQL 9.0.x

We have around ten different applications that use the same database.  When one particular application is active it does an enormous number of inserts.  Each insert is very small.  During this time the database seems to slow down in general.  The application in question is inserting into a particular table that is not used by the other applications.

 

1)      What should I do to confirm that the database is the issue and not the applications?

2)      How can I identify where the bottle neck is occurring if the issue happens to be with the database?

 

I have been using PostgreSQL for eight years.  It is an amazing database.

 

Thanks,

 

Lance Campbell

Software Architect

Web Services at Public Affairs

217-333-0382

 




--
Best regards,
 Vitalii Tymchyshyn
Permalink | Reply | 0 comments |
headers
Samuel Gendler | 2 Apr 2012 10:25

Re: TCP Overhead on Local Loopback



On Sun, Apr 1, 2012 at 6:11 PM, Andrew Dunstan <andrew <at> 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 <at> 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 | Reply | 4 comments |
headers
Andrew Dunstan | 2 Apr 2012 13:34
Gravatar

Re: TCP Overhead on Local Loopback


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 <at> 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

--

-- 
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Permalink | Reply | 0 comments |

Gmane