X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 5D5F39DCD24 for ; Sun, 4 Dec 2005 05:21:52 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 61973-03 for ; Sun, 4 Dec 2005 05:21:48 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from floppy.pyrenet.fr (news.pyrenet.fr [194.116.145.2]) by postgresql.org (Postfix) with ESMTP id 6A3D59DCD4D for ; Sun, 4 Dec 2005 05:21:49 -0400 (AST) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 5BCC231F51; Sun, 4 Dec 2005 10:21:50 +0100 (MET) From: William Yu X-Newsgroups: pgsql.performance Subject: Re: 15,000 tables - next step Date: Sun, 04 Dec 2005 01:21:42 -0800 Organization: Hub.Org Networking Services Lines: 46 Message-ID: References: <4391BF0F.9000208@Yahoo.com> <20051203162642.GA29022@surnet.cl> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@news.hub.org User-Agent: Mozilla Thunderbird 1.0 (X11/20041206) X-Accept-Language: en-us, en In-Reply-To: To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200512/112 X-Sequence-Number: 15933 Michael Riess wrote: >> Well, I'd think that's were your problem is. Not only you have a >> (relatively speaking) small server -- you also share it with other >> very-memory-hungry services! That's not a situation I'd like to be in. >> Try putting Apache and Tomcat elsewhere, and leave the bulk of the 1GB >> to Postgres. > > > No can do. I can try to switch to a 2GB machine, but I will not use > several machines. Not for a 5GB database. ;-) > >> With 1500 shared buffers you are not really going >> anywhere -- you should have ten times that at the very least. >> > > Like I said - I tried to double the buffers and the performance did not > improve in the least. And I also tried this on a 2GB machine, and > swapping was not a problem. If I used 10x more buffers, I would in > essence remove the OS buffers. Increasing buffers do improve performance -- if you have enough memory. You just don't have enough memory to play with. My servers run w/ 10K buffers (128MB on 64-bit FC4) and it definitely runs better w/ it at 10K versus 1500. With that many tables, your system catalogs are probably huge. To keep your system catalog from continually cycling in-out of buffers/OS cache/disk, you need a lot more memory. Ordinarily, I'd say the 500MB you have available for Postgres to cache 5GB is a workable ratio. My servers all have similar ratios of ~1:10 and they perform pretty good -- *except* when the system catalogs bloated due to lack of vacuuming on system tables. My app regularly creates & drops thousands of temporary tables leaving a lot of dead rows in the system catalogs. (Nearly the same situation as you -- instead of 15K live tables, I had 200 live tables and tens of thousands of dead table records.) Even with almost 8GB of RAM dedicated to postgres, performance on every single query -- not matter how small the table was -- took forever because the query planner had to spend a significant period of time scanning through my huge system catalogs to build the execution plan. While my situtation was fixable by scheduling a nightly vacuum/analyze on the system catalogs to get rid of the bazillion dead table/index info, you have no choice but to get more memory so you can stuff your entire system catalog into buffers/os cache. Personally, w/ 1GB of ECC RAM at ~$85, it's a no brainer. Get as much memory as your server can support.