DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=Yahoo.com;
	h=Received:Received:Message-ID:Date:From:User-Agent:X-Accept-Language:MIME-Version:To:CC:Subject:References:In-Reply-To:Content-Type:Content-Transfer-Encoding;
	b=DmxEjc9XWdlSFHSy1ZtY10huMb3c6ymyvxKar9Ru25imkuN1Tt6MI3K8ht5LOsnWQuCPB2QSKflnlXk+seVtoonKshemky4sce1yqNKFqGMkgttWa64WTl2rLqGqxmZbdib2u2fvteh60nEWSPcX6Nj8yXkX6SERrC5avtuA/44=
	;
Message-ID: <43950068.3040402@Yahoo.com>
Date: Mon, 05 Dec 2005 22:07:20 -0500
From: Jan Wieck <JanWieck@Yahoo.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
	rv:1.7.1) Gecko/20040707
MIME-Version: 1.0
To: Michael Riess <mlriess@gmx.de>
CC: pgsql-performance@postgresql.org
Subject: Re: 15,000 tables - next step
References: <dmqjou$2a10$1@news.hub.org> <4391BF0F.9000208@Yahoo.com>
	<dmsgke$24so$1@news.hub.org> <20051203162642.GA29022@surnet.cl>
	<dmshs4$2blb$1@news.hub.org> <dmucf7$20ff$1@news.hub.org>
	<dmud5m$245l$1@news.hub.org>
In-Reply-To: <dmud5m$245l$1@news.hub.org>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

On 12/4/2005 4:33 AM, Michael Riess wrote:
> 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.

I hacked pgbench a little and did some tests (finally had to figure out 
for myself if there is much of an impact with hundreds or thousands of 
tables).

The changes done to pgbench:

     - Use the [-s n] value allways, instead of determining the
       scaling from the DB.

     - Lower the number of accounts per scaling factor to 10,000.

     - Add another scaling type. Option [-a n] splits up the test
       into n schemas, each containing [-s n] branches.

The tests were performed on a 667 MHz P3, 640MB Ram with a single IDE 
disk. All tests were IO bound. In all tests the number of clients was 5 
default transaction and 50 readonly (option -S). The FreeBSD kernel of 
the system is configured to handle up to 50,000 open files, fully cache 
directories in virtual memory and to lock all shared memory into 
physical ram.

The different scalings used were

     init -a1 -s3000
     run  -a1 -s300

and

     init -a3000 -s1
     run  -a300 -s1

The latter creates a database of 12,000 tables with 1,200 of them 
actually in use during the test. Both databases are about 4 GB in size.

The performance loss for going from -s3000 to -a3000 is about 10-15%.

The performance gain for going from 1,000 shared_buffers to 48,000 is 
roughly 70% (-a3000 test case) and 100% (-s3000 test case).

Conclusion: The right shared memory configuration easily outperforms the 
loss from increase in number of tables, given that the kernel is 
configured to be up to the task of dealing with thousands of files 
accessed by that number of backends too.


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #