pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: John Jensen <JRJ@ft.fo>
To: arnaulist@andromeiberica.com
To: pgsql-admin@postgresql.org
Subject: Re: Where is my bottleneck?
Date: Wed, 25 Jan 2006 09:58:09 +0000
Message-ID: <s3d74bce.018@fs3.ft.fo> (raw)

Hi Arnau,
Poor performance on idle cpu is normally due to an I/O bottleneck.
The bottleneck can be either network (unlikely but easy to check) or
disk i/o. Excessive disk i/o can be caused by memory starvation or
maybe you just need to move a lot of data. Adding memory will
give you more cache space and in "some cases" reduce physical i/o
and thereby improve performance. This depends on how the data
is accessed.

These comments are pretty general and goes for any application.

Your post is a bit slim on information. So here are some questions:
- Is all the memory used by postgres ?
- Do you run any other applications on the machine ?
(if other apps use all the memory then move them to an other box to
free up memory)
- Run vmstat 1   and post the first 50 lines of data
- How large is your database (disk usage under postgres-x.x.x/data/base
)
- Do you have indices on all fields you query on ?
(if not then you force full-table scan's which cause excessive i/o
- Make sure the datatypes in your queries match those in the indices ?
(if not then the indices are not used and you force full-table scans)
- Did you install from source or an rpm ?
(the default source config is set up to use far to little memory for
buffer cache)

Apart from that I would suggest turning on the statistics collection.
That tells you:
- How many times each table is hit
- Number of full table vs. index scans for each table
(that tells A LOT about your indices)
- Number of blocks read for each table

Armed with that information you can hunt down the expensive queries
and optimize them. Use EXPLAIN PLAN a lot here.

For optimizing queries I can recommend this book (
http://www.singingsql.com/ ).


You could always throw money at the problem:
- more memory
- Hotter disks (Raid 10)
- Split the base into multiple tablespaces (postgresql 8.0 or higher)
on multiple 
disk systems and controllers.


Cheers,

John

>>> Arnau <arnaulist@andromeiberica.com> 01/24/06 6:39 pm >>>
Hi all,

   I have a performance problem and I don't know where is my
bottleneck.
I have postgresql 7.4.2 running on a debian server with kernel 
2.4.26-1-686-smp with two Xeon(TM) at 2.80GHz and 4GB of RAM and a RAID

5 made with SCSI disks. Maybe its not the latest hardware but I think 
it's not that bad.

   My problem is that the general performance is not good enough and I

don't know where is the bottleneck. It could be because the queries are

not optimized as they should be, but I also think it can be a
postgresql 
configuration problem or hardware problem (HDs not beeing fast enough,

not enough RAM, ... )

   The configuration of postgresql is the default, I tried to tune the

postgresql.conf and the results where disappointing, so I left again
the 
default values.

When I do top I get:
top - 19:10:24 up 452 days, 15:48,  4 users,  load average: 6.31, 6.27,
6.52
Tasks:  91 total,   8 running,  83 sleeping,   0 stopped,   0 zombie
Cpu(s):  24.8% user,  15.4% system,   0.0% nice,  59.9% idle
Mem:   3748956k total,  3629252k used,   119704k free,    57604k
buffers
Swap:  2097136k total,    14188k used,  2082948k free,  3303620k
cached

   Most of the time the idle value is even higher than 60%.

I know it's a  problem with a very big scope, but could you give me a 
hint about where I should look to?


Thank you very much
-- 
Arnau

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

               http://archives.postgresql.org 




view thread (11+ messages)  latest in thread

Message-ID: <s3d74bce.018@fs3.ft.fo>
Permalink:  ../s3d74bce.018@fs3.ft.fo/
Also on:    postgresql.org/message-id/s3d74bce.018@fs3.ft.fo

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: JRJ@ft.fo, arnaulist@andromeiberica.com
  Subject: Re: Where is my bottleneck?
  In-Reply-To: <s3d74bce.018@fs3.ft.fo>
  References: <s3d74bce.018@fs3.ft.fo>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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