Subject: Re: Insert performance
From: Hannu Krosing <hannu@tm.ee>
To: shridhar_daithankar@persistent.co.in
Cc: pgsql-performance@postgresql.org
In-Reply-To: <3F4145EB.20645.B5452D9@localhost>
References: <3F40BDC4.1605.9407ADF@localhost>
	<3F4145EB.20645.B5452D9@localhost>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Message-Id: <1061245017.2620.14.camel@fuji.krosing.net>
Mime-Version: 1.0
Date: Tue, 19 Aug 2003 01:16:58 +0300

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