Re: [PERFORM] TCP Overhead on Local Loopback

2012-04-05 Thread Ofer Israeli
On Tue, Apr 3, 2012 at 7:04 PM, Dave Crooke wrote: >On Tue, Apr 3, 2012 at 10:38 AM, Claudio Freire 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 l

Re: [PERFORM] TCP Overhead on Local Loopback

2012-04-03 Thread Ofer Israeli
On Sun, Apr 2, 2012 at 11:25 AM, Samuel Gendler < sgend...@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 / met

[PERFORM] TCP Overhead on Local Loopback

2012-04-01 Thread Ofer Israeli
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 databas

Re: [PERFORM] Insertions slower than Updates?

2012-02-20 Thread Ofer Israeli
Ofer Israeli wrote: >Hi all, > In performance testing we're doing we are currently running two scenarios: > 1. Starting from an empty db, therefore all operations are INSERTs. > 2. Starting from an existing db - thereby UPDATing all records. > I should also mention th

Re: [PERFORM] Insertions slower than Updates?

2012-02-20 Thread Ofer Israeli
Kevin Grittner wrote: > Ofer Israeli wrote: >> Kevin Grittner wrote: >>> Ofer Israeli wrote: >>>> Anyone have any ideas on why the empty db is giving worse results?? >>> >>> Besides the HOT updates being fast, there is the issue of having >&g

Re: [PERFORM] Insertions slower than Updates?

2012-02-20 Thread Ofer Israeli
Kevin Grittner wrote: > Ofer Israeli wrote: >> Anyone have any ideas on why the empty db is giving worse results?? > > Besides the HOT updates being fast, there is the issue of having > space already allocated and ready for the database to use, rather > than needing to ma

[PERFORM] Insertions slower than Updates?

2012-02-20 Thread Ofer Israeli
Hi all, In performance testing we're doing we are currently running two scenarios: 1. Starting from an empty db, therefore all operations are INSERTs. 2. Starting from an existing db - thereby UPDATing all records. I should also mention that the tables we are dealing with are heavily indexed

Re: [PERFORM] Inserts or Updates

2012-02-12 Thread Ofer Israeli
Frank Lanitz wrote: > Am 12.02.2012 11:48, schrieb Ofer Israeli: >> Frank Lanitz wrote: >>>> Am 07.02.2012 18:40, schrieb Ofer Israeli: >>>>>> Table 1: 46 columns 23 indexes on fields of the following >>>>>> types: INTEGER - 7 TIMESTA

Re: [PERFORM] Inserts or Updates

2012-02-12 Thread Ofer Israeli
Frank Lanitz wrote: > Am 07.02.2012 18:40, schrieb Ofer Israeli: >> Table 1: >> 46 columns >> 23 indexes on fields of the following types: >> INTEGER - 7 >> TIMESTAMP - 2 >> VARCHAR - 12 >> UUID - 2 >> >> 23 columns >> 12 indexes on

Re: [PERFORM] Vacuuming problems on TOAST table

2012-02-08 Thread Ofer Israeli
Tom Lane wrote: > Ofer Israeli writes: >> During our testing we see that the table size increases >> substantially. When looking at the autovacuum log, set with default >> configuration, it seems that it ran for around 60 seconds (see below >> and note that this was

[PERFORM] Vacuuming problems on TOAST table

2012-02-08 Thread Ofer Israeli
Hi all, We have a 4-columned table that is also split up into a TOAST table, where the TOASTed entries are ~35KB each. The table size is 10K records. The table is updated at a rate of ~100 updates a minute. During our testing we see that the table size increases substantially. When looking at

Re: [PERFORM] Inserts or Updates

2012-02-08 Thread Ofer Israeli
Andy Colson wrote: > Oh, I knew I'd seen index usage stats someplace. > > give this a run: > > select * from pg_stat_user_indexes where relname = 'SuperBigTable'; > > http://www.postgresql.org/docs/current/static/monitoring-stats.html > > -Andy > > Scanned by Check Point Total Security Gateway

Re: [PERFORM] Inserts or Updates

2012-02-08 Thread Ofer Israeli
Claudio Freire wrote: > On Tue, Feb 7, 2012 at 4:12 PM, Ofer Israeli > wrote: >> Something specific that you refer to in autovacuum's non-perfection, >> that is, what types of issues are you aware of? > > I refer to its criteria for when to perform vacuum/anal

Re: [PERFORM] Inserts or Updates

2012-02-07 Thread Ofer Israeli
>> You mean running a VACUUM statement manually?  I would basically try to >> avoid such a situation as the way I see it, the database should be >> configured in such a manner that it will be able to handle the load at >> any given moment and so I wouldn't want to manually intervene here.  If >>

Re: [PERFORM] Inserts or Updates

2012-02-07 Thread Ofer Israeli
erformance@postgresql.org Subject: Re: [PERFORM] Inserts or Updates On Tue, Feb 7, 2012 at 2:27 PM, Ofer Israeli wrote: > Thanks Kevin for the ideas.  Now that you have corrected our misconception > regarding the autovacuum not handling index bloating, we are looking into > running autovacuum freque

Re: [PERFORM] Inserts or Updates

2012-02-07 Thread Ofer Israeli
, February 07, 2012 4:47 PM To: Ofer Israeli Cc: pgsql-performance@postgresql.org; Olga Vingurt; Netta Kabala Subject: Re: [PERFORM] Inserts or Updates On 2/7/2012 4:18 AM, Ofer Israeli wrote: > Hi all, > > We are currently "stuck" with a performance bottleneck in our server &g

Re: [PERFORM] Inserts or Updates

2012-02-07 Thread Ofer Israeli
p our transactions short enough not to reach the situation where vacuum full or CLUSTER is needed. Thanks, Ofer -Original Message- From: Kevin Grittner [mailto:kevin.gritt...@wicourts.gov] Sent: Tuesday, February 07, 2012 2:28 PM To: Ofer Israeli; pgsql-performance@postgresql.org Cc: Netta Kabala;

[PERFORM] Inserts or Updates

2012-02-07 Thread Ofer Israeli
Hi all, We are currently "stuck" with a performance bottleneck in our server using PG and we are thinking of two potential solutions which I would be happy to hear your opinion about. Our system has a couple of tables that hold client generated information. The clients communicate every minut