Обсуждение: Hardware upgraded but performance still ain't good enough

Hi,

First of all I must tell that my reality in a southern brazilian city is
way different than what we read in the list. I was lookig for ways to
find the HW bottleneck and saw a configuration like:

"we recently upgraded our dual Xeon Dell to a brand new Sun v40z with 4
opterons, 16GB of memory and MegaRAID with enough disks. OS is Debian
Sarge amd64, PostgreSQL is 8.0.3." on
(http://archives.postgresql.org/pgsql-performance/2005-07/msg00431.php)

Our old server was a very modest Dell Xeon 2.8 (512 Kb Cache), with 1 GB
RAM and one SCSI disc. This server runs PostgreSQL (8.1.4), Apache (PHP)
and other minor services. We managed to get a test machine, a HP Xeon
3.2 (2 MB cache), also with 1 GB RAM but 4 SCSI discs (in one sigle
array controller). They're organized in the following way:

disk 0: Linux Root
disk 1: Database Cluster
disk 2: pg_xlog
disk 3: a dir the suffers constant read/write operations

The database size stands around 10 GB. The new server has a better
performance than the old one, but sometimes it still stucks. We tried to
use a HP proprietary tool to monitor the server, and find out what is
the bottleneck, but it's been difficult to install it on Debian. The
tool is only certified for SuSe and RedHat. So we tried to use some
Linux tools to see what's going on, like vmstat and iostat. Are this
tools (vm and iostat) enough? Should we use something else? Is there any
specifical material about finding bottlenecks in Linux/PostgreSQL
machines? Is our disks design proper?

I really apologize for my lack of knowledge in this area, and for the
excessive number of questions in a single e-mail.

Best regards,
Alvaro

First off - very few third party tools support debian.  Debian is a sure fire way to have an unsupported system.  Use RedHat or SuSe (flame me all you want, it doesn't make it less true).

Second, run bonnie++ benchmark against your disk array(s) to see what performance you are getting, and make sure it's reasonable.

Single drives for stuff is not a great way to go for either speed or reliability, highly not recommended for a production system.  Use SAS or SATA for the best speed for your $$s, don't buy SAN, they are overpriced and often don't perform.  RAM could be more to be honest too.

Diagnosing the bottleneck can be done with combinations of top, iostat and vmstat.  If you have high iowait numbers then your system is waiting on the disks.  If you have high system CPU usage, then disks are also worth a look, but not as bad as high iowait.  If you have high user CPU with little iowait and little system CPU, and very little io activity in iostat, then you are CPU bound.  If you are IO bound, you need to figure if it's reads or writes.  If it's reads, then more RAM will help.  if it's writes, then you need more spindles and more controller cache with RAID (please think carefully before using RAID 5 in a write intensive environment, it's not ideal).

The other thing is you will probably want to turn on stats in postgres to figure out which queries are the bad ones (does anyone have good docs posted for this?).  Once you have identified the bad queries, you can explain analyze them, and figure out why they suck.

Alex.

* Alex Turner (armtuk@gmail.com) wrote:
> First off - very few third party tools support debian.  Debian is a sure
> fire way to have an unsupported system.  Use RedHat or SuSe (flame me all
> you want, it doesn't make it less true).

Yeah, actually, it does make it less true since, well, it's really not
all that true to begin with.

What you're probably intending to say is that fewer companies say "Works
with Debian!" on their advertising material or list it as "officially
supported".  I've had *very* few problems running commercial apps on
Debian (including things like Oracle and IBM SAN management software).
Generally it's just take the rpms and either install them *using* rpm
(which is available in Debian...) or use alien to convert them to a
tarball and/or deb.

HP is actually pretty big into Debian and I'd be curious as to what the
problems installing the monitoring tools were.  My guess is that the
issue is actually some kernel module or something, in which case any
kernel that they don't build the module (or write it, depending..) for
may be problematic.  This would probably include some releases of
RedHat/SuSe (ES, Fedora, who knows) and pretty much any kernel you build
using sources off of kernel.org or for any other distribution unless you
know exactly what versions/patches they support.

Feel free to contact me off-list if you'd like to continue this
discussion since I don't really see it as appropriate for this list.

    Thanks,

        Stephen

Alvaro,

* Alex Turner (armtuk@gmail.com) wrote:
> The other thing is you will probably want to turn on stats in postgres to
> figure out which queries are the bad ones (does anyone have good docs posted
> for this?).  Once you have identified the bad queries, you can explain
> analyze them, and figure out why they suck.

Given your position, this might be the best approach to take to find
some 'low-hanging fruit'.  Do you have queries which are complex in some
way?  Do you have many long-open transactions?  If you're doing more
than simple queries then you may want to explain analyze the more
complex ones and try to speed them up.  If you run into trouble
understanding the output or how to improve it then post it here (with as
much info as you can, schema definitions, the query, the explain analyze
results, etc) and we can help.

top/iostat/vmstat are very useful tools too and can help with hardware
decisions but you probably want to review your queries and make sure the
database is performing as best it can with the setup you have today
before throwing more hardware at it.

    Thanks,

        Stephen

On Tue, 8 Aug 2006, Stephen Frost wrote:

> * Alex Turner (armtuk@gmail.com) wrote:
>> First off - very few third party tools support debian.  Debian is a sure
>> fire way to have an unsupported system.  Use RedHat or SuSe (flame me all
>> you want, it doesn't make it less true).
>
> Yeah, actually, it does make it less true since, well, it's really not
> all that true to begin with.
>
> What you're probably intending to say is that fewer companies say "Works
> with Debian!" on their advertising material or list it as "officially
> supported".  I've had *very* few problems running commercial apps on
> Debian (including things like Oracle and IBM SAN management software).
> Generally it's just take the rpms and either install them *using* rpm
> (which is available in Debian...) or use alien to convert them to a
> tarball and/or deb.

there's a huge difference between 'works on debian' and 'supported on
debian'. I do use debian extensivly, (along with slackware on my personal
machines), so i am comfortable getting things to work. but 'supported'
means that when you run into a problem you can call for help without being
told 'sorry, switch distros, then call us back'.

even many of the companies that offer support for postgres have this
problem. the explination is always that they can't test every distro out
there so they pick a few and support those (this is one of the reasons why
I am watching ubuntu with great interest, it's debian under the covers,
but they're starting to get the recognition from the support groups of
companies)

David Lang

* David Lang (david@lang.hm) wrote:
> there's a huge difference between 'works on debian' and 'supported on
> debian'. I do use debian extensivly, (along with slackware on my personal
> machines), so i am comfortable getting things to work. but 'supported'
> means that when you run into a problem you can call for help without being
> told 'sorry, switch distros, then call us back'.

Have you ever actually had that happen?  I havn't and I've called
support for a number of different issues for various commercial
software.  In the end it might boil down to some distribution-specific
issue that they're not willing to fix but honestly that's pretty rare.

> even many of the companies that offer support for postgres have this
> problem. the explination is always that they can't test every distro out
> there so they pick a few and support those (this is one of the reasons why

My experience has been that unless it's pretty clearly some
distro-specific issue (which doesn't happen all that often, but it's
good to be familiar with what would probably be a distro-specific issue
and what wouldn't), the support folks are willing to help debug it.

    Thanks,

        Stephen

Alex Turner wrote:
> First off - very few third party tools support debian.  Debian is a sure
> fire way to have an unsupported system.  Use RedHat or SuSe (flame me all
> you want, it doesn't make it less true).

*cough* BS *cough*

Linux is Linux. It doesn't matter what trademark you put on top of it.
As long as they are running a current version of Linux (e.g; kernel 2.6)
they should be fine.

With Debian that may or may not be the case and that could be an issue.
To get the best luck, I would suggest (if you want to stay with a Debian
base) Ubuntu Dapper LTS.

Sincerely,

Joshua D. Drake




--

    === 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/

> Have you ever actually had that happen?  I havn't and I've called
> support for a number of different issues for various commercial
> software.  In the end it might boil down to some distribution-specific
> issue that they're not willing to fix but honestly that's pretty rare.

Very rare, if you are using a reputable vendor.

>
>> even many of the companies that offer support for postgres have this
>> problem. the explination is always that they can't test every distro out
>> there so they pick a few and support those (this is one of the reasons why

Ahh and which companies would these be? As a representative of the most
prominent one in the  US I can tell you that you are not speaking from a
knowledgeable position.

Sincerely,

Joshua D. Drake


--

    === 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/

On 8/7/06, Alvaro Nunes Melo <al_nunes@atua.com.br> wrote:
> "we recently upgraded our dual Xeon Dell to a brand new Sun v40z with 4
> opterons, 16GB of memory and MegaRAID with enough disks. OS is Debian
> Sarge amd64, PostgreSQL is 8.0.3." on
> (http://archives.postgresql.org/pgsql-performance/2005-07/msg00431.php)

well, if you spend three months optimizing your application or buy a
10k$ server to get the same result, which is cheaper? :)

> The database size stands around 10 GB. The new server has a better
> performance than the old one, but sometimes it still stucks. We tried to
> use a HP proprietary tool to monitor the server, and find out what is
> the bottleneck, but it's been difficult to install it on Debian. The

I'm not familiar with the hp tool, but I suspect you are not missing
much.  If you are looking for a free distro, you might have some luck
with centos.  most redhat binary rpms will install on it.

> tool is only certified for SuSe and RedHat. So we tried to use some
> Linux tools to see what's going on, like vmstat and iostat. Are this
> tools (vm and iostat) enough? Should we use something else? Is there any
> specifical material about finding bottlenecks in Linux/PostgreSQL
> machines? Is our disks design proper?

those are pretty broad questions, so you will only get broad answers.
you might want to consider hooking up with some commercial support
(I've heard good things about commandprompt) or providing more
detailed information so that you can get some help from this list,
including:
iostat/vmstat reports
explain analyze
information from top

nicely summarized at the time the problems occur.
regards,
merlin

> I really apologize for my lack of knowledge in this area, and for the
> excessive number of questions in a single e-mail.
>
> Best regards,
> Alvaro

On Wed, 9 Aug 2006, Stephen Frost wrote:

> * David Lang (david@lang.hm) wrote:
>> there's a huge difference between 'works on debian' and 'supported on
>> debian'. I do use debian extensivly, (along with slackware on my personal
>> machines), so i am comfortable getting things to work. but 'supported'
>> means that when you run into a problem you can call for help without being
>> told 'sorry, switch distros, then call us back'.
>
> Have you ever actually had that happen?  I havn't and I've called
> support for a number of different issues for various commercial
> software.  In the end it might boil down to some distribution-specific
> issue that they're not willing to fix but honestly that's pretty rare.

unfortunantly I have, repeatedly with different products.

if you can manage to get past the first couple of levels of support to
people who really understand things rather then just useing checklists you
are more likly to get help, but even there I've run into people who seem
eager to take the easy way out by assuming that it must be a distro thing
rather then anything with their product (even in cases where it ended up
being a simple config thing)

David Lang

On Wed, 9 Aug 2006, Joshua D. Drake wrote:

>>> even many of the companies that offer support for postgres have this
>>> problem. the explination is always that they can't test every distro out
>>> there so they pick a few and support those (this is one of the reasons why
>
> Ahh and which companies would these be? As a representative of the most
> prominent one in the  US I can tell you that you are not speaking from a
> knowledgeable position.

note I said many, not all. I am aware that your company does not fall into
this catagory.

David Lang

>> Ahh and which companies would these be? As a representative of the
>> most prominent one in the  US I can tell you that you are not speaking
>> from a knowledgeable position.
>
> note I said many, not all. I am aware that your company does not fall
> into this catagory.

I know, but I am curious as to *what* companies. Any reputable
PostgreSQL company is going to support Linux as a whole except maybe
some fringe distros like Gentoo or RedFlag. Not to mention FreeBSD and
Solaris.

Sincerely,

Joshua D. Drake


>
> David Lang
>


--

    === 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/

On Wed, 9 Aug 2006, Joshua D. Drake wrote:

>>> Ahh and which companies would these be? As a representative of the most
>>> prominent one in the  US I can tell you that you are not speaking from a
>>> knowledgeable position.
>>
>> note I said many, not all. I am aware that your company does not fall into
>> this catagory.
>
> I know, but I am curious as to *what* companies. Any reputable PostgreSQL
> company is going to support Linux as a whole except maybe some fringe distros
> like Gentoo or RedFlag. Not to mention FreeBSD and Solaris.

I'm not going to name names in public, but I will point out that different
companies definitions of what constatutes 'fringe distros' are different.
For some any linux other then RedHat Enterprise or SuSE is a fringe distro
(with SuSE being a relativly recent addition, for a while RedHat were
frequently the only supported distro versions)

and please note, when I'm talking about support, it's not just postgresql
support, but also hardware/driver support that can run into these problems

David Lang

On Wed, 2006-08-09 at 11:37, David Lang wrote:
> On Wed, 9 Aug 2006, Joshua D. Drake wrote:
>
> >>> Ahh and which companies would these be? As a representative of the most
> >>> prominent one in the  US I can tell you that you are not speaking from a
> >>> knowledgeable position.
> >>
> >> note I said many, not all. I am aware that your company does not fall into
> >> this catagory.
> >
> > I know, but I am curious as to *what* companies. Any reputable PostgreSQL
> > company is going to support Linux as a whole except maybe some fringe distros
> > like Gentoo or RedFlag. Not to mention FreeBSD and Solaris.
>
> I'm not going to name names in public, but I will point out that different
> companies definitions of what constatutes 'fringe distros' are different.
> For some any linux other then RedHat Enterprise or SuSE is a fringe distro
> (with SuSE being a relativly recent addition, for a while RedHat were
> frequently the only supported distro versions)
>
> and please note, when I'm talking about support, it's not just postgresql
> support, but also hardware/driver support that can run into these problems

I've run into this as well.  Generally speaking, the larger the company,
the more likely you are to get the "we don't support that" line.

On Aug 9, 2006, at 5:47 AM, Joshua D. Drake wrote:

> Alex Turner wrote:
>> First off - very few third party tools support debian.  Debian is
>> a sure
>> fire way to have an unsupported system.  Use RedHat or SuSe (flame
>> me all
>> you want, it doesn't make it less true).
>
> *cough* BS *cough*
>
> Linux is Linux. It doesn't matter what trademark you put on top of
> it. As long as they are running a current version of Linux (e.g;
> kernel 2.6) they should be fine.

That's really not the case, at least to the degree that makes a
difference between "supported" and "unsupported".
>
> With Debian that may or may not be the case and that could be an
> issue.
> To get the best luck, I would suggest (if you want to stay with a
> Debian base) Ubuntu Dapper LTS.

Different Linux distributions include different shared libraries, put
different things in different places and generally break applications
in a variety of different ways (SELinux would be one example of
that commonly seen here).

If I don't QA my application on it, it isn't supported. I can't
necessarily
replicate problems on Linux distributions I don't have installed in
my QA lab, so I can't guarantee to fix problems that are specific
to that distribution. I can't even be sure that it will install and run
correctly without doing basic QA of the installation process on
that distribution.

And in my case that's just for user space applications. It's got to
be even worse for hardware drivers.

Our usual phrase is "We support RedHat versions *mumble*
only. We expect our application to run correctly on any Linux
distribution, though you may have to install additional shared
libraries."

I'm quite happy with customers running Debian, SuSe or what
have you, as long as they have access to a sysadmin who's
comfortable with that distribution. (I'd probably deny support to
anyone running Gentoo, though :) )

We've never had big problems with people running our apps on
"unsupported" problems, but those users have had to do some
more diagnosis of problems themselves, and we've been less
able to support them than we can users who use the same
distribution we QA on.

(It's not just Linux, either. We "support" Windows XP, but we run
just fine on 2000 and 95/98.)

Cheers,
   Steve

>>
>> and please note, when I'm talking about support, it's not just postgresql
>> support, but also hardware/driver support that can run into these problems
>
> I've run into this as well.  Generally speaking, the larger the company,
> the more likely you are to get the "we don't support that" line.
>

/me *chuckles* and whispers to himself.. no wonder were winning.

Sincerely,

Joshua D. Drake


--

    === 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/

> > First off - very few third party tools support debian.  Debian is
> a
> > sure fire way to have an unsupported system.  Use RedHat or SuSe
> > (flame me all you want, it doesn't make it less true).
>
> *cough* BS *cough*
>
> Linux is Linux. It doesn't matter what trademark you put on top of
> it.
> As long as they are running a current version of Linux (e.g; kernel
> 2.6) they should be fine.

Unfortunatly, that' not my experience either.
Both RedHat and SuSE heavily modify the kernel. So anything that needs
anything near kernel space (two examples: the HP management/monitoring
tools and the EMC/Legato Networker backup software) simply does not work
on Linux (linux being the kernel from kernel.org). They only work on
RedHat/SuSE. To the point of not compiling/starting/working, not just
the support part.

(One could argue that they shouldn't claim linux support then, but
specifically RH/SuSE, but I don't expect them to do that..)

BTW, it used to work much better with 2.4, but since there is no real
"stable series" kernel in 2.6, it's just a lost cause there it seems.

//Magnus

On 8/18/06, Magnus Hagander <mha@sollentuna.net> wrote:
> > > First off - very few third party tools support debian.  Debian is
> > a
> > > sure fire way to have an unsupported system.  Use RedHat or SuSe
> > > (flame me all you want, it doesn't make it less true).
> >
> > *cough* BS *cough*
> >
> > Linux is Linux. It doesn't matter what trademark you put on top of
> > it.
> > As long as they are running a current version of Linux (e.g; kernel
> > 2.6) they should be fine.
>
> Unfortunatly, that' not my experience either.
> Both RedHat and SuSE heavily modify the kernel. So anything that needs
> anything near kernel space (two examples: the HP management/monitoring
> tools and the EMC/Legato Networker backup software) simply does not work
> on Linux (linux being the kernel from kernel.org). They only work on
> RedHat/SuSE. To the point of not compiling/starting/working, not just
> the support part.
>
> (One could argue that they shouldn't claim linux support then, but
> specifically RH/SuSE, but I don't expect them to do that..)
>
> BTW, it used to work much better with 2.4, but since there is no real
> "stable series" kernel in 2.6, it's just a lost cause there it seems.