I originally posted the question below back in Dec 2006, and many
helpful suggestions resulted. Unfortunately, since this was a closet
effort, my official duties pushed further exploration to the back
burner, then I lost my original test environment. So while I can no
longer compare to
On Tue, Jan 09, 2007 at 09:10:51AM -0800, Jeff Frost wrote:
On Tue, 9 Jan 2007, Jim C. Nasby wrote:
On Thu, Dec 28, 2006 at 02:15:31PM -0800, Jeff Frost wrote:
When benchmarking various options for a new PG server at one of my
clients,
I tried ext2 and ext3 (data=writeback) for the WAL and
On Wed, 10 Jan 2007, Jim C. Nasby wrote:
RAID1 for those of you who have been wondering if the BBU write back cache
mitigates the need for separate WAL (at least on this workload). Those
are
the fastest times for each config, but ext2 WAL was always faster than the
other two options. I didn't
On Thu, Dec 28, 2006 at 02:15:31PM -0800, Jeff Frost wrote:
When benchmarking various options for a new PG server at one of my clients,
I tried ext2 and ext3 (data=writeback) for the WAL and it appeared to be
fastest to have ext2 for the WAL. The winning time was 157m46.713s for
ext2,
On Sun, Jan 07, 2007 at 11:26:01PM -0500, Guy Rouillier wrote:
Ok, I ran with the settings below, but with
shared_buffers=768MB
effective_cache_size=2048MB
fsync=on
This run took 29000 seconds. I'm beginning to think configuration
changes are not going to buy significant additional
On Tue, 9 Jan 2007, Jim C. Nasby wrote:
On Thu, Dec 28, 2006 at 02:15:31PM -0800, Jeff Frost wrote:
When benchmarking various options for a new PG server at one of my clients,
I tried ext2 and ext3 (data=writeback) for the WAL and it appeared to be
fastest to have ext2 for the WAL. The
Craig A. James wrote:
Postgres functions like count() and max() are plug ins which has huge
architectural advantages. But in pre-8.1 releases, there was a big
speed penalty for this: functions like count() were very, very slow,
requiring a full table scan. I think this is vastly improved
Guy Rouillier wrote:
The application is fairly straightforward, but as you say, what is
working okay with BigDBMS isn't working as well under PG. I'm going to
try other configuration suggestions made by others before I attempt
logic changes. The core logic is unchangeable; millions of rows
On 6-Jan-07, at 11:32 PM, Guy Rouillier wrote:
Dave Cramer wrote:
The box has 3 GB of memory. I would think that BigDBMS would be
hurt by this more than PG. Here are the settings I've modified
in postgresql.conf:
As I said you need to set shared_buffers to at least 750MB this is
the
Craig A. James wrote:
The idiom to replace count() was
select col from tbl order by col desc limit 1. It worked miracles for
my app.
Sorry, I meant to write, the idiom to replace MAX(), not count()... MAX()
was the function that was killing me, 'tho count() also gave me problems.
Craig
takes longer ther 700ms. Luckily we can design around this issue.
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Craig A.
James
Sent: Sunday, January 07, 2007 5:57 PM
To: Guy Rouillier; PostgreSQL Performance
Subject: Re: [PERFORM] High update activity
Adam Rich [EMAIL PROTECTED] writes:
I'm using 8.2 and using order by limit is still faster than MAX()
even though MAX() now seems to rewrite to an almost identical plan
internally.
Care to quantify that? AFAICT any difference is within measurement
noise, at least for the case of
On Sun, 2007-01-07 at 20:26 -0600, Adam Rich wrote:
I'm using 8.2 and using order by limit is still faster than MAX()
even though MAX() now seems to rewrite to an almost identical plan
internally.
Gonna need you to back that up :) Can we get an explain analyze?
Count(*) still seems to use
Dave Cramer wrote:
On 6-Jan-07, at 11:32 PM, Guy Rouillier wrote:
Dave Cramer wrote:
The box has 3 GB of memory. I would think that BigDBMS would be
hurt by this more than PG. Here are the settings I've modified in
postgresql.conf:
As I said you need to set shared_buffers to at least
activity, PostgreSQL vs BigDBMS
Adam Rich [EMAIL PROTECTED] writes:
I'm using 8.2 and using order by limit is still faster than MAX()
even though MAX() now seems to rewrite to an almost identical plan
internally.
Care to quantify that? AFAICT any difference is within measurement
noise, at least
Ron wrote:
C= What file system are you using? Unlike BigDBMS, pg does not have its
own native one, so you have to choose the one that best suits your
needs. For update heavy applications involving lots of small updates
jfs and XFS should both be seriously considered.
Ron, thanks for your
: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Joshua D.
Drake
Sent: Sunday, January 07, 2007 9:10 PM
To: Adam Rich
Cc: 'Craig A. James'; 'Guy Rouillier'; 'PostgreSQL Performance'
Subject: Re: [PERFORM] High update activity, PostgreSQL vs BigDBMS
On Sun, 2007-01-07 at 20:26 -0600, Adam
Craig A. James wrote:
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
Dave Cramer wrote:
The box has 3 GB of memory. I would think that BigDBMS would be hurt
by this more than PG. Here are the settings I've modified in
postgresql.conf:
As I said you need to set shared_buffers to at least 750MB this is the
starting point, it can actually go higher.
Regarding shared_buffers=750MB, the last discussions I remember on this
subject said that anything over 10,000 (8K buffers = 80 MB) had unproven
benefits. So I'm surprised to see such a large value suggested. I'll
certainly give it a try and see what happens.
That is old news :) As of
Guy,
The application is fairly straightforward, but as you say, what is
working okay with BigDBMS isn't working as well under PG. I'm going to
try other configuration suggestions made by others before I attempt
logic changes. The core logic is unchangeable; millions of rows of data
in a
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
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
On 5-Jan-07, at 9:51 PM, 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
Alvaro Herrera wrote:
Ron wrote:
C= What file system are you using? Unlike BigDBMS, pg does not have
its own native one, so you have to choose the one that best suits
your needs. For update heavy applications involving lots of small
updates jfs and XFS should both be seriously considered.
On þri, 2007-01-02 at 09:04 -0500, Geoffrey wrote:
Alvaro Herrera wrote:
Actually it has been suggested that a combination of ext2 (for WAL) and
ext3 (for data, with data journalling disabled) is a good performer.
AFAIK you don't want the overhead of journalling for the WAL partition.
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 2 Jan 2007, at 14:54, Ragnar wrote:
On þri, 2007-01-02 at 09:04 -0500, Geoffrey wrote:
Alvaro Herrera wrote:
Actually it has been suggested that a combination of ext2 (for
WAL) and
ext3 (for data, with data journalling disabled) is a good
More specifically, you should set the noatime,data=writeback options in
fstab on ext3 partitions for best performance. Correct?
it doesn't really belong here but ext3 has
data journaled (data and meta data)
ordered (meta data journald but data written before meta data (default))
journald
On Fri, 2006-12-29 at 07:52 -0500, Ron wrote:
A= go through each query and see what work_mem needs to be for that
query to be as RAM resident as possible. If you have enough RAM, set
work_mem for that query that large. Remember that work_mem is =per
query=, so queries running in parallel
Tom Lane wrote:
Shoaib Mir [EMAIL PROTECTED] writes:
Here are my few recommendations that might help you:
[ snip good advice ]
Another thing to look at is whether you are doing inserts/updates as
individual transactions, and if so see if you can batch them to
reduce the per-transaction
At 12:46 AM 12/28/2006, Guy Rouillier wrote:
I don't want to violate any license agreement by discussing
performance, so I'll refer to a large, commercial
PostgreSQL-compatible DBMS only as BigDBMS here.
I'm trying to convince my employer to replace BigDBMS with
PostgreSQL for at least some
Hi all,
A= go through each query and see what work_mem needs to be for that
query to be as RAM resident as possible. If you have enough RAM, set
work_mem for that query that large. Remember that work_mem is =per
query=, so queries running in parallel eat the sum of each of their
Ron wrote:
C= What file system are you using? Unlike BigDBMS, pg does not have
its own native one, so you have to choose the one that best suits
your needs. For update heavy applications involving lots of small
updates jfs and XFS should both be seriously considered.
Actually it has
On Fri, 29 Dec 2006, Alvaro Herrera wrote:
Ron wrote:
C= What file system are you using? Unlike BigDBMS, pg does not have
its own native one, so you have to choose the one that best suits
your needs. For update heavy applications involving lots of small
updates jfs and XFS should both be
You should search the archives for Luke Lonegran's posting about how IO in
Postgresql is significantly bottlenecked because it's not async. A 12 disk
array is going to max out Postgresql's max theoretical write capacity to
disk, and therefore BigRDBMS is always going to win in such a config.
Here are my few recommendations that might help you:
- You will need to do table partitioning (
http://www.postgresql.org/docs/current/static/ddl-partitioning.html) as you
are storing quite a lot of data in one table per day.
- You are using a RAID5 setup which is something that can also affect
Guy,
Did you tune postgresql ? How much memory does the box have? Have you
tuned postgresql ?
Dave
On 28-Dec-06, at 12:46 AM, Guy Rouillier wrote:
I don't want to violate any license agreement by discussing
performance, so I'll refer to a large, commercial PostgreSQL-
compatible DBMS
Shoaib Mir [EMAIL PROTECTED] writes:
Here are my few recommendations that might help you:
[ snip good advice ]
Another thing to look at is whether you are doing inserts/updates as
individual transactions, and if so see if you can batch them to
reduce the per-transaction overhead.
I don't want to violate any license agreement by discussing performance,
so I'll refer to a large, commercial PostgreSQL-compatible DBMS only as
BigDBMS here.
I'm trying to convince my employer to replace BigDBMS with PostgreSQL
for at least some of our Java applications. As a proof of
39 matches
Mail list logo
