date:20100708

Eliot Gable  wrote:

> For about $2k - $3k, you can get a server that will do upwards of
> 300 MB/sec, assuming the bulk of that cost goes to a good
> hardware-based RAID controller with a battery backed-up cache and
> some good 15k RPM SAS drives.

FWIW, I concur that the description so far suggests that this server
either doesn't have a good RAID controller card with battery backed-
up (BBU) cache, or that it isn't configured properly.

-Kevin

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] performance on new linux box

2010-07-08 Thread Eliot Gable

On Thu, Jul 8, 2010 at 9:53 AM, Kevin Grittner
wrote:

> Eliot Gable 
> >
> wrote:
>
> > For about $2k - $3k, you can get a server that will do upwards of
> > 300 MB/sec, assuming the bulk of that cost goes to a good
> > hardware-based RAID controller with a battery backed-up cache and
> > some good 15k RPM SAS drives.
>
> FWIW, I concur that the description so far suggests that this server
> either doesn't have a good RAID controller card with battery backed-
> up (BBU) cache, or that it isn't configured properly.
>
>
On another note, it is also entirely possible that just re-writing your
queries will completely solve your problem and make your performance
bottleneck go away. Sometimes throwing hardware at a problem is not the best
(or cheapest) solution. Personally, I would never throw hardware at a
problem until I am certain that I have everything else optimized as much as
possible. One of the stored procedures I recently wrote in pl/pgsql was
originally chewing up my entire development box's processing capabilities at
just 20 transactions per second. It's a pretty wimpy box, so I was not
really expecting a lot out of it. However, after spending several weeks
optimizing my queries, I now have it doing twice as much work at 120
transactions per second on the same box. So, if I had thrown hardware at the
problem, I would have spent 12 times more on hardware than I need to spend
now for the same level of performance.

If you can post some of your queries, there are a lot of bright people on
this discussion list that can probably help you solve your bottleneck
without spending a ton of money on new hardware. Obviously, there is no
guarantee -- you might already be as optimized as you can get in your
queries, but I doubt it. Even after spending months tweaking my queries, I
am still finding things here and there where I can get a bit more
performance out of them.

-- 
Eliot Gable

Re: [PERFORM] performance on new linux box

Eliot Gable  wrote:

> If you can post some of your queries, there are a lot of bright
> people on this discussion list that can probably help you solve
> your bottleneck

Sure, but the original post was because the brand new server class
machine was performing much worse than the single-drive desktop
machine *on the same queries*, which seems like an issue worthy of
investigation independently of what you suggest.

-Kevin

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] performance on new linux box

Thanks a lot for all the comments.  The fact that both my windows box and
the old linux box both show a massive performance improvement over the new
linux box seems to point to hardware to me.  I am not sure how to test the
fsync issue, but i don't see how that could be it.

The raid card the server has in it is:
3Ware 4 Port 9650SE-4LPML RAID Card

Looking it up, it seems to indicate that it has BBU

The only other difference between the boxes is the postgresql version.  The
new one has 8.4-2 from the yum install instructions on the site:
http://yum.pgrpms.org/reporpms/repoview/pgdg-centos.html

Any more thoughts?

On Thu, Jul 8, 2010 at 8:02 AM, Kevin Grittner
wrote:

> Eliot Gable 
> >
> wrote:
>
> > If you can post some of your queries, there are a lot of bright
> > people on this discussion list that can probably help you solve
> > your bottleneck
>
> Sure, but the original post was because the brand new server class
> machine was performing much worse than the single-drive desktop
> machine *on the same queries*, which seems like an issue worthy of
> investigation independently of what you suggest.
>
> -Kevin
>

Re: [PERFORM] performance on new linux box

2010-07-08 Thread Joshua D. Drake

On Thu, 2010-07-08 at 09:31 -0700, Ryan Wexler wrote:
> The raid card the server has in it is:
> 3Ware 4 Port 9650SE-4LPML RAID Card
> 
> Looking it up, it seems to indicate that it has BBU 

No. It supports a BBU. It doesn't have one necessarily.

You need to go into your RAID BIOS. It will tell you.

Sincerely,

Joshua D. Drake


-- 
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] performance on new linux boxeradmin(11983)i: STATEMENT: update license set expires= '2010-06-15' where lic

2010-07-08 Thread John Rouillard

