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 95EEA9DCC42 for ; Sun, 4 Dec 2005 05:33:50 -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 62897-03 for ; Sun, 4 Dec 2005 05:33:47 -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 DB6E29DCAD0 for ; Sun, 4 Dec 2005 05:33:47 -0400 (AST) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 4F37831F51; Sun, 4 Dec 2005 10:33:49 +0100 (MET) From: Michael Riess X-Newsgroups: pgsql.performance Subject: Re: 15,000 tables - next step Date: Sun, 04 Dec 2005 10:33:47 +0100 Organization: Hub.Org Networking Services Lines: 57 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: Thunderbird 1.5 (Windows/20051025) 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/113 X-Sequence-Number: 15934 William Yu schrieb: > 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. content2=# select sum(relpages) from pg_class where relname like 'pg_%'; sum ------- 64088 (1 row) :-) > 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. The problem is that we use pre-built hardware which isn't configurable. We can only switch to a bigger server with 2GB, but that's tops. I will do the following: - switch to 10k buffers on a 1GB machine, 20k buffers on a 2GB machine - try to optimize my connection polls to remember which apps (groups of 30 tables) were accessed, so that there is a better chance of using caches - "swap out" tables which are rarely used: export the content, drop the table, and re-create it on the fly upon access. Thanks for your comments!