Re: tuning questions

Jack Coates wrote:

>
> latest changes:
> shared_buffers = 35642
> max_fsm_relations = 1000
> max_fsm_pages = 10000
> wal_buffers = 64
> sort_mem = 32768
> vacuum_mem = 32768
> effective_cache_size = 10000
>
> /proc/sys/kernel/shmmax = 500000000
>
> IO is active, but hardly saturated. CPU load is hefty though, load
> average is at 4 now.
>
> procs memory swap io
> system cpu
> r b w swpd free buff cache si so bi bo in cs us
> sy id
> 0 2 1 2808 11436 39616 1902988 0 0 240 896 765 469
> 2 11 87
> 0 2 1 2808 11432 39616 1902988 0 0 244 848 768 540
> 4 3 93
> 0 2 1 2808 11432 39616 1902984 0 0 204 876 788 507
> 3 4 93
> 0 2 1 2808 11432 39616 1902984 0 0 360 416 715 495
> 4 1 96
> 0 2 1 2808 11432 39616 1902984 0 0 376 328 689 441
> 2 1 97
> 0 2 0 2808 11428 39616 1902976 0 0 464 360 705 479
> 2 1 97
> 0 2 1 2808 11428 39616 1902976 0 0 432 380 718 547
> 3 1 97
> 0 2 1 2808 11428 39616 1902972 0 0 440 372 742 512
> 1 3 96
> 0 2 1 2808 11428 39616 1902972 0 0 416 364 711 504
> 3 1 96
> 0 2 1 2808 11424 39616 1902972 0 0 456 492 743 592
> 2 1 97
> 0 2 1 2808 11424 39616 1902972 0 0 440 352 707 494
> 2 1 97
> 0 2 1 2808 11424 39616 1902972 0 0 456 360 709 494
> 2 2 97
> 0 2 1 2808 11436 39616 1902968 0 0 536 516 807 708
> 3 2 94
>

Hi Jack,

As show by vmstat, your Operating System is spending 96% of its time in Idle. On
RedHat 8.0 IA32, Idle means idle and Wait I/O.
In your case, i think they are Wait I/O as you are working on 2.8 GB DB with only
2GB RAM, but it should be arround 30%.
Your performances whould increase only if User CPU increase otherwise, for exemple
if your system swap, only Sys CPU whould increase and your application will stay
slow.

You can better check your I/O with : iostat 3 1000, and check that the max tps are
on the database filesystem.

So, all the Postgres tuning you have tried do not change a lot as the bottleneck is
your I/O throuput.
But, one thing you can check is which parts of Postgres need a lot of I/O.
To do that, after shuting down PG, move your database on an other disk (OS disk ?)
for exemple /mypg/data and create a symblolic link for /mypg/data/<mydb> to
$PGDATA/base.

Restart PG, and while you execute your application, check with iostat which disk as
the max of tps. I bet, it is the disk where the WAL buffer are logged.

One more thing about I/O, for an IDE disk, the maximum number of Write Block + Read
Block per sec is about 10000 based on the I/O block size is 1 K. That means 10
Mb/s. if you need more, you can try Stripped SCSI disks or RAID0 subsystem disks.

Thierry Missimilly

>
> --
> Jack Coates, Lyris Technologies Applications Engineer
> 510-549-4350 x148, jack(at)lyris(dot)com
> "Interoperability is the keyword, uniformity is a dead end."
> --Olivier Fourdan
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly