Re: Insert performance

Shridhar Daithankar kirjutas E, 18.08.2003 kell 19:02:
> On 18 Aug 2003 at 18:52, Hannu Krosing wrote:
> > My own experimentation also got numbers in 9k/sec range (on a quad
> > 1.3GHz Xeons, 2GM mem, 50MB/sec raid) when doing 10-20 parallel runs of
> > ~1000 inserts/transaction.
> >
> > Performance dropped to ~300/sec (at about 60M rows) when I added an
> > index (primary key) - as I did random inserts, the hit rates for index
> > pages were probably low.
>
> I was loading a geographic data couple of months back.. It was 3GB data when
> loaded in postgresql.

With or without indexes ?

> I tried loading data first and creating index later. It ran out of available
> 9GB space. So I created index on an empty table and started loading it. It was
> slow but at least finished after 3 hours... Co-incidentally oracle had same
> problems as well. So creating index beforehand remains only option at times, it
> seems. Tom remarked that it shouldn't have made difference but apparently it
> does..

Tom just fixed some memory leaks on array indexing the other day. Could
there be something like that on geographic types ?

> You mentioned parallel runs and still getting 9K/sec. Was that overall 9K or
> per connection?

Overall. But notice that my setup was (a little) slower per processor.

> If it is former, probably WAL is hit too hard. You could do
> some additional testing by having WALit's own disk.

I guess that todays IDE disks are about the same speed (~50MB/sec) as my
test RAID was.

I run multiple parallel runs to have a chance to use all 4 processors
(but IIRC it was heavyly IO-bound) as well as to better use writing time
on WAL platters (not to wait for full rotation on each platter)

--------------
Hannu