Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 654962E3374
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Thu, 10 Jan 2008 10:21:42 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 53139-01 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Thu, 10 Jan 2008 10:21:38 -0400 (AST)
Received: from puck.nether.net (puck.nether.net [204.42.254.5])
	by postgresql.org (Postfix) with ESMTP id 1D47F2E335F
	for <pgsql-performance@postgresql.org>;
	Thu, 10 Jan 2008 10:21:37 -0400 (AST)
Received: from puck.nether.net (puck.nether.net [204.42.254.5])
	by puck.nether.net (8.14.1/8.12.9) with ESMTP id m0AELNRv003375
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO);
	Thu, 10 Jan 2008 09:21:23 -0500 (EST)
	(envelope-from jared@puck.nether.net)
Received: (from jared@localhost)
	by puck.nether.net (8.14.1/8.12.9/Submit) id m0AELMdq003374;
	Thu, 10 Jan 2008 09:21:22 -0500 (EST) (envelope-from jared)
Date: Thu, 10 Jan 2008 09:21:22 -0500
From: Jared Mauch <jared@puck.nether.net>
To: Adrian Moisey <adrian@careerjunction.co.za>
Cc: Jared Mauch <jared@puck.nether.net>, pgsql-performance@postgresql.org
Subject: Re: big database performance
Message-ID: <20080110142122.GA79941@puck.nether.net>
References: <4784835A.8070604@careerjunction.co.za>
	<47848575.8000007@commandprompt.com>
	<20080109174243.GA8915@puck.nether.net>
	<4785DE0A.6020501@careerjunction.co.za>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <4785DE0A.6020501@careerjunction.co.za>
User-Agent: Mutt/1.5.17 (2007-11-01)
X-Greylist: Sender is SPF-compliant, not delayed by milter-greylist-4.0
	(puck.nether.net [204.42.254.5]);
	Thu, 10 Jan 2008 09:21:23 -0500 (EST)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200801/54
X-Sequence-Number: 28293

On Thu, Jan 10, 2008 at 10:57:46AM +0200, Adrian Moisey wrote:
> What sort of information do you need from me ?

	Ratio of read vs write operations (select vs insert/copy).

	average number of indicies per table

	average table size.  (analyze verbose <tablename> if you want to get
into more details).

	What is the process doing (eg: in top, is it just on the CPU or
is it blocking for I/O?).

	I/O information, from iostat -d (You may need to build an iostat
binary for Linux, the source is out there, i can give you a pointer if
you need it).

>> 	Is your problem with performance database reads? writes? (insert/copy?)
>> How many indicies do you have?
>
> I think the problem is related to load.  Everything is slow because there 
> are way too many connections.  So everything is making everything else 
> slow.  Not much detail, is it?
>
> We have 345 indicies on the db.

	If the tables are heavily indexed this could easily slow down
insert performance.  Taking a large dataset and adding a second
index, postgres doesn't use threads to create the two indicies on
different cpus/cores in parallel.  This could represent some of your
performance difference.  If you're doing a lot of write operations
and fewer read, perhaps the cost of an index isn't worth it in the
cpu time spent creating it vs the amount of time for a seq scan.

	- Jared

-- 
Jared Mauch  | pgp key available via finger from jared@puck.nether.net
clue++;      | http://puck.nether.net/~jared/  My statements are only mine.