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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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
>

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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

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
>

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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






---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend