From: Hannu Krosing <hannu@tm.ee>
To: shridhar_daithankar@persistent.co.in
Cc: pgsql-performance@postgresql.org
Subject: Re: Insert performance
Date: Tue, 19 Aug 2003 01:16:58 +0300
Message-ID: <1061245017.2620.14.camel@fuji.krosing.net> (raw)
In-Reply-To: <3F4145EB.20645.B5452D9@localhost>
References: <3F40BDC4.1605.9407ADF@localhost>
<3F4145EB.20645.B5452D9@localhost>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: hannu@tm.ee, shridhar_daithankar@persistent.co.in
Subject: Re: Insert performance
In-Reply-To: <1061245017.2620.14.camel@fuji.krosing.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox