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 7919ED1C4E3 for ; Thu, 7 Aug 2003 17:04:37 +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 01955-01 for ; Thu, 7 Aug 2003 14:03:12 -0300 (ADT) Received: from fw.wubn.net (unknown [213.156.64.226]) by svr1.postgresql.org (Postfix) with ESMTP id B7126D1C4EB for ; Thu, 7 Aug 2003 14:04:23 -0300 (ADT) Received: from (172.24.4.34) by fw.wubn.net (8.12.9/8.11.3) with ESMTP id h77H4GPt067030 for ; Thu, 7 Aug 2003 20:04:20 +0300 (EEST) (envelope-from yamazurak@Lviv.Bank.Gov.UA) Received: from (172.24.4.57) by MTA (8.11.5/8.11.2) with ESMTP id h77H4Ff18920 for ; Thu, 7 Aug 2003 20:04:15 +0300 Message-ID: <3F328683.7050008@lviv.bank.gov.ua> Date: Thu, 07 Aug 2003 20:04:03 +0300 From: Yaroslav Mazurak Organization: National Bank of Ukraine Lviv OU User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.4) Gecko/20030624 X-Accept-Language: uk, ru, en-us, en MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: PostgreSQL performance problem -> tuning References: In-Reply-To: Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/138 X-Sequence-Number: 2955 scott.marlowe wrote: > On Thu, 7 Aug 2003, Yaroslav Mazurak wrote: >>Shridhar Daithankar wrote: > That's a nice theory, but it doesn't work out that way. About every two > months someone shows up wanting postgresql to use all the memory in their > box for caching and we wind up explaining that the kernel is better at > caching than postgresql is, and how it's better not to push the usage of > the memory right up to the limit. I'm reading this mailing list just few days. :))) > The reason you don't want to use every bit for postgresql is that, if you > use add load after that you may make the machine start to swap out and > slow down considerably. What kind of load? PostgreSQL or another? I say that for this PC primary task and critical goal is DBMS and it's performance. > My guess is that this is exactly what's happening to you, you're using so > much memory that the machine is running out and slowing down. > Drop shared_buffers to 1000 to 4000, sort_mem to 8192 and start over from > there. Then, increase them each one at a time until there's no increase > in speed, or stop if it starts getting slower and back off. > bigger is NOT always better. Let I want to use all available RAM with PostgreSQL. Without executing query (PostgreSQL is running) top say now: Mem: 71M Active, 23M Inact, 72M Wired, 436K Cache, 48M Buf, 208M Free Swap: 368M Total, 2852K Used, 366M Free It's right that I can figure that I can use 384M (total RAM) - 72M (wired) - 48M (buf) = 264M for PostgreSQL. Hence, if I set effective_cache_size to 24M (3072 8K blocks), reasonable value (less than 240M, say 48M) for sort_mem, some value for shared_buffers (i.e. 24M, or 6144 4K blocks (FreeBSD), or 3072 8K blocks (PostgreSQL)), and rest of RAM 264M (total free with OS cache) - 24M (reserved for OS cache) - 48M (sort) - 24M (shared) = 168M PostgreSQL allocate dynamically by himself? With best regards Yaroslav Mazurak.