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 29F7AD1B478 for ; Thu, 4 Dec 2003 20:38:53 +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 17654-01 for ; Thu, 4 Dec 2003 16:38:23 -0400 (AST) Received: from luna.lyris.com (luna.lyris.com [216.91.57.116]) by svr1.postgresql.org (Postfix) with ESMTP id B2D08D1B44C for ; Thu, 4 Dec 2003 16:38:20 -0400 (AST) Received: from luna.lyris.com (luna.lyris.com [216.91.57.116]) by luna.lyris.com (8.12.8/8.12.8) with ESMTP id hB4KblIt007660; Thu, 4 Dec 2003 12:37:47 -0800 Received: from luna.lyris.com ([216.91.57.116]) by luna.lyris.com (MailMonitor for SMTP v1.2.2 ) ; Thu, 4 Dec 2003 12:37:47 -0800 (PST) Received: from cletus.lyris.com (cletus.lyris.com [216.91.56.155]) by luna.lyris.net with SMTP (MailShield v2.04 - LINUX Jul 17 2001 16:58:31); Thu, 04 Dec 2003 12:37:47 -0800 Subject: Re: tuning questions From: Jack Coates To: Richard Huxton Cc: josh@agliodbs.com, pgsql-performance In-Reply-To: <200312042027.22223.dev@archonet.com> 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> Content-Type: text/plain Organization: Lyris Technologies, Inc. Message-Id: <1070570264.13923.88.camel@cletus.lyris.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.4-9mdk Date: Thu, 04 Dec 2003 12:37:45 -0800 Content-Transfer-Encoding: 7bit X-SMTP-HELO: cletus.lyris.com X-SMTP-MAIL-FROM: jack@lyris.com X-SMTP-RCPT-TO: dev@archonet.com, josh@agliodbs.com, pgsql-performance@postgresql.org X-SMTP-PEER-INFO: cletus.lyris.com [216.91.56.155] X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/93 X-Sequence-Number: 4953 On Thu, 2003-12-04 at 12:27, Richard Huxton wrote: > On Thursday 04 December 2003 19:50, Jack Coates wrote: > > > > I'm trying to set Postgres's shared memory usage in a fashion that > > allows it to return requested results quickly. Unfortunately, none of > > these changes allow PG to use more than a little under 300M RAM. > > vacuumdb --analyze is now taking an inordinate amount of time as well > > (40 minutes and counting), so that change needs to be rolled back. > > You don't want PG to use all your RAM, it's designed to let the underlying OS > do a lot of caching for it. Probably worth having a look at vmstat/iostat and > see if it's saturating on I/O. 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 -- Jack Coates, Lyris Technologies Applications Engineer 510-549-4350 x148, jack@lyris.com "Interoperability is the keyword, uniformity is a dead end." --Olivier Fourdan