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

---------------------------(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

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.

On 8/7/06, Alvaro Nunes Melo <al_nunes@atua.com.br> wrote:
>
> 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...7/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
>
> ---------------------------(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
>

* 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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)

iD8DBQFE2H4JrzgMPqB3kigRAhmfAJ96HuRV4jk/Xlwv50fH/vPyPvXyxQCfb39M
cOhoPjFNMbzLu5c45Zm7xKw=
=Bdsp
-----END PGP SIGNATURE-----

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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)

iD8DBQFE2IArrzgMPqB3kigRAlLGAJ4ySSrMSHf9B+LKcuXnki v/QAWsDQCfddW9
r8t4/iE3AoaoQGfY1UGXPLk=
=BC8o
-----END PGP SIGNATURE-----

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


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

http://archives.postgresql.org

* 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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)

iD8DBQFE2bk0rzgMPqB3kigRAtKNAJ9Lf6tIlP4YxNlZykvBdp S+az/4wACeJKZW
KOTd91al0yeQqzXvp6vs7K8=
=Pe+f
-----END PGP SIGNATURE-----

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/



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

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



---------------------------(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

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

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

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster