pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Tryint to match Solaris-Oracle performance with directio?
6+ messages / 6 participants
[nested] [flat]

* Tryint to match Solaris-Oracle performance with directio?
@ 2004-09-17 19:23 Mischa Sandberg <ischamay.andbergsay@activestateway.com>
  2004-09-17 23:42 ` Re: Tryint to match Solaris-Oracle performance with directio? Gaetano Mendola <mendola@bigfoot.com>
  2004-09-18 16:35 ` Re: Tryint to match Solaris-Oracle performance with directio? Tom Lane <tgl@sss.pgh.pa.us>
  2004-09-18 17:02 ` Re: Tryint to match Solaris-Oracle performance with directio? Shachar Shemesh <psql@shemesh.biz>
  2004-09-19 10:04 ` Re: Tryint to match Solaris-Oracle performance with markir@coretech.co.nz
  0 siblings, 4 replies; 6+ messages in thread

From: Mischa Sandberg @ 2004-09-17 19:23 UTC (permalink / raw)
  To: pgsql-performance

Our product (Sophos PureMessage) runs on a Postgres database.

Some of our Solaris customers have Oracle licenses, and they've 
commented on the performance difference between Oracle and Postgresql
on such boxes. In-house, we've noticed the 2:1 (sometimes 5:1)
performance difference in inserting rows (mostly 2-4K), between
Postgresql on Solaris 8 and on Linux, for machines with comparable
CPU's and RAM.

These (big) customers are starting to ask, why don't we just port our 
dataserver to Oracle for them? I'd like to avoid that, if possible :-)

