From: Thierry Missimilly <THIERRY.MISSIMILLY@BULL.NET>
To: Jack Coates <jack@lyris.com>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: tuning questions
Date: Fri, 05 Dec 2003 10:13:09 +0100
Message-ID: <3FD04C25.307690CC@BULL.NET> (raw)
References: <1070553983.6498.52.camel@cletus.lyris.com>
<200312041120.21124.josh@agliodbs.com>
<1070567455.13923.83.camel@cletus.lyris.com>
<200312042027.22223.dev@archonet.com>
<1070570264.13923.88.camel@cletus.lyris.com>
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
Attachments:THIERRY.MISSIMILLY.vcf (text/x-vcard, 326B) download
begin:vcard
n:Missimilly;Thierry
tel;fax:+33 (0)4 76 29 78 78
tel;work:+33 (0)4 76 29 74 54
x-mozilla-html:FALSE
url:http:\\www.bull.com
org:BIS/R&D
adr:;;Bull SA, 1, rue de provence - BP 208;ECHIROLLES;;38432;FRANCE
version:2.1
email;internet:Thierry.Missimilly@bull.net
x-mozilla-cpt:;-18184
fn:Thierry Missimilly
end:vcard
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: THIERRY.MISSIMILLY@BULL.NET, jack@lyris.com
Subject: Re: tuning questions
In-Reply-To: <3FD04C25.307690CC@BULL.NET>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox