X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id E32D0D1B475 for ; Fri, 5 Dec 2003 09:13:30 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 10446-07 for ; Fri, 5 Dec 2003 05:13:01 -0400 (AST) Received: from ecbull20.frec.bull.fr (ecbull20.frec.bull.fr [129.183.4.3]) by svr1.postgresql.org (Postfix) with ESMTP id 0EB03D1B47A for ; Fri, 5 Dec 2003 05:12:57 -0400 (AST) Received: from ecn002.frec.bull.fr (ecn002.frec.bull.fr [129.183.4.6]) by ecbull20.frec.bull.fr (8.9.2/8.9.1) with ESMTP id KAA16934; Fri, 5 Dec 2003 10:13:05 +0100 Received: from BULL.NET ([129.183.148.134]) by ecn002.frec.bull.fr (Lotus Domino Release 5.0.12) with ESMTP id 2003120510163396:112 ; Fri, 5 Dec 2003 10:16:33 +0100 Message-ID: <3FD04C25.307690CC@BULL.NET> Date: Fri, 05 Dec 2003 10:13:09 +0100 From: Thierry Missimilly Organization: BSIS/R&D X-Mailer: Mozilla 4.75 [en] (Windows NT 5.0; U) X-Accept-Language: fr,en MIME-Version: 1.0 To: Jack Coates Cc: pgsql-performance Subject: Re: tuning questions 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> X-MIMETrack: Itemize by SMTP Server on ECN002/FR/BULL(Release 5.0.12 |February 13, 2003) at 05/12/2003 10:16:34, Serialize by Router on ECN002/FR/BULL(Release 5.0.12 |February 13, 2003) at 05/12/2003 10:16:40, Serialize complete at 05/12/2003 10:16:40 Content-Type: multipart/mixed; boundary="------------A086F02D18D306A929300A18" X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/119 X-Sequence-Number: 4979 This is a multi-part message in MIME format. --------------A086F02D18D306A929300A18 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit 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/ 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 --------------A086F02D18D306A929300A18 Content-Type: text/x-vcard; charset=us-ascii; name="THIERRY.MISSIMILLY.vcf" Content-Description: Card for Thierry Missimilly Content-Disposition: attachment; filename="THIERRY.MISSIMILLY.vcf" Content-Transfer-Encoding: 7bit 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 --------------A086F02D18D306A929300A18--