Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id EA054632436
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Mon, 19 Jan 2009 09:44:54 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 73555-05 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Mon, 19 Jan 2009 09:44:50 -0400 (AST)
X-Greylist: delayed 00:32:55.751512 by SQLgrey-1.7.6
Received: from mail.rp-online.de (mail.rp-online.de [149.221.1.199])
	by mail.postgresql.org (Postfix) with ESMTP id 01ACB632403
	for <pgsql-admin@postgresql.org>; Mon, 19 Jan 2009 09:44:48 -0400 (AST)
Received: from idefix.d.rp-online.de (idefix.d.rp-online.de [149.221.100.194])
	by mail.rp-online.de (Postfix) with ESMTP id 8535F311E4;
	Mon, 19 Jan 2009 14:11:50 +0100 (CET)
From: Thomas Pundt <mlists@rp-online.de>
Reply-To: Thomas Pundt <thomas.pundt@rp-online.de>
Organization: RP Online GmbH
To: pgsql-admin@postgresql.org
Subject: Re: tuning tips, speed problem
Date: Mon, 19 Jan 2009 14:12:06 +0100
User-Agent: KMail/1.9.10
Cc: BRAHMA PRAKASH TIWARI <prakashr2n5@gmail.com>,
	Francesco Andreozzi <francesco.andreozzi@gamestorm.it>
References: <20090116161802.v6tj5jmhw0c80sow@webmail.gamestorm.it>
	<2acc2c820901190435m4062f8d3v1e6099f32ed009af@mail.gmail.com>
In-Reply-To: <2acc2c820901190435m4062f8d3v1e6099f32ed009af@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-15"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200901191412.07048.mlists@rp-online.de>
X-Anti-Virus: Kaspersky Anti-Virus for MailServers 5.5.10/RELEASE,
	bases: 19012009 #1478659, status: clean
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200901/60
X-Sequence-Number: 31107

On Montag, 19. Januar 2009, BRAHMA PRAKASH TIWARI wrote:
| most probabely this is due to the auto vacuum option on and if auto vacuum
| is on then the in condition of high transaction on database it slows the
| speed of the hole database.Set it off in postgres.conf and vacuum and
| reindex transactional tables manualy with in  every two million
| transactions.
| like
| vacuum full <table name>;
| reindex table <table name>;

Please, a "vacuum full" should only be a measure of last resort ever.
Regular vacuum (without the "full" keyword) should always be enough. 
And I don't recommend turning autovacuum off. 

You might need to increase the "max_fsm_pages" setting to avoid 
database bloat. The output of a database wide "vacuum verbose" should
tell you, if it is set appropriately.

And Francesco, if you say 

  "but on a mysql to execute a simple select with an order by the time was jus
  1 or 2 seconds ... on this installation the time it's about 10 seconds or
  more", 

could you provide the "explain analyze"-output for those queries? This should
help analyzing your problems.

Ciao,
Thomas

-- 
Thomas Pundt <thomas.pundt@rp-online.de> ---- http://rp-online.de/ ----