On Thu, Jul 08, 2010 at 09:31:32AM -0700, Ryan Wexler wrote:
> Thanks a lot for all the comments.  The fact that both my windows box and
> the old linux box both show a massive performance improvement over the new
> linux box seems to point to hardware to me.  I am not sure how to test the
> fsync issue, but i don't see how that could be it.
> 
> The raid card the server has in it is:
> 3Ware 4 Port 9650SE-4LPML RAID Card
> 
> Looking it up, it seems to indicate that it has BBU

By "looking it up", I assume you mean running tw_cli and looking at
the output to make sure the bbu is enabled and the cache is turned on
for the raid array u0 or u1 ...?

-- 
-- rouilj

John Rouillard   System Administrator
Renesys Corporation  603-244-9084 (cell)  603-643-9300 x 111

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] performance on new linux box

2010-07-08 Thread Craig James


On 7/8/10 9:31 AM, Ryan Wexler wrote:

Thanks a lot for all the comments.  The fact that both my windows box
and the old linux box both show a massive performance improvement over
the new linux box seems to point to hardware to me.  I am not sure how
to test the fsync issue, but i don't see how that could be it.

The raid card the server has in it is:
3Ware 4 Port 9650SE-4LPML RAID Card

Looking it up, it seems to indicate that it has BBU


Make sure the battery isn't dead.  Most RAID controllers drop to non-BBU speeds 
if they detect that the battery is faulty.

Craig

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Fwd: [PERFORM] performance on new linux box

-- Forwarded message --
From: Ryan Wexler 
Date: Thu, Jul 8, 2010 at 10:12 AM
Subject: Re: [PERFORM] performance on new linux box
To: Craig James 


On Thu, Jul 8, 2010 at 10:10 AM, Craig James wrote:

> On 7/8/10 9:31 AM, Ryan Wexler wrote:
>
>> Thanks a lot for all the comments.  The fact that both my windows box
>> and the old linux box both show a massive performance improvement over
>> the new linux box seems to point to hardware to me.  I am not sure how
>> to test the fsync issue, but i don't see how that could be it.
>>
>> The raid card the server has in it is:
>> 3Ware 4 Port 9650SE-4LPML RAID Card
>>
>> Looking it up, it seems to indicate that it has BBU
>>
>
> Make sure the battery isn't dead.  Most RAID controllers drop to non-BBU
> speeds if they detect that the battery is faulty.
>
> Craig
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>


Thanks.  The server is hosted, so it is a bit of a hassle to figure this
stuff out, but I am having someone check.

Re: [PERFORM] Slow query with planner row strange estimation

2010-07-08 Thread Robert Haas

On Wed, Jul 7, 2010 at 10:39 AM, damien hostin  wrote:
> Hello again,
>
> At last, I check the same query with the same data on my desktop computer.
> Just after loading the data, the queries were slow, I launch a vaccum
> analyse which collect good stats on the main table, the query became quick
> (~200ms). Now 1classic sata disk computer is faster than our little monster
> server !!

Have you tried running ANALYZE on the production server?

You might also want to try ALTER TABLE ... SET STATISTICS to a large
value on some of the join columns involved in the query.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [Slony1-general] [PERFORM] WAL partition overloaded--by autovacuum?

2010-07-08 Thread Robert Haas

On Wed, Jul 7, 2010 at 3:32 PM, Richard Yen  wrote:
> On Jul 6, 2010, at 8:25 PM, Scott Marlowe wrote:
>
>> Tell us what you can about your hardware setup.
>
> Sorry, I made the bad assumption that the hardware setup would be 
> irrelevant--dunno why I thought that.
>
> My hardware setup is 2 FusionIO 160GB drives in a RAID-1 configuration, 
> running on an HP DL360 G5
>
> I think I figured out the problem:
>
> -- I figured that pg_xlog and data/base could both be on the FusionIO drive, 
> since there would be no latency when there are no spindles.
> -- However, I didn't take into account the fact that pg_xlog might grow in 
> size when autovacuum does its work when vacuuming to prevent XID wraparound.  
> I *just* discovered this when one of my other replication nodes decided to 
> die on me and fill up its disk.
> -- Unfortunately, my db is 114GB (including indexes) or 60GB (without 
> indexes), leaving ~37GB for pg_xlog (since they are sharing a partition).  So 
> I'm guessing what happened was that when autovacuum ran to prevent XID 
> wraparound, it takes each table and changes the XID, and it gets recorded in 
> WAL, causing WAL to bloat.  This this the correct understanding?

