Re: [PERFORM] Decreasing BLKSZ

2006-09-26 Thread Jim Nasby
On Sep 26, 2006, at 5:36 PM, Marc Morin wrote: 1- partitions loaded without indexes on them.. And build index "when partition is full". Slow to drill down into incomplete partitions. 2- paritions with index as loaded. Slow, on insert (problem mentioned) but good to drill down How big a

Re: [PERFORM] Decreasing BLKSZ

2006-09-26 Thread Markus Schaber
Hi, Marc, Marc Morin wrote: >> I wonder whether there is a way to use table partitioning to >> make the insert pattern more localized? We'd need to know a >> lot more about your insertion patterns to guess how, though. > > We're doing partitioning as well. And is constraint exclusion set

Re: [PERFORM] Decreasing BLKSZ

2006-09-26 Thread Bucky Jordan
> > So, I'd like my cake and eat it too... :-) > > I'd like to have my indexes built as rows are inserted into the > partition so help with the drill down... > So you want to drill down so fine grained that summary tables don't do much good? Keep in mind, even if you roll up only two records, th

Re: [PERFORM] Decreasing BLKSZ

2006-09-26 Thread Marc Morin
2006 5:26 PM > To: Marc Morin; Tom Lane > Cc: Markus Schaber; pgsql-performance@postgresql.org > Subject: RE: [PERFORM] Decreasing BLKSZ > > > > The bottom line here is likely to be "you need more RAM" :-( > > > > Yup. Just trying to get a handle on wha

Re: [PERFORM] Decreasing BLKSZ

2006-09-26 Thread Bucky Jordan
> > The bottom line here is likely to be "you need more RAM" :-( > > Yup. Just trying to get a handle on what I can do if I need more than > 16G > Of ram... That's as much as I can put on the installed based of > servers 100s of them. > > > > > I wonder whether there is a way to use table pa

Re: [PERFORM] Decreasing BLKSZ

2006-09-25 Thread Marc Morin
> > The bottom line here is likely to be "you need more RAM" :-( Yup. Just trying to get a handle on what I can do if I need more than 16G Of ram... That's as much as I can put on the installed based of servers 100s of them. > > I wonder whether there is a way to use table partitioning t

Re: [PERFORM] Decreasing BLKSZ

2006-09-25 Thread Mark Lewis
I'm not sure if decreasing BLKSZ is the way to go. It would allow you to have more smaller blocks in memory, but the actual coverage of the index would remain the same; if only 33% of the index fits in memory with the 8K BLKSZ then only 33% would fit in memory with a 4k BLKSZ. I can see where you

Re: [PERFORM] Decreasing BLKSZ

2006-09-25 Thread Tom Lane
"Marc Morin" <[EMAIL PROTECTED]> writes: > No, an insert consists of roughly 10,000+ rows per transaction block. Perhaps it would help to pre-sort these rows by key? Like Markus, I'm pretty suspicious of lowering BLCKSZ ... you can try it but it's likely to prove counterproductive (more btree i

Re: [PERFORM] Decreasing BLKSZ

2006-09-25 Thread Marc Morin
> Would it be possible to change the primary key to > (logtime,key)? This could help keeping the "working window" small. No, the application accessing the data wants all the rows between start and end time for a particular key value. > > Secondly, the real working set is smaller, as the rows

Re: [PERFORM] Decreasing BLKSZ

2006-09-25 Thread Markus Schaber
Hi, Marc, Marc Morin wrote: > The problem is, the insert pattern has low correlation with the > (key,logtime) index. In this case, would need >1M blocks in my > shared_buffer space to prevent a read-modify-write type of pattern > happening during the inserts (given a large enough database). Wo

[PERFORM] Decreasing BLKSZ

2006-09-25 Thread Marc Morin
Our application has a number of inserters posting rows of network statistics into a database.  This is occuring continously.  The following is an example of a stats table (simplified but maintains key concepts).     CREATE TABLE stats (   logtime timestamptz,   key int,   s