SEO

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

On Tue, 2006-01-24 at 12:39, Arnau wrote:
> 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?

Well, this might get more traction on the perform list, just fyi. Admin
is more generally for questions about adding users, setting permissions
and such.

That said, it looks like you're likely I/O bound.

Do you have Hyperthreading turned on? generally this results in slower,
not faster performance, as the caches / registers in the pseudo CPUs are
often thrashed harder by having it turned on. I've found that having it
turned off generally gives better performance under heavy parallel load.

The most common changes to look at making in postgresql.conf are to
raise shared buffers. A setting of a 1000 to 10000 is pretty common.

You might do better with a lower random_page_cost, normally between 1.4
and 2.0 is good. Any lower than that and you're likely to see index
scans chosen when seq scans are really the better choice.

You should really upgrade your version to the latest 7.4 branch.

There are a LOT of performance enhancements in 8.0/8.1. If you can
upgrade to the latest 8.1 version that might help as well.

Turn on the logging of long queries, and run explain analyze on one or
more of those long running queries, and post the output here.

What do iostat and vmstat have to say?

Does your RAID 5 have the option for batter backed cache?

Is your load mostly read, or a mix or read and write?

Read through this document:

http://www.varlena.com/varlena/Gener...bits/perf.html

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

Scott Marlowe wrote:

> Well, this might get more traction on the perform list, just fyi. Admin
> is more generally for questions about adding users, setting permissions
> and such.

I know and I apologize for the cross-posting, I also sent the same
message to the performance list.

>
> That said, it looks like you're likely I/O bound.
>
> Do you have Hyperthreading turned on?

yes I have, when I do cat /proc/cpuinfo I get 4 CPUs

>
> What do iostat and vmstat have to say?

here I post the result of iostat 10 -x

asme@SD22-SINER5:/$ iostat 10 -x
Linux 2.4.26-1-686-smp (SD22-SINER5) 01/24/06

avg-cpu: %user %nice %sys %iowait %idle
16.03 0.00 94.98 0.00 144.54

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s
avgrq-sz avgqu-sz await svctm %util
scsi/host0/bus1/target0/lun0/disc
89.37 14.15 48.15 95.82 1.30 25.54 0.65 12.77
0.79 0.00 0.05 0.00 0.00
scsi/host0/bus1/target0/lun0/part1
0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
4.26 0.00 48.69 48.63 0.00
scsi/host0/bus1/target0/lun0/part2
0.01 0.25 0.02 0.22 0.22 3.77 0.11 1.89
17.12 0.00 6.29 1.80 0.04
scsi/host0/bus1/target0/lun0/part5
0.02 0.02 0.01 0.00 0.21 0.20 0.10 0.10
33.85 0.00 58.89 38.02 0.05
scsi/host0/bus1/target0/lun0/part6
0.05 0.35 0.10 0.47 1.18 6.55 0.59 3.27
13.58 0.00 2.70 1.64 0.09
scsi/host0/bus1/target0/lun0/part7
0.06 0.60 0.01 0.03 0.53 5.10 0.27 2.55
137.26 0.00 38.02 22.68 0.09
scsi/host0/bus1/target0/lun0/part8
0.14 0.57 0.01 0.03 1.18 4.79 0.59 2.40
155.70 0.00 12.07 14.63 0.06
scsi/host0/bus1/target0/lun0/part9
89.09 12.35 48.00 95.07 107.78 5.13 53.89 2.56
0.09 0.00 0.04 0.02 0.05

avg-cpu: %user %nice %sys %iowait %idle
27.35 0.00 16.25 0.00 56.40

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s
avgrq-sz avgqu-sz await svctm %util
scsi/host0/bus1/target0/lun0/disc
420.60 2070.00 87.30 152.10 4060.00 17830.40 2030.00
8915.20 91.44 14.06 57.70 4.16 99.60
scsi/host0/bus1/target0/lun0/part1
0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00 0.00
scsi/host0/bus1/target0/lun0/part2
0.00 0.70 0.00 1.40 0.00 16.80 0.00 8.40
12.00 0.07 52.14 5.00 0.70
scsi/host0/bus1/target0/lun0/part5
0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00 0.00
scsi/host0/bus1/target0/lun0/part6
0.00 0.40 0.00 0.40 0.00 6.40 0.00 3.20
16.00 0.02 40.00 40.00 1.60
scsi/host0/bus1/target0/lun0/part7
0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00 0.00
scsi/host0/bus1/target0/lun0/part8
0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00 0.00
scsi/host0/bus1/target0/lun0/part9
420.60 2068.90 87.30 150.30 4060.00 17807.20 2030.00
8903.60 92.03 13.97 57.77 4.19 99.60

avg-cpu: %user %nice %sys %iowait %idle
31.38 0.00 15.55 0.00 53.08

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s
avgrq-sz avgqu-sz await svctm %util
scsi/host0/bus1/target0/lun0/disc
388.80 2266.70 219.20 182.00 4876.80 19571.20 2438.40
9785.60 60.94 21.54 1059879.18 2.49 99.80
scsi/host0/bus1/target0/lun0/part1
0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00 0.00
scsi/host0/bus1/target0/lun0/part2
0.00 0.30 0.00 0.40 0.00 5.60 0.00 2.80
14.00 0.01 17.50 17.50 0.70
scsi/host0/bus1/target0/lun0/part5
0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00 0.00
scsi/host0/bus1/target0/lun0/part6
0.00 0.20 0.00 0.40 0.00 4.80 0.00 2.40
12.00 0.01 22.50 22.50 0.90
scsi/host0/bus1/target0/lun0/part7
0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00 0.00
scsi/host0/bus1/target0/lun0/part8
0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00 0.00
scsi/host0/bus1/target0/lun0/part9
388.80 2266.20 219.20 181.20 4876.80 19560.80 2438.40
9780.40 61.03 21.52 54.32 2.49 99.80


> Does your RAID 5 have the option for batter backed cache?

I don't know how to do that, I didn't configure this machine.

> Is your load mostly read, or a mix or read and write?

On this machine there are about 200 DDBB running on it, the activity
on them is a mix of read/write operations some depending on the tables
of each DB. The size of the DB varies a lot, every day we do a pg_dump
with a compressed format. The biggest one takes 746M, 5 are between 46M
and 13M. 40 between 10M - 1M and the rest their dump takes less than 1M.

Cheers!
--
Arnau

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings