Re: [PERFORM] Guidance Requested - Bulk Inserting + Queries

2011-11-30 Thread Leonardo Francalanci
> We now found (thanks Andres and Snow-Man in #postgresql) that in our > tests, after the indexes get too large performance drops signficantly > and our system limps forward due to  disk reads (presumably for the > indexes).  If we remove the indexes, performance for our entire sample > test is gre

Re: [PERFORM] Adding more memory = hugh cpu load

2011-10-11 Thread Leonardo Francalanci
> checkpoint_completion_targets spreads out the writes to disk.  PostgreSQL  > doesn't make any attempt yet to spread out the sync calls.  On a busy > server, what can happen is that the whole OS write cache fills with dirty > data--none of which is written out to disk because of the high kernel

Re: [PERFORM] Adding more memory = hugh cpu load

2011-10-10 Thread Leonardo Francalanci
> Then the  > database makes the fsync call, and suddenly the OS wants to flush 2-6GB of > data > straight to disk. Without that background trickle, you now have a flood that > only the highest-end disk controller or a backing-store full of SSDs or PCIe > NVRAM could ever hope to absorb. Isn

Re: [PERFORM] Adding more memory = hugh cpu load

2011-10-10 Thread Leonardo Francalanci
> That's not entirely surprising. The problem with having lots of memory is... > that you have lots of memory. The operating system likes to cache, and this > includes writes. Normally this isn't a problem, but with 48GB of RAM, the > defaults (for CentOS 5.5 in particular) are to use up to 40

Re: [PERFORM] : Performance Improvement Strategy

2011-10-10 Thread Leonardo Francalanci
>>        * Allow CLUSTER to sort the table rather than scanning the index  > when it seems likely to be cheaper (Leonardo Francalanci) > > Looks like I owe Leonardo Francalanci a pizza. Well, the patch started from a work by Gregory Stark, and Tom fixed a nasty bug; but I&

Re: [PERFORM] Hash index use presently(?) discouraged since 2005: revive or bury it?

2011-09-14 Thread Leonardo Francalanci
>> Hash indexes have been improved since 2005 - their performance was >> improved quite a bit in 9.0. Here's a more recent analysis: > >> http://www.depesz.com/index.php/2010/06/28/should-you-use-hash-index/ > > The big picture though is that we're not going to remove hash indexes, > even if

Re: [PERFORM] Performance degradation of inserts when database size grows

2011-05-21 Thread Leonardo Francalanci
>1. How does database size affect insert performance? >2. Why does number of written buffers increase when database size grows? It might be related to indexes. Indexes size affect insert performance. >3. How can I further analyze this problem? Try without indexes? -- Sent via pgsql-performa

Re: [PERFORM] Postgres insert performance and storage requirement compared to Oracle

2010-10-26 Thread Leonardo Francalanci
> temp tables are not wal logged or > synced. Periodically they can be flushed to a permanent table. What do you mean with "Periodically they can be flushed to a permanent table"? Just doing insert into tabb select * from temptable or using a proper, per-temporary table command??? --

Re: [PERFORM] Periodically slow inserts

2010-10-22 Thread Leonardo Francalanci
>>> I think this may be caused by GIN's habit of queuing index insertions >>> until it's accumulated a reasonable-size batch: So the fact that it takes 21s to sync 365 buffers in this case is normal? -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make chan

Re: [PERFORM] Periodically slow inserts

2010-10-21 Thread Leonardo Francalanci
> 2010-10-21 16:39:15 CEST LOG: checkpoint complete: wrote 365 buffers > (11.9%); 0 transaction log file(s) added, 0 removed, 3 recycled; > write=0.403 s, sync=21.312 s, total=21.829 s I'm no expert, but isn't 21s to sync 365 buffers a big amount of time? -- Sent via pgsql-performance m

Re: [PERFORM] Periodically slow inserts

2010-10-21 Thread Leonardo Francalanci
> > does it use a BBU?) Sorry, this was supposed to read "do you have cache on the controller", of course a battery can't change the performance... but you got it anyway... -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: h

Re: [PERFORM] Periodically slow inserts

2010-10-21 Thread Leonardo Francalanci
> We are using PostgreSQL for storing data and full-text search indexes > for the webiste of a daily newspaper. We are very happy overall with the > results, but we have one "weird" behaviour that we would like to solve. I think there's a lot of missing info worth knowing: 1) checkpoints