pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedinsert speed - Mac OSX vs Redhat
4+ messages / 3 participants
[nested] [flat]
* insert speed - Mac OSX vs Redhat
@ 2004-01-15 13:56 Syd <569170@aliencamel.com>
2004-01-15 14:17 ` Re: insert speed - Mac OSX vs Redhat Matt Clark <matt@ymogen.net>
2004-01-15 15:44 ` Re: insert speed - Mac OSX vs Redhat Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 4+ messages in thread
From: Syd @ 2004-01-15 13:56 UTC (permalink / raw)
To: pgsql-performance
I've read most of the threads on insert speed in this list and wanted
to share some interesting observations and a question.
We've been benchmarking some dbs to implement Bayesian processing on an
email server. This involves frequent insert and updates to the
following table:
create table bayes_token (
username varchar(200) not null default '',
token varchar(200) not null default '',
spam_count integer not null default 0,
ham_count integer not null default 0,
atime integer not null default 0,
primary key (username, token));
On a variety of hardware with Redhat, and versions of postgres, we're
not getting much better than 50 inserts per second. This is prior to
moving WAL to another disk, and fsync is on.
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.
Can anyone shed light on why Redhat appears to be so much poorer than
Mac OS X in supporting postgres insert transactions? Or why MacOS
appears to be so much better?
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.
-SL
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: insert speed - Mac OSX vs Redhat
2004-01-15 13:56 insert speed - Mac OSX vs Redhat Syd <569170@aliencamel.com>
@ 2004-01-15 14:17 ` Matt Clark <matt@ymogen.net>
1 sibling, 0 replies; 4+ messages in thread
From: Matt Clark @ 2004-01-15 14:17 UTC (permalink / raw)
To: Syd <569170@aliencamel.com>; pgsql-performance
> On a variety of hardware with Redhat, and versions of postgres, we're
> not getting much better than 50 inserts per second. This is prior to
> moving WAL to another disk, and fsync is on.
>
> 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.
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
You haven't really produced much evidence to support that statement. Given that the differences in performance between Postgres
running on *BSD and Linux on Intel hardware are not large at all, it seems to be almost certainly false in fact.
It may of course be due to some settings of the different OSes, but not the OSes themselves.
It would help if you gave a straight PG7.4 comparison with hardware specs as well, and config file differences if any.
One thought: assuming the Apple has IDE disks, then the disks probably have write caching turned on, which is good for speed, but
not crash-safe.
matt
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: insert speed - Mac OSX vs Redhat
2004-01-15 13:56 insert speed - Mac OSX vs Redhat Syd <569170@aliencamel.com>
@ 2004-01-15 15:44 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-01-15 21:17 ` Re: insert speed - Mac OSX vs Redhat Syd <569170@aliencamel.com>
1 sibling, 1 reply; 4+ messages in thread
From: Tom Lane @ 2004-01-15 15:44 UTC (permalink / raw)
To: Syd <569170@aliencamel.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: insert speed - Mac OSX vs Redhat
2004-01-15 13:56 insert speed - Mac OSX vs Redhat Syd <569170@aliencamel.com>
2004-01-15 15:44 ` Re: insert speed - Mac OSX vs Redhat Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-01-15 21:17 ` Syd <569170@aliencamel.com>
0 siblings, 0 replies; 4+ messages in thread
From: Syd @ 2004-01-15 21:17 UTC (permalink / raw)
To: pgsql-performance
On 16/01/2004, at 2:44 AM, Tom Lane wrote:
...
> 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).
>
Tom, thanks for this explanation - we'll check this out straight away,
but it would explain a lot.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2004-01-15 21:17 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-01-15 13:56 insert speed - Mac OSX vs Redhat Syd <569170@aliencamel.com>
2004-01-15 14:17 ` Matt Clark <matt@ymogen.net>
2004-01-15 15:44 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-01-15 21:17 ` Syd <569170@aliencamel.com>
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