pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedstrange performance regression between 7.4 and 8.1
53+ messages / 13 participants
[nested] [flat]
* strange performance regression between 7.4 and 8.1
@ 2007-03-01 20:40 Alex Deucher <alexdeucher@gmail.com>
2007-03-06 02:14 ` Re: strange performance regression between 7.4 and 8.1 Guido Neitzer <lists@event-s.net>
0 siblings, 1 reply; 53+ messages in thread
From: Alex Deucher @ 2007-03-01 20:40 UTC (permalink / raw)
To: pgsql-performance
Hello,
I have noticed a strange performance regression and I'm at a loss as
to what's happening. We have a fairly large database (~16 GB). The
original postgres 7.4 was running on a sun v880 with 4 CPUs and 8 GB
of ram running Solaris on local scsi discs. The new server is a sun
Opteron box with 4 cores, 8 GB of ram running postgres 8.1.4 on Linux
(AMD64) on a 4 Gbps FC SAN volume. When we created the new database
it was created from scratch rather than copying over the old one,
however the table structure is almost identical (UTF8 on the new one
vs. C on the old). The problem is queries are ~10x slower on the new
hardware. I read several places that the SAN might be to blame, but
testing with bonnie and dd indicates that the SAN is actually almost
twice as fast as the scsi discs in the old sun server. I've tried
adjusting just about every option in the postgres config file, but
performance remains the same. Any ideas?
Thanks,
Alex
^ permalink raw reply [nested|flat] 53+ messages in thread
* Re: strange performance regression between 7.4 and 8.1
2007-03-01 20:40 strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
@ 2007-03-06 02:14 ` Guido Neitzer <lists@event-s.net>
2007-03-06 02:56 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
0 siblings, 1 reply; 53+ messages in thread
From: Guido Neitzer @ 2007-03-06 02:14 UTC (permalink / raw)
To: pgsql-performance
On 01.03.2007, at 13:40, Alex Deucher wrote:
> I read several places that the SAN might be to blame, but
> testing with bonnie and dd indicates that the SAN is actually almost
> twice as fast as the scsi discs in the old sun server. I've tried
> adjusting just about every option in the postgres config file, but
> performance remains the same. Any ideas?
As mentioned last week:
Did you actually try to use the local drives for speed testing? It
might be that the SAN introduces latency especially for random access
you don't see on local drives.
cug
^ permalink raw reply [nested|flat] 53+ messages in thread
* Re: strange performance regression between 7.4 and 8.1
2007-03-01 20:40 strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-06 02:14 ` Re: strange performance regression between 7.4 and 8.1 Guido Neitzer <lists@event-s.net>
@ 2007-03-06 02:56 ` Alex Deucher <alexdeucher@gmail.com>
2007-03-06 02:58 ` Re: strange performance regression between 7.4 and 8.1 Guido Neitzer <lists@event-s.net>
0 siblings, 1 reply; 53+ messages in thread
From: Alex Deucher @ 2007-03-06 02:56 UTC (permalink / raw)
To: Guido Neitzer <lists@event-s.net>; +Cc: pgsql-performance
On 3/5/07, Guido Neitzer <lists@event-s.net> wrote:
> On 01.03.2007, at 13:40, Alex Deucher wrote:
>
> > I read several places that the SAN might be to blame, but
> > testing with bonnie and dd indicates that the SAN is actually almost
> > twice as fast as the scsi discs in the old sun server. I've tried
> > adjusting just about every option in the postgres config file, but
> > performance remains the same. Any ideas?
>
> As mentioned last week:
>
> Did you actually try to use the local drives for speed testing? It
> might be that the SAN introduces latency especially for random access
> you don't see on local drives.
Yes, I started setting that up this afternoon. I'm going to test that
tomorrow and post the results.
Alex
>
> cug
>
^ permalink raw reply [nested|flat] 53+ messages in thread
* Re: strange performance regression between 7.4 and 8.1
2007-03-01 20:40 strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-06 02:14 ` Re: strange performance regression between 7.4 and 8.1 Guido Neitzer <lists@event-s.net>
2007-03-06 02:56 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
@ 2007-03-06 02:58 ` Guido Neitzer <lists@event-s.net>
2007-03-06 15:25 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
0 siblings, 1 reply; 53+ messages in thread
From: Guido Neitzer @ 2007-03-06 02:58 UTC (permalink / raw)
To: Alex Deucher <alexdeucher@gmail.com>; pgsql-performance
On 05.03.2007, at 19:56, Alex Deucher wrote:
> Yes, I started setting that up this afternoon. I'm going to test that
> tomorrow and post the results.
Good - that may or may not give some insight in the actual
bottleneck. You never know but it seems to be one of the easiest to
find out ...
cug
^ permalink raw reply [nested|flat] 53+ messages in thread
* Re: strange performance regression between 7.4 and 8.1
2007-03-01 20:40 strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-06 02:14 ` Re: strange performance regression between 7.4 and 8.1 Guido Neitzer <lists@event-s.net>
2007-03-06 02:56 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-06 02:58 ` Re: strange performance regression between 7.4 and 8.1 Guido Neitzer <lists@event-s.net>
@ 2007-03-06 15:25 ` Alex Deucher <alexdeucher@gmail.com>
2007-03-06 16:58 ` Re: strange performance regression between 7.4 and 8.1 Ron <rjpeace@earthlink.net>
0 siblings, 1 reply; 53+ messages in thread
From: Alex Deucher @ 2007-03-06 15:25 UTC (permalink / raw)
To: Guido Neitzer <lists@event-s.net>; +Cc: pgsql-performance
On 3/5/07, Guido Neitzer <lists@event-s.net> wrote:
> On 05.03.2007, at 19:56, Alex Deucher wrote:
>
> > Yes, I started setting that up this afternoon. I'm going to test that
> > tomorrow and post the results.
>
> Good - that may or may not give some insight in the actual
> bottleneck. You never know but it seems to be one of the easiest to
> find out ...
>
Well, the SAN appears to be the limiting factor. I set up the DB on
the local scsi discs (software RAID 1) and performance is excellent
(better than the old server). Thanks for everyone's help.
Alex
^ permalink raw reply [nested|flat] 53+ messages in thread
* Re: strange performance regression between 7.4 and 8.1
2007-03-01 20:40 strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-06 02:14 ` Re: strange performance regression between 7.4 and 8.1 Guido Neitzer <lists@event-s.net>
2007-03-06 02:56 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-06 02:58 ` Re: strange performance regression between 7.4 and 8.1 Guido Neitzer <lists@event-s.net>
2007-03-06 15:25 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
@ 2007-03-06 16:58 ` Ron <rjpeace@earthlink.net>
2007-03-06 18:11 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
0 siblings, 1 reply; 53+ messages in thread
From: Ron @ 2007-03-06 16:58 UTC (permalink / raw)
To: Alex Deucher <alexdeucher@gmail.com>; +Cc: pgsql-performance
At 10:25 AM 3/6/2007, Alex Deucher wrote:
>On 3/5/07, Guido Neitzer <lists@event-s.net> wrote:
>>On 05.03.2007, at 19:56, Alex Deucher wrote:
>>
>> > Yes, I started setting that up this afternoon. I'm going to test that
>> > tomorrow and post the results.
>>
>>Good - that may or may not give some insight in the actual
>>bottleneck. You never know but it seems to be one of the easiest to
>>find out ...
>
>Well, the SAN appears to be the limiting factor. I set up the DB on
>the local scsi discs (software RAID 1) and performance is excellent
>(better than the old server). Thanks for everyone's help.
>
>Alex
What kind of SAN is it and how many + what kind of HDs are in it?
Assuming the answers are reasonable...
Profile the table IO pattern your workload generates and start
allocating RAID sets to tables or groups of tables based on IO pattern.
For any table or group of tables that has a significant level of
write IO, say >= ~25% of the IO mix, try RAID 5 or 6 first, but be
prepared to go RAID 10 if performance is not acceptable.
Don't believe any of the standard "lore" regarding what tables to put
where or what tables to give dedicated spindles to.
Profile, benchmark, and only then start allocating dedicated resources.
For instance, I've seen situations where putting pg_xlog on its own
spindles was !not! the right thing to do.
Best Wishes,
Ron Peacetree
^ permalink raw reply [nested|flat] 53+ messages in thread
* Re: strange performance regression between 7.4 and 8.1
2007-03-01 20:40 strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-06 02:14 ` Re: strange performance regression between 7.4 and 8.1 Guido Neitzer <lists@event-s.net>
2007-03-06 02:56 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-06 02:58 ` Re: strange performance regression between 7.4 and 8.1 Guido Neitzer <lists@event-s.net>
2007-03-06 15:25 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-06 16:58 ` Re: strange performance regression between 7.4 and 8.1 Ron <rjpeace@earthlink.net>
@ 2007-03-06 18:11 ` Alex Deucher <alexdeucher@gmail.com>
2007-03-08 12:23 ` Re: strange performance regression between 7.4 and 8.1 Rodrigo Madera <rodrigo.madera@gmail.com>
0 siblings, 1 reply; 53+ messages in thread
From: Alex Deucher @ 2007-03-06 18:11 UTC (permalink / raw)
To: Ron <rjpeace@earthlink.net>; +Cc: pgsql-performance
On 3/6/07, Ron <rjpeace@earthlink.net> wrote:
> At 10:25 AM 3/6/2007, Alex Deucher wrote:
> >On 3/5/07, Guido Neitzer <lists@event-s.net> wrote:
> >>On 05.03.2007, at 19:56, Alex Deucher wrote:
> >>
> >> > Yes, I started setting that up this afternoon. I'm going to test that
> >> > tomorrow and post the results.
> >>
> >>Good - that may or may not give some insight in the actual
> >>bottleneck. You never know but it seems to be one of the easiest to
> >>find out ...
> >
> >Well, the SAN appears to be the limiting factor. I set up the DB on
> >the local scsi discs (software RAID 1) and performance is excellent
> >(better than the old server). Thanks for everyone's help.
> >
> >Alex
>
> What kind of SAN is it and how many + what kind of HDs are in it?
> Assuming the answers are reasonable...
>
It's a Hitachi WMS/Tagmastore. 105 hitachi SATA drives; 4 Gbps FC.
> Profile the table IO pattern your workload generates and start
> allocating RAID sets to tables or groups of tables based on IO pattern.
>
> For any table or group of tables that has a significant level of
> write IO, say >= ~25% of the IO mix, try RAID 5 or 6 first, but be
> prepared to go RAID 10 if performance is not acceptable.
>
Right now it's designed for max capacity: big RAID 5 groups. I expect
I'll probably need RAID 10 for decent performance.
> Don't believe any of the standard "lore" regarding what tables to put
> where or what tables to give dedicated spindles to.
> Profile, benchmark, and only then start allocating dedicated resources.
> For instance, I've seen situations where putting pg_xlog on its own
> spindles was !not! the right thing to do.
>
Right. Thanks for the advice. I'll post my results when I get around
to testing some new SAN configurations.
Alex
^ permalink raw reply [nested|flat] 53+ messages in thread
* Re: strange performance regression between 7.4 and 8.1
2007-03-01 20:40 strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-06 02:14 ` Re: strange performance regression between 7.4 and 8.1 Guido Neitzer <lists@event-s.net>
2007-03-06 02:56 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-06 02:58 ` Re: strange performance regression between 7.4 and 8.1 Guido Neitzer <lists@event-s.net>
2007-03-06 15:25 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-06 16:58 ` Re: strange performance regression between 7.4 and 8.1 Ron <rjpeace@earthlink.net>
2007-03-06 18:11 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
@ 2007-03-08 12:23 ` Rodrigo Madera <rodrigo.madera@gmail.com>
2007-03-08 18:34 ` Re: strange performance regression between 7.4 and 8.1 Craig A. James <cjames@modgraph-usa.com>
0 siblings, 1 reply; 53+ messages in thread
From: Rodrigo Madera @ 2007-03-08 12:23 UTC (permalink / raw)
To: Alex Deucher <alexdeucher@gmail.com>; +Cc: Ron <rjpeace@earthlink.net>; pgsql-performance
I would just like to note here that this is an example of inefficient
strategy.
We could all agree (up to a certain economical point) that Alex saved the
most expensive one thousand dollars of his life.
I don't know the financial status nor the size of your organization, but I'm
sure that you have selected the path that has cost you more.
In the future, an investment on memory for a (let's say) rather small
database should be your first attempt.
Yours,
Rodrigo Madera
On 3/6/07, Alex Deucher <alexdeucher@gmail.com> wrote:
>
> On 3/6/07, Ron <rjpeace@earthlink.net> wrote:
> > At 10:25 AM 3/6/2007, Alex Deucher wrote:
> > >On 3/5/07, Guido Neitzer <lists@event-s.net> wrote:
> > >>On 05.03.2007, at 19:56, Alex Deucher wrote:
> > >>
> > >> > Yes, I started setting that up this afternoon. I'm going to test
> that
> > >> > tomorrow and post the results.
> > >>
> > >>Good - that may or may not give some insight in the actual
> > >>bottleneck. You never know but it seems to be one of the easiest to
> > >>find out ...
> > >
> > >Well, the SAN appears to be the limiting factor. I set up the DB on
> > >the local scsi discs (software RAID 1) and performance is excellent
> > >(better than the old server). Thanks for everyone's help.
> > >
> > >Alex
> >
> > What kind of SAN is it and how many + what kind of HDs are in it?
> > Assuming the answers are reasonable...
> >
>
> It's a Hitachi WMS/Tagmastore. 105 hitachi SATA drives; 4 Gbps FC.
>
> > Profile the table IO pattern your workload generates and start
> > allocating RAID sets to tables or groups of tables based on IO pattern.
> >
> > For any table or group of tables that has a significant level of
> > write IO, say >= ~25% of the IO mix, try RAID 5 or 6 first, but be
> > prepared to go RAID 10 if performance is not acceptable.
> >
>
> Right now it's designed for max capacity: big RAID 5 groups. I expect
> I'll probably need RAID 10 for decent performance.
>
> > Don't believe any of the standard "lore" regarding what tables to put
> > where or what tables to give dedicated spindles to.
> > Profile, benchmark, and only then start allocating dedicated resources.
> > For instance, I've seen situations where putting pg_xlog on its own
> > spindles was !not! the right thing to do.
> >
>
> Right. Thanks for the advice. I'll post my results when I get around
> to testing some new SAN configurations.
>
> Alex
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
^ permalink raw reply [nested|flat] 53+ messages in thread
* Re: strange performance regression between 7.4 and 8.1
2007-03-01 20:40 strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-06 02:14 ` Re: strange performance regression between 7.4 and 8.1 Guido Neitzer <lists@event-s.net>
2007-03-06 02:56 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-06 02:58 ` Re: strange performance regression between 7.4 and 8.1 Guido Neitzer <lists@event-s.net>
2007-03-06 15:25 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-06 16:58 ` Re: strange performance regression between 7.4 and 8.1 Ron <rjpeace@earthlink.net>
2007-03-06 18:11 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-08 12:23 ` Re: strange performance regression between 7.4 and 8.1 Rodrigo Madera <rodrigo.madera@gmail.com>
@ 2007-03-08 18:34 ` Craig A. James <cjames@modgraph-usa.com>
2007-03-08 18:59 ` Re: strange performance regression between 7.4 and 8.1 Ron <rjpeace@earthlink.net>
2007-03-08 19:34 ` Re: strange performance regression between 7.4 and 8.1 Carlos Moreno <moreno_pg@mochima.com>
0 siblings, 2 replies; 53+ messages in thread
From: Craig A. James @ 2007-03-08 18:34 UTC (permalink / raw)
To: Rodrigo Madera <rodrigo.madera@gmail.com>; +Cc: pgsql-performance; Alex Deucher <alexdeucher@gmail.com>
Rodrigo Madera wrote:
> I would just like to note here that this is an example of inefficient
> strategy.
>
> We could all agree (up to a certain economical point) that Alex saved
> the most expensive one thousand dollars of his life.
>
> I don't know the financial status nor the size of your organization, but
> I'm sure that you have selected the path that has cost you more.
>
> In the future, an investment on memory for a (let's say) rather small
> database should be your first attempt.
Alex may have made the correct, rational choice, given the state of accounting at most corporations. Corporate accounting practices and the budgetary process give different weights to cash and labor. Labor is fixed, and can be grossly wasted without (apparently) affecting the quarterly bottom line. Cash expenditures come directly off profits.
It's shortsighted and irrational, but nearly 100% of corporations operate this way. You can waste a week of your time and nobody complains, but spend a thousand dollars, and the company president is breathing down your neck.
When we answer a question on this forum, we need to understand that the person who needs help may be under irrational, but real, constraints, and offer appropriate advice. Sure, it's good to fight corporate stupidity, but sometimes you just want to get the system back online.
Craig
^ permalink raw reply [nested|flat] 53+ messages in thread
* Re: strange performance regression between 7.4 and 8.1
2007-03-01 20:40 strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-06 02:14 ` Re: strange performance regression between 7.4 and 8.1 Guido Neitzer <lists@event-s.net>
2007-03-06 02:56 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-06 02:58 ` Re: strange performance regression between 7.4 and 8.1 Guido Neitzer <lists@event-s.net>
2007-03-06 15:25 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-06 16:58 ` Re: strange performance regression between 7.4 and 8.1 Ron <rjpeace@earthlink.net>
2007-03-06 18:11 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-08 12:23 ` Re: strange performance regression between 7.4 and 8.1 Rodrigo Madera <rodrigo.madera@gmail.com>
2007-03-08 18:34 ` Re: strange performance regression between 7.4 and 8.1 Craig A. James <cjames@modgraph-usa.com>
@ 2007-03-08 18:59 ` Ron <rjpeace@earthlink.net>
2007-03-08 19:38 ` Re: strange performance regression between 7.4 and 8.1 Carlos Moreno <moreno_pg@mochima.com>
1 sibling, 1 reply; 53+ messages in thread
From: Ron @ 2007-03-08 18:59 UTC (permalink / raw)
To: Craig A. James <cjames@modgraph-usa.com>; +Cc: pgsql-performance; Alex Deucher <alexdeucher@gmail.com>
At 01:34 PM 3/8/2007, Craig A. James wrote:
>Rodrigo Madera wrote:
>>I would just like to note here that this is an example of
>>inefficient strategy.
>>We could all agree (up to a certain economical point) that Alex
>>saved the most expensive one thousand dollars of his life.
>>I don't know the financial status nor the size of your
>>organization, but I'm sure that you have selected the path that has
>>cost you more.
>>In the future, an investment on memory for a (let's say) rather
>>small database should be your first attempt.
>
>Alex may have made the correct, rational choice, given the state of
>accounting at most corporations. Corporate accounting practices and
>the budgetary process give different weights to cash and
>labor. Labor is fixed, and can be grossly wasted without
>(apparently) affecting the quarterly bottom line. Cash expenditures
>come directly off profits.
>
>It's shortsighted and irrational, but nearly 100% of corporations
>operate this way. You can waste a week of your time and nobody
>complains, but spend a thousand dollars, and the company president
>is breathing down your neck.
>
>When we answer a question on this forum, we need to understand that
>the person who needs help may be under irrational, but real,
>constraints, and offer appropriate advice. Sure, it's good to fight
>corporate stupidity, but sometimes you just want to get the system back online.
>
>Craig
All good points.
However, when we allow or help (even tacitly by "looking the other
way") our organizations to waste IT dollars we increase the risk that
we are going to be paid less because there's less money. Or even
that we will be unemployed because there's less money (as in "we
wasted enough money we went out of business").
The correct strategy is to Speak Their Language (tm) to the
accounting and management folks and give them the information needed
to Do The Right Thing (tm) (or at least authorize you doing it ;-)
). They may still not be / act sane, but at that point your hands are clean.
(...and if your organization has a habit of Not Listening to Reason
(tm), strongly consider finding a new job before you are forced to by
their fiscal or managerial irresponsibility.)
Cap Ex may not be the same as Discretionary Expenses, but at the end
of the day dollars are dollars.
Any we spend in one place can't be spent in any other place; and
there's a finite pile of them.
Spending 10x as much in labor and opportunity costs (you can only do
one thing at a time...) as you would on CapEx to address a problem is
simply not smart money management nor good business. Even spending
2x as much in that fashion is probably not.
Cheers,
Ron Peacetree
^ permalink raw reply [nested|flat] 53+ messages in thread
* Re: strange performance regression between 7.4 and 8.1
2007-03-01 20:40 strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-06 02:14 ` Re: strange performance regression between 7.4 and 8.1 Guido Neitzer <lists@event-s.net>
2007-03-06 02:56 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-06 02:58 ` Re: strange performance regression between 7.4 and 8.1 Guido Neitzer <lists@event-s.net>
2007-03-06 15:25 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-06 16:58 ` Re: strange performance regression between 7.4 and 8.1 Ron <rjpeace@earthlink.net>
2007-03-06 18:11 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-08 12:23 ` Re: strange performance regression between 7.4 and 8.1 Rodrigo Madera <rodrigo.madera@gmail.com>
2007-03-08 18:34 ` Re: strange performance regression between 7.4 and 8.1 Craig A. James <cjames@modgraph-usa.com>
2007-03-08 18:59 ` Re: strange performance regression between 7.4 and 8.1 Ron <rjpeace@earthlink.net>
@ 2007-03-08 19:38 ` Carlos Moreno <moreno_pg@mochima.com>
0 siblings, 0 replies; 53+ messages in thread
From: Carlos Moreno @ 2007-03-08 19:38 UTC (permalink / raw)
To: Ron <rjpeace@earthlink.net>; +Cc: pgsql-performance
Ron wrote:
>
> Speak Their Language (tm) [ ... ] Do The Right Thing (tm)
> [...] Not Listening to Reason (tm),
> [...]
>
> fiscal or managerial irresponsibility.)
And *here*, of all the instances, you don't put a (TM) sign ??!!!!
Tsk-tsk-tsk
:-)
Carlos
--
^ permalink raw reply [nested|flat] 53+ messages in thread
* Re: strange performance regression between 7.4 and 8.1
2007-03-01 20:40 strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-06 02:14 ` Re: strange performance regression between 7.4 and 8.1 Guido Neitzer <lists@event-s.net>
2007-03-06 02:56 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-06 02:58 ` Re: strange performance regression between 7.4 and 8.1 Guido Neitzer <lists@event-s.net>
2007-03-06 15:25 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-06 16:58 ` Re: strange performance regression between 7.4 and 8.1 Ron <rjpeace@earthlink.net>
2007-03-06 18:11 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-08 12:23 ` Re: strange performance regression between 7.4 and 8.1 Rodrigo Madera <rodrigo.madera@gmail.com>
2007-03-08 18:34 ` Re: strange performance regression between 7.4 and 8.1 Craig A. James <cjames@modgraph-usa.com>
@ 2007-03-08 19:34 ` Carlos Moreno <moreno_pg@mochima.com>
2007-03-08 21:07 ` Re: strange performance regression between 7.4 and 8.1 Craig A. James <cjames@modgraph-usa.com>
1 sibling, 1 reply; 53+ messages in thread
From: Carlos Moreno @ 2007-03-08 19:34 UTC (permalink / raw)
To: pgsql-performance
>> I would just like to note here that this is an example of inefficient
>> strategy.
>> [ ... ]
>
>
> Alex may have made the correct, rational choice, given the state of
> accounting at most corporations. Corporate accounting practices and
> the budgetary process give different weights to cash and labor. Labor
> is fixed, and can be grossly wasted without (apparently) affecting the
> quarterly bottom line. Cash expenditures come directly off profits.
>
> It's shortsighted and irrational, but nearly 100% of corporations
> operate this way. You can waste a week of your time and nobody
> complains, but spend a thousand dollars, and the company president is
> breathing down your neck.
>
> When we answer a question on this forum, we need to understand that
> the person who needs help may be under irrational, but real,
> constraints, and offer appropriate advice. Sure, it's good to fight
> corporate stupidity, but sometimes you just want to get the system
> back online.
Another thing --- which may or may not apply to Alex's case and to the
particular state of the thread, but it's still related and IMHO
important to
take into account:
There may be other consrtaints that makes it impossible to even consider
a memory upgrade --- for example, us (our project). We *rent* the servers
from a Web hoster (dedicated servers). This particular hoster does not
even offer the possibility of upgrading the hardware --- 2GB of RAM,
take it r leave it. Period.
In other cases, the memory upgrade has a *monthly* cost (and quite
often I find it excessive --- granted, that may be just me). So, $50 or
$100 per month *additional* expenses may be considerable.
Now, yet another thing that you (Craig) seem to be missing: you're
simply putting the expense of all this time under the expenses column
in exchange for solving the particular problem --- gaining the insight
on the internals and performance tuning techniques for PG may well
be worth tens of thousands of dollars for his company in the future.
The "quick and dirty" solution is not giving a damn about knowledge
but to the ability to solve the problem at hand *now*, at whatever
"petty cash cost" because it looks more cost effective (when seen
from the non-irrational accounting point of view, that is) --- but isn't
going for the "quick and dirty" solution without learning anything
from the experience also shortsighted ???
Carlos
--
^ permalink raw reply [nested|flat] 53+ messages in thread
* Re: strange performance regression between 7.4 and 8.1
2007-03-01 20:40 strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-06 02:14 ` Re: strange performance regression between 7.4 and 8.1 Guido Neitzer <lists@event-s.net>
2007-03-06 02:56 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-06 02:58 ` Re: strange performance regression between 7.4 and 8.1 Guido Neitzer <lists@event-s.net>
2007-03-06 15:25 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-06 16:58 ` Re: strange performance regression between 7.4 and 8.1 Ron <rjpeace@earthlink.net>
2007-03-06 18:11 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-08 12:23 ` Re: strange performance regression between 7.4 and 8.1 Rodrigo Madera <rodrigo.madera@gmail.com>
2007-03-08 18:34 ` Re: strange performance regression between 7.4 and 8.1 Craig A. James <cjames@modgraph-usa.com>
2007-03-08 19:34 ` Re: strange performance regression between 7.4 and 8.1 Carlos Moreno <moreno_pg@mochima.com>
@ 2007-03-08 21:07 ` Craig A. James <cjames@modgraph-usa.com>
0 siblings, 0 replies; 53+ messages in thread
From: Craig A. James @ 2007-03-08 21:07 UTC (permalink / raw)
To: Carlos Moreno <moreno_pg@mochima.com>; +Cc: pgsql-performance
Carlos,
> Now, yet another thing that you (Craig) seem to be missing: you're
> simply putting the expense of all this time under the expenses column
> in exchange for solving the particular problem...
More like I was trying to keep my response short ;-). I think we're all in agreement on pretty much everything:
1. Understand your problem
2. Find potential solutions
3. Find the technical, economic AND situational tradeoffs
4. Choose the best course of action
My original comment was directed at item #3. I was trying to remind everyone that a simple cost analysis may point to solutions that simply aren't possible, given business constraints.
I know we also agree that we should constantly fight corporate stupidity and short-sighted budgetary oversight. But that's a second battle, one that goes on forever. Sometimes you just have to get the job done within the current constraints.
'Nuff said.
Craig
^ permalink raw reply [nested|flat] 53+ messages in thread
* strange performance regression between 7.4 and 8.1
@ 2007-03-01 20:44 Alex Deucher <alexdeucher@gmail.com>
2007-03-01 20:50 ` Re: strange performance regression between 7.4 and 8.1 Joshua D. Drake <jd@commandprompt.com>
2007-03-02 08:51 ` Re: strange performance regression between 7.4 and 8.1 Florian Weimer <fweimer@bfk.de>
2007-03-02 17:45 ` Re: strange performance regression between 7.4 and 8.1 Anton Rommerskirchen <atr@atrsoft.de>
0 siblings, 3 replies; 53+ messages in thread
From: Alex Deucher @ 2007-03-01 20:44 UTC (permalink / raw)
To: pgsql-performance
Hello,
I have noticed a strange performance regression and I'm at a loss as
to what's happening. We have a fairly large database (~16 GB). The
original postgres 7.4 was running on a sun v880 with 4 CPUs and 8 GB
of ram running Solaris on local scsi discs. The new server is a sun
Opteron box with 4 cores, 8 GB of ram running postgres 8.1.4 on Linux
(AMD64) on a 4 Gbps FC SAN volume. When we created the new database
it was created from scratch rather than copying over the old one,
however the table structure is almost identical (UTF8 on the new one
vs. C on the old). The problem is queries are ~10x slower on the new
hardware. I read several places that the SAN might be to blame, but
testing with bonnie and dd indicates that the SAN is actually almost
twice as fast as the scsi discs in the old sun server. I've tried
adjusting just about every option in the postgres config file, but
performance remains the same. Any ideas?
Thanks,
Alex
^ permalink raw reply [nested|flat] 53+ messages in thread
* Re: strange performance regression between 7.4 and 8.1
2007-03-01 20:44 strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
@ 2007-03-01 20:50 ` Joshua D. Drake <jd@commandprompt.com>
2007-03-01 20:56 ` Re: strange performance regression between 7.4 and 8.1 Jeff Frost <jeff@frostconsultingllc.com>
2007-03-01 21:06 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2 siblings, 2 replies; 53+ messages in thread
From: Joshua D. Drake @ 2007-03-01 20:50 UTC (permalink / raw)
To: Alex Deucher <alexdeucher@gmail.com>; +Cc: pgsql-performance
Alex Deucher wrote:
> Hello,
>
> I have noticed a strange performance regression and I'm at a loss as
> to what's happening. We have a fairly large database (~16 GB). The
> original postgres 7.4 was running on a sun v880 with 4 CPUs and 8 GB
> of ram running Solaris on local scsi discs. The new server is a sun
> Opteron box with 4 cores, 8 GB of ram running postgres 8.1.4 on Linux
> (AMD64) on a 4 Gbps FC SAN volume. When we created the new database
> it was created from scratch rather than copying over the old one,
> however the table structure is almost identical (UTF8 on the new one
> vs. C on the old). The problem is queries are ~10x slower on the new
> hardware. I read several places that the SAN might be to blame, but
> testing with bonnie and dd indicates that the SAN is actually almost
> twice as fast as the scsi discs in the old sun server. I've tried
> adjusting just about every option in the postgres config file, but
> performance remains the same. Any ideas?
Vacuum? Analayze? default_statistics_target? How many shared_buffers?
effective_cache_size? work_mem?
Sincerely,
Joshua D. Drake
>
> Thanks,
>
> Alex
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
^ permalink raw reply [nested|flat] 53+ messages in thread
* Re: strange performance regression between 7.4 and 8.1
2007-03-01 20:44 strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-01 20:50 ` Re: strange performance regression between 7.4 and 8.1 Joshua D. Drake <jd@commandprompt.com>
@ 2007-03-01 20:56 ` Jeff Frost <jeff@frostconsultingllc.com>
2007-03-01 21:11 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-01 23:11 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
1 sibling, 2 replies; 53+ messages in thread
From: Jeff Frost @ 2007-03-01 20:56 UTC (permalink / raw)
To: Joshua D. Drake <jd@commandprompt.com>; +Cc: Alex Deucher <alexdeucher@gmail.com>; pgsql-performance
On Thu, 1 Mar 2007, Joshua D. Drake wrote:
> Alex Deucher wrote:
>> Hello,
>>
>> I have noticed a strange performance regression and I'm at a loss as
>> to what's happening. We have a fairly large database (~16 GB). The
>> original postgres 7.4 was running on a sun v880 with 4 CPUs and 8 GB
>> of ram running Solaris on local scsi discs. The new server is a sun
>> Opteron box with 4 cores, 8 GB of ram running postgres 8.1.4 on Linux
>> (AMD64) on a 4 Gbps FC SAN volume. When we created the new database
>> it was created from scratch rather than copying over the old one,
>> however the table structure is almost identical (UTF8 on the new one
>> vs. C on the old). The problem is queries are ~10x slower on the new
>> hardware. I read several places that the SAN might be to blame, but
>> testing with bonnie and dd indicates that the SAN is actually almost
>> twice as fast as the scsi discs in the old sun server. I've tried
>> adjusting just about every option in the postgres config file, but
>> performance remains the same. Any ideas?
>
> Vacuum? Analayze? default_statistics_target? How many shared_buffers?
> effective_cache_size? work_mem?
Also, an explain analyze from both the 7.4 and 8.1 systems with one of the
10x slower queries would probably be handy.
What do you mean by "created from scratch rather than copying over the old
one"? How did you put the data in? Did you run analyze after loading it?
Is autovacuum enabled and if so, what are the thresholds?
--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
^ permalink raw reply [nested|flat] 53+ messages in thread
* Re: strange performance regression between 7.4 and 8.1
2007-03-01 20:44 strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-01 20:50 ` Re: strange performance regression between 7.4 and 8.1 Joshua D. Drake <jd@commandprompt.com>
2007-03-01 20:56 ` Re: strange performance regression between 7.4 and 8.1 Jeff Frost <jeff@frostconsultingllc.com>
@ 2007-03-01 21:11 ` Alex Deucher <alexdeucher@gmail.com>
2007-03-01 21:34 ` Re: strange performance regression between 7.4 and 8.1 Jeff Frost <jeff@frostconsultingllc.com>
1 sibling, 1 reply; 53+ messages in thread
From: Alex Deucher @ 2007-03-01 21:11 UTC (permalink / raw)
To: Jeff Frost <jeff@frostconsultingllc.com>; +Cc: Joshua D. Drake <jd@commandprompt.com>; pgsql-performance
On 3/1/07, Jeff Frost <jeff@frostconsultingllc.com> wrote:
> On Thu, 1 Mar 2007, Joshua D. Drake wrote:
>
> > Alex Deucher wrote:
> >> Hello,
> >>
> >> I have noticed a strange performance regression and I'm at a loss as
> >> to what's happening. We have a fairly large database (~16 GB). The
> >> original postgres 7.4 was running on a sun v880 with 4 CPUs and 8 GB
> >> of ram running Solaris on local scsi discs. The new server is a sun
> >> Opteron box with 4 cores, 8 GB of ram running postgres 8.1.4 on Linux
> >> (AMD64) on a 4 Gbps FC SAN volume. When we created the new database
> >> it was created from scratch rather than copying over the old one,
> >> however the table structure is almost identical (UTF8 on the new one
> >> vs. C on the old). The problem is queries are ~10x slower on the new
> >> hardware. I read several places that the SAN might be to blame, but
> >> testing with bonnie and dd indicates that the SAN is actually almost
> >> twice as fast as the scsi discs in the old sun server. I've tried
> >> adjusting just about every option in the postgres config file, but
> >> performance remains the same. Any ideas?
> >
> > Vacuum? Analayze? default_statistics_target? How many shared_buffers?
> > effective_cache_size? work_mem?
>
> Also, an explain analyze from both the 7.4 and 8.1 systems with one of the
> 10x slower queries would probably be handy.
>
I'll run some and get back to you.
> What do you mean by "created from scratch rather than copying over the old
> one"? How did you put the data in? Did you run analyze after loading it?
> Is autovacuum enabled and if so, what are the thresholds?
Both the databases were originally created from xml files. We just
re-created the new one from the xml rather than copying the old
database over. I didn't manually run analyze on it, but we are
running the autovacuum process:
autovacuum = on #off # enable autovacuum subprocess?
autovacuum_naptime = 360 #60 # time between autovacuum runs, in secs
autovacuum_vacuum_threshold = 10000 #1000 # min # of tuple updates before
# vacuum
autovacuum_analyze_threshold = 5000 #500 # min # of tuple updates before
Thanks,
Alex
>
> --
> Jeff Frost, Owner <jeff@frostconsultingllc.com>
> Frost Consulting, LLC http://www.frostconsultingllc.com/
> Phone: 650-780-7908 FAX: 650-649-1954
>
^ permalink raw reply [nested|flat] 53+ messages in thread
* Re: strange performance regression between 7.4 and 8.1
2007-03-01 20:44 strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-01 20:50 ` Re: strange performance regression between 7.4 and 8.1 Joshua D. Drake <jd@commandprompt.com>
2007-03-01 20:56 ` Re: strange performance regression between 7.4 and 8.1 Jeff Frost <jeff@frostconsultingllc.com>
2007-03-01 21:11 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
@ 2007-03-01 21:34 ` Jeff Frost <jeff@frostconsultingllc.com>
0 siblings, 0 replies; 53+ messages in thread
From: Jeff Frost @ 2007-03-01 21:34 UTC (permalink / raw)
To: Alex Deucher <alexdeucher@gmail.com>; +Cc: Joshua D. Drake <jd@commandprompt.com>; pgsql-performance
On Thu, 1 Mar 2007, Alex Deucher wrote:
> On 3/1/07, Jeff Frost <jeff@frostconsultingllc.com> wrote:
>> On Thu, 1 Mar 2007, Joshua D. Drake wrote:
>>
>> > Alex Deucher wrote:
>> >> Hello,
>> >>
>> >> I have noticed a strange performance regression and I'm at a loss as
>> >> to what's happening. We have a fairly large database (~16 GB). The
>> >> original postgres 7.4 was running on a sun v880 with 4 CPUs and 8 GB
>> >> of ram running Solaris on local scsi discs. The new server is a sun
>> >> Opteron box with 4 cores, 8 GB of ram running postgres 8.1.4 on Linux
>> >> (AMD64) on a 4 Gbps FC SAN volume. When we created the new database
>> >> it was created from scratch rather than copying over the old one,
>> >> however the table structure is almost identical (UTF8 on the new one
>> >> vs. C on the old). The problem is queries are ~10x slower on the new
>> >> hardware. I read several places that the SAN might be to blame, but
>> >> testing with bonnie and dd indicates that the SAN is actually almost
>> >> twice as fast as the scsi discs in the old sun server. I've tried
>> >> adjusting just about every option in the postgres config file, but
>> >> performance remains the same. Any ideas?
>> >
>> > Vacuum? Analayze? default_statistics_target? How many shared_buffers?
>> > effective_cache_size? work_mem?
>>
>> Also, an explain analyze from both the 7.4 and 8.1 systems with one of the
>> 10x slower queries would probably be handy.
>>
>
> I'll run some and get back to you.
>
>> What do you mean by "created from scratch rather than copying over the old
>> one"? How did you put the data in? Did you run analyze after loading it?
>> Is autovacuum enabled and if so, what are the thresholds?
>
> Both the databases were originally created from xml files. We just
> re-created the new one from the xml rather than copying the old
> database over. I didn't manually run analyze on it, but we are
> running the autovacuum process:
You should probably manually run analyze and see if that resolves your
problem.
>
> autovacuum = on #off # enable autovacuum subprocess?
> autovacuum_naptime = 360 #60 # time between autovacuum runs, in
> secs
> autovacuum_vacuum_threshold = 10000 #1000 # min # of tuple updates
> before
> # vacuum
> autovacuum_analyze_threshold = 5000 #500 # min # of tuple updates
> before
Most people make autovacuum more aggressive and not less aggressive. In fact,
the new defaults in 8.2 are:
#autovacuum_vacuum_threshold = 500 # min # of tuple updates before
# vacuum
#autovacuum_analyze_threshold = 250 # min # of tuple updates before
# analyze
#autovacuum_vacuum_scale_factor = 0.2 # fraction of rel size before
# vacuum
#autovacuum_analyze_scale_factor = 0.1 # fraction of rel size before
I'd recommend trying those, otherwise you might not vacuum enough.
It'll be interesting to see the explain analyze output after you've run
analyze by hand.
--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
^ permalink raw reply [nested|flat] 53+ messages in thread
* Re: strange performance regression between 7.4 and 8.1
2007-03-01 20:44 strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-01 20:50 ` Re: strange performance regression between 7.4 and 8.1 Joshua D. Drake <jd@commandprompt.com>
2007-03-01 20:56 ` Re: strange performance regression between 7.4 and 8.1 Jeff Frost <jeff@frostconsultingllc.com>
@ 2007-03-01 23:11 ` Alex Deucher <alexdeucher@gmail.com>
2007-03-02 00:21 ` Re: strange performance regression between 7.4 and 8.1 Jeff Frost <jeff@frostconsultingllc.com>
1 sibling, 1 reply; 53+ messages in thread
From: Alex Deucher @ 2007-03-01 23:11 UTC (permalink / raw)
To: Jeff Frost <jeff@frostconsultingllc.com>; +Cc: Joshua D. Drake <jd@commandprompt.com>; pgsql-performance
On 3/1/07, Jeff Frost <jeff@frostconsultingllc.com> wrote:
> On Thu, 1 Mar 2007, Joshua D. Drake wrote:
>
> > Alex Deucher wrote:
> >> Hello,
> >>
> >> I have noticed a strange performance regression and I'm at a loss as
> >> to what's happening. We have a fairly large database (~16 GB). The
> >> original postgres 7.4 was running on a sun v880 with 4 CPUs and 8 GB
> >> of ram running Solaris on local scsi discs. The new server is a sun
> >> Opteron box with 4 cores, 8 GB of ram running postgres 8.1.4 on Linux
> >> (AMD64) on a 4 Gbps FC SAN volume. When we created the new database
> >> it was created from scratch rather than copying over the old one,
> >> however the table structure is almost identical (UTF8 on the new one
> >> vs. C on the old). The problem is queries are ~10x slower on the new
> >> hardware. I read several places that the SAN might be to blame, but
> >> testing with bonnie and dd indicates that the SAN is actually almost
> >> twice as fast as the scsi discs in the old sun server. I've tried
> >> adjusting just about every option in the postgres config file, but
> >> performance remains the same. Any ideas?
> >
> > Vacuum? Analayze? default_statistics_target? How many shared_buffers?
> > effective_cache_size? work_mem?
>
> Also, an explain analyze from both the 7.4 and 8.1 systems with one of the
> 10x slower queries would probably be handy.
here are some examples. Analyze is still running on the new db, I'll
post results when that is done. Mostly what our apps do is prepared
row selects from different tables:
select c1,c2,c3,c4,c5 from t1 where c1='XXX';
old server:
db=# EXPLAIN ANALYZE select c1,c2 from t1 where c2='6258261';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Index Scan using t1_c2_index on t1 (cost=0.00..166.89 rows=42
width=26) (actual time=5.722..5.809 rows=2 loops=1)
Index Cond: ((c2)::text = '6258261'::text)
Total runtime: 5.912 ms
(3 rows)
db=# EXPLAIN ANALYZE select c1,c2 from t1 where c1='6258261';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Index Scan using t1_c1_key on t1 (cost=0.00..286.08 rows=72
width=26) (actual time=12.423..12.475 rows=12 loops=1)
Index Cond: ((c1)::text = '6258261'::text)
Total runtime: 12.538 ms
(3 rows)
new server:
db=# EXPLAIN ANALYZE select c1,c2 from t1 where c2='6258261';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Index Scan using t1_c2_index on t1 (cost=0.00..37.63 rows=11
width=26) (actual time=33.461..51.377 rows=2 loops=1)
Index Cond: ((c2)::text = '6258261'::text)
Total runtime: 51.419 ms
(3 rows)
db=# EXPLAIN ANALYZE select c1,c2 from t1 where c1='6258261';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Index Scan using t1_c1_index on t1 (cost=0.00..630.45 rows=2907
width=26) (actual time=45.733..46.271 rows=12 loops=1)
Index Cond: ((c1)::text = '6258261'::text)
Total runtime: 46.325 ms
(3 rows)
Alex
^ permalink raw reply [nested|flat] 53+ messages in thread
* Re: strange performance regression between 7.4 and 8.1
2007-03-01 20:44 strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-01 20:50 ` Re: strange performance regression between 7.4 and 8.1 Joshua D. Drake <jd@commandprompt.com>
2007-03-01 20:56 ` Re: strange performance regression between 7.4 and 8.1 Jeff Frost <jeff@frostconsultingllc.com>
2007-03-01 23:11 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
@ 2007-03-02 00:21 ` Jeff Frost <jeff@frostconsultingllc.com>
2007-03-02 15:28 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
0 siblings, 1 reply; 53+ messages in thread
From: Jeff Frost @ 2007-03-02 00:21 UTC (permalink / raw)
To: Alex Deucher <alexdeucher@gmail.com>; +Cc: Joshua D. Drake <jd@commandprompt.com>; pgsql-performance
On Thu, 1 Mar 2007, Alex Deucher wrote:
> here are some examples. Analyze is still running on the new db, I'll
> post results when that is done. Mostly what our apps do is prepared
> row selects from different tables:
> select c1,c2,c3,c4,c5 from t1 where c1='XXX';
>
> old server:
> db=# EXPLAIN ANALYZE select c1,c2 from t1 where c2='6258261';
> QUERY PLAN
> ---------------------------------------------------------------------------------------------------------------------------
> Index Scan using t1_c2_index on t1 (cost=0.00..166.89 rows=42
> width=26) (actual time=5.722..5.809 rows=2 loops=1)
> Index Cond: ((c2)::text = '6258261'::text)
> Total runtime: 5.912 ms
> (3 rows)
>
> db=# EXPLAIN ANALYZE select c1,c2 from t1 where c1='6258261';
> QUERY PLAN
> ----------------------------------------------------------------------------------------------------------------------------
> Index Scan using t1_c1_key on t1 (cost=0.00..286.08 rows=72
> width=26) (actual time=12.423..12.475 rows=12 loops=1)
> Index Cond: ((c1)::text = '6258261'::text)
> Total runtime: 12.538 ms
> (3 rows)
>
>
> new server:
> db=# EXPLAIN ANALYZE select c1,c2 from t1 where c2='6258261';
> QUERY PLAN
> ----------------------------------------------------------------------------------------------------------------------------
> Index Scan using t1_c2_index on t1 (cost=0.00..37.63 rows=11
> width=26) (actual time=33.461..51.377 rows=2 loops=1)
> Index Cond: ((c2)::text = '6258261'::text)
> Total runtime: 51.419 ms
> (3 rows)
>
> db=# EXPLAIN ANALYZE select c1,c2 from t1 where c1='6258261';
> QUERY PLAN
> --------------------------------------------------------------------------------------------------------------------------------
> Index Scan using t1_c1_index on t1 (cost=0.00..630.45 rows=2907
> width=26) (actual time=45.733..46.271 rows=12 loops=1)
> Index Cond: ((c1)::text = '6258261'::text)
> Total runtime: 46.325 ms
> (3 rows)
Notice the huge disparity here betwen the expected number of rows (2907) and
the actual rows? That's indicative of needing to run analyze. The time is
only about 4x the 7.4 runtime and that's with the analyze running merrily
along in the background. It's probably not as bad off as you think. At least
this query isn't 10x. :-)
Run these again for us after analyze is complete.
--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
^ permalink raw reply [nested|flat] 53+ messages in thread
* Re: strange performance regression between 7.4 and 8.1
2007-03-01 20:44 strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-01 20:50 ` Re: strange performance regression between 7.4 and 8.1 Joshua D. Drake <jd@commandprompt.com>
2007-03-01 20:56 ` Re: strange performance regression between 7.4 and 8.1 Jeff Frost <jeff@frostconsultingllc.com>
2007-03-01 23:11 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-02 00:21 ` Re: strange performance regression between 7.4 and 8.1 Jeff Frost <jeff@frostconsultingllc.com>
@ 2007-03-02 15:28 ` Alex Deucher <alexdeucher@gmail.com>
2007-03-02 16:10 ` Re: strange performance regression between 7.4 and 8.1 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 53+ messages in thread
From: Alex Deucher @ 2007-03-02 15:28 UTC (permalink / raw)
To: Jeff Frost <jeff@frostconsultingllc.com>; +Cc: Joshua D. Drake <jd@commandprompt.com>; pgsql-performance
On 3/1/07, Jeff Frost <jeff@frostconsultingllc.com> wrote:
> On Thu, 1 Mar 2007, Alex Deucher wrote:
>
> > here are some examples. Analyze is still running on the new db, I'll
> > post results when that is done. Mostly what our apps do is prepared
> > row selects from different tables:
> > select c1,c2,c3,c4,c5 from t1 where c1='XXX';
> >
> > old server:
> > db=# EXPLAIN ANALYZE select c1,c2 from t1 where c2='6258261';
> > QUERY PLAN
> > ---------------------------------------------------------------------------------------------------------------------------
> > Index Scan using t1_c2_index on t1 (cost=0.00..166.89 rows=42
> > width=26) (actual time=5.722..5.809 rows=2 loops=1)
> > Index Cond: ((c2)::text = '6258261'::text)
> > Total runtime: 5.912 ms
> > (3 rows)
> >
> > db=# EXPLAIN ANALYZE select c1,c2 from t1 where c1='6258261';
> > QUERY PLAN
> > ----------------------------------------------------------------------------------------------------------------------------
> > Index Scan using t1_c1_key on t1 (cost=0.00..286.08 rows=72
> > width=26) (actual time=12.423..12.475 rows=12 loops=1)
> > Index Cond: ((c1)::text = '6258261'::text)
> > Total runtime: 12.538 ms
> > (3 rows)
> >
> >
> > new server:
> > db=# EXPLAIN ANALYZE select c1,c2 from t1 where c2='6258261';
> > QUERY PLAN
> > ----------------------------------------------------------------------------------------------------------------------------
> > Index Scan using t1_c2_index on t1 (cost=0.00..37.63 rows=11
> > width=26) (actual time=33.461..51.377 rows=2 loops=1)
> > Index Cond: ((c2)::text = '6258261'::text)
> > Total runtime: 51.419 ms
> > (3 rows)
> >
> > db=# EXPLAIN ANALYZE select c1,c2 from t1 where c1='6258261';
> > QUERY PLAN
> > --------------------------------------------------------------------------------------------------------------------------------
> > Index Scan using t1_c1_index on t1 (cost=0.00..630.45 rows=2907
> > width=26) (actual time=45.733..46.271 rows=12 loops=1)
> > Index Cond: ((c1)::text = '6258261'::text)
> > Total runtime: 46.325 ms
> > (3 rows)
>
> Notice the huge disparity here betwen the expected number of rows (2907) and
> the actual rows? That's indicative of needing to run analyze. The time is
> only about 4x the 7.4 runtime and that's with the analyze running merrily
> along in the background. It's probably not as bad off as you think. At least
> this query isn't 10x. :-)
>
> Run these again for us after analyze is complete.
well, while the DB isn't 10x, the application using the DB shoes a 10x
decrease in performance. Pages that used to take 5 seconds to load
take 50 secs (I supposed the problem is compounded as there are
several queries per page.). Anyway, new numbers after the analyze.
Unfortunately, they are improved, but still not great:
old server:
db=# EXPLAIN ANALYZE select c1,c2 from t1 where c2='6258261';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Index Scan using t1_c2_index on t1 (cost=0.00..166.89 rows=42
width=26) (actual time=0.204..0.284 rows=2 loops=1)
Index Cond: ((c2)::text = '6258261'::text)
Total runtime: 0.421 ms
(3 rows)
db=# EXPLAIN ANALYZE select c1,c2 from t1 where c1='6258261';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Index Scan using t1_c1_key on t1 (cost=0.00..286.08 rows=72
width=26) (actual time=0.299..0.354 rows=12 loops=1)
Index Cond: ((c1)::text = '6258261'::text)
Total runtime: 0.451 ms
(3 rows)
new server:
db=# EXPLAIN ANALYZE select c1,c2 from t1 where c2='6258261';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Index Scan using t1_c2_index on t1 (cost=0.00..37.63 rows=11
width=26) (actual time=0.126..0.134 rows=2 loops=1)
Index Cond: ((c2)::text = '6258261'::text)
Total runtime: 0.197 ms
(3 rows)
db=# EXPLAIN ANALYZE select c1,c2 from t1 where c1='6258261';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Index Scan using t1_c1_index on t1 (cost=0.00..630.45 rows=2907
width=26) (actual time=5.820..5.848 rows=12 loops=1)
Index Cond: ((c1)::text = '6258261'::text)
Total runtime: 5.899 ms
(3 rows)
Here's another example:
old server:
db=# EXPLAIN ANALYZE select c1,c2 from t1 where c2='6000001';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Index Scan using t1_c2_index on t1 (cost=0.00..166.89 rows=42
width=26) (actual time=4.031..55.349 rows=8 loops=1)
Index Cond: ((c2)::text = '6000001'::text)
Total runtime: 55.459 ms
(3 rows)
db=# EXPLAIN ANALYZE select c1,c2 from t1 where c1='6000001';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Index Scan using t1_c1_key on t1 (cost=0.00..286.08 rows=72
width=26) (actual time=0.183..0.203 rows=4 loops=1)
Index Cond: ((c1)::text = '6000001'::text)
Total runtime: 0.289 ms
(3 rows)
new server:
db=# EXPLAIN ANALYZE select c1,c2 from t1 where c2='6000001';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Index Scan using t1_c2_index on t1 (cost=0.00..37.63 rows=11
width=26) (actual time=115.412..202.151 rows=8 loops=1)
Index Cond: ((c2)::text = '6000001'::text)
Total runtime: 202.234 ms
(3 rows)
db=# EXPLAIN ANALYZE select c1,c2 from t1 where c1='6000001';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Index Scan using t1_c1_index on t1 (cost=0.00..630.45 rows=2907
width=26) (actual time=99.811..99.820 rows=4 loops=1)
Index Cond: ((c1)::text = '6000001'::text)
Total runtime: 99.861 ms
(3 rows)
I haven't gotten a chance to restart postgres this the config changes
you suggested yet. The rows have improved for some but not all and
the times are still slow. Any ideas?
Alex
^ permalink raw reply [nested|flat] 53+ messages in thread
* Re: strange performance regression between 7.4 and 8.1
2007-03-01 20:44 strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-01 20:50 ` Re: strange performance regression between 7.4 and 8.1 Joshua D. Drake <jd@commandprompt.com>
2007-03-01 20:56 ` Re: strange performance regression between 7.4 and 8.1 Jeff Frost <jeff@frostconsultingllc.com>
2007-03-01 23:11 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-02 00:21 ` Re: strange performance regression between 7.4 and 8.1 Jeff Frost <jeff@frostconsultingllc.com>
2007-03-02 15:28 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
@ 2007-03-02 16:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-03-02 16:14 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
0 siblings, 1 reply; 53+ messages in thread
From: Tom Lane @ 2007-03-02 16:10 UTC (permalink / raw)
To: Alex Deucher <alexdeucher@gmail.com>; +Cc: Jeff Frost <jeff@frostconsultingllc.com>; Joshua D. Drake <jd@commandprompt.com>; pgsql-performance
"Alex Deucher" <alexdeucher@gmail.com> writes:
> Anyway, new numbers after the analyze.
> Unfortunately, they are improved, but still not great:
Why are the index names different between the old and new servers?
Is that just cosmetic, or is 8.2 actually picking a different
(and less suitable) index for the c1 queries?
regards, tom lane
^ permalink raw reply [nested|flat] 53+ messages in thread
* Re: strange performance regression between 7.4 and 8.1
2007-03-01 20:44 strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-01 20:50 ` Re: strange performance regression between 7.4 and 8.1 Joshua D. Drake <jd@commandprompt.com>
2007-03-01 20:56 ` Re: strange performance regression between 7.4 and 8.1 Jeff Frost <jeff@frostconsultingllc.com>
2007-03-01 23:11 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-02 00:21 ` Re: strange performance regression between 7.4 and 8.1 Jeff Frost <jeff@frostconsultingllc.com>
2007-03-02 15:28 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-02 16:10 ` Re: strange performance regression between 7.4 and 8.1 Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-03-02 16:14 ` Alex Deucher <alexdeucher@gmail.com>
0 siblings, 0 replies; 53+ messages in thread
From: Alex Deucher @ 2007-03-02 16:14 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jeff Frost <jeff@frostconsultingllc.com>; Joshua D. Drake <jd@commandprompt.com>; pgsql-performance
On 3/2/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Alex Deucher" <alexdeucher@gmail.com> writes:
> > Anyway, new numbers after the analyze.
> > Unfortunately, they are improved, but still not great:
>
> Why are the index names different between the old and new servers?
> Is that just cosmetic, or is 8.2 actually picking a different
> (and less suitable) index for the c1 queries?
>
That's just cosmetic. They are the same.
Alex
^ permalink raw reply [nested|flat] 53+ messages in thread
* Re: strange performance regression between 7.4 and 8.1
2007-03-01 20:44 strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-01 20:50 ` Re: strange performance regression between 7.4 and 8.1 Joshua D. Drake <jd@commandprompt.com>
@ 2007-03-01 21:06 ` Alex Deucher <alexdeucher@gmail.com>
2007-03-01 21:36 ` Re: strange performance regression between 7.4 and 8.1 Jeff Frost <jeff@frostconsultingllc.com>
1 sibling, 1 reply; 53+ messages in thread
From: Alex Deucher @ 2007-03-01 21:06 UTC (permalink / raw)
To: Joshua D. Drake <jd@commandprompt.com>; +Cc: pgsql-performance
On 3/1/07, Joshua D. Drake <jd@commandprompt.com> wrote:
> Alex Deucher wrote:
> > Hello,
> >
> > I have noticed a strange performance regression and I'm at a loss as
> > to what's happening. We have a fairly large database (~16 GB). The
> > original postgres 7.4 was running on a sun v880 with 4 CPUs and 8 GB
> > of ram running Solaris on local scsi discs. The new server is a sun
> > Opteron box with 4 cores, 8 GB of ram running postgres 8.1.4 on Linux
> > (AMD64) on a 4 Gbps FC SAN volume. When we created the new database
> > it was created from scratch rather than copying over the old one,
> > however the table structure is almost identical (UTF8 on the new one
> > vs. C on the old). The problem is queries are ~10x slower on the new
> > hardware. I read several places that the SAN might be to blame, but
> > testing with bonnie and dd indicates that the SAN is actually almost
> > twice as fast as the scsi discs in the old sun server. I've tried
> > adjusting just about every option in the postgres config file, but
> > performance remains the same. Any ideas?
>
> Vacuum? Analayze? default_statistics_target? How many shared_buffers?
> effective_cache_size? work_mem?
>
I'm running the autovacuum process on the 8.1 server. vacuuming on
the old server was done manually.
default_statistics_target and effective_cache_size are set to the the
defaults on both.
postgres 7.4 server:
# - Memory -
shared_buffers = 82000 # 1000 min 16, at least
max_connections*2, 8KB each
sort_mem = 8000 # 1024 min 64, size in KB
vacuum_mem = 32000 # 8192 min 1024, size in KB
# - Free Space Map -
#max_fsm_pages = 20000 # min max_fsm_relations*16, 6 bytes each
#max_fsm_relations = 1000 # min 100, ~50 bytes each
# - Kernel Resource Usage -
#max_files_per_process = 1000 # min 25
postgres 8.1 server:
# - Memory -
shared_buffers = 100000 # min 16 or max_connections*2, 8KB each
temp_buffers = 2000 #1000 # min 100, 8KB each
max_prepared_transactions = 100 #5 # can be 0 or more
# note: increasing max_prepared_transactions costs ~600 bytes of shared memory
# per transaction slot, plus lock space (see max_locks_per_transaction).
work_mem = 10000 #1024 # min 64, size in KB
maintenance_work_mem = 524288 #16384 # min 1024, size in KB
#max_stack_depth = 2048 # min 100, size in KB
I've also tried using the same settings from the old server on the new
one; same performance issues.
Thanks,
Alex
> Sincerely,
>
> Joshua D. Drake
>
>
> >
> > Thanks,
> >
> > Alex
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/docs/faq
> >
>
>
> --
>
> === The PostgreSQL Company: Command Prompt, Inc. ===
> Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
> Providing the most comprehensive PostgreSQL solutions since 1997
> http://www.commandprompt.com/
>
> Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
> PostgreSQL Replication: http://www.commandprompt.com/products/
>
>
^ permalink raw reply [nested|flat] 53+ messages in thread
* Re: strange performance regression between 7.4 and 8.1
2007-03-01 20:44 strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-01 20:50 ` Re: strange performance regression between 7.4 and 8.1 Joshua D. Drake <jd@commandprompt.com>
2007-03-01 21:06 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
@ 2007-03-01 21:36 ` Jeff Frost <jeff@frostconsultingllc.com>
2007-03-01 21:50 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
0 siblings, 1 reply; 53+ messages in thread
From: Jeff Frost @ 2007-03-01 21:36 UTC (permalink / raw)
To: Alex Deucher <alexdeucher@gmail.com>; +Cc: Joshua D. Drake <jd@commandprompt.com>; pgsql-performance
On Thu, 1 Mar 2007, Alex Deucher wrote:
>> Vacuum? Analayze? default_statistics_target? How many shared_buffers?
>> effective_cache_size? work_mem?
>>
>
> I'm running the autovacuum process on the 8.1 server. vacuuming on
> the old server was done manually.
>
> default_statistics_target and effective_cache_size are set to the the
> defaults on both.
>
> postgres 7.4 server:
> # - Memory -
> shared_buffers = 82000 # 1000 min 16, at least
> max_connections*2, 8KB each
> sort_mem = 8000 # 1024 min 64, size in KB
> vacuum_mem = 32000 # 8192 min 1024, size in KB
> # - Free Space Map -
> #max_fsm_pages = 20000 # min max_fsm_relations*16, 6 bytes each
> #max_fsm_relations = 1000 # min 100, ~50 bytes each
> # - Kernel Resource Usage -
> #max_files_per_process = 1000 # min 25
>
> postgres 8.1 server:
> # - Memory -
> shared_buffers = 100000 # min 16 or max_connections*2, 8KB
> each
> temp_buffers = 2000 #1000 # min 100, 8KB each
> max_prepared_transactions = 100 #5 # can be 0 or more
> # note: increasing max_prepared_transactions costs ~600 bytes of shared
> memory
> # per transaction slot, plus lock space (see max_locks_per_transaction).
> work_mem = 10000 #1024 # min 64, size in KB
> maintenance_work_mem = 524288 #16384 # min 1024, size in KB
> #max_stack_depth = 2048 # min 100, size in KB
>
> I've also tried using the same settings from the old server on the new
> one; same performance issues.
>
If this is a linux system, could you give us the output of the 'free' command?
Postgresql might be choosing a bad plan because your effective_cache_size is
way off (it's the default now right?). Also, what was the block read/write
speed of the SAN from your bonnie tests? Probably want to tune
random_page_cost as well if it's also at the default.
--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
^ permalink raw reply [nested|flat] 53+ messages in thread
* Re: strange performance regression between 7.4 and 8.1
2007-03-01 20:44 strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-01 20:50 ` Re: strange performance regression between 7.4 and 8.1 Joshua D. Drake <jd@commandprompt.com>
2007-03-01 21:06 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-01 21:36 ` Re: strange performance regression between 7.4 and 8.1 Jeff Frost <jeff@frostconsultingllc.com>
@ 2007-03-01 21:50 ` Alex Deucher <alexdeucher@gmail.com>
2007-03-01 22:01 ` Re: strange performance regression between 7.4 and 8.1 Jeff Frost <jeff@frostconsultingllc.com>
0 siblings, 1 reply; 53+ messages in thread
From: Alex Deucher @ 2007-03-01 21:50 UTC (permalink / raw)
To: Jeff Frost <jeff@frostconsultingllc.com>; +Cc: Joshua D. Drake <jd@commandprompt.com>; pgsql-performance
On 3/1/07, Jeff Frost <jeff@frostconsultingllc.com> wrote:
> On Thu, 1 Mar 2007, Alex Deucher wrote:
>
> >> Vacuum? Analayze? default_statistics_target? How many shared_buffers?
> >> effective_cache_size? work_mem?
> >>
> >
> > I'm running the autovacuum process on the 8.1 server. vacuuming on
> > the old server was done manually.
> >
> > default_statistics_target and effective_cache_size are set to the the
> > defaults on both.
> >
> > postgres 7.4 server:
> > # - Memory -
> > shared_buffers = 82000 # 1000 min 16, at least
> > max_connections*2, 8KB each
> > sort_mem = 8000 # 1024 min 64, size in KB
> > vacuum_mem = 32000 # 8192 min 1024, size in KB
> > # - Free Space Map -
> > #max_fsm_pages = 20000 # min max_fsm_relations*16, 6 bytes each
> > #max_fsm_relations = 1000 # min 100, ~50 bytes each
> > # - Kernel Resource Usage -
> > #max_files_per_process = 1000 # min 25
> >
> > postgres 8.1 server:
> > # - Memory -
> > shared_buffers = 100000 # min 16 or max_connections*2, 8KB
> > each
> > temp_buffers = 2000 #1000 # min 100, 8KB each
> > max_prepared_transactions = 100 #5 # can be 0 or more
> > # note: increasing max_prepared_transactions costs ~600 bytes of shared
> > memory
> > # per transaction slot, plus lock space (see max_locks_per_transaction).
> > work_mem = 10000 #1024 # min 64, size in KB
> > maintenance_work_mem = 524288 #16384 # min 1024, size in KB
> > #max_stack_depth = 2048 # min 100, size in KB
> >
> > I've also tried using the same settings from the old server on the new
> > one; same performance issues.
> >
>
> If this is a linux system, could you give us the output of the 'free' command?
total used free shared buffers cached
Mem: 8059852 8042868 16984 0 228 7888648
-/+ buffers/cache: 153992 7905860
Swap: 15631224 2164 15629060
> Postgresql might be choosing a bad plan because your effective_cache_size is
> way off (it's the default now right?). Also, what was the block read/write
yes it's set to the default.
> speed of the SAN from your bonnie tests? Probably want to tune
> random_page_cost as well if it's also at the default.
>
------Sequential Output------ --Sequential Input- --Random-
-Per Chr- --Block-- -Rewrite- -Per Chr- --Block-- --Seeks--
Machine Size K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP /sec %CP
luna12-san 16000M 58896 91 62931 9 35870 5 54869 82 145504 13 397.7 0
effective_cache_size is the default.
Alex
^ permalink raw reply [nested|flat] 53+ messages in thread
* Re: strange performance regression between 7.4 and 8.1
2007-03-01 20:44 strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-01 20:50 ` Re: strange performance regression between 7.4 and 8.1 Joshua D. Drake <jd@commandprompt.com>
2007-03-01 21:06 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-01 21:36 ` Re: strange performance regression between 7.4 and 8.1 Jeff Frost <jeff@frostconsultingllc.com>
2007-03-01 21:50 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
@ 2007-03-01 22:01 ` Jeff Frost <jeff@frostconsultingllc.com>
2007-03-01 22:12 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
0 siblings, 1 reply; 53+ messages in thread
From: Jeff Frost @ 2007-03-01 22:01 UTC (permalink / raw)
To: Alex Deucher <alexdeucher@gmail.com>; +Cc: Joshua D. Drake <jd@commandprompt.com>; pgsql-performance
On Thu, 1 Mar 2007, Alex Deucher wrote:
> On 3/1/07, Jeff Frost <jeff@frostconsultingllc.com> wrote:
>> On Thu, 1 Mar 2007, Alex Deucher wrote:
>>
>> >> Vacuum? Analayze? default_statistics_target? How many shared_buffers?
>> >> effective_cache_size? work_mem?
>> >>
>> >
>> > I'm running the autovacuum process on the 8.1 server. vacuuming on
>> > the old server was done manually.
>> >
>> > default_statistics_target and effective_cache_size are set to the the
>> > defaults on both.
>> >
>> > postgres 7.4 server:
>> > # - Memory -
>> > shared_buffers = 82000 # 1000 min 16, at least
>> > max_connections*2, 8KB each
>> > sort_mem = 8000 # 1024 min 64, size in KB
>> > vacuum_mem = 32000 # 8192 min 1024, size in KB
>> > # - Free Space Map -
>> > #max_fsm_pages = 20000 # min max_fsm_relations*16, 6 bytes each
>> > #max_fsm_relations = 1000 # min 100, ~50 bytes each
>> > # - Kernel Resource Usage -
>> > #max_files_per_process = 1000 # min 25
>> >
>> > postgres 8.1 server:
>> > # - Memory -
>> > shared_buffers = 100000 # min 16 or max_connections*2,
>> 8KB
>> > each
>> > temp_buffers = 2000 #1000 # min 100, 8KB each
>> > max_prepared_transactions = 100 #5 # can be 0 or more
>> > # note: increasing max_prepared_transactions costs ~600 bytes of shared
>> > memory
>> > # per transaction slot, plus lock space (see max_locks_per_transaction).
>> > work_mem = 10000 #1024 # min 64, size in KB
>> > maintenance_work_mem = 524288 #16384 # min 1024, size in KB
>> > #max_stack_depth = 2048 # min 100, size in KB
>> >
>> > I've also tried using the same settings from the old server on the new
>> > one; same performance issues.
>> >
>>
>> If this is a linux system, could you give us the output of the 'free'
>> command?
>
> total used free shared buffers cached
> Mem: 8059852 8042868 16984 0 228 7888648
> -/+ buffers/cache: 153992 7905860
> Swap: 15631224 2164 15629060
So, I would set effective_cache_size = 988232 (7905860/8).
>
>> Postgresql might be choosing a bad plan because your effective_cache_size
>> is
>> way off (it's the default now right?). Also, what was the block read/write
>
> yes it's set to the default.
>
>> speed of the SAN from your bonnie tests? Probably want to tune
>> random_page_cost as well if it's also at the default.
>>
>
> ------Sequential Output------ --Sequential Input-
> --Random-
> -Per Chr- --Block-- -Rewrite- -Per Chr- --Block--
> --Seeks--
> Machine Size K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP /sec
> %CP
> luna12-san 16000M 58896 91 62931 9 35870 5 54869 82 145504 13 397.7
> 0
>
So, you're getting 62MB/s writes and 145MB/s reads. Just FYI, that write
speed is about the same as my single SATA drive write speed on my workstation,
so not that great. The read speed is decent, though and with that sort of
read performance, you might want to lower random_page_cost to something like
2.5 or 2 so the planner will tend to prefer index scans.
--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
^ permalink raw reply [nested|flat] 53+ messages in thread
* Re: strange performance regression between 7.4 and 8.1
2007-03-01 20:44 strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-01 20:50 ` Re: strange performance regression between 7.4 and 8.1 Joshua D. Drake <jd@commandprompt.com>
2007-03-01 21:06 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-01 21:36 ` Re: strange performance regression between 7.4 and 8.1 Jeff Frost <jeff@frostconsultingllc.com>
2007-03-01 21:50 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-01 22:01 ` Re: strange performance regression between 7.4 and 8.1 Jeff Frost <jeff@frostconsultingllc.com>
@ 2007-03-01 22:12 ` Alex Deucher <alexdeucher@gmail.com>
2007-03-02 00:36 ` Re: strange performance regression between 7.4 and 8.1 Jeff Frost <jeff@frostconsultingllc.com>
0 siblings, 1 reply; 53+ messages in thread
From: Alex Deucher @ 2007-03-01 22:12 UTC (permalink / raw)
To: Jeff Frost <jeff@frostconsultingllc.com>; +Cc: Joshua D. Drake <jd@commandprompt.com>; pgsql-performance
On 3/1/07, Jeff Frost <jeff@frostconsultingllc.com> wrote:
> On Thu, 1 Mar 2007, Alex Deucher wrote:
>
> > On 3/1/07, Jeff Frost <jeff@frostconsultingllc.com> wrote:
> >> On Thu, 1 Mar 2007, Alex Deucher wrote:
> >>
> >> >> Vacuum? Analayze? default_statistics_target? How many shared_buffers?
> >> >> effective_cache_size? work_mem?
> >> >>
> >> >
> >> > I'm running the autovacuum process on the 8.1 server. vacuuming on
> >> > the old server was done manually.
> >> >
> >> > default_statistics_target and effective_cache_size are set to the the
> >> > defaults on both.
> >> >
> >> > postgres 7.4 server:
> >> > # - Memory -
> >> > shared_buffers = 82000 # 1000 min 16, at least
> >> > max_connections*2, 8KB each
> >> > sort_mem = 8000 # 1024 min 64, size in KB
> >> > vacuum_mem = 32000 # 8192 min 1024, size in KB
> >> > # - Free Space Map -
> >> > #max_fsm_pages = 20000 # min max_fsm_relations*16, 6 bytes each
> >> > #max_fsm_relations = 1000 # min 100, ~50 bytes each
> >> > # - Kernel Resource Usage -
> >> > #max_files_per_process = 1000 # min 25
> >> >
> >> > postgres 8.1 server:
> >> > # - Memory -
> >> > shared_buffers = 100000 # min 16 or max_connections*2,
> >> 8KB
> >> > each
> >> > temp_buffers = 2000 #1000 # min 100, 8KB each
> >> > max_prepared_transactions = 100 #5 # can be 0 or more
> >> > # note: increasing max_prepared_transactions costs ~600 bytes of shared
> >> > memory
> >> > # per transaction slot, plus lock space (see max_locks_per_transaction).
> >> > work_mem = 10000 #1024 # min 64, size in KB
> >> > maintenance_work_mem = 524288 #16384 # min 1024, size in KB
> >> > #max_stack_depth = 2048 # min 100, size in KB
> >> >
> >> > I've also tried using the same settings from the old server on the new
> >> > one; same performance issues.
> >> >
> >>
> >> If this is a linux system, could you give us the output of the 'free'
> >> command?
> >
> > total used free shared buffers cached
> > Mem: 8059852 8042868 16984 0 228 7888648
> > -/+ buffers/cache: 153992 7905860
> > Swap: 15631224 2164 15629060
>
> So, I would set effective_cache_size = 988232 (7905860/8).
>
> >
> >> Postgresql might be choosing a bad plan because your effective_cache_size
> >> is
> >> way off (it's the default now right?). Also, what was the block read/write
> >
> > yes it's set to the default.
> >
> >> speed of the SAN from your bonnie tests? Probably want to tune
> >> random_page_cost as well if it's also at the default.
> >>
> >
> > ------Sequential Output------ --Sequential Input-
> > --Random-
> > -Per Chr- --Block-- -Rewrite- -Per Chr- --Block--
> > --Seeks--
> > Machine Size K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP /sec
> > %CP
> > luna12-san 16000M 58896 91 62931 9 35870 5 54869 82 145504 13 397.7
> > 0
> >
>
> So, you're getting 62MB/s writes and 145MB/s reads. Just FYI, that write
> speed is about the same as my single SATA drive write speed on my workstation,
> so not that great. The read speed is decent, though and with that sort of
> read performance, you might want to lower random_page_cost to something like
> 2.5 or 2 so the planner will tend to prefer index scans.
>
Right, but the old box was getting ~45MBps on both reads and writes,
so it's an improvement for me :) Thanks for the advice, I'll let you
know how it goes.
Alex
^ permalink raw reply [nested|flat] 53+ messages in thread
* Re: strange performance regression between 7.4 and 8.1
2007-03-01 20:44 strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-01 20:50 ` Re: strange performance regression between 7.4 and 8.1 Joshua D. Drake <jd@commandprompt.com>
2007-03-01 21:06 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-01 21:36 ` Re: strange performance regression between 7.4 and 8.1 Jeff Frost <jeff@frostconsultingllc.com>
2007-03-01 21:50 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-01 22:01 ` Re: strange performance regression between 7.4 and 8.1 Jeff Frost <jeff@frostconsultingllc.com>
2007-03-01 22:12 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
@ 2007-03-02 00:36 ` Jeff Frost <jeff@frostconsultingllc.com>
2007-03-02 01:01 ` Re: strange performance regression between 7.4 and 8.1 Ron <rjpeace@earthlink.net>
2007-03-02 01:44 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
0 siblings, 2 replies; 53+ messages in thread
From: Jeff Frost @ 2007-03-02 00:36 UTC (permalink / raw)
To: Alex Deucher <alexdeucher@gmail.com>; +Cc: Joshua D. Drake <jd@commandprompt.com>; pgsql-performance
On Thu, 1 Mar 2007, Alex Deucher wrote:
>> >> Postgresql might be choosing a bad plan because your
>> effective_cache_size
>> >> is
>> >> way off (it's the default now right?). Also, what was the block
>> read/write
>> >
>> > yes it's set to the default.
>> >
>> >> speed of the SAN from your bonnie tests? Probably want to tune
>> >> random_page_cost as well if it's also at the default.
>> >>
>> >
>> > ------Sequential Output------ --Sequential Input-
>> > --Random-
>> > -Per Chr- --Block-- -Rewrite- -Per Chr- --Block--
>> > --Seeks--
>> > Machine Size K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP
>> /sec
>> > %CP
>> > luna12-san 16000M 58896 91 62931 9 35870 5 54869 82 145504 13
>> 397.7
>> > 0
>> >
>>
>> So, you're getting 62MB/s writes and 145MB/s reads. Just FYI, that write
>> speed is about the same as my single SATA drive write speed on my
>> workstation,
>> so not that great. The read speed is decent, though and with that sort of
>> read performance, you might want to lower random_page_cost to something
>> like
>> 2.5 or 2 so the planner will tend to prefer index scans.
>>
>
> Right, but the old box was getting ~45MBps on both reads and writes,
> so it's an improvement for me :) Thanks for the advice, I'll let you
> know how it goes.
Do you think that is because you have a different interface between you and
the SAN? ~45MBps is pretty slow - your average 7200RPM ATA133 drive can do
that and costs quite a bit less than a SAN.
Is the SAN being shared between the database servers and other servers? Maybe
it was just random timing that gave you the poor write performance on the old
server which might be also yielding occassional poor performance on the new
one.
--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
^ permalink raw reply [nested|flat] 53+ messages in thread
* Re: strange performance regression between 7.4 and 8.1
2007-03-01 20:44 strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-01 20:50 ` Re: strange performance regression between 7.4 and 8.1 Joshua D. Drake <jd@commandprompt.com>
2007-03-01 21:06 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-01 21:36 ` Re: strange performance regression between 7.4 and 8.1 Jeff Frost <jeff@frostconsultingllc.com>
2007-03-01 21:50 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-01 22:01 ` Re: strange performance regression between 7.4 and 8.1 Jeff Frost <jeff@frostconsultingllc.com>
2007-03-01 22:12 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-02 00:36 ` Re: strange performance regression between 7.4 and 8.1 Jeff Frost <jeff@frostconsultingllc.com>
@ 2007-03-02 01:01 ` Ron <rjpeace@earthlink.net>
1 sibling, 0 replies; 53+ messages in thread
From: Ron @ 2007-03-02 01:01 UTC (permalink / raw)
To: Jeff Frost <jeff@frostconsultingllc.com>; Alex Deucher <alexdeucher@gmail.com>; +Cc: pgsql-performance
At 07:36 PM 3/1/2007, Jeff Frost wrote:
>On Thu, 1 Mar 2007, Alex Deucher wrote:
>
>>> >> Postgresql might be choosing a bad plan because your
>>> effective_cache_size
>>> >> is
>>> >> way off (it's the default now right?). Also, what was the
>>> block read/write
>>> >
>>> > yes it's set to the default.
>>> >
>>> >> speed of the SAN from your bonnie tests? Probably want to tune
>>> >> random_page_cost as well if it's also at the default.
>>> >>
>>> >
>>> > ------Sequential Output------ --Sequential Input-
>>> > --Random-
>>> > -Per Chr- --Block-- -Rewrite- -Per Chr- --Block--
>>> > --Seeks--
>>> > Machine Size K/sec %CP K/sec %CP K/sec %CP K/sec %CP
>>> K/sec %CP /sec
>>> > %CP
>>> > luna12-san 16000M 58896 91 62931 9 35870 5 54869 82
>>> 145504 13 397.7
>>> > 0
>>> >
>>>So, you're getting 62MB/s writes and 145MB/s reads. Just FYI, that write
>>>speed is about the same as my single SATA drive write speed on my
>>>workstation,
>>>so not that great. The read speed is decent, though and with that sort of
>>>read performance, you might want to lower random_page_cost to something like
>>>2.5 or 2 so the planner will tend to prefer index scans.
>>
>>Right, but the old box was getting ~45MBps on both reads and writes,
>>so it's an improvement for me :) Thanks for the advice, I'll let you
>>know how it goes.
>
>Do you think that is because you have a different interface between
>you and the SAN? ~45MBps is pretty slow - your average 7200RPM
>ATA133 drive can do that and costs quite a bit less than a SAN.
>
>Is the SAN being shared between the database servers and other
>servers? Maybe it was just random timing that gave you the poor
>write performance on the old server which might be also yielding
>occassional poor performance on the new one.
Remember that pg, even pg 8.2.3, has a known history of very poor
insert speed (see comments on this point by Josh Berkus, Luke Lonergan, etc)
For some reason, the code changes that have resulted in dramatic
improvements in pg's read speed have not had nearly the same efficacy
for writes.
Bottom line: pg presently has a fairly low and fairly harsh upper
bound on write performance. What exactly that bound is has been the
subject of some discussion, but IIUC the fact of its existence is
well established.
Various proposals for improving the situation exist, I've even made
some of them, but AFAIK this is currently considered one of the
"tough pg problems".
Cheers,
Ron Peacetree
^ permalink raw reply [nested|flat] 53+ messages in thread
* Re: strange performance regression between 7.4 and 8.1
2007-03-01 20:44 strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-01 20:50 ` Re: strange performance regression between 7.4 and 8.1 Joshua D. Drake <jd@commandprompt.com>
2007-03-01 21:06 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-01 21:36 ` Re: strange performance regression between 7.4 and 8.1 Jeff Frost <jeff@frostconsultingllc.com>
2007-03-01 21:50 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-01 22:01 ` Re: strange performance regression between 7.4 and 8.1 Jeff Frost <jeff@frostconsultingllc.com>
2007-03-01 22:12 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-02 00:36 ` Re: strange performance regression between 7.4 and 8.1 Jeff Frost <jeff@frostconsultingllc.com>
@ 2007-03-02 01:44 ` Alex Deucher <alexdeucher@gmail.com>
2007-03-02 01:46 ` Re: strange performance regression between 7.4 and 8.1 Joshua D. Drake <jd@commandprompt.com>
2007-03-02 01:56 ` Re: strange performance regression between 7.4 and 8.1 Jeff Frost <jeff@frostconsultingllc.com>
1 sibling, 2 replies; 53+ messages in thread
From: Alex Deucher @ 2007-03-02 01:44 UTC (permalink / raw)
To: Jeff Frost <jeff@frostconsultingllc.com>; +Cc: Joshua D. Drake <jd@commandprompt.com>; pgsql-performance
On 3/1/07, Jeff Frost <jeff@frostconsultingllc.com> wrote:
> On Thu, 1 Mar 2007, Alex Deucher wrote:
>
> >> >> Postgresql might be choosing a bad plan because your
> >> effective_cache_size
> >> >> is
> >> >> way off (it's the default now right?). Also, what was the block
> >> read/write
> >> >
> >> > yes it's set to the default.
> >> >
> >> >> speed of the SAN from your bonnie tests? Probably want to tune
> >> >> random_page_cost as well if it's also at the default.
> >> >>
> >> >
> >> > ------Sequential Output------ --Sequential Input-
> >> > --Random-
> >> > -Per Chr- --Block-- -Rewrite- -Per Chr- --Block--
> >> > --Seeks--
> >> > Machine Size K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP
> >> /sec
> >> > %CP
> >> > luna12-san 16000M 58896 91 62931 9 35870 5 54869 82 145504 13
> >> 397.7
> >> > 0
> >> >
> >>
> >> So, you're getting 62MB/s writes and 145MB/s reads. Just FYI, that write
> >> speed is about the same as my single SATA drive write speed on my
> >> workstation,
> >> so not that great. The read speed is decent, though and with that sort of
> >> read performance, you might want to lower random_page_cost to something
> >> like
> >> 2.5 or 2 so the planner will tend to prefer index scans.
> >>
> >
> > Right, but the old box was getting ~45MBps on both reads and writes,
> > so it's an improvement for me :) Thanks for the advice, I'll let you
> > know how it goes.
>
> Do you think that is because you have a different interface between you and
> the SAN? ~45MBps is pretty slow - your average 7200RPM ATA133 drive can do
> that and costs quite a bit less than a SAN.
>
> Is the SAN being shared between the database servers and other servers? Maybe
> it was just random timing that gave you the poor write performance on the old
> server which might be also yielding occassional poor performance on the new
> one.
>
The direct attached scsi discs on the old database server we getting
45MBps not the SAN. The SAN got 62/145Mbps, which is not as bad. We
have 4 servers on the SAN each with it's own 4 GBps FC link via an FC
switch. I'll try and re-run the numbers when the servers are idle
this weekend.
Alex
^ permalink raw reply [nested|flat] 53+ messages in thread
* Re: strange performance regression between 7.4 and 8.1
2007-03-01 20:44 strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-01 20:50 ` Re: strange performance regression between 7.4 and 8.1 Joshua D. Drake <jd@commandprompt.com>
2007-03-01 21:06 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-01 21:36 ` Re: strange performance regression between 7.4 and 8.1 Jeff Frost <jeff@frostconsultingllc.com>
2007-03-01 21:50 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-01 22:01 ` Re: strange performance regression between 7.4 and 8.1 Jeff Frost <jeff@frostconsultingllc.com>
2007-03-01 22:12 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-02 00:36 ` Re: strange performance regression between 7.4 and 8.1 Jeff Frost <jeff@frostconsultingllc.com>
2007-03-02 01:44 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
@ 2007-03-02 01:46 ` Joshua D. Drake <jd@commandprompt.com>
2007-03-02 01:47 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
1 sibling, 1 reply; 53+ messages in thread
From: Joshua D. Drake @ 2007-03-02 01:46 UTC (permalink / raw)
To: Alex Deucher <alexdeucher@gmail.com>; +Cc: Jeff Frost <jeff@frostconsultingllc.com>; pgsql-performance
\
>> Is the SAN being shared between the database servers and other
>> servers? Maybe
>> it was just random timing that gave you the poor write performance on
>> the old
>> server which might be also yielding occassional poor performance on
>> the new
>> one.
>>
>
> The direct attached scsi discs on the old database server we getting
> 45MBps not the SAN. The SAN got 62/145Mbps, which is not as bad.
How many spindles you got in that SAN?
We
> have 4 servers on the SAN each with it's own 4 GBps FC link via an FC
> switch. I'll try and re-run the numbers when the servers are idle
> this weekend.
>
> Alex
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
^ permalink raw reply [nested|flat] 53+ messages in thread
* Re: strange performance regression between 7.4 and 8.1
2007-03-01 20:44 strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-01 20:50 ` Re: strange performance regression between 7.4 and 8.1 Joshua D. Drake <jd@commandprompt.com>
2007-03-01 21:06 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-01 21:36 ` Re: strange performance regression between 7.4 and 8.1 Jeff Frost <jeff@frostconsultingllc.com>
2007-03-01 21:50 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-01 22:01 ` Re: strange performance regression between 7.4 and 8.1 Jeff Frost <jeff@frostconsultingllc.com>
2007-03-01 22:12 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-02 00:36 ` Re: strange performance regression between 7.4 and 8.1 Jeff Frost <jeff@frostconsultingllc.com>
2007-03-02 01:44 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-02 01:46 ` Re: strange performance regression between 7.4 and 8.1 Joshua D. Drake <jd@commandprompt.com>
@ 2007-03-02 01:47 ` Alex Deucher <alexdeucher@gmail.com>
2007-03-02 02:21 ` Re: strange performance regression between 7.4 and 8.1 Joshua D. Drake <jd@commandprompt.com>
0 siblings, 1 reply; 53+ messages in thread
From: Alex Deucher @ 2007-03-02 01:47 UTC (permalink / raw)
To: Joshua D. Drake <jd@commandprompt.com>; +Cc: Jeff Frost <jeff@frostconsultingllc.com>; pgsql-performance
On 3/1/07, Joshua D. Drake <jd@commandprompt.com> wrote:
> \
> >> Is the SAN being shared between the database servers and other
> >> servers? Maybe
> >> it was just random timing that gave you the poor write performance on
> >> the old
> >> server which might be also yielding occassional poor performance on
> >> the new
> >> one.
> >>
> >
> > The direct attached scsi discs on the old database server we getting
> > 45MBps not the SAN. The SAN got 62/145Mbps, which is not as bad.
>
> How many spindles you got in that SAN?
105 IIRC.
Alex
^ permalink raw reply [nested|flat] 53+ messages in thread
* Re: strange performance regression between 7.4 and 8.1
2007-03-01 20:44 strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-01 20:50 ` Re: strange performance regression between 7.4 and 8.1 Joshua D. Drake <jd@commandprompt.com>
2007-03-01 21:06 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-01 21:36 ` Re: strange performance regression between 7.4 and 8.1 Jeff Frost <jeff@frostconsultingllc.com>
2007-03-01 21:50 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-01 22:01 ` Re: strange performance regression between 7.4 and 8.1 Jeff Frost <jeff@frostconsultingllc.com>
2007-03-01 22:12 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-02 00:36 ` Re: strange performance regression between 7.4 and 8.1 Jeff Frost <jeff@frostconsultingllc.com>
2007-03-02 01:44 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-02 01:46 ` Re: strange performance regression between 7.4 and 8.1 Joshua D. Drake <jd@commandprompt.com>
2007-03-02 01:47 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
@ 2007-03-02 02:21 ` Joshua D. Drake <jd@commandprompt.com>
2007-03-02 03:16 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
0 siblings, 1 reply; 53+ messages in thread
From: Joshua D. Drake @ 2007-03-02 02:21 UTC (permalink / raw)
To: Alex Deucher <alexdeucher@gmail.com>; +Cc: Jeff Frost <jeff@frostconsultingllc.com>; pgsql-performance
Alex Deucher wrote:
> On 3/1/07, Joshua D. Drake <jd@commandprompt.com> wrote:
>> \
>> >> Is the SAN being shared between the database servers and other
>> >> servers? Maybe
>> >> it was just random timing that gave you the poor write performance on
>> >> the old
>> >> server which might be also yielding occassional poor performance on
>> >> the new
>> >> one.
>> >>
>> >
>> > The direct attached scsi discs on the old database server we getting
>> > 45MBps not the SAN. The SAN got 62/145Mbps, which is not as bad.
>>
>> How many spindles you got in that SAN?
>
> 105 IIRC.
You have 105 spindles are you are only get 62megs on writes? That seems
about half what you should be getting. (at least).
Joshua D. Drake
>
> Alex
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
^ permalink raw reply [nested|flat] 53+ messages in thread
* Re: strange performance regression between 7.4 and 8.1
2007-03-01 20:44 strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-01 20:50 ` Re: strange performance regression between 7.4 and 8.1 Joshua D. Drake <jd@commandprompt.com>
2007-03-01 21:06 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-01 21:36 ` Re: strange performance regression between 7.4 and 8.1 Jeff Frost <jeff@frostconsultingllc.com>
2007-03-01 21:50 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-01 22:01 ` Re: strange performance regression between 7.4 and 8.1 Jeff Frost <jeff@frostconsultingllc.com>
2007-03-01 22:12 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-02 00:36 ` Re: strange performance regression between 7.4 and 8.1 Jeff Frost <jeff@frostconsultingllc.com>
2007-03-02 01:44 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-02 01:46 ` Re: strange performance regression between 7.4 and 8.1 Joshua D. Drake <jd@commandprompt.com>
2007-03-02 01:47 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-02 02:21 ` Re: strange performance regression between 7.4 and 8.1 Joshua D. Drake <jd@commandprompt.com>
@ 2007-03-02 03:16 ` Alex Deucher <alexdeucher@gmail.com>
0 siblings, 0 replies; 53+ messages in thread
From: Alex Deucher @ 2007-03-02 03:16 UTC (permalink / raw)
To: Joshua D. Drake <jd@commandprompt.com>; +Cc: Jeff Frost <jeff@frostconsultingllc.com>; pgsql-performance
On 3/1/07, Joshua D. Drake <jd@commandprompt.com> wrote:
> Alex Deucher wrote:
> > On 3/1/07, Joshua D. Drake <jd@commandprompt.com> wrote:
> >> \
> >> >> Is the SAN being shared between the database servers and other
> >> >> servers? Maybe
> >> >> it was just random timing that gave you the poor write performance on
> >> >> the old
> >> >> server which might be also yielding occassional poor performance on
> >> >> the new
> >> >> one.
> >> >>
> >> >
> >> > The direct attached scsi discs on the old database server we getting
> >> > 45MBps not the SAN. The SAN got 62/145Mbps, which is not as bad.
> >>
> >> How many spindles you got in that SAN?
> >
> > 105 IIRC.
>
> You have 105 spindles are you are only get 62megs on writes? That seems
> about half what you should be getting. (at least).
>
Take the numbers with grain of salt. They are by no means a thorough
evaluation. I just ran bonnie a couple times to get a rough reference
point. I can do a more thorough analysis.
Alex
> Joshua D. Drake
>
>
> >
> > Alex
> >
>
^ permalink raw reply [nested|flat] 53+ messages in thread
* Re: strange performance regression between 7.4 and 8.1
2007-03-01 20:44 strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-01 20:50 ` Re: strange performance regression between 7.4 and 8.1 Joshua D. Drake <jd@commandprompt.com>
2007-03-01 21:06 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-01 21:36 ` Re: strange performance regression between 7.4 and 8.1 Jeff Frost <jeff@frostconsultingllc.com>
2007-03-01 21:50 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-01 22:01 ` Re: strange performance regression between 7.4 and 8.1 Jeff Frost <jeff@frostconsultingllc.com>
2007-03-01 22:12 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-02 00:36 ` Re: strange performance regression between 7.4 and 8.1 Jeff Frost <jeff@frostconsultingllc.com>
2007-03-02 01:44 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
@ 2007-03-02 01:56 ` Jeff Frost <jeff@frostconsultingllc.com>
2007-03-02 02:06 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
1 sibling, 1 reply; 53+ messages in thread
From: Jeff Frost @ 2007-03-02 01:56 UTC (permalink / raw)
To: Alex Deucher <alexdeucher@gmail.com>; +Cc: Joshua D. Drake <jd@commandprompt.com>; pgsql-performance
On Thu, 1 Mar 2007, Alex Deucher wrote:
> On 3/1/07, Jeff Frost <jeff@frostconsultingllc.com> wrote:
>> On Thu, 1 Mar 2007, Alex Deucher wrote:
>>
>> >> >> Postgresql might be choosing a bad plan because your
>> >> effective_cache_size
>> >> >> is
>> >> >> way off (it's the default now right?). Also, what was the block
>> >> read/write
>> >> >
>> >> > yes it's set to the default.
>> >> >
>> >> >> speed of the SAN from your bonnie tests? Probably want to tune
>> >> >> random_page_cost as well if it's also at the default.
>> >> >>
>> >> >
>> >> > ------Sequential Output------ --Sequential Input-
>> >> > --Random-
>> >> > -Per Chr- --Block-- -Rewrite- -Per Chr- --Block--
>> >> > --Seeks--
>> >> > Machine Size K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP
>> >> /sec
>> >> > %CP
>> >> > luna12-san 16000M 58896 91 62931 9 35870 5 54869 82 145504 13
>> >> 397.7
>> >> > 0
>> >> >
>> >>
>> >> So, you're getting 62MB/s writes and 145MB/s reads. Just FYI, that
>> write
>> >> speed is about the same as my single SATA drive write speed on my
>> >> workstation,
>> >> so not that great. The read speed is decent, though and with that sort
>> of
>> >> read performance, you might want to lower random_page_cost to something
>> >> like
>> >> 2.5 or 2 so the planner will tend to prefer index scans.
>> >>
>> >
>> > Right, but the old box was getting ~45MBps on both reads and writes,
>> > so it's an improvement for me :) Thanks for the advice, I'll let you
>> > know how it goes.
>>
>> Do you think that is because you have a different interface between you and
>> the SAN? ~45MBps is pretty slow - your average 7200RPM ATA133 drive can do
>> that and costs quite a bit less than a SAN.
>>
>> Is the SAN being shared between the database servers and other servers?
>> Maybe
>> it was just random timing that gave you the poor write performance on the
>> old
>> server which might be also yielding occassional poor performance on the new
>> one.
>>
>
> The direct attached scsi discs on the old database server we getting
> 45MBps not the SAN. The SAN got 62/145Mbps, which is not as bad. We
> have 4 servers on the SAN each with it's own 4 GBps FC link via an FC
> switch. I'll try and re-run the numbers when the servers are idle
> this weekend.
Sorry, I thought the old server was also attached to the SAN. My fault for
not hanging onto the entire email thread.
I think you're mixing and matching your capitol and lower case Bs in your
sentence above though. :-)
I suspect what you really mean is The SAN got 62/145MBps (megabytes/sec) and
teh FC link is 4Gbps (gigabits/sec) or 500MBps. Is that correct? If so, and
seeing that you think there are 105 spindles on the SAN, I'd say you're either
maxxing out the switch fabric of the SAN with your servers or you have a
really poorly performing SAN in general, or you just misunderstood the .
As a comparison With 8 WD Raptors configured in a RAID10 with normal ext3 I
get about 160MB/s write and 305MB/s read performance. Hopefully the SAN has
lots of other super nifty features that make up for the poor performance. :-(
--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
^ permalink raw reply [nested|flat] 53+ messages in thread
* Re: strange performance regression between 7.4 and 8.1
2007-03-01 20:44 strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-01 20:50 ` Re: strange performance regression between 7.4 and 8.1 Joshua D. Drake <jd@commandprompt.com>
2007-03-01 21:06 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-01 21:36 ` Re: strange performance regression between 7.4 and 8.1 Jeff Frost <jeff@frostconsultingllc.com>
2007-03-01 21:50 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-01 22:01 ` Re: strange performance regression between 7.4 and 8.1 Jeff Frost <jeff@frostconsultingllc.com>
2007-03-01 22:12 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-02 00:36 ` Re: strange performance regression between 7.4 and 8.1 Jeff Frost <jeff@frostconsultingllc.com>
2007-03-02 01:44 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-02 01:56 ` Re: strange performance regression between 7.4 and 8.1 Jeff Frost <jeff@frostconsultingllc.com>
@ 2007-03-02 02:06 ` Alex Deucher <alexdeucher@gmail.com>
0 siblings, 0 replies; 53+ messages in thread
From: Alex Deucher @ 2007-03-02 02:06 UTC (permalink / raw)
To: Jeff Frost <jeff@frostconsultingllc.com>; +Cc: Joshua D. Drake <jd@commandprompt.com>; pgsql-performance
On 3/1/07, Jeff Frost <jeff@frostconsultingllc.com> wrote:
> On Thu, 1 Mar 2007, Alex Deucher wrote:
>
> > On 3/1/07, Jeff Frost <jeff@frostconsultingllc.com> wrote:
> >> On Thu, 1 Mar 2007, Alex Deucher wrote:
> >>
> >> >> >> Postgresql might be choosing a bad plan because your
> >> >> effective_cache_size
> >> >> >> is
> >> >> >> way off (it's the default now right?). Also, what was the block
> >> >> read/write
> >> >> >
> >> >> > yes it's set to the default.
> >> >> >
> >> >> >> speed of the SAN from your bonnie tests? Probably want to tune
> >> >> >> random_page_cost as well if it's also at the default.
> >> >> >>
> >> >> >
> >> >> > ------Sequential Output------ --Sequential Input-
> >> >> > --Random-
> >> >> > -Per Chr- --Block-- -Rewrite- -Per Chr- --Block--
> >> >> > --Seeks--
> >> >> > Machine Size K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP
> >> >> /sec
> >> >> > %CP
> >> >> > luna12-san 16000M 58896 91 62931 9 35870 5 54869 82 145504 13
> >> >> 397.7
> >> >> > 0
> >> >> >
> >> >>
> >> >> So, you're getting 62MB/s writes and 145MB/s reads. Just FYI, that
> >> write
> >> >> speed is about the same as my single SATA drive write speed on my
> >> >> workstation,
> >> >> so not that great. The read speed is decent, though and with that sort
> >> of
> >> >> read performance, you might want to lower random_page_cost to something
> >> >> like
> >> >> 2.5 or 2 so the planner will tend to prefer index scans.
> >> >>
> >> >
> >> > Right, but the old box was getting ~45MBps on both reads and writes,
> >> > so it's an improvement for me :) Thanks for the advice, I'll let you
> >> > know how it goes.
> >>
> >> Do you think that is because you have a different interface between you and
> >> the SAN? ~45MBps is pretty slow - your average 7200RPM ATA133 drive can do
> >> that and costs quite a bit less than a SAN.
> >>
> >> Is the SAN being shared between the database servers and other servers?
> >> Maybe
> >> it was just random timing that gave you the poor write performance on the
> >> old
> >> server which might be also yielding occassional poor performance on the new
> >> one.
> >>
> >
> > The direct attached scsi discs on the old database server we getting
> > 45MBps not the SAN. The SAN got 62/145Mbps, which is not as bad. We
> > have 4 servers on the SAN each with it's own 4 GBps FC link via an FC
> > switch. I'll try and re-run the numbers when the servers are idle
> > this weekend.
>
> Sorry, I thought the old server was also attached to the SAN. My fault for
> not hanging onto the entire email thread.
>
> I think you're mixing and matching your capitol and lower case Bs in your
> sentence above though. :-)
whoops :)
>
> I suspect what you really mean is The SAN got 62/145MBps (megabytes/sec) and
> teh FC link is 4Gbps (gigabits/sec) or 500MBps. Is that correct? If so, and
> seeing that you think there are 105 spindles on the SAN, I'd say you're either
> maxxing out the switch fabric of the SAN with your servers or you have a
> really poorly performing SAN in general, or you just misunderstood the .
>
> As a comparison With 8 WD Raptors configured in a RAID10 with normal ext3 I
> get about 160MB/s write and 305MB/s read performance. Hopefully the SAN has
> lots of other super nifty features that make up for the poor performance. :-(
>
It's big and reliable (and compared to lots of others, relatively
inexpensive) which is why we bought it. We bought it mostly as a huge
file store. The RAID groups on the SAN were set up for maximum
capacity rather than for performance. Using it for the databases just
came up recently.
Alex
^ permalink raw reply [nested|flat] 53+ messages in thread
* Re: strange performance regression between 7.4 and 8.1
2007-03-01 20:44 strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
@ 2007-03-02 08:51 ` Florian Weimer <fweimer@bfk.de>
2007-03-02 13:56 ` Re: strange performance regression between 7.4 and 8.1 Carlos Moreno <moreno_pg@mochima.com>
2007-03-02 15:16 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-02 19:33 ` Re: strange performance regression between 7.4 and 8.1 Alvaro Herrera <alvherre@commandprompt.com>
2 siblings, 3 replies; 53+ messages in thread
From: Florian Weimer @ 2007-03-02 08:51 UTC (permalink / raw)
To: Alex Deucher <alexdeucher@gmail.com>; +Cc: pgsql-performance
* Alex Deucher:
> I have noticed a strange performance regression and I'm at a loss as
> to what's happening. We have a fairly large database (~16 GB).
Sorry for asking, but is this a typo? Do you mean 16 *TB* instead of
16 *GB*?
If it's really 16 GB, you should check if it's cheaper to buy more RAM
than to fiddle with the existing infrastructure.
> however the table structure is almost identical (UTF8 on the new one
> vs. C on the old).
Locale settings make a huge difference for sorting and LIKE queries.
We usually use the C locale and SQL_ASCII encoding, mostly for
performance reasons. (Proper UTF-8 can be enforced through
constraints if necessary.)
--
Florian Weimer <fweimer@bfk.de>
BFK edv-consulting GmbH http://www.bfk.de/
Kriegsstraße 100 tel: +49-721-96201-1
D-76133 Karlsruhe fax: +49-721-96201-99
^ permalink raw reply [nested|flat] 53+ messages in thread
* Re: strange performance regression between 7.4 and 8.1
2007-03-01 20:44 strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-02 08:51 ` Re: strange performance regression between 7.4 and 8.1 Florian Weimer <fweimer@bfk.de>
@ 2007-03-02 13:56 ` Carlos Moreno <moreno_pg@mochima.com>
2007-03-02 15:21 ` Re: strange performance regression between 7.4 and 8.1 Ron <rjpeace@earthlink.net>
2 siblings, 1 reply; 53+ messages in thread
From: Carlos Moreno @ 2007-03-02 13:56 UTC (permalink / raw)
To: pgsql-performance
Florian Weimer wrote:
> * Alex Deucher:
>
>
>> I have noticed a strange performance regression and I'm at a loss as
>> to what's happening. We have a fairly large database (~16 GB).
>>
>
> Sorry for asking, but is this a typo? Do you mean 16 *TB* instead of
> 16 *GB*?
>
> If it's really 16 GB, you should check if it's cheaper to buy more RAM
> than to fiddle with the existing infrastructure.
>
This brings me to a related question:
Do I need to specifically configure something to take advantage of
such increase of RAM?
In particular, is the amount of things that postgres can do with RAM
limited by the amount of shared_buffers or some other parameter?
Should shared_buffers be a fixed fraction of the total amount of
physical RAM, or should it be the total amount minus half a gigabyte
or so?
As an example, if one upgrades a host from 1GB to 4GB, what would
be the right thing to do in the configuration, assuming 8.1 or 8.2? (at
least what would be the critical aspects?)
Thanks,
Carlos
--
^ permalink raw reply [nested|flat] 53+ messages in thread
* Re: strange performance regression between 7.4 and 8.1
2007-03-01 20:44 strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-02 08:51 ` Re: strange performance regression between 7.4 and 8.1 Florian Weimer <fweimer@bfk.de>
2007-03-02 13:56 ` Re: strange performance regression between 7.4 and 8.1 Carlos Moreno <moreno_pg@mochima.com>
@ 2007-03-02 15:21 ` Ron <rjpeace@earthlink.net>
0 siblings, 0 replies; 53+ messages in thread
From: Ron @ 2007-03-02 15:21 UTC (permalink / raw)
To: Carlos Moreno <moreno_pg@mochima.com>; +Cc: pgsql-performance
At 08:56 AM 3/2/2007, Carlos Moreno wrote:
>Florian Weimer wrote:
>>* Alex Deucher:
>>
>>
>>>I have noticed a strange performance regression and I'm at a loss as
>>>to what's happening. We have a fairly large database (~16 GB).
>>>
>>
>>Sorry for asking, but is this a typo? Do you mean 16 *TB* instead of
>>16 *GB*?
>>
>>If it's really 16 GB, you should check if it's cheaper to buy more RAM
>>than to fiddle with the existing infrastructure.
>>
>
>This brings me to a related question:
>
>Do I need to specifically configure something to take advantage of
>such increase of RAM?
>
>In particular, is the amount of things that postgres can do with RAM
>limited by the amount of shared_buffers or some other parameter?
>Should shared_buffers be a fixed fraction of the total amount of
>physical RAM, or should it be the total amount minus half a gigabyte
>or so?
>
>As an example, if one upgrades a host from 1GB to 4GB, what would
>be the right thing to do in the configuration, assuming 8.1 or 8.2? (at
>least what would be the critical aspects?)
>
>Thanks,
>
>Carlos
Unfortunately, pg does not (yet! ;-) ) treat all available RAM as a
common pool and dynamically allocate it intelligently to each of the
various memory data structures.
So if you increase your RAM, you will have to manually change the
entries in the pg config file to take advantage of it.
(and start pg after changing it for the new config values to take effect)
The pertinent values are all those listed under "Memory" in the
annotated pg conf file: shared_buffers, work_mem, maintenance_work_mem, etc.
http://www.powerpostgresql.com/Downloads/annotated_conf_80.html
Cheers,
Ron Peacetree
^ permalink raw reply [nested|flat] 53+ messages in thread
* Re: strange performance regression between 7.4 and 8.1
2007-03-01 20:44 strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-02 08:51 ` Re: strange performance regression between 7.4 and 8.1 Florian Weimer <fweimer@bfk.de>
@ 2007-03-02 15:16 ` Alex Deucher <alexdeucher@gmail.com>
2007-03-02 15:48 ` Re: strange performance regression between 7.4 and 8.1 Ron <rjpeace@earthlink.net>
2 siblings, 1 reply; 53+ messages in thread
From: Alex Deucher @ 2007-03-02 15:16 UTC (permalink / raw)
To: Florian Weimer <fweimer@bfk.de>; +Cc: pgsql-performance
On 3/2/07, Florian Weimer <fweimer@bfk.de> wrote:
> * Alex Deucher:
>
> > I have noticed a strange performance regression and I'm at a loss as
> > to what's happening. We have a fairly large database (~16 GB).
>
> Sorry for asking, but is this a typo? Do you mean 16 *TB* instead of
> 16 *GB*?
>
> If it's really 16 GB, you should check if it's cheaper to buy more RAM
> than to fiddle with the existing infrastructure.
>
Yes, 16 GB. I'd rather not shell out for more ram, if I'm not even
sure that will help. The new system should be faster, or at least as
fast, so I'd like to sort out what's going on before I buy more ram.
> > however the table structure is almost identical (UTF8 on the new one
> > vs. C on the old).
>
> Locale settings make a huge difference for sorting and LIKE queries.
> We usually use the C locale and SQL_ASCII encoding, mostly for
> performance reasons. (Proper UTF-8 can be enforced through
> constraints if necessary.)
>
I suppose that might be a factor. How much of a performance
difference do you see between utf-8 and C?
Alex
^ permalink raw reply [nested|flat] 53+ messages in thread
* Re: strange performance regression between 7.4 and 8.1
2007-03-01 20:44 strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-02 08:51 ` Re: strange performance regression between 7.4 and 8.1 Florian Weimer <fweimer@bfk.de>
2007-03-02 15:16 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
@ 2007-03-02 15:48 ` Ron <rjpeace@earthlink.net>
2007-03-02 16:03 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
0 siblings, 1 reply; 53+ messages in thread
From: Ron @ 2007-03-02 15:48 UTC (permalink / raw)
To: Alex Deucher <alexdeucher@gmail.com>; +Cc: pgsql-performance
At 10:16 AM 3/2/2007, Alex Deucher wrote:
>On 3/2/07, Florian Weimer <fweimer@bfk.de> wrote:
>>* Alex Deucher:
>>
>> > I have noticed a strange performance regression and I'm at a loss as
>> > to what's happening. We have a fairly large database (~16 GB).
>>
>>Sorry for asking, but is this a typo? Do you mean 16 *TB* instead of
>>16 *GB*?
>>
>>If it's really 16 GB, you should check if it's cheaper to buy more RAM
>>than to fiddle with the existing infrastructure.
>
>Yes, 16 GB. I'd rather not shell out for more ram, if I'm not even
>sure that will help. The new system should be faster, or at least as
>fast, so I'd like to sort out what's going on before I buy more ram.
>
OK. You
a= went from pg 7.4.x to 8.1.4 AND
b= you changed from 4 SPARC CPUs (how many cores? If this is > 4...)
to 2 2C Opterons AND
(SPEC and TPC bench differences between these CPUs?)
c= you went from a Sun box to a "white box" AND
(memory subsystem differences? other differences?)
d= you went from local HD IO to a SAN
(many differences hidden in that one line... ...and is the physical
layout of tables and things like pg_xlog sane on the SAN?)
...and you did this by just pulling over the old DB onto the new HW?
May I suggest that it is possible that your schema, queries, etc were
all optimized for pg 7.x running on the old HW?
(explain analyze shows the old system taking ~1/10 the time per row
as well as estimating the number of rows more accurately)
RAM is =cheap=. Much cheaper than the cost of a detective hunt
followed by rework to queries, schema, etc.
Fitting the entire DB into RAM is guaranteed to help unless this is
an OLTP like application where HD IO is required to be synchronous..
If you can fit the entire DB comfortably into RAM, do it and buy
yourself the time to figure out the rest of the story w/o impacting
on production performance.
Cheers,
Ron Peacetree
^ permalink raw reply [nested|flat] 53+ messages in thread
* Re: strange performance regression between 7.4 and 8.1
2007-03-01 20:44 strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-02 08:51 ` Re: strange performance regression between 7.4 and 8.1 Florian Weimer <fweimer@bfk.de>
2007-03-02 15:16 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-02 15:48 ` Re: strange performance regression between 7.4 and 8.1 Ron <rjpeace@earthlink.net>
@ 2007-03-02 16:03 ` Alex Deucher <alexdeucher@gmail.com>
2007-03-02 16:50 ` Re: strange performance regression between 7.4 and 8.1 Scott Marlowe <smarlowe@g2switchworks.com>
2007-03-02 17:59 ` Re: strange performance regression between 7.4 and 8.1 Ron <rjpeace@earthlink.net>
0 siblings, 2 replies; 53+ messages in thread
From: Alex Deucher @ 2007-03-02 16:03 UTC (permalink / raw)
To: Ron <rjpeace@earthlink.net>; +Cc: pgsql-performance
On 3/2/07, Ron <rjpeace@earthlink.net> wrote:
> At 10:16 AM 3/2/2007, Alex Deucher wrote:
> >On 3/2/07, Florian Weimer <fweimer@bfk.de> wrote:
> >>* Alex Deucher:
> >>
> >> > I have noticed a strange performance regression and I'm at a loss as
> >> > to what's happening. We have a fairly large database (~16 GB).
> >>
> >>Sorry for asking, but is this a typo? Do you mean 16 *TB* instead of
> >>16 *GB*?
> >>
> >>If it's really 16 GB, you should check if it's cheaper to buy more RAM
> >>than to fiddle with the existing infrastructure.
> >
> >Yes, 16 GB. I'd rather not shell out for more ram, if I'm not even
> >sure that will help. The new system should be faster, or at least as
> >fast, so I'd like to sort out what's going on before I buy more ram.
> >
> OK. You
> a= went from pg 7.4.x to 8.1.4 AND
>
yes.
> b= you changed from 4 SPARC CPUs (how many cores? If this is > 4...)
> to 2 2C Opterons AND
> (SPEC and TPC bench differences between these CPUs?)
>
4 single core 800 Mhz sparcs to 2 dual core 2.2 Ghz opterons.
> c= you went from a Sun box to a "white box" AND
> (memory subsystem differences? other differences?)
>
The new hardware is Sun as well. X4100s running Linux. It should be
faster all around because the old server is 5 years old.
> d= you went from local HD IO to a SAN
> (many differences hidden in that one line... ...and is the physical
> layout of tables and things like pg_xlog sane on the SAN?)
>
>
> ...and you did this by just pulling over the old DB onto the new HW?
>
We rebuild the DB from scratch on the new server. Same table
structure though. We reloaded from the source material directly.
> May I suggest that it is possible that your schema, queries, etc were
> all optimized for pg 7.x running on the old HW?
> (explain analyze shows the old system taking ~1/10 the time per row
> as well as estimating the number of rows more accurately)
>
> RAM is =cheap=. Much cheaper than the cost of a detective hunt
> followed by rework to queries, schema, etc.
> Fitting the entire DB into RAM is guaranteed to help unless this is
> an OLTP like application where HD IO is required to be synchronous..
> If you can fit the entire DB comfortably into RAM, do it and buy
> yourself the time to figure out the rest of the story w/o impacting
> on production performance.
Perhaps so. I just don't want to spend $1000 on ram and have it only
marginally improve performance if at all. The old DB works, so we can
keep using that until we sort this out.
Alex
>
> Cheers,
> Ron Peacetree
>
>
^ permalink raw reply [nested|flat] 53+ messages in thread
* Re: strange performance regression between 7.4 and 8.1
2007-03-01 20:44 strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-02 08:51 ` Re: strange performance regression between 7.4 and 8.1 Florian Weimer <fweimer@bfk.de>
2007-03-02 15:16 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-02 15:48 ` Re: strange performance regression between 7.4 and 8.1 Ron <rjpeace@earthlink.net>
2007-03-02 16:03 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
@ 2007-03-02 16:50 ` Scott Marlowe <smarlowe@g2switchworks.com>
1 sibling, 0 replies; 53+ messages in thread
From: Scott Marlowe @ 2007-03-02 16:50 UTC (permalink / raw)
To: Alex Deucher <alexdeucher@gmail.com>; +Cc: Ron <rjpeace@earthlink.net>; pgsql-performance
On Fri, 2007-03-02 at 10:03, Alex Deucher wrote:
> On 3/2/07, Ron <rjpeace@earthlink.net> wrote:
> > At 10:16 AM 3/2/2007, Alex Deucher wrote:
> > d= you went from local HD IO to a SAN
> > (many differences hidden in that one line... ...and is the physical
> > layout of tables and things like pg_xlog sane on the SAN?)
> >
> >
> > ...and you did this by just pulling over the old DB onto the new HW?
> >
>
> We rebuild the DB from scratch on the new server. Same table
> structure though. We reloaded from the source material directly.
I would REALLY recommend testing this machine out with a simple software
RAID-1 pair of SCSI or SATA drives just to eliminate or confirm the SAN
as the root problem.
^ permalink raw reply [nested|flat] 53+ messages in thread
* Re: strange performance regression between 7.4 and 8.1
2007-03-01 20:44 strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-02 08:51 ` Re: strange performance regression between 7.4 and 8.1 Florian Weimer <fweimer@bfk.de>
2007-03-02 15:16 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-02 15:48 ` Re: strange performance regression between 7.4 and 8.1 Ron <rjpeace@earthlink.net>
2007-03-02 16:03 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
@ 2007-03-02 17:59 ` Ron <rjpeace@earthlink.net>
2007-03-02 19:43 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
1 sibling, 1 reply; 53+ messages in thread
From: Ron @ 2007-03-02 17:59 UTC (permalink / raw)
To: Alex Deucher <alexdeucher@gmail.com>; +Cc: pgsql-performance
At 11:03 AM 3/2/2007, Alex Deucher wrote:
>On 3/2/07, Ron <rjpeace@earthlink.net> wrote:
>
>>May I suggest that it is possible that your schema, queries, etc were
>>all optimized for pg 7.x running on the old HW?
>>(explain analyze shows the old system taking ~1/10 the time per row
>>as well as estimating the number of rows more accurately)
>>
>>RAM is =cheap=. Much cheaper than the cost of a detective hunt
>>followed by rework to queries, schema, etc.
>>Fitting the entire DB into RAM is guaranteed to help unless this is
>>an OLTP like application where HD IO is required to be synchronous..
>>If you can fit the entire DB comfortably into RAM, do it and buy
>>yourself the time to figure out the rest of the story w/o impacting
>>on production performance.
>
>Perhaps so. I just don't want to spend $1000 on ram and have it only
>marginally improve performance if at all. The old DB works, so we can
>keep using that until we sort this out.
>
>Alex
1= $1000 worth of RAM is very likely less than the $ worth of, say,
10 hours of your time to your company. Perhaps much less.
(Your =worth=, not your pay or even your fully loaded cost. This
number tends to be >= 4x what you are paid unless the organization
you are working for is in imminent financial danger.)
You've already put more considerably more than 10 hours of your time
into this...
2= If the DB goes from not fitting completely into RAM to being
completely RAM resident, you are almost 100% guaranteed a big
performance boost.
The exception is an OLTP like app where DB writes can't be done
a-synchronously (doing financial transactions, real time control systems, etc).
Data mines should never have this issue.
3= Whether adding enough RAM to make the DB RAM resident (and
re-configuring conf, etc, appropriately) solves the problem or not,
you will have gotten a serious lead as to what's wrong.
...and I still think looking closely at the actual physical layout of
the tables in the SAN is likely to be worth it.
Cheers,
Ron Peacetree
^ permalink raw reply [nested|flat] 53+ messages in thread
* Re: strange performance regression between 7.4 and 8.1
2007-03-01 20:44 strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-02 08:51 ` Re: strange performance regression between 7.4 and 8.1 Florian Weimer <fweimer@bfk.de>
2007-03-02 15:16 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-02 15:48 ` Re: strange performance regression between 7.4 and 8.1 Ron <rjpeace@earthlink.net>
2007-03-02 16:03 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-02 17:59 ` Re: strange performance regression between 7.4 and 8.1 Ron <rjpeace@earthlink.net>
@ 2007-03-02 19:43 ` Alex Deucher <alexdeucher@gmail.com>
2007-03-02 20:28 ` Re: strange performance regression between 7.4 and 8.1 Ron <rjpeace@earthlink.net>
0 siblings, 1 reply; 53+ messages in thread
From: Alex Deucher @ 2007-03-02 19:43 UTC (permalink / raw)
To: Ron <rjpeace@earthlink.net>; +Cc: pgsql-performance
On 3/2/07, Ron <rjpeace@earthlink.net> wrote:
> At 11:03 AM 3/2/2007, Alex Deucher wrote:
> >On 3/2/07, Ron <rjpeace@earthlink.net> wrote:
> >
> >>May I suggest that it is possible that your schema, queries, etc were
> >>all optimized for pg 7.x running on the old HW?
> >>(explain analyze shows the old system taking ~1/10 the time per row
> >>as well as estimating the number of rows more accurately)
> >>
> >>RAM is =cheap=. Much cheaper than the cost of a detective hunt
> >>followed by rework to queries, schema, etc.
> >>Fitting the entire DB into RAM is guaranteed to help unless this is
> >>an OLTP like application where HD IO is required to be synchronous..
> >>If you can fit the entire DB comfortably into RAM, do it and buy
> >>yourself the time to figure out the rest of the story w/o impacting
> >>on production performance.
> >
> >Perhaps so. I just don't want to spend $1000 on ram and have it only
> >marginally improve performance if at all. The old DB works, so we can
> >keep using that until we sort this out.
> >
> >Alex
> 1= $1000 worth of RAM is very likely less than the $ worth of, say,
> 10 hours of your time to your company. Perhaps much less.
> (Your =worth=, not your pay or even your fully loaded cost. This
> number tends to be >= 4x what you are paid unless the organization
> you are working for is in imminent financial danger.)
> You've already put more considerably more than 10 hours of your time
> into this...
>
> 2= If the DB goes from not fitting completely into RAM to being
> completely RAM resident, you are almost 100% guaranteed a big
> performance boost.
> The exception is an OLTP like app where DB writes can't be done
> a-synchronously (doing financial transactions, real time control systems, etc).
> Data mines should never have this issue.
>
> 3= Whether adding enough RAM to make the DB RAM resident (and
> re-configuring conf, etc, appropriately) solves the problem or not,
> you will have gotten a serious lead as to what's wrong.
>
> ...and I still think looking closely at the actual physical layout of
> the tables in the SAN is likely to be worth it.
>
How would I go about doing that?
Thanks,
Alex
^ permalink raw reply [nested|flat] 53+ messages in thread
* Re: strange performance regression between 7.4 and 8.1
2007-03-01 20:44 strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-02 08:51 ` Re: strange performance regression between 7.4 and 8.1 Florian Weimer <fweimer@bfk.de>
2007-03-02 15:16 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-02 15:48 ` Re: strange performance regression between 7.4 and 8.1 Ron <rjpeace@earthlink.net>
2007-03-02 16:03 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-02 17:59 ` Re: strange performance regression between 7.4 and 8.1 Ron <rjpeace@earthlink.net>
2007-03-02 19:43 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
@ 2007-03-02 20:28 ` Ron <rjpeace@earthlink.net>
2007-03-02 21:20 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
0 siblings, 1 reply; 53+ messages in thread
From: Ron @ 2007-03-02 20:28 UTC (permalink / raw)
To: Alex Deucher <alexdeucher@gmail.com>; +Cc: pgsql-performance
At 02:43 PM 3/2/2007, Alex Deucher wrote:
>On 3/2/07, Ron <rjpeace@earthlink.net> wrote:
>>
>>...and I still think looking closely at the actual physical layout of
>>the tables in the SAN is likely to be worth it.
>
>How would I go about doing that?
>
>Alex
Hard for me to give specific advice when I don't know what SAN
product we are talking about nor what kind of HDs are in it nor how
those HDs are presently configured...
I quote you in an earlier post:
"The RAID groups on the SAN were set up for maximum capacity rather
than for performance. Using it for the databases just came up recently."
That implies to me that the SAN is more or less set up as a huge 105
HD (assuming this number is correct? We all know how "assume" is
spelled...) JBOD or RAID 5 (or 6, or 5*, or 6*) set.
=IF= that is true, tables are not being given dedicated RAID
groups. That implies that traditional lore like having pg_xlog on
dedicated spindles is being ignored.
Nor is the more general Best Practice of putting the most heavily
used tables onto dedicated spindles being followed.
In addition, the most space efficient RAID levels: 5* or 6*, are not
the best performing one (RAID 10 striping your mirrors)
In short, configuring a SAN for maximum capacity is exactly the wrong
thing to do if one is planning to use it in the best way to support
DB performance.
I assume (there's that word again...) that there is someone in your
organization who understands how the SAN is configured and administered.
You need to talk to them about these issues.
Cheers,
Ron
^ permalink raw reply [nested|flat] 53+ messages in thread
* Re: strange performance regression between 7.4 and 8.1
2007-03-01 20:44 strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-02 08:51 ` Re: strange performance regression between 7.4 and 8.1 Florian Weimer <fweimer@bfk.de>
2007-03-02 15:16 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-02 15:48 ` Re: strange performance regression between 7.4 and 8.1 Ron <rjpeace@earthlink.net>
2007-03-02 16:03 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-02 17:59 ` Re: strange performance regression between 7.4 and 8.1 Ron <rjpeace@earthlink.net>
2007-03-02 19:43 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-02 20:28 ` Re: strange performance regression between 7.4 and 8.1 Ron <rjpeace@earthlink.net>
@ 2007-03-02 21:20 ` Alex Deucher <alexdeucher@gmail.com>
2007-03-02 21:34 ` Re: strange performance regression between 7.4 and 8.1 Guido Neitzer <lists@event-s.net>
0 siblings, 1 reply; 53+ messages in thread
From: Alex Deucher @ 2007-03-02 21:20 UTC (permalink / raw)
To: Ron <rjpeace@earthlink.net>; +Cc: pgsql-performance
On 3/2/07, Ron <rjpeace@earthlink.net> wrote:
> At 02:43 PM 3/2/2007, Alex Deucher wrote:
> >On 3/2/07, Ron <rjpeace@earthlink.net> wrote:
> >>
> >>...and I still think looking closely at the actual physical layout of
> >>the tables in the SAN is likely to be worth it.
> >
> >How would I go about doing that?
> >
> >Alex
>
> Hard for me to give specific advice when I don't know what SAN
> product we are talking about nor what kind of HDs are in it nor how
> those HDs are presently configured...
>
> I quote you in an earlier post:
> "The RAID groups on the SAN were set up for maximum capacity rather
> than for performance. Using it for the databases just came up recently."
>
> That implies to me that the SAN is more or less set up as a huge 105
> HD (assuming this number is correct? We all know how "assume" is
> spelled...) JBOD or RAID 5 (or 6, or 5*, or 6*) set.
>
> =IF= that is true, tables are not being given dedicated RAID
> groups. That implies that traditional lore like having pg_xlog on
> dedicated spindles is being ignored.
> Nor is the more general Best Practice of putting the most heavily
> used tables onto dedicated spindles being followed.
>
> In addition, the most space efficient RAID levels: 5* or 6*, are not
> the best performing one (RAID 10 striping your mirrors)
>
> In short, configuring a SAN for maximum capacity is exactly the wrong
> thing to do if one is planning to use it in the best way to support
> DB performance.
>
> I assume (there's that word again...) that there is someone in your
> organization who understands how the SAN is configured and administered.
> You need to talk to them about these issues.
>
Ah OK. I see what you are saying; thank you for clarifying. Yes,
the SAN is configured for maximum capacity; it has large RAID 5
groups. As I said earlier, we never intended to run a DB on the SAN,
it just happened to come up, hence the configuration.
Alex
^ permalink raw reply [nested|flat] 53+ messages in thread
* Re: strange performance regression between 7.4 and 8.1
2007-03-01 20:44 strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-02 08:51 ` Re: strange performance regression between 7.4 and 8.1 Florian Weimer <fweimer@bfk.de>
2007-03-02 15:16 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-02 15:48 ` Re: strange performance regression between 7.4 and 8.1 Ron <rjpeace@earthlink.net>
2007-03-02 16:03 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-02 17:59 ` Re: strange performance regression between 7.4 and 8.1 Ron <rjpeace@earthlink.net>
2007-03-02 19:43 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-02 20:28 ` Re: strange performance regression between 7.4 and 8.1 Ron <rjpeace@earthlink.net>
2007-03-02 21:20 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
@ 2007-03-02 21:34 ` Guido Neitzer <lists@event-s.net>
2007-03-02 22:59 ` Re: strange performance regression between 7.4 and 8.1 Jeff Frost <jeff@frostconsultingllc.com>
0 siblings, 1 reply; 53+ messages in thread
From: Guido Neitzer @ 2007-03-02 21:34 UTC (permalink / raw)
To: Alex Deucher <alexdeucher@gmail.com>; +Cc: pgsql-performance
On 02.03.2007, at 14:20, Alex Deucher wrote:
> Ah OK. I see what you are saying; thank you for clarifying. Yes,
> the SAN is configured for maximum capacity; it has large RAID 5
> groups. As I said earlier, we never intended to run a DB on the SAN,
> it just happened to come up, hence the configuration.
So why not dumping the stuff ones, importing into a PG configured to
use local discs (Or even ONE local disc, you might have the 16GB you
gave as a size for the db on the local machine, right?) and testing
whether the problem is with PG connecting to the SAN. So you have one
factor less to consider after all your changes.
Maybe it's just that something in the chain from PG to the actual HD
spindles kills your random access performance for getting the actual
rows.
cug
^ permalink raw reply [nested|flat] 53+ messages in thread
* Re: strange performance regression between 7.4 and 8.1
2007-03-01 20:44 strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-02 08:51 ` Re: strange performance regression between 7.4 and 8.1 Florian Weimer <fweimer@bfk.de>
2007-03-02 15:16 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-02 15:48 ` Re: strange performance regression between 7.4 and 8.1 Ron <rjpeace@earthlink.net>
2007-03-02 16:03 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-02 17:59 ` Re: strange performance regression between 7.4 and 8.1 Ron <rjpeace@earthlink.net>
2007-03-02 19:43 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-02 20:28 ` Re: strange performance regression between 7.4 and 8.1 Ron <rjpeace@earthlink.net>
2007-03-02 21:20 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-02 21:34 ` Re: strange performance regression between 7.4 and 8.1 Guido Neitzer <lists@event-s.net>
@ 2007-03-02 22:59 ` Jeff Frost <jeff@frostconsultingllc.com>
2007-03-02 23:31 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
0 siblings, 1 reply; 53+ messages in thread
From: Jeff Frost @ 2007-03-02 22:59 UTC (permalink / raw)
To: Guido Neitzer <lists@event-s.net>; +Cc: Alex Deucher <alexdeucher@gmail.com>; pgsql-performance
On Fri, 2 Mar 2007, Guido Neitzer wrote:
> On 02.03.2007, at 14:20, Alex Deucher wrote:
>
>> Ah OK. I see what you are saying; thank you for clarifying. Yes,
>> the SAN is configured for maximum capacity; it has large RAID 5
>> groups. As I said earlier, we never intended to run a DB on the SAN,
>> it just happened to come up, hence the configuration.
>
> So why not dumping the stuff ones, importing into a PG configured to use
> local discs (Or even ONE local disc, you might have the 16GB you gave as a
> size for the db on the local machine, right?) and testing whether the problem
> is with PG connecting to the SAN. So you have one factor less to consider
> after all your changes.
>
> Maybe it's just that something in the chain from PG to the actual HD spindles
> kills your random access performance for getting the actual rows.
I am actually starting to think that the SAN may be introducing some amount of
latency that is enough to kill your random IO which is what all of the queries
in question are doing - look up in index - fetch row from table.
If you have the time, it would be totally worth it to test with a local disk
and see how that affects the speed.
I would think that even with RAID5, a SAN with that many spindles would be
quite fast in raw throughput, but perhaps it's just seek latency that's
killing you.
When you run the bonnie tests again, take note of what the seeks/sec is
compared with the old disk. Also, you should run bonnie with the -b switch to
see if that causes significant slowdown of the writes...maybe minor synced
write activity to pg_xlog is bogging the entire system down. Is the system
spending most of its time in IO wait?
Also, another item of note might be the actual on disk DB size..I wonder if it
has changed significantly going from SQL_ASCII to UTF8.
In 8.1 you can do this:
SELECT datname,
pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database;
In 7.4, you'll need to install the dbsize contrib module to get the same info.
--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
^ permalink raw reply [nested|flat] 53+ messages in thread
* Re: strange performance regression between 7.4 and 8.1
2007-03-01 20:44 strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-02 08:51 ` Re: strange performance regression between 7.4 and 8.1 Florian Weimer <fweimer@bfk.de>
2007-03-02 15:16 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-02 15:48 ` Re: strange performance regression between 7.4 and 8.1 Ron <rjpeace@earthlink.net>
2007-03-02 16:03 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-02 17:59 ` Re: strange performance regression between 7.4 and 8.1 Ron <rjpeace@earthlink.net>
2007-03-02 19:43 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-02 20:28 ` Re: strange performance regression between 7.4 and 8.1 Ron <rjpeace@earthlink.net>
2007-03-02 21:20 ` Re: strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-02 21:34 ` Re: strange performance regression between 7.4 and 8.1 Guido Neitzer <lists@event-s.net>
2007-03-02 22:59 ` Re: strange performance regression between 7.4 and 8.1 Jeff Frost <jeff@frostconsultingllc.com>
@ 2007-03-02 23:31 ` Alex Deucher <alexdeucher@gmail.com>
0 siblings, 0 replies; 53+ messages in thread
From: Alex Deucher @ 2007-03-02 23:31 UTC (permalink / raw)
To: Jeff Frost <jeff@frostconsultingllc.com>; +Cc: Guido Neitzer <lists@event-s.net>; pgsql-performance
On 3/2/07, Jeff Frost <jeff@frostconsultingllc.com> wrote:
> On Fri, 2 Mar 2007, Guido Neitzer wrote:
>
> > On 02.03.2007, at 14:20, Alex Deucher wrote:
> >
> >> Ah OK. I see what you are saying; thank you for clarifying. Yes,
> >> the SAN is configured for maximum capacity; it has large RAID 5
> >> groups. As I said earlier, we never intended to run a DB on the SAN,
> >> it just happened to come up, hence the configuration.
> >
> > So why not dumping the stuff ones, importing into a PG configured to use
> > local discs (Or even ONE local disc, you might have the 16GB you gave as a
> > size for the db on the local machine, right?) and testing whether the problem
> > is with PG connecting to the SAN. So you have one factor less to consider
> > after all your changes.
> >
> > Maybe it's just that something in the chain from PG to the actual HD spindles
> > kills your random access performance for getting the actual rows.
>
> I am actually starting to think that the SAN may be introducing some amount of
> latency that is enough to kill your random IO which is what all of the queries
> in question are doing - look up in index - fetch row from table.
>
> If you have the time, it would be totally worth it to test with a local disk
> and see how that affects the speed.
>
> I would think that even with RAID5, a SAN with that many spindles would be
> quite fast in raw throughput, but perhaps it's just seek latency that's
> killing you.
>
> When you run the bonnie tests again, take note of what the seeks/sec is
> compared with the old disk. Also, you should run bonnie with the -b switch to
> see if that causes significant slowdown of the writes...maybe minor synced
> write activity to pg_xlog is bogging the entire system down. Is the system
> spending most of its time in IO wait?
>
> Also, another item of note might be the actual on disk DB size..I wonder if it
> has changed significantly going from SQL_ASCII to UTF8.
>
> In 8.1 you can do this:
>
> SELECT datname,
> pg_size_pretty(pg_database_size(datname)) AS size
> FROM pg_database;
>
> In 7.4, you'll need to install the dbsize contrib module to get the same info.
>
I'm beginning the think the same thing. I'm planning to try the tests
above next week. I'll let you know what I find out.
Thanks!
Alex
^ permalink raw reply [nested|flat] 53+ messages in thread
* Re: strange performance regression between 7.4 and 8.1
2007-03-01 20:44 strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-02 08:51 ` Re: strange performance regression between 7.4 and 8.1 Florian Weimer <fweimer@bfk.de>
@ 2007-03-02 19:33 ` Alvaro Herrera <alvherre@commandprompt.com>
2 siblings, 0 replies; 53+ messages in thread
From: Alvaro Herrera @ 2007-03-02 19:33 UTC (permalink / raw)
To: Florian Weimer <fweimer@bfk.de>; +Cc: Alex Deucher <alexdeucher@gmail.com>; pgsql-performance
Florian Weimer escribió:
> Locale settings make a huge difference for sorting and LIKE queries.
> We usually use the C locale and SQL_ASCII encoding, mostly for
> performance reasons. (Proper UTF-8 can be enforced through
> constraints if necessary.)
Hmm, you are aware of varchar_pattern_ops and related opclasses, right?
That helps for LIKE queries in non-C locales (though you do have to keep
almost-duplicate indexes).
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
^ permalink raw reply [nested|flat] 53+ messages in thread
* Re: strange performance regression between 7.4 and 8.1
2007-03-01 20:44 strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
@ 2007-03-02 17:45 ` Anton Rommerskirchen <atr@atrsoft.de>
2 siblings, 0 replies; 53+ messages in thread
From: Anton Rommerskirchen @ 2007-03-02 17:45 UTC (permalink / raw)
To: pgsql-performance
Am Donnerstag 01 März 2007 21:44 schrieb Alex Deucher:
> Hello,
>
> I have noticed a strange performance regression and I'm at a loss as
> to what's happening. We have a fairly large database (~16 GB). The
> original postgres 7.4 was running on a sun v880 with 4 CPUs and 8 GB
> of ram running Solaris on local scsi discs. The new server is a sun
> Opteron box with 4 cores, 8 GB of ram running postgres 8.1.4 on Linux
> (AMD64) on a 4 Gbps FC SAN volume. When we created the new database
> it was created from scratch rather than copying over the old one,
> however the table structure is almost identical (UTF8 on the new one
> vs. C on the old). The problem is queries are ~10x slower on the new
> hardware. I read several places that the SAN might be to blame, but
> testing with bonnie and dd indicates that the SAN is actually almost
> twice as fast as the scsi discs in the old sun server. I've tried
> adjusting just about every option in the postgres config file, but
> performance remains the same. Any ideas?
>
1. Do you use NUMA ctl for locking the db on one node ?
2. do you use bios to interleave memeory ?
3. do you expand cache over mor than one numa node ?
> Thanks,
>
> Alex
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
--
ATRSoft GmbH
Rosellstrasse 9
D 50354 Hürth
Deutschland
Tel .: +49(0)2233 691324
Geschäftsführer Anton Rommerskirchen
Köln HRB 44927
STNR 224/5701 - 1010
^ permalink raw reply [nested|flat] 53+ messages in thread
end of thread, other threads:[~2007-03-08 21:07 UTC | newest]
Thread overview: 53+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-03-01 20:40 strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-06 02:14 ` Guido Neitzer <lists@event-s.net>
2007-03-06 02:56 ` Alex Deucher <alexdeucher@gmail.com>
2007-03-06 02:58 ` Guido Neitzer <lists@event-s.net>
2007-03-06 15:25 ` Alex Deucher <alexdeucher@gmail.com>
2007-03-06 16:58 ` Ron <rjpeace@earthlink.net>
2007-03-06 18:11 ` Alex Deucher <alexdeucher@gmail.com>
2007-03-08 12:23 ` Rodrigo Madera <rodrigo.madera@gmail.com>
2007-03-08 18:34 ` Craig A. James <cjames@modgraph-usa.com>
2007-03-08 18:59 ` Ron <rjpeace@earthlink.net>
2007-03-08 19:38 ` Re: strange performance regression between 7.4 and 8.1 Carlos Moreno <moreno_pg@mochima.com>
2007-03-08 19:34 ` Carlos Moreno <moreno_pg@mochima.com>
2007-03-08 21:07 ` Craig A. James <cjames@modgraph-usa.com>
2007-03-01 20:44 strange performance regression between 7.4 and 8.1 Alex Deucher <alexdeucher@gmail.com>
2007-03-01 20:50 ` Joshua D. Drake <jd@commandprompt.com>
2007-03-01 20:56 ` Jeff Frost <jeff@frostconsultingllc.com>
2007-03-01 21:11 ` Alex Deucher <alexdeucher@gmail.com>
2007-03-01 21:34 ` Jeff Frost <jeff@frostconsultingllc.com>
2007-03-01 23:11 ` Alex Deucher <alexdeucher@gmail.com>
2007-03-02 00:21 ` Jeff Frost <jeff@frostconsultingllc.com>
2007-03-02 15:28 ` Alex Deucher <alexdeucher@gmail.com>
2007-03-02 16:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-03-02 16:14 ` Alex Deucher <alexdeucher@gmail.com>
2007-03-01 21:06 ` Alex Deucher <alexdeucher@gmail.com>
2007-03-01 21:36 ` Jeff Frost <jeff@frostconsultingllc.com>
2007-03-01 21:50 ` Alex Deucher <alexdeucher@gmail.com>
2007-03-01 22:01 ` Jeff Frost <jeff@frostconsultingllc.com>
2007-03-01 22:12 ` Alex Deucher <alexdeucher@gmail.com>
2007-03-02 00:36 ` Jeff Frost <jeff@frostconsultingllc.com>
2007-03-02 01:01 ` Ron <rjpeace@earthlink.net>
2007-03-02 01:44 ` Alex Deucher <alexdeucher@gmail.com>
2007-03-02 01:46 ` Joshua D. Drake <jd@commandprompt.com>
2007-03-02 01:47 ` Alex Deucher <alexdeucher@gmail.com>
2007-03-02 02:21 ` Joshua D. Drake <jd@commandprompt.com>
2007-03-02 03:16 ` Alex Deucher <alexdeucher@gmail.com>
2007-03-02 01:56 ` Jeff Frost <jeff@frostconsultingllc.com>
2007-03-02 02:06 ` Alex Deucher <alexdeucher@gmail.com>
2007-03-02 08:51 ` Florian Weimer <fweimer@bfk.de>
2007-03-02 13:56 ` Carlos Moreno <moreno_pg@mochima.com>
2007-03-02 15:21 ` Ron <rjpeace@earthlink.net>
2007-03-02 15:16 ` Alex Deucher <alexdeucher@gmail.com>
2007-03-02 15:48 ` Ron <rjpeace@earthlink.net>
2007-03-02 16:03 ` Alex Deucher <alexdeucher@gmail.com>
2007-03-02 16:50 ` Scott Marlowe <smarlowe@g2switchworks.com>
2007-03-02 17:59 ` Ron <rjpeace@earthlink.net>
2007-03-02 19:43 ` Alex Deucher <alexdeucher@gmail.com>
2007-03-02 20:28 ` Ron <rjpeace@earthlink.net>
2007-03-02 21:20 ` Alex Deucher <alexdeucher@gmail.com>
2007-03-02 21:34 ` Guido Neitzer <lists@event-s.net>
2007-03-02 22:59 ` Jeff Frost <jeff@frostconsultingllc.com>
2007-03-02 23:31 ` Alex Deucher <alexdeucher@gmail.com>
2007-03-02 19:33 ` Alvaro Herrera <alvherre@commandprompt.com>
2007-03-02 17:45 ` Anton Rommerskirchen <atr@atrsoft.de>
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