That seems logical (and un-fun), but I don't understand how you
managed to fill up 37GB of disk with WAL files.  Every time you fill
up checkpoint_segments * 16MB of WAL files, you ought to get a
checkpoint.  When it's complete, WAL segments completely written
before the start of the checkpoint should be recyclable.  Unless I'm
confused, which apparently I am.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [Slony1-general] [PERFORM] WAL partition overloaded--by autovacuum?

Robert Haas  wrote:

> I don't understand how you managed to fill up 37GB of disk with
> WAL files.  Every time you fill up checkpoint_segments * 16MB of
> WAL files, you ought to get a checkpoint.  When it's complete, WAL
> segments completely written before the start of the checkpoint
> should be recyclable.  Unless I'm confused, which apparently I am.

You're not alone.  At first I was assuming that it was because of
archiving, but the OP says that's turned off.  Unless it had been on
and there wasn't a *restart* after changing the configuration, I
can't see how this could happen, and was hoping someone else could
cast some light on the issue.

The one setting that gave me pause was:

commit_siblings=200

but it doesn't seem like that should matter with:

commit_delay=0;

-Kevin

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] performance on new linux box

2010-07-08 Thread Jochen Erwied

Thursday, July 8, 2010, 7:16:47 PM you wrote:

> Thanks.  The server is hosted, so it is a bit of a hassle to figure this
> stuff out, but I am having someone check.

If you have root access to the machine, you should try 'tw_cli /cx show',
where the x in /cx is the controller number. If not present on the machine,
the command-line-tools are available from 3ware in their download-section.

You should get an output showing something like this:

Name  OnlineState  BBUReady  StatusVolt Temp Hours  LastCapTest
---
bbu   On   Yes   OKOK   OK   20201-Jan-1970

Don't ask why the 'LastCapTest' does not show a valid value, the bbu here 
completed the test successfully.

-- 
Jochen Erwied |   home: joc...@erwied.eu +49-208-38800-18, FAX: -19
Sauerbruchstr. 17 |   work: j...@mbs-software.de  +49-2151-7294-24, FAX: -50
D-45470 Muelheim  | mobile: jochen.erw...@vodafone.de   +49-173-5404164

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] performance on new linux box

On Thu, Jul 8, 2010 at 12:13 PM, Jochen Erwied <
joc...@pgsql-performance.erwied.eu> wrote:

> Thursday, July 8, 2010, 7:16:47 PM you wrote:
>
> > Thanks.  The server is hosted, so it is a bit of a hassle to figure this
> > stuff out, but I am having someone check.
>
> If you have root access to the machine, you should try 'tw_cli /cx show',
> where the x in /cx is the controller number. If not present on the machine,
> the command-line-tools are available from 3ware in their download-section.
>
> You should get an output showing something like this:
>
> Name  OnlineState  BBUReady  StatusVolt Temp Hours  LastCapTest
> ---
> bbu   On   Yes   OKOK   OK   20201-Jan-1970
>
> Don't ask why the 'LastCapTest' does not show a valid value, the bbu here
> completed the test successfully.
>
> --
> Jochen Erwied |   home: joc...@erwied.eu +49-208-38800-18, FAX:
> -19
> Sauerbruchstr. 17 |   work: j...@mbs-software.de  +49-2151-7294-24, FAX:
> -50
> D-45470 Muelheim  | mobile: jochen.erw...@vodafone.de
> +49-173-5404164
>
>
The twi_cli package doesn't appear to be installed.  I will try to hunt it
down.
However, I just verified with the hosting company that BBU is off on the
raid controller.  I am trying to find out my options, turn it on, different
card, etc...

[PERFORM] Need help in performance tuning.

2010-07-08 Thread Harpreet singh Wadhwa

Hi,


I want to fine tune my postgresql to increase number of connects it
can handle in a minutes time.
Decrease the response time per request etc.
The exact case will be to handle around 100 concurrent requests.

Can any one please help me in this.
Any hardware suggestions are also welcomed.


Regards
Harpreet

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [Slony1-general] [PERFORM] WAL partition overloaded--by autovacuum?

On Jul 8, 2010, at 12:04 PM, Kevin Grittner wrote:

> Robert Haas  wrote:
> 
>> I don't understand how you managed to fill up 37GB of disk with
>> WAL files.  Every time you fill up checkpoint_segments * 16MB of
>> WAL files, you ought to get a checkpoint.  When it's complete, WAL
>> segments completely written before the start of the checkpoint
>> should be recyclable.  Unless I'm confused, which apparently I am.
> 
> You're not alone.  At first I was assuming that it was because of
> archiving, but the OP says that's turned off.  Unless it had been on
> and there wasn't a *restart* after changing the configuration, I
> can't see how this could happen, and was hoping someone else could
> cast some light on the issue.

