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=eEwMPaj4EG6yP9Lhr5wiQZbeLK5D7330EzhcB3jy1fCMYlTbIZEu0oJ8WphJK+kucQEvpKEe8E6T4rPxGu1Pop3F8eBpfNeJ3wkkg9V216Ezps1GNbW+IrjEnWN9ZpXq/hl6SYzYoMVYM488eAbJtCr7HnaezDXlPvy6qm6s3zU=;
Date: Tue, 24 Jun 2008 05:12: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: <20665.217.77.161.17.1214301728.squirrel@sq.gransy.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Message-ID: <585634.44380.qm@web32701.mail.mud.yahoo.com>

Thank you and all very much for your support. Now i have understood the pro=
blem related to my server. I will try the suggested thing like CLUSTER and =
then let you all know what happens after that.

Once again Thanking you all.

Bijayant Kumar


--- 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: bijayant4u@yahoo.com
> Cc: pgsql-performance@postgresql.org
> Date: Tuesday, 24 June, 2008, 3:32 PM
> >> 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
> >>
> >> SELECT relpages, reltuples FROM pg_class WHERE
> relname =3D
> >> 'users';
> >>
> >> which reads the values from system catalogue.
> >>
> > 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
> >
> >
>=20
> This definitely confirms the suspicion about dead tuples
> etc. On the old
> server the table has 1637 tuples and occupies just 42 pages
> (i.e. 330kB
> with 8k pages), which gives about 0.025 of a page (0.2kB
> per) per row.
>=20
> Let's suppose the characteristics of data (row sizes,
> etc.) are the same
> on both servers - in that case the 2307 rows should occuppy
> about 58
> pages, but as you can see from the first output it occupies
> 54063, i.e.
> 400MB instead of 450kB.
>=20
> >> > Definitely need a vacuum full on this table,
> likely
> >> followed by a reindex.
> >>
> >
> > The Slow server load increases whenever i run a simple
> query, is it the
> > good 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.
>=20
> The load increases because with the queries you've sent
> the database has
> to read the whole table (sequential scan) and may be spread
> through the
> disk (thus the disk has to seek).
>=20
> I'd recommend running CLUSTER instead of VACUUM - that
> should be much
> faster in this case. It will lock the table, but the
> performance already
> sucks, so I'd probably prefer a short downtime with a
> much faster
> processing after that.
>=20
> >
> >> 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
> In that case there's something running vacuum - maybe
> autovacuum (see
> postgresql.conf), or so.
>=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
> >>
> >>
> http://www.postgresql.org/docs/8.3/interactive/sql-cluster.html
> >>
> >> The plain reindex won't help here - it
> won't remove
> >> dead tuples.
> >>
> > I am new to Postgres database, i didnt understand the
> "indexing" part. Is
> > it related to PRIMARY_KEY column of the table?
>=20
> Not sure what you mean by the 'nd
>=20
> Principle of clustering is quite simple - by sorting the
> table according
> to an index (by the columns in the index) you may get
> better performance
> when using the index. Another 'bonus' is that it
> compacts the table on the
> disk,  so disk seeking is less frequent. These two effects
> may mean a
> serious increase of performance. You may cluster according
> to any index on
> the table, not just by primary key - just choose the most
> frequently used
> index.
>=20
> Sure, there are some drawbacks - it locks the table, so you
> may not use it
> when the command is running. It's not an incremental
> operation, the order
> is not enforced when modifying the table - when you modify
> a row the new
> version won't respect the order and you have to run the
> CLUSTER command
> from time to time. And it's possible to cluster by one
> index only.
>=20
> >
> > Should i have to run:- CLUSTER USERS using
> 'username';
>=20
> I guess 'username' is a column, so it won't
> work. You have to choose an
> index (I'd recommend the primary key index, i.e. the
> one with _pk at the
> end).
>=20
> 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