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
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
>
> 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
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
> > 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
>
> 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
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
"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
> 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
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
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
11 matches
Mail list logo
