DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Received:X-Mailer:Date:From:Reply-To:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID;
	b=2NSg46oskoKYhlTWTLzhAWXvzdavyI1oEJndE+7HWebRBhAp3c+XCU7wxGp3+M3AM5fWO2/54VmcTVN0nH7/GeeVyGPGo6Z/oSs1u02C/GISPwXaB7pcxHTkWEFunY2OvLHtw3Kb7AVPCZmbhv4PeVtW8ykJpyIqu5NZ8D2MiOg=;
Date: Mon, 23 Jun 2008 22:48:53 -0700 (PDT)
From: bijayant kumar <bijayant4u@yahoo.com>
Reply-To: bijayant4u@yahoo.com
Subject: Re: Postgresql is very slow
To: tv@fuzzy.cz
Cc: pgsql-performance@postgresql.org
In-Reply-To: <20229.217.77.161.17.1214229014.squirrel@sq.gransy.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Message-ID: <569091.76971.qm@web32704.mail.mud.yahoo.com>

Hi,

Thanks for the reply. Many gentlemans have replied to my question, thanks t=
o all of them. I have tried to answer all questions in one mail.

--- On Mon, 23/6/08, tv@fuzzy.cz <tv@fuzzy.cz> wrote:

> From: tv@fuzzy.cz <tv@fuzzy.cz>
> Subject: Re: [PERFORM] Postgresql is very slow
> To: bijayant4u@yahoo.com
> Cc: pgsql-performance@postgresql.org
> Date: Monday, 23 June, 2008, 7:20 PM
> Hi,
>=20
> > Hello to list,
> >
> > We have a CentOS-5 server with postgresql-8.1.8
> installed. I am struggling
> > with postgresql performance. Any query say select *
> from tablename takes
> > 10-15 mins to give the output, and while executing the
> query system loads
> > goes up like anything. After the query output, system
> loads starts
> > decresing.
>=20
> I doubt the 'select * from tablename' is a good
> candidate for tuning, but
> give us more information about the table. What is it's
> size - how many
> rows does it have and how much space does it occupy on the
> disk? What is a
> typical usage of the table - is it modified (update /
> delete) frequently?
> How is it maintained - is there a autovacuum running, or
> did you set a
> routine vacuum (and analyze) job to maintain the database?
>=20
> I guess one of the servers (the slow one) is running for a
> long time
> without a proper db maintenance (vacuum / analyze) and you
> dumped / loaded
> the db onto a new server. So the 'new server' has
> much more 'compact'
> tables and thus gives the responses much faster. And this
> holds for more
> complicated queries (with indexes etc) too.
>=20
> An output from 'EXPLAIN' (or 'EXPLAIN
> ANALYZE') command would give a much
> better overview.
>=20

We maintains mail server, for this datas are stored in postgresql. There ar=
e total 24 tables but only two are used. Basically one table say USER store=
s the users information like mailid and his password, and there are 1669 ro=
ws in this table. The other table stores the domains name and no updation/d=
eletion/insertion happens very frequently. Once in a month this table is to=
uched.
But the second table USER is modified frequently(like on an average 10 time=
s daily) because users changes their password, new users are being added, o=
ld ones are deleted.

We have created this database with the dump of our old server, and with the=
 same dump the database is running fine on the new server but not on the sl=
ow server.

I was not aware of the VACUUM functionality earlier, but some times back i =
read and run this on the server but i did not achieve anything in terms of =
performance. The server is running from 1 to 1.5 years and we have done VAC=
UUM only once.

Is this the problem of slow database?  One more thing if i recreate the dat=
abase, will it help?

The output of ANALYZE

ANALYZE verbose USERS;
INFO:  analyzing "public.USERS"
INFO:  "USERS": scanned 3000 of 54063 pages, containing 128 live rows and 1=
 dead rows; 128 rows in sample, 2307 estimated total rows
ANALYZE

The output of EXPLAIN query;

select * from USERS where email like '%bijayant.kumar%';
This simplest query tooks 10 minutes and server loads goes from 0.35 to 16.=
94.

EXPLAIN select * from USERS where email like '%bijayant.kumar%';
                          QUERY PLAN
--------------------------------------------------------------
 Seq Scan on USERS  (cost=3D0.00..54091.84 rows=3D1 width=3D161)
   Filter: ((email)::text ~~ '%bijayant.kumar%'::text)
(2 rows)


I hope i have covered everything in my mail to troubleshoot my problem.

> Tomas
>=20
>=20
> --=20
> Sent via pgsql-performance mailing list
> (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance=0A=0ASend instant me=
ssages to your online friends http://uk.messenger.yahoo.com