From: Craig A. James <cjames@modgraph-usa.com>
To: Guy Rouillier <guyr-ml1@burntmail.com>
Cc: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: High update activity, PostgreSQL vs BigDBMS
Date: Fri, 05 Jan 2007 19:14:41 -0800
Message-ID: <459F1421.9090905@modgraph-usa.com> (raw)
In-Reply-To: <459F0EA6.30403@burntmail.com>
References: <45935A49.6080602@burntmail.com>
<bf54be870612280106n1247520dtb48e10cb4bdc43e9@mail.gmail.com>
<459F0EA6.30403@burntmail.com>
Guy Rouillier wrote:
> I've got back access to my test system. I ran another test run with the
> same input data set. This time I put pg_xlog on a different RAID volume
> (the unused one that I suspect is a software RAID), and I turned
> fsync=off in postgresql.conf. I left the rest of the configuration
> alone (all foreign keys removed), etc. Unfortunately, this only dropped
> elapsed time down to about 28000 seconds (from 30000), still
> significantly more than BigDBMS. Additional info inline below.
Although tuning is extremely important, you also have to look at the application itself. I discovered (the hard way) that there's simply no substitute for a bit of redesign/rewriting of the schema and/or SQL statements.
Many of us who "grew up" on Oracle assume that their SQL is standard stuff, and that Oracle's optimizer is "the way it's done." But in fact most Oracle applications are tweaked and tuned to take advantage of Oracle's strengths and avoid its weaknesses. If you designed an application from the ground up to use Postgres, then migrated to Oracle, you would probably be equally frustrated by Oracle's poor performance on your Postgres-tuned application.
I don't know if you have access to the application's SQL, or the time to experiment a bit, but unless your schema is trival and your SQL is boneheaded simple, you're not going to get equal performance from Postgres until you do some analysis of your application under real-world conditions, and optimize the problem areas.
In my case, I found just a few specific SQL constructs that, with a bit of tuning, made massive differences in performance.
Craig
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: cjames@modgraph-usa.com, guyr-ml1@burntmail.com
Subject: Re: High update activity, PostgreSQL vs BigDBMS
In-Reply-To: <459F1421.9090905@modgraph-usa.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