Re: Where is my bottleneck?

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(at)andromeiberica(dot)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