What we can test on, in-house are leetle Sun workstations, while some of 
our customers have BIG Sun iron --- so I have no means to-date to 
reproduce what their bottleneck is :-( Yes, it has been recommended that 
we talk to Sun about their iForce test lab ... that's in the pipe.

In the meantime, what I gather from browsing mail archives is that 
postgresql on Solaris seems to get hung up on IO rather than CPU.
Furthermore, I notice that Oracle and now MySQL use directio to bypass 
the system cache, when doing heavy writes to the disk; and Postgresql 
does not.

Not wishing to alter backend/store/file for this test, I figured I could 
get a customer to mount the UFS volume for pg_xlog  with the option 
"forcedirectio".

Any comment on this? No consideration of what the wal_sync_method is at 
this point. Presumably it's defaulting to fdatasync on Solaris.

BTW this is Postgres 7.4.1, and our customers are Solaris 8 and 9.



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Tryint to match Solaris-Oracle performance with directio?
  2004-09-17 19:23 Tryint to match Solaris-Oracle performance with directio? Mischa Sandberg <ischamay.andbergsay@activestateway.com>
@ 2004-09-17 23:42 ` Gaetano Mendola <mendola@bigfoot.com>
  2004-09-18 01:22   ` Re: Tryint to match Solaris-Oracle performance with directio? mudfoot@rawbw.com
  3 siblings, 1 reply; 6+ messages in thread

From: Gaetano Mendola @ 2004-09-17 23:42 UTC (permalink / raw)
  To: ischamay.andbergsay@activestateway.com

Mischa Sandberg wrote:
> In the meantime, what I gather from browsing mail archives is that 
> postgresql on Solaris seems to get hung up on IO rather than CPU.
> Furthermore, I notice that Oracle and now MySQL use directio to bypass 
> the system cache, when doing heavy writes to the disk; and Postgresql 
> does not.
> 
> Not wishing to alter backend/store/file for this test, I figured I could 
> get a customer to mount the UFS volume for pg_xlog  with the option 
> "forcedirectio".
> 
> Any comment on this? No consideration of what the wal_sync_method is at 
> this point. Presumably it's defaulting to fdatasync on Solaris.
> 
> BTW this is Postgres 7.4.1, and our customers are Solaris 8 and 9.

If you care your data upgrade to more recent 7.4.5

Test your better sync method using /src/tools/fsync  however do some
experiment changing the sync method, you can also avoid to update the
acces time for the inodes mounting the partition with noatime option
( this however have more impact on performance for read activities )


Regards
Gaetano Mendola





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Tryint to match Solaris-Oracle performance with directio?
  2004-09-17 19:23 Tryint to match Solaris-Oracle performance with directio? Mischa Sandberg <ischamay.andbergsay@activestateway.com>
  2004-09-17 23:42 ` Re: Tryint to match Solaris-Oracle performance with directio? Gaetano Mendola <mendola@bigfoot.com>
@ 2004-09-18 01:22   ` mudfoot@rawbw.com
  0 siblings, 0 replies; 6+ messages in thread

From: mudfoot@rawbw.com @ 2004-09-18 01:22 UTC (permalink / raw)
  To: pgsql-performance

I fully agree with Gaetano about testing sync methods.  From testing I've done
on two different Solaris 8 boxes, the O_DSYNC option on Solaris 8 beats fsync
and fdatasync easily.  Test it yourself though.  There's probably some
opportuntiy there for better performance for you.

> > BTW this is Postgres 7.4.1, and our customers are Solaris 8 and 9.
> 
> If you care your data upgrade to more recent 7.4.5
> 
> Test your better sync method using /src/tools/fsync  however do some
> experiment changing the sync method, you can also avoid to update the
> acces time for the inodes mounting the partition with noatime option
> ( this however have more impact on performance for read activities )
> 
> 
> Regards
> Gaetano Mendola
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Tryint to match Solaris-Oracle performance with directio?
  2004-09-17 19:23 Tryint to match Solaris-Oracle performance with directio? Mischa Sandberg <ischamay.andbergsay@activestateway.com>
@ 2004-09-18 16:35 ` Tom Lane <tgl@sss.pgh.pa.us>
  3 siblings, 0 replies; 6+ messages in thread

From: Tom Lane @ 2004-09-18 16:35 UTC (permalink / raw)
  To: ischamay.andbergsay@activestateway.com; +Cc: pgsql-performance

Mischa Sandberg <ischamay.andbergsay@activestateway.com> writes:
> Our product (Sophos PureMessage) runs on a Postgres database.
> Some of our Solaris customers have Oracle licenses, and they've 
> commented on the performance difference between Oracle and Postgresql
> on such boxes. In-house, we've noticed the 2:1 (sometimes 5:1)
> performance difference in inserting rows (mostly 2-4K), between
> Postgresql on Solaris 8 and on Linux, for machines with comparable
> CPU's and RAM.

You haven't given any evidence at all to say that I/O is where the
problem is.  I think it would be good first to work through the
conventional issues such as configuration parameters, foreign key
problems, etc.  Give us some more detail about the slow INSERT
queries ...

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Tryint to match Solaris-Oracle performance with directio?
  2004-09-17 19:23 Tryint to match Solaris-Oracle performance with directio? Mischa Sandberg <ischamay.andbergsay@activestateway.com>
@ 2004-09-18 17:02 ` Shachar Shemesh <psql@shemesh.biz>
  3 siblings, 0 replies; 6+ messages in thread

From: Shachar Shemesh @ 2004-09-18 17:02 UTC (permalink / raw)
  To: ischamay.andbergsay@activestateway.com; +Cc: pgsql-performance

Mischa Sandberg wrote:

> In the meantime, what I gather from browsing mail archives is that 
> postgresql on Solaris seems to get hung up on IO rather than CPU.

Well, people more knowledgeable in the secrets of postgres seem 
confident that this is not your problem. Fortunetly, however, there is a 
simple way to find out.

Just download the utinyint var type from pgfoundry 
(http://pgfoundry.org/projects/sql2pg/). There are some stuff there you 
will need to compile yourself from CVS. I'm sorry, but I haven't done a 
proper release just yet. In any case, the utinyint type should provide 
you with the data type you seek, and thus allow you to find out whether 
this is, indeed, the problem.

-- 
Shachar Shemesh
Lingnu Open Source Consulting ltd.
http://www.lingnu.com/




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Tryint to match Solaris-Oracle performance with
  2004-09-17 19:23 Tryint to match Solaris-Oracle performance with directio? Mischa Sandberg <ischamay.andbergsay@activestateway.com>
@ 2004-09-19 10:04 ` markir@coretech.co.nz
  3 siblings, 0 replies; 6+ messages in thread

From: markir@coretech.co.nz @ 2004-09-19 10:04 UTC (permalink / raw)
  To: ischamay.andbergsay@activestateway.com; +Cc: pgsql-performance

Hi Mischa,

You probably need to determine whether the bottleneck is cpu or disk (should be
easy enough!)

Having said that, assuming your application is insert/update intensive I would
recommend:

- mount the ufs filesystems Pg uses *without* logging
- use postgresql.conf setting fsync_method=fdatasync

These changes made my Pgbench results improve by a factor or 4 (enough to catch
the big O maybe...)

Then you will need to have a look at your other postgresql.conf parameters!
(posting this file to the list might be a plan)

Cheers

Mark



Quoting Mischa Sandberg <ischamay.andbergsay@activestateway.com>:

> Our product (Sophos PureMessage) runs on a Postgres database.
>
> Some of our Solaris customers have Oracle licenses, and they've
> commented on the performance difference between Oracle and Postgresql
> on such boxes. In-house, we've noticed the 2:1 (sometimes 5:1)
> performance difference in inserting rows (mostly 2-4K), between
> Postgresql on Solaris 8 and on Linux, for machines with comparable
> CPU's and RAM.
>
>




^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2004-09-19 10:04 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-09-17 19:23 Tryint to match Solaris-Oracle performance with directio? Mischa Sandberg <ischamay.andbergsay@activestateway.com>
2004-09-17 23:42 ` Gaetano Mendola <mendola@bigfoot.com>
2004-09-18 01:22   ` mudfoot@rawbw.com
2004-09-18 16:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-09-18 17:02 ` Shachar Shemesh <psql@shemesh.biz>
2004-09-19 10:04 ` Re: Tryint to match Solaris-Oracle performance with markir@coretech.co.nz

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