I'm fairly confused myself.  I'm beginning to think that because data/base and 
data/pg_xlog were on the same partition (/db), when the /db partition filled, 
up the WAL files couldn't get flushed to data/base, thereby preventing 
data/pg_xlog from being emptied out, as per the documentation.

My concern is that--as in the original post--there were moments where 129 WAL 
files were generated in one minute.  Is it plausible that this autovacuum could 
be responsible for this?

--Richard
-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [Slony1-general] [PERFORM] WAL partition overloaded--by autovacuum?

2010-07-08 Thread Tom Lane

"Kevin Grittner"  writes:
> You're not alone.  At first I was assuming that it was because of
> archiving, but the OP says that's turned off.  Unless it had been on
> and there wasn't a *restart* after changing the configuration,

Yeah, I was less than convinced by his eyeball report of that, too.
"show archive_mode" would be a much more convincing check of the
server's state.  Or would have been, if the server hadn't been restarted
since the problem occurred.

archive_mode on with a bad archive_command would lead directly to the
reported problem ... although it should also lead to pretty obvious
complaints in the postmaster log.

(Hmm ... but those complaints are logged at level WARNING, which as
discussed elsewhere is really lower than LOG.  Should we change them?)

regards, tom lane

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] performance on new linux box

Ryan Wexler  wrote:

> I just verified with the hosting company that BBU is off on the
> raid controller.  I am trying to find out my options, turn it on,
> different card, etc...

In the "etc." category, make sure that when you get it turned on,
the cache is configured for "write back" mode, not "write through"
mode.  Ideally (if you can't afford to lose the data), it will be
configured to degrade to "write through" if the battery fails.

-Kevin

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] performance on new linux box

2010-07-08 Thread Jochen Erwied

Thursday, July 8, 2010, 9:18:20 PM you wrote:

> However, I just verified with the hosting company that BBU is off on the
> raid controller.  I am trying to find out my options, turn it on, different
> card, etc...

Turning it on requires the external BBU to be installed, so even if a 9650
has BBU support, it requires the hardware on a pluggable card.

And even If the BBU is present, it requires to pass the selftest once until
you are able to turn on write caching.


-- 
Jochen Erwied |   home: joc...@erwied.eu +49-208-38800-18, FAX: -19
Sauerbruchstr. 17 |   work: j...@mbs-software.de  +49-2151-7294-24, FAX: -50
D-45470 Muelheim  | mobile: jochen.erw...@vodafone.de   +49-173-5404164


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [Slony1-general] [PERFORM] WAL partition overloaded--by autovacuum?

On Jul 8, 2010, at 12:27 PM, Tom Lane wrote:
> 
> (Hmm ... but those complaints are logged at level WARNING, which as
> discussed elsewhere is really lower than LOG.  Should we change them?)

Hmm, I did a grep on "WARNING" on my log, and the only thing that turns up are 
the "WARNING:  terminating connection because of crash of another server 
process" entries when postgres died and shut down when the disks filled up.

Would this be conclusive evidence that archive_mode=off?

--Richard
-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] performance on new linux box

On Thu, Jul 8, 2010 at 12:32 PM, Jochen Erwied <
joc...@pgsql-performance.erwied.eu> wrote:

> Thursday, July 8, 2010, 9:18:20 PM you wrote:
>
> > However, I just verified with the hosting company that BBU is off on the
> > raid controller.  I am trying to find out my options, turn it on,
> different
> > card, etc...
>
> Turning it on requires the external BBU to be installed, so even if a 9650
> has BBU support, it requires the hardware on a pluggable card.
>
> And even If the BBU is present, it requires to pass the selftest once until
> you are able to turn on write caching.
>
>
> --
> Jochen Erwied |   home: joc...@erwied.eu +49-208-38800-18, FAX:
> -19
> Sauerbruchstr. 17 |   work: j...@mbs-software.de  +49-2151-7294-24, FAX:
> -50
> D-45470 Muelheim  | mobile: jochen.erw...@vodafone.de
> +49-173-5404164
>
>
One thing I don't understand is why BBU will result in a huge performance
gain.  I thought BBU was all about power failures?

Re: [PERFORM] performance on new linux box

2010-07-08 Thread Ben Chobot

On Jul 8, 2010, at 12:37 PM, Ryan Wexler wrote:

