DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Received:X-Mailer:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID;
	b=ErgQ8b/3VLUrbQGXQ1lmqNbNV3xORK+j+hmEhfib77NKjyoEpp/NlvY4VjrN7UnYK0v6iUlTRd5Sn2zz8sEwamOua6bWpd4KsCq0SqyaXk5qRLltRShql4DF9C+wgh+qBwwVt+UtLqjZGjZxSnLi4PitVTGByJeU03UV1d2BaEc=;
Date: Tue, 24 Jun 2008 02:17:34 -0700 (PDT)
From: bijayant kumar <bijayant4u@yahoo.com>
Reply-To: bijayant4u@yahoo.com
Subject: Re: Postgresql is very slow
To: pgsql-performance@postgresql.org
In-Reply-To: <28919.217.77.161.17.1214295453.squirrel@sq.gransy.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Message-ID: <473587.97248.qm@web32701.mail.mud.yahoo.com>

--- On Tue, 24/6/08, tv@fuzzy.cz <tv@fuzzy.cz> wrote:

> From: tv@fuzzy.cz <tv@fuzzy.cz>
> Subject: Re: [PERFORM] Postgresql is very slow
> To: "Scott Marlowe" <scott.marlowe@gmail.com>
> Cc: bijayant4u@yahoo.com, tv@fuzzy.cz, pgsql-performance@postgresql.org
> Date: Tuesday, 24 June, 2008, 1:47 PM
> >> 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 VACUUM only once.
> >
> > vacuuming isn't so much about performance as about
> maintenance.  You
> > don't change the oil in your car to make it go
> faster, you do it to
> > keep it running smoothly.  Don't change it for 1.5
> years and you could
> > have problems.  sludge build up / dead tuple build up.
>  Kinda similar.
> >
>=20
> I have to disagree - the VACUUM is a maintenance task, but
> with a direct
> impact on performance. The point is that Postgresql holds
> dead rows (old
> versions, deleted, etc.) until freed by vacuum, and these
> rows need to be
> checked every time (are they still visible to the
> transaction?). So on a
> heavily modified table you may easily end up with most of
> the tuples being
> dead and table consisting of mostly dead tuples.
>=20
> >> 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)
> >
> > You're scanning ~ 54094 sequential pages to
> retrieve 1 row.   Note
> > that explain analyze is generally a better choice, it
> gives more data
> > useful for troubleshooting.
>=20
> Not necessarily, the 'cost' depends on
> seq_page_cost and there might be
> other value than 1 (which is the default). A better
> approach is
>=20
> SELECT relpages, reltuples FROM pg_class WHERE relname =3D
> 'users';
>=20
> which reads the values from system catalogue.
>=20
The Output of query on the Slow Server

SELECT relpages, reltuples FROM pg_class WHERE relname =3D'users';
 relpages | reltuples
----------+-----------
    54063 |      2307
(1 row)

The Output of query on the old server which is fast

 relpages | reltuples
----------+-----------
       42 |      1637


> > Definitely need a vacuum full on this table, likely
> followed by a reindex.
>=20

The Slow server load increases whenever i run a simple query, is it the goo=
d idea to run VACUUM full on the live server's database now or it should be=
 run when the traffic is very low may be in weekend.

> Yes, that's true. I guess the table holds a lot of dead
> tuples. I'm not
> sure why this happens on one server (the new one) and not
> on the other
> one. I guess the original one uses some automatic vacuuming
> (autovacuum,
> cron job, or something like that).

There was nothing related to VACUUM of database in the crontab.
>=20
> As someone already posted, clustering the table (by primary
> key for
> example) should be much faster than vacuuming and give
> better performance
> in the end. See
>=20
> http://www.postgresql.org/docs/8.3/interactive/sql-cluster.html
>=20
> The plain reindex won't help here - it won't remove
> dead tuples.
>=20
I am new to Postgres database, i didnt understand the "indexing" part. Is i=
t related to PRIMARY_KEY column of the table?

Should i have to run:- CLUSTER USERS using 'username';

> Tomas=0A=0ASend instant messages to your online friends http://uk.messeng=
er.yahoo.com