[PERFORM] Vacuum looping?

2007-07-27 Thread Steven Flatt
Postgres 8.2.4. We have a large table, let's call it "foo", whereby an automated process periodically inserts many (hundreds of thousands or millions) rows into it at a time. It's essentially INSERT INTO foo SELECT FROM WHERE . Recently, for whatever reason, the query started to run out of memo

Re: [PERFORM] User concurrency thresholding: where do I look?

2007-07-27 Thread Jignesh K. Shah
Using CLOG Buffers 32 and the commit sibling check patch I still see a drop at 1200-1300 users.. bash-3.00# ./4_lwlock_waits.d 18250 Lock IdMode Count XidGenLock Shared 1 CLogControlLock Shared 2

Re: [PERFORM] User concurrency thresholding: where do I look?

2007-07-27 Thread Jignesh K. Shah
I tried CLOG Buffers 32 and the performance is as good as 64 bit.. (I havent tried 16 yet though.. ) I am going to try your second patch now.. Also here is the breakup by Mode. The combined time is the total time it waits for all counts. Lock IdMode Count

Re: [PERFORM] Slow query with backwards index scan

2007-07-27 Thread Nis Jørgensen
Tilmann Singer skrev: > The query works fine for the common cases when matching rows are found > early in the sorted large table, like this: > > testdb=# EXPLAIN ANALYZE > SELECT * FROM large_table lt > LEFT JOIN relationships r ON lt.user_id=r.contact_id > WHERE r.user_id = 5 OR lt.user_id =

Re: [PERFORM] How to use a trigger to write rows to a remote server

2007-07-27 Thread Merlin Moncure
On 7/18/07, Michael Dengler <[EMAIL PROTECTED]> wrote: > Hmm..I was hoping to avoid personal insults > > Anyway, Nuts or not...what I am attempting is to simply have row from one > table inserted into another servers DB I don't see it as replication > because: I think you took Tom's comments t

[PERFORM] Slow query with backwards index scan

2007-07-27 Thread Tilmann Singer
Dear list, I am having problems selecting the 10 most recent rows from a large table (4.5M rows), sorted by a date column of that table. The large table has a column user_id which either should match a given user_id, or should match the column contact_id in a correlated table where the user_id of

Re: [PERFORM] disk filling up

2007-07-27 Thread Merlin Moncure
On 7/26/07, Joshua D. Drake <[EMAIL PROTECTED]> wrote: > Brandon Shalton wrote: > > Hello all, > > > > My hard disk is filling up in the /base directory to where it has > > consumed all 200gig of that drive. > > > > All the posts that i see keep saying move to a bigger drive, but at some > > poin

Re: [PERFORM] User concurrency thresholding: where do I look?

2007-07-27 Thread Jignesh K. Shah
Yes I can try to breakup the Shared and exclusive time.. Also yes I use commit delays =10, it helps out a lot in reducing IO load.. I will try out the patch soon. -Jignesh Simon Riggs wrote: On Thu, 2007-07-26 at 17:17 -0400, Jignesh K. Shah wrote: Lock Id Combined Time (n

Re: [PERFORM] User concurrency thresholding: where do I look?

2007-07-27 Thread Simon Riggs
On Fri, 2007-07-27 at 04:58 -0400, Alvaro Herrera wrote: > Jignesh K. Shah wrote: > > > Top Wait time seems to come from the following code path for > > ProcArrayLock: > > > > Lock IdMode Count > > ProcArrayLock Exclusive 21 > > > >

Re: [PERFORM] User concurrency thresholding: where do I look?

2007-07-27 Thread Simon Riggs
On Thu, 2007-07-26 at 17:17 -0400, Jignesh K. Shah wrote: > Lock Id Combined Time (ns) > XidGenLock194966200 >WALInsertLock517955000 > CLogControlLock679665100 > WALWriteLock 2838716200 >ProcArrayLoc

Re: [PERFORM] User concurrency thresholding: where do I look?

2007-07-27 Thread Alvaro Herrera
Jignesh K. Shah wrote: > Top Wait time seems to come from the following code path for > ProcArrayLock: > > Lock IdMode Count > ProcArrayLock Exclusive 21 > > Lock Id Combined Time (ns) > ProcArrayLock 52559