X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 20D8ED1B4F4 for ; Tue, 7 Oct 2003 15:29:23 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 36073-07 for ; Tue, 7 Oct 2003 12:28:34 -0300 (ADT) Received: from ucsns.ucs.co.za (ucs.co.za [196.23.43.2]) by svr1.postgresql.org (Postfix) with ESMTP id 4A6DBD1B500 for ; Tue, 7 Oct 2003 12:28:33 -0300 (ADT) Received: from ucspost.ucs.co.za (mailgw1.ucs.co.za [196.23.43.253]) by ucsns.ucs.co.za (Postfix) with ESMTP id AEDD42BD95 for ; Tue, 7 Oct 2003 17:28:28 +0200 (SAST) Received: from jhb.ucs.co.za (jhb.ucs.co.za [172.31.1.3]) by ucspost.ucs.co.za (Postfix) with ESMTP id B29ECD9F17 for ; Tue, 7 Oct 2003 17:28:28 +0200 (SAST) Received: from svb.ucs.co.za (svb.ucs.co.za [172.31.1.148]) by jhb.ucs.co.za (Postfix) with SMTP id 021CC976E3 for ; Tue, 7 Oct 2003 17:28:28 +0200 (SAST) Date: Tue, 7 Oct 2003 17:28:22 +0200 From: Stef To: pg-perform Subject: Re: Postgres low end processing. Message-Id: <20031007172822.59df027d.svb@ucs.co.za> In-Reply-To: <20031006095551.1d7bfeb8.svb@ucs.co.za> References: <20031003163040.3641f39a.svb@ucs.co.za> <200310031108.49002.josh@agliodbs.com> <20031006095551.1d7bfeb8.svb@ucs.co.za> X-Mailer: Sylpheed version 0.9.5claws28 (GTK+ 1.2.10; i386-pc-linux-gnu) User-Agent: sillypheed-claws (anti-aliased) X-Face: LV^-*d3kQlL,6{Y:X)M~tuL#y!p.kO_Qbc!xxqhZC4s}Y]L0g)z^aiLsQGY34d|}Xp:HNm)MRsc?^?ZQ}smznF&jx|w@,a**K&QL.Dc~2~M5V`zb>hExCJDB[[o=M)e"; k-n[7tz2TY7+; *_xX's(5cUEtdw*yG-OmKm}6($zpUz8S`Nz@w X-Operating-System: sid X-X-X: _-^-_ Mime-Version: 1.0 Content-Type: multipart/signed; protocol="application/pgp-signature"; micalg="pgp-sha1"; boundary="Signature=_Tue__7_Oct_2003_17_28_22_+0200_g7OKzaPf8NELWA29" X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/192 X-Sequence-Number: 3940 --Signature=_Tue__7_Oct_2003_17_28_22_+0200_g7OKzaPf8NELWA29 Content-Type: text/plain; charset=US-ASCII Content-Disposition: inline Content-Transfer-Encoding: 7bit Hi again all, I've tested postgres 7.3.4 on Linux version 2.4.17 and this is what I found : The initial instance took up 8372K and this fluctuated between +- 8372K and 10372K, plus +- 3500K for every connection. I did quite a few transactions on both connections, plus a few vacuums and a pg_dump and the total memory usage didn't seem to go over 16M I set all the _buffers, _mem, _fsm settings to the minimum, restarted every time, but this had absolutely no noticeable increase or decrease in total memory usage. (I used a program called gmemusage to get these stats.) On the same machine , I tested postgres 7.1.2 with basically the same conf options (not _fsm) and got the following : The initial instance was 1772K and fluctuated to +- 4000K, plus +- 3400K for every connection. Doing the same transactions, vacuum + pg_dump, total memory usage didn't really go over 11M, which was exactly what I needed. Although I've lived through some of the shortcomings of 7.1.2, it is still very stable, and works perfectly for what it is going to be used for. Again, here, I was only able to restrict things a little by changing the configuration options, but no major difference in memory usage. Regards Stef On Mon, 6 Oct 2003 09:55:51 +0200 Stef wrote: => Thanks for the replies, => => On Fri, 3 Oct 2003 11:08:48 -0700 => Josh Berkus wrote: => => 1. Make sure that the WAL files (pg_xlog) are on a seperate disk from the => => database files, either through mounting or symlinking. => => I'm not sure I understand how this helps? => => => 2. Tweak the .conf file for low vacuum_mem (1024?), but vacuum very => => frequently, like every 1-5 minutes. Spend some time tuning your => => fsm_max_pages to the ideal level so that you're not allocating any extra => => memory to the FSM. => => => => 3. If your concern is *average* CPU/RAM consumption, and not peak load => => activity, increase wal_files and checkpoint_segments to do more efficient => => batch processing of pending updates as the cost of some disk space. If peak => => load activity is a problem, don't do this. => => => => 4. Tune all of your queries carefully to avoid anything requiring a => => RAM-intensive merge join or CPU-eating calculated expression hash join, or => => similar computation-or-RAM-intensive operations. => => Thanks, I'll try some of these, and post the results. => The actual machines seem to be Pentium I machines, => with 32M RAM. I've gathered that it is theoretically => possible, so no to go try it. => => Regards => Stef => --Signature=_Tue__7_Oct_2003_17_28_22_+0200_g7OKzaPf8NELWA29 Content-Type: application/pgp-signature -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQE/gtubgDYs3UkRjZkRAhfEAJ4vWSOLjAc/n3sjeyfNfJJ17aYVjACeMMI1 V0y7yzdoFIJb85J9dv+ADPA= =SSHs -----END PGP SIGNATURE----- --Signature=_Tue__7_Oct_2003_17_28_22_+0200_g7OKzaPf8NELWA29--