pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Syd <569170@aliencamel.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: insert speed - Mac OSX vs Redhat
Date: Thu, 15 Jan 2004 10:44:41 -0500
Message-ID: <22333.1074181481@sss.pgh.pa.us> (raw)
In-Reply-To: <A278005E-4762-11D8-BC83-00039398E788@aliencamel.com>
References: <A278005E-4762-11D8-BC83-00039398E788@aliencamel.com>

Syd <569170@aliencamel.com> writes:
> However, with postgres 7.4 on Mac OSX 10.2.3, we're getting an amazing 
> 500 inserts per second.

> We can only put this down to the OS.

As noted elsewhere, it's highly likely that this has nothing to do with
the OS, and everything to do with write caching in the disks being used.

I assume you are benchmarking small individual transactions (one insert
per xact).  In such scenarios it's essentially impossible to commit more
than one transaction per revolution of the WAL disk, because you have to
write the same WAL disk page repeatedly and wait for it to get down to
the platter.  When you get results that are markedly in excess of the
disk RPM figure, it's proof positive that the disk is lying about write
complete (or that you don't have fsync on).

The only way to get better performance and still have genuine ACID
behavior is to gang multiple insertions per WAL write.  You can do
multiple insertions per transaction, or if you are doing several
insertion transactions in parallel, you can try to commit them all in
one write (experiment with the commit_delay and commit_siblings
parameters).

> BTW, on the same hardware that postgres is running on to get 50 inserts 
> per sec, MySQL (4.0.17) is getting an almost unbelievable 5,500 inserts 
> per second.

I'll bet a good lunch that MySQL is not being ACID compliant in this
test.  Are you using a transaction-safe table type (InnoDB) and
committing after every insert?

If you don't in fact care about ACID safety, turn off fsync in Postgres
so that you have an apples-to-apples comparison (or at least
apples-to-oranges rather than apples-to-cannonballs).

			regards, tom lane



view thread (4+ messages)  latest in thread

Message-ID: <22333.1074181481@sss.pgh.pa.us>
Permalink:  ../22333.1074181481@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/22333.1074181481@sss.pgh.pa.us

 · 

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-performance@postgresql.org
  Cc: tgl@sss.pgh.pa.us, 569170@aliencamel.com
  Subject: Re: insert speed - Mac OSX vs Redhat
  In-Reply-To: <22333.1074181481@sss.pgh.pa.us>

* 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