> One thing I don't understand is why BBU will result in a huge performance 
> gain.  I thought BBU was all about power failures?

When you have a working BBU, the raid card can safely do write caching. Without 
it, many raid cards are good about turning off write caching on the disks and 
refusing to do it themselves. (Safety over performance.)
-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] performance on new linux box

Ryan Wexler  wrote:

> One thing I don't understand is why BBU will result in a huge
> performance gain.  I thought BBU was all about power failures?

Well, it makes it safe for the controller to consider the write
complete as soon as it hits the RAM cache, rather than waiting for
persistence to the disk itself.  It can then schedule the writes in
a manner which is efficient based on the physical medium.

Something like this was probably happening on your non-server
machines, but without BBU it was not actually safe.  Server class
machines tend to be more conservative about not losing your data,
but without a RAID controller with BBU cache, that slows writes down
to the speed of the rotating disks.

-Kevin

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] performance on new linux box

On Thu, Jul 8, 2010 at 12:46 PM, Kevin Grittner  wrote:

> Ryan Wexler  wrote:
>
> > One thing I don't understand is why BBU will result in a huge
> > performance gain.  I thought BBU was all about power failures?
>
> Well, it makes it safe for the controller to consider the write
> complete as soon as it hits the RAM cache, rather than waiting for
> persistence to the disk itself.  It can then schedule the writes in
> a manner which is efficient based on the physical medium.
>
> Something like this was probably happening on your non-server
> machines, but without BBU it was not actually safe.  Server class
> machines tend to be more conservative about not losing your data,
> but without a RAID controller with BBU cache, that slows writes down
> to the speed of the rotating disks.
>
> -Kevin
>
Thanks for the explanations that makes things clearer.  It still amazes me
that it would account for a 5x change in IO.

Re: [Slony1-general] [PERFORM] WAL partition overloaded--by autovacuum?

Richard Yen  wrote:

> there were moments where 129 WAL files were generated in one
> minute.  Is it plausible that this autovacuum could be responsible
> for this?

I don't remember seeing your autovacuum and vacuum config settings,
or an answer to my question about whether there was a bulk load of a
significant portion of current data.  With agressive autovacuum
settings, hitting the freeze threshold for bulk-loaded data could do
that.

-Kevin

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [Slony1-general] [PERFORM] WAL partition overloaded--by autovacuum?

2010-07-08 Thread Tom Lane

Richard Yen  writes:
> My concern is that--as in the original post--there were moments where 129 WAL 
> files were generated in one minute.  Is it plausible that this autovacuum 
> could be responsible for this?

That's not a particularly surprising WAL creation rate for a busy
database.  I wouldn't expect autovacuum to cause it by itself, but
that's true only because autovacuum processing is typically throttled
by autovacuum_vacuum_cost_delay.  Perhaps you had that set to zero?

regards, tom lane

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] performance on new linux box

2010-07-08 Thread David Boreham


On 7/8/2010 1:47 PM, Ryan Wexler wrote:
Thanks for the explanations that makes things clearer.  It still 
amazes me that it would account for a 5x change in IO.


The buffering allows decoupling of the write rate from the disk rotation 
speed.
Disks don't spin that fast, at least not relative to the speed the CPU 
is running at.





--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] performance on new linux box

Ryan Wexler  wrote:
 
> It still amazes me that it would account for a 5x change in IO.
 
If you were doing one INSERT per database transaction, for instance,
that would not be at all surprising.  If you were doing one COPY in
of a million rows, it would be a bit more surprising.
 
Each COMMIT of a database transaction, without caching, requires
that you wait for the disk to rotate around to the right position. 
Compared to the speed of RAM, that can take quite a long time.  With
write caching, you might write quite a few adjacent disk sectors to
the cache, which can then all be streamed to disk on one rotation. 
It can also do tricks like writing a bunch of sectors on one part of
the disk before pulling the heads all the way over to another
portion of the disk to write a bunch of sectors.
 
It is very good for performance to cache writes.
 
-Kevin

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [Slony1-general] [PERFORM] WAL partition overloaded--by autovacuum?


On Jul 8, 2010, at 12:50 PM, Tom Lane wrote:

> Richard Yen  writes:
>> My concern is that--as in the original post--there were moments where 129 
>> WAL files were generated in one minute.  Is it plausible that this 
>> autovacuum could be responsible for this?
> 
> That's not a particularly surprising WAL creation rate for a busy
> database.  I wouldn't expect autovacuum to cause it by itself, but
> that's true only because autovacuum processing is typically throttled
> by autovacuum_vacuum_cost_delay.  Perhaps you had that set to zero?
> 

