From: bijayant kumar <bijayant4u@yahoo.com>
To: pgsql-performance@postgresql.org
Subject: Re: Postgresql is very slow
Date: Tue, 24 Jun 2008 02:17:34 -0700 (PDT)
Message-ID: <473587.97248.qm@web32701.mail.mud.yahoo.com> (raw)
In-Reply-To: <28919.217.77.161.17.1214295453.squirrel@sq.gransy.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.
> >
>
> 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.
>
> >> 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=0.00..54091.84 rows=1
> width=161)
> >> 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.
>
> 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 =
> 'users';
>
> which reads the values from system catalogue.
>
The Output of query on the Slow Server
SELECT relpages, reltuples FROM pg_class WHERE relname ='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.
>
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.
> 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.
>
> 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?
Should i have to run:- CLUSTER USERS using 'username';
> Tomas
Send instant messages to your online friends http://uk.messenger.yahoo.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: bijayant4u@yahoo.com
Subject: Re: Postgresql is very slow
In-Reply-To: <473587.97248.qm@web32701.mail.mud.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox