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.

Search Discussions

  • Gaetano Mendola at Sep 18, 2004 at 12:07 am

    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
  • Mudfoot at Sep 18, 2004 at 1:22 am
    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)
  • Tom Lane at Sep 18, 2004 at 4:35 pm

    Mischa Sandberg 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
  • Shachar Shemesh at Sep 18, 2004 at 5:03 pm

    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/
  • Markir at Sep 19, 2004 at 10:04 am
    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.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-performance @
categoriespostgresql
postedSep 17, '04 at 7:37p
activeSep 19, '04 at 10:04a
posts6
users6
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase