Re: tuning questions - Mailing list pgsql-performance

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@lyris.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@postgresql.org so that your
>       message can get through to the mailing list cleanly