> 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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
No comments:
Post a Comment