pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Richard Huxton <dev@archonet.com>
To: Hiltibidal, Robert <Robert.Hiltibidal@argushealth.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Index ANDing & Index ORing
Date: Tue, 06 Feb 2007 14:05:28 +0000
Message-ID: <45C88B28.1010503@archonet.com> (raw)
In-Reply-To: <A6C93EF9812C8346A8E8BD6AE573E52D055B97C6@ARG-EXVS01.corp.argushealth.com>
References: <A6C93EF9812C8346A8E8BD6AE573E52D055B97C6@ARG-EXVS01.corp.argushealth.com>

Hiltibidal, Robert wrote:
> Keep in mind that 2/3 of the inserts are actual log entries. 1/3 are for
> supporting information like ip, query string values etc. 
> 
> The table is a "star" table.
> 
> You are correct tho in terms of time.. When I did the same application
> with fpc pascal (http://www.freepascal.org) and MS SQL 2000 I averaged
> over 700 inserts a second on a Compaq DL380 with 2 gb ram and dual 1.3
> ghz processors. 15000 rpm scsi hard drives in a raid 5 config. 
> 
> The different there is MS SQL 2000 allows transaction logging to be
> turned off. I think the transaction logging presents a "speed bump"

If you can batch things into transactions of 2+ inserts (up to say 5000) 
you'll find things go much faster. The limiting factor should be (as you 
say) the speed of committing the transaction log.

Failing that, try having the WAL on its own disks, with battery-backed 
write cache too.

Failing that, you can turn fsync off, but don't complain if the power 
fails and your database gets corrupted.

-- 
   Richard Huxton
   Archonet Ltd



view thread (4+ messages)

Message-ID: <45C88B28.1010503@archonet.com>
Permalink:  ../45C88B28.1010503@archonet.com/
Also on:    postgresql.org/message-id/45C88B28.1010503@archonet.com

 · 

reply
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-sql@postgresql.org
  Cc: dev@archonet.com, Robert.Hiltibidal@argushealth.com
  Subject: Re: Index ANDing & Index ORing
  In-Reply-To: <45C88B28.1010503@archonet.com>

* 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