Skip site navigation (1) Skip section navigation (2)

Re: Getting even more insert performance (250m+rows/day)

From: "Larry Rosenman" <lrosenman(at)pervasive(dot)com>
To: "Daniel J(dot) Luke" <dluke(at)geeklair(dot)net>,"Dave Dutcher" <dave(at)tridecap(dot)com>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Getting even more insert performance (250m+rows/day)
Date: 2006-05-24 20:12:26
Message-ID: F6616E0E81AC0841B1F9DD252F7C4B55041A9A@ausmaildd.aus.pervasive.com (view raw or flat)
Thread:
Lists: pgsql-performance
Daniel J. Luke wrote:
> On May 24, 2006, at 4:02 PM, Dave Dutcher wrote:
>> If you can live with possible database corruption, you could try
>> turning Fsync off.  For example if you could just reinsert the data
>> on the off chance a hardware failure corrupts the database, you
>> might get a decent improvement.
> 
> I tried, but I didn't see much of an improvement (and it's not really
> acceptable for this application).
> 
>> Also have you tried creating the index after you have inserted all
>> your data?  (Or maybe copy already disables the indexes while
>> inserting?) 
> 
> The data gets inserted in batches every 5 minutes and I potentially
> have people querying it constantly, so I can't remove and re-create
> the index.
> 
are the batches single insert's, or within a big transaction?

I.E., does the inserts look like:
INSERT
INSERT
INSERT

or

BEGIN
INSERT
INSERT
INSERT
COMMIT

If the former, the latter is a big win.

Also, what release(s) are you running?

LER

-- 
Larry Rosenman		
Database Support Engineer

PERVASIVE SOFTWARE. INC.
12365B RIATA TRACE PKWY
3015
AUSTIN TX  78727-6531 

Tel: 512.231.6173
Fax: 512.231.6597
Email: Larry(dot)Rosenman(at)pervasive(dot)com
Web: www.pervasive.com 

Responses

pgsql-performance by date

Next:From: Steinar H. GundersonDate: 2006-05-24 20:13:50
Subject: Re: Getting even more insert performance (250m+rows/day)
Previous:From: Daniel J. LukeDate: 2006-05-24 20:09:54
Subject: Re: Getting even more insert performance (250m+rows/day)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group