Re: Where is my bottleneck? - Mailing list pgsql-admin

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