Ah, yes, autovacuum_vacuum_cost_delay = 0 in my config.  That explains 
it--guess I'm playing with knives if I set things that way...

--Richard


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [Slony1-general] [PERFORM] WAL partition overloaded--by autovacuum?


On Jul 8, 2010, at 12:50 PM, Kevin Grittner wrote:

> Richard Yen  wrote:
> 
>> there were moments where 129 WAL files were generated in one
>> minute.  Is it plausible that this autovacuum could be responsible
>> for this?
> 
> I don't remember seeing your autovacuum and vacuum config settings,
> or an answer to my question about whether there was a bulk load of a
> significant portion of current data.  With agressive autovacuum
> settings, hitting the freeze threshold for bulk-loaded data could do
> that.
> 

Yeah, autovacuum is pretty aggressive, as I recall:
autovacuum = on
log_autovacuum_min_duration = 0
autovacuum_max_workers = 8
autovacuum_naptime = 1min
autovacuum_vacuum_threshold = 400
autovacuum_analyze_threshold = 200
autovacuum_vacuum_scale_factor = 0.2
autovacuum_analyze_scale_factor = 0.1
autovacuum_freeze_max_age = 2
autovacuum_vacuum_cost_delay = 0
autovacuum_vacuum_cost_limit = -1

vacuum_cost_delay = 0
vacuum_cost_limit = 200

When you say "bulk-loaded," I suppose that also includes loading the data via 
slony ADD NODE as well--correct?  I created this node maybe 6 months ago via 
slony ADD NODE

Thanks much for your time,
--Richard
-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [Slony1-general] [PERFORM] WAL partition overloaded--by autovacuum?

Richard Yen  wrote:

> When you say "bulk-loaded," I suppose that also includes loading
> the data via slony ADD NODE as well--correct?

I would think so.  Anything which loads a lot of data in relatively
few database transactions would qualify; I would think slony would
do this, as it's generally required to get decent performance.

-Kevin

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Need help in performance tuning.

Harpreet singh Wadhwa  wrote:

> I want to fine tune my postgresql to increase number of connects
> it can handle in a minutes time.
> Decrease the response time per request etc.
> The exact case will be to handle around 100 concurrent requests.

I have found that connection pooling is crucial.

The "concurrent requests" phrase worries me a bit -- you should be
focusing more on "concurrent connections" and perhaps "requests per
second".  With most hardware, you will get faster response time and
better overall throughput by funneling 100 connections through a
connection pool which limits the number of concurrent requests to
just enough to keep all your hardware resources busy, queuing any
requests beyond that for submission when a pending request
completes.

> Any hardware suggestions are also welcomed.

If you don't have the hardware yet, you'd need to provide a bit more
information to get advice on what hardware you need.

-Kevin

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] performance on new linux box

2010-07-08 Thread Craig James

On 7/8/10 12:47 PM, Ryan Wexler wrote:

On Thu, Jul 8, 2010 at 12:46 PM, Kevin Grittner
mailto:kevin.gritt...@wicourts.gov>> wrote:

Ryan Wexler mailto:r...@iridiumsuite.com>>
wrote:

> One thing I don't understand is why BBU will result in a huge
> performance gain. I thought BBU was all about power failures?

Well, it makes it safe for the controller to consider the write
complete as soon as it hits the RAM cache, rather than waiting for
persistence to the disk itself. It can then schedule the writes in
a manner which is efficient based on the physical medium.

Something like this was probably happening on your non-server
machines, but without BBU it was not actually safe. Server class
machines tend to be more conservative about not losing your data,
but without a RAID controller with BBU cache, that slows writes down
to the speed of the rotating disks.

-Kevin

Thanks for the explanations that makes things clearer. It still amazes
me that it would account for a 5x change in IO.

It's not exactly a 5x change in I/O, rather it's a 5x change in *transactions*. Without a BBU
Postgres has to wait for each transaction to by physically written to the disk, which at 7200 RPM
(or 10K or 15K) means a few hundred per second. Most of the time Postgres is just sitting there
waiting for the disk to say, "OK, I did it." With BBU, once the RAID card has the data,
it's virtually guaranteed it will get to the disk even if the power fails, so the RAID controller
says, "OK, I did it" even though the data is still in the controller's cache and not
actually on the disk.

It means there's no tight relationship between the disk's rotational speed and
your transaction rate.

Craig

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] performance on new linux box