From: tv@fuzzy.cz
To: Scott Marlowe <scott.marlowe@gmail.com>
Cc: bijayant4u@yahoo.com
Cc: tv@fuzzy.cz
Cc: pgsql-performance@postgresql.org
Subject: Re: Postgresql is very slow
Date: Tue, 24 Jun 2008 10:17:33 +0200 (CEST)
Message-ID: <28919.217.77.161.17.1214295453.squirrel@sq.gransy.com> (raw)
In-Reply-To: <dcc563d10806240051k23647056j36388305e1a977e1@mail.gmail.com>
References: <20229.217.77.161.17.1214229014.squirrel@sq.gransy.com>
<569091.76971.qm@web32704.mail.mud.yahoo.com>
<dcc563d10806240051k23647056j36388305e1a977e1@mail.gmail.com>
>> 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.
> Definitely need a vacuum full on this table, likely followed by a reindex.
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).
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.
Tomas
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: tv@fuzzy.cz, scott.marlowe@gmail.com, bijayant4u@yahoo.com
Subject: Re: Postgresql is very slow
In-Reply-To: <28919.217.77.161.17.1214295453.squirrel@sq.gransy.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