pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgresql is very slow
15+ messages / 8 participants
[nested] [flat]
* Postgresql is very slow
@ 2008-06-23 11:06 bijayant kumar <bijayant4u@yahoo.com>
2008-06-23 12:35 ` Re: Postgresql is very slow Jan de Visser <jdevisser@digitalfairway.com>
2008-06-23 13:36 ` Re: Postgresql is very slow Jeremy Harris <jgh@wizmail.org>
2008-06-23 13:50 ` Re: Postgresql is very slow tv@fuzzy.cz
2008-06-23 16:06 ` Re: Postgresql is very slow Gregory S. Youngblood <greg@tcscs.com>
0 siblings, 4 replies; 15+ messages in thread
From: bijayant kumar @ 2008-06-23 11:06 UTC (permalink / raw)
To: pgsql-performance
Hello to list,
We have a CentOS-5 server with postgresql-8.1.8 installed. I am struggling with postgresql performance. Any query say select * from tablename takes 10-15 mins to give the output, and while executing the query system loads goes up like anything. After the query output, system loads starts decresing.
Any query select,insert,update simple or complex behaves in the same way, what i have explained above.
System Specification:
OS :- CentOs 5
Postgresql 8.1.8
RAM :- 1 GB
SWAP 2 GB
Some relevent part(uncommented) of my /var/lib/pgsql/data/postgresql.conf
listen_addresses = 'localhost'
max_connections = 100
shared_buffers = 1000
The one more strange thing is that with the same setting on another server, postgresql is running very smooth. I had run vacum also some times back.
Please help me out and let me know if you need any other information.
Thanks & Regards,
Bijayant Kumar
Send instant messages to your online friends http://uk.messenger.yahoo.com
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Postgresql is very slow
2008-06-23 11:06 Postgresql is very slow bijayant kumar <bijayant4u@yahoo.com>
@ 2008-06-23 12:35 ` Jan de Visser <jdevisser@digitalfairway.com>
3 siblings, 0 replies; 15+ messages in thread
From: Jan de Visser @ 2008-06-23 12:35 UTC (permalink / raw)
To: pgsql-performance
On Monday 23 June 2008 07:06:54 bijayant kumar wrote:
> Hello to list,
>
> We have a CentOS-5 server with postgresql-8.1.8 installed. I am struggling
> with postgresql performance. Any query say select * from tablename takes
> 10-15 mins to give the output, and while executing the query system loads
> goes up like anything. After the query output, system loads starts
> decresing.
Sounds like a vacuum problem.
>
> Any query select,insert,update simple or complex behaves in the same way,
> what i have explained above.
>
> System Specification:
>
> OS :- CentOs 5
> Postgresql 8.1.8
> RAM :- 1 GB
> SWAP 2 GB
>
> Some relevent part(uncommented) of my /var/lib/pgsql/data/postgresql.conf
>
> listen_addresses = 'localhost'
> max_connections = 100
> shared_buffers = 1000
You shared_buffers seems low.
>
> The one more strange thing is that with the same setting on another server,
> postgresql is running very smooth. I had run vacum also some times back.
You are aware that vacuum is supposed to be an ongoing maintenance activity,
right?
>
> Please help me out and let me know if you need any other information.
>
> Thanks & Regards,
>
> Bijayant Kumar
>
> Send instant messages to your online friends http://uk.messenger.yahoo.com
jan
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Postgresql is very slow
2008-06-23 11:06 Postgresql is very slow bijayant kumar <bijayant4u@yahoo.com>
@ 2008-06-23 13:36 ` Jeremy Harris <jgh@wizmail.org>
3 siblings, 0 replies; 15+ messages in thread
From: Jeremy Harris @ 2008-06-23 13:36 UTC (permalink / raw)
To: pgsql-performance
bijayant kumar wrote:
> select * from tablename takes 10-15 mins to give the output
There are better ways to dump data than using a database; that's
not a useful query.
> Any query select,insert,update simple or complex behaves in the same way
Have you set up suitable indexes for your operations (and then run analyze)?
Cheers,
Jeremy
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Postgresql is very slow
2008-06-23 11:06 Postgresql is very slow bijayant kumar <bijayant4u@yahoo.com>
@ 2008-06-23 13:50 ` tv@fuzzy.cz
2008-06-24 05:48 ` Re: Postgresql is very slow bijayant kumar <bijayant4u@yahoo.com>
3 siblings, 1 reply; 15+ messages in thread
From: tv@fuzzy.cz @ 2008-06-23 13:50 UTC (permalink / raw)
To: bijayant4u@yahoo.com; +Cc: pgsql-performance
Hi,
> Hello to list,
>
> We have a CentOS-5 server with postgresql-8.1.8 installed. I am struggling
> with postgresql performance. Any query say select * from tablename takes
> 10-15 mins to give the output, and while executing the query system loads
> goes up like anything. After the query output, system loads starts
> decresing.
I doubt the 'select * from tablename' is a good candidate for tuning, but
give us more information about the table. What is it's size - how many
rows does it have and how much space does it occupy on the disk? What is a
typical usage of the table - is it modified (update / delete) frequently?
How is it maintained - is there a autovacuum running, or did you set a
routine vacuum (and analyze) job to maintain the database?
I guess one of the servers (the slow one) is running for a long time
without a proper db maintenance (vacuum / analyze) and you dumped / loaded
the db onto a new server. So the 'new server' has much more 'compact'
tables and thus gives the responses much faster. And this holds for more
complicated queries (with indexes etc) too.
An output from 'EXPLAIN' (or 'EXPLAIN ANALYZE') command would give a much
better overview.
Tomas
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Postgresql is very slow
2008-06-23 11:06 Postgresql is very slow bijayant kumar <bijayant4u@yahoo.com>
2008-06-23 13:50 ` Re: Postgresql is very slow tv@fuzzy.cz
@ 2008-06-24 05:48 ` bijayant kumar <bijayant4u@yahoo.com>
2008-06-24 07:51 ` Re: Postgresql is very slow Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: bijayant kumar @ 2008-06-24 05:48 UTC (permalink / raw)
To: tv@fuzzy.cz; +Cc: pgsql-performance
Hi,
Thanks for the reply. Many gentlemans have replied to my question, thanks to all of them. I have tried to answer all questions in one mail.
--- On Mon, 23/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: Monday, 23 June, 2008, 7:20 PM
> Hi,
>
> > Hello to list,
> >
> > We have a CentOS-5 server with postgresql-8.1.8
> installed. I am struggling
> > with postgresql performance. Any query say select *
> from tablename takes
> > 10-15 mins to give the output, and while executing the
> query system loads
> > goes up like anything. After the query output, system
> loads starts
> > decresing.
>
> I doubt the 'select * from tablename' is a good
> candidate for tuning, but
> give us more information about the table. What is it's
> size - how many
> rows does it have and how much space does it occupy on the
> disk? What is a
> typical usage of the table - is it modified (update /
> delete) frequently?
> How is it maintained - is there a autovacuum running, or
> did you set a
> routine vacuum (and analyze) job to maintain the database?
>
> I guess one of the servers (the slow one) is running for a
> long time
> without a proper db maintenance (vacuum / analyze) and you
> dumped / loaded
> the db onto a new server. So the 'new server' has
> much more 'compact'
> tables and thus gives the responses much faster. And this
> holds for more
> complicated queries (with indexes etc) too.
>
> An output from 'EXPLAIN' (or 'EXPLAIN
> ANALYZE') command would give a much
> better overview.
>
We maintains mail server, for this datas are stored in postgresql. There are total 24 tables but only two are used. Basically one table say USER stores the users information like mailid and his password, and there are 1669 rows in this table. The other table stores the domains name and no updation/deletion/insertion happens very frequently. Once in a month this table is touched.
But the second table USER is modified frequently(like on an average 10 times daily) because users changes their password, new users are being added, old ones are deleted.
We have created this database with the dump of our old server, and with the same dump the database is running fine on the new server but not on the slow server.
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.
Is this the problem of slow database? One more thing if i recreate the database, will it help?
The output of ANALYZE
ANALYZE verbose USERS;
INFO: analyzing "public.USERS"
INFO: "USERS": scanned 3000 of 54063 pages, containing 128 live rows and 1 dead rows; 128 rows in sample, 2307 estimated total rows
ANALYZE
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)
I hope i have covered everything in my mail to troubleshoot my problem.
> Tomas
>
>
> --
> Sent via pgsql-performance mailing list
> (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
Send instant messages to your online friends http://uk.messenger.yahoo.com
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Postgresql is very slow
2008-06-23 11:06 Postgresql is very slow bijayant kumar <bijayant4u@yahoo.com>
2008-06-23 13:50 ` Re: Postgresql is very slow tv@fuzzy.cz
2008-06-24 05:48 ` Re: Postgresql is very slow bijayant kumar <bijayant4u@yahoo.com>
@ 2008-06-24 07:51 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-06-24 07:52 ` Re: Postgresql is very slow Scott Marlowe <scott.marlowe@gmail.com>
2008-06-24 08:17 ` Re: Postgresql is very slow tv@fuzzy.cz
2008-06-24 09:33 ` Re: Postgresql is very slow Ian Barwick <barwick@gmail.com>
0 siblings, 3 replies; 15+ messages in thread
From: Scott Marlowe @ 2008-06-24 07:51 UTC (permalink / raw)
To: bijayant4u@yahoo.com; +Cc: tv@fuzzy.cz; pgsql-performance
On Mon, Jun 23, 2008 at 11:48 PM, bijayant kumar <bijayant4u@yahoo.com> wrote:
OK, you don't have a ton of updates each day, but they add up over time.
> 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.
> Is this the problem of slow database? One more thing if i recreate the database, will it help?
Most likely. What does
vacuum verbose;
on the main database say?
> The output of ANALYZE
>
> ANALYZE verbose USERS;
> INFO: analyzing "public.USERS"
> INFO: "USERS": scanned 3000 of 54063 pages, containing 128 live rows and 1 dead rows; 128 rows in sample, 2307 estimated total rows
> ANALYZE
So, 54963 pages hold 128 live database rows. A page is 8k. that
means you're storing 128 live rows in approximately a 400+ megabyte
file.
> 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.
Definitely need a vacuum full on this table, likely followed by a reindex.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Postgresql is very slow
2008-06-23 11:06 Postgresql is very slow bijayant kumar <bijayant4u@yahoo.com>
2008-06-23 13:50 ` Re: Postgresql is very slow tv@fuzzy.cz
2008-06-24 05:48 ` Re: Postgresql is very slow bijayant kumar <bijayant4u@yahoo.com>
2008-06-24 07:51 ` Re: Postgresql is very slow Scott Marlowe <scott.marlowe@gmail.com>
@ 2008-06-24 07:52 ` Scott Marlowe <scott.marlowe@gmail.com>
2 siblings, 0 replies; 15+ messages in thread
From: Scott Marlowe @ 2008-06-24 07:52 UTC (permalink / raw)
To: bijayant4u@yahoo.com; +Cc: tv@fuzzy.cz; pgsql-performance
> Definitely need a vacuum full on this table, likely followed by a reindex.
Or a cluster on the table...
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Postgresql is very slow
2008-06-23 11:06 Postgresql is very slow bijayant kumar <bijayant4u@yahoo.com>
2008-06-23 13:50 ` Re: Postgresql is very slow tv@fuzzy.cz
2008-06-24 05:48 ` Re: Postgresql is very slow bijayant kumar <bijayant4u@yahoo.com>
2008-06-24 07:51 ` Re: Postgresql is very slow Scott Marlowe <scott.marlowe@gmail.com>
@ 2008-06-24 08:17 ` tv@fuzzy.cz
2008-06-24 09:17 ` Re: Postgresql is very slow bijayant kumar <bijayant4u@yahoo.com>
2 siblings, 1 reply; 15+ messages in thread
From: tv@fuzzy.cz @ 2008-06-24 08:17 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: bijayant4u@yahoo.com; tv@fuzzy.cz; pgsql-performance
>> 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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Postgresql is very slow
2008-06-23 11:06 Postgresql is very slow bijayant kumar <bijayant4u@yahoo.com>
2008-06-23 13:50 ` Re: Postgresql is very slow tv@fuzzy.cz
2008-06-24 05:48 ` Re: Postgresql is very slow bijayant kumar <bijayant4u@yahoo.com>
2008-06-24 07:51 ` Re: Postgresql is very slow Scott Marlowe <scott.marlowe@gmail.com>
2008-06-24 08:17 ` Re: Postgresql is very slow tv@fuzzy.cz
@ 2008-06-24 09:17 ` bijayant kumar <bijayant4u@yahoo.com>
2008-06-24 10:02 ` Re: Postgresql is very slow tv@fuzzy.cz
2008-06-24 11:25 ` Re: Postgresql is very slow PFC <lists@peufeu.com>
0 siblings, 2 replies; 15+ messages in thread
From: bijayant kumar @ 2008-06-24 09:17 UTC (permalink / raw)
To: pgsql-performance
--- 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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Postgresql is very slow
2008-06-23 11:06 Postgresql is very slow bijayant kumar <bijayant4u@yahoo.com>
2008-06-23 13:50 ` Re: Postgresql is very slow tv@fuzzy.cz
2008-06-24 05:48 ` Re: Postgresql is very slow bijayant kumar <bijayant4u@yahoo.com>
2008-06-24 07:51 ` Re: Postgresql is very slow Scott Marlowe <scott.marlowe@gmail.com>
2008-06-24 08:17 ` Re: Postgresql is very slow tv@fuzzy.cz
2008-06-24 09:17 ` Re: Postgresql is very slow bijayant kumar <bijayant4u@yahoo.com>
@ 2008-06-24 10:02 ` tv@fuzzy.cz
2008-06-24 12:12 ` Re: Postgresql is very slow bijayant kumar <bijayant4u@yahoo.com>
2008-06-25 05:41 ` Re: [SOLVED] Postgresql is very slow bijayant kumar <bijayant4u@yahoo.com>
1 sibling, 2 replies; 15+ messages in thread
From: tv@fuzzy.cz @ 2008-06-24 10:02 UTC (permalink / raw)
To: bijayant4u@yahoo.com; +Cc: pgsql-performance
>> 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
>
>
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.
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.
>> > 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.
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).
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.
>
>> 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.
In that case there's something running vacuum - maybe autovacuum (see
postgresql.conf), or so.
>> 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?
Not sure what you mean by the 'nd
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.
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.
>
> Should i have to run:- CLUSTER USERS using 'username';
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).
Tomas
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Postgresql is very slow
2008-06-23 11:06 Postgresql is very slow bijayant kumar <bijayant4u@yahoo.com>
2008-06-23 13:50 ` Re: Postgresql is very slow tv@fuzzy.cz
2008-06-24 05:48 ` Re: Postgresql is very slow bijayant kumar <bijayant4u@yahoo.com>
2008-06-24 07:51 ` Re: Postgresql is very slow Scott Marlowe <scott.marlowe@gmail.com>
2008-06-24 08:17 ` Re: Postgresql is very slow tv@fuzzy.cz
2008-06-24 09:17 ` Re: Postgresql is very slow bijayant kumar <bijayant4u@yahoo.com>
2008-06-24 10:02 ` Re: Postgresql is very slow tv@fuzzy.cz
@ 2008-06-24 12:12 ` bijayant kumar <bijayant4u@yahoo.com>
1 sibling, 0 replies; 15+ messages in thread
From: bijayant kumar @ 2008-06-24 12:12 UTC (permalink / raw)
To: tv@fuzzy.cz; +Cc: pgsql-performance
Thank you and all very much for your support. Now i have understood the problem 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 =
> >> '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
> >
> >
>
> 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.
>
> 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.
>
> >> > 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.
>
> 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).
>
> 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.
>
> >
> >> 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.
>
> In that case there's something running vacuum - maybe
> autovacuum (see
> postgresql.conf), or so.
>
> >> 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?
>
> Not sure what you mean by the 'nd
>
> 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.
>
> 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.
>
> >
> > Should i have to run:- CLUSTER USERS using
> 'username';
>
> 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).
>
> Tomas
>
>
> --
> Sent via pgsql-performance mailing list
> (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
Send instant messages to your online friends http://uk.messenger.yahoo.com
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: [SOLVED] Postgresql is very slow
2008-06-23 11:06 Postgresql is very slow bijayant kumar <bijayant4u@yahoo.com>
2008-06-23 13:50 ` Re: Postgresql is very slow tv@fuzzy.cz
2008-06-24 05:48 ` Re: Postgresql is very slow bijayant kumar <bijayant4u@yahoo.com>
2008-06-24 07:51 ` Re: Postgresql is very slow Scott Marlowe <scott.marlowe@gmail.com>
2008-06-24 08:17 ` Re: Postgresql is very slow tv@fuzzy.cz
2008-06-24 09:17 ` Re: Postgresql is very slow bijayant kumar <bijayant4u@yahoo.com>
2008-06-24 10:02 ` Re: Postgresql is very slow tv@fuzzy.cz
@ 2008-06-25 05:41 ` bijayant kumar <bijayant4u@yahoo.com>
1 sibling, 0 replies; 15+ messages in thread
From: bijayant kumar @ 2008-06-25 05:41 UTC (permalink / raw)
To: tv@fuzzy.cz; +Cc: pgsql-performance; lists@peufeu.com
Thank you all very very much. After running CLUSTER on the "USERS" table, now the speed is very very good. Now i have also understood the importance of VACUUM and ANALYZE.
Once again thank you all very very much. You guys rock.
--- 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 =
> >> '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
> >
> >
>
> 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.
>
> 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.
>
> >> > 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.
>
> 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).
>
> 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.
>
> >
> >> 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.
>
> In that case there's something running vacuum - maybe
> autovacuum (see
> postgresql.conf), or so.
>
> >> 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?
>
> Not sure what you mean by the 'nd
>
> 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.
>
> 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.
>
> >
> > Should i have to run:- CLUSTER USERS using
> 'username';
>
> 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).
>
> Tomas
>
>
> --
> Sent via pgsql-performance mailing list
> (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
Send instant messages to your online friends http://uk.messenger.yahoo.com
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Postgresql is very slow
2008-06-23 11:06 Postgresql is very slow bijayant kumar <bijayant4u@yahoo.com>
2008-06-23 13:50 ` Re: Postgresql is very slow tv@fuzzy.cz
2008-06-24 05:48 ` Re: Postgresql is very slow bijayant kumar <bijayant4u@yahoo.com>
2008-06-24 07:51 ` Re: Postgresql is very slow Scott Marlowe <scott.marlowe@gmail.com>
2008-06-24 08:17 ` Re: Postgresql is very slow tv@fuzzy.cz
2008-06-24 09:17 ` Re: Postgresql is very slow bijayant kumar <bijayant4u@yahoo.com>
@ 2008-06-24 11:25 ` PFC <lists@peufeu.com>
1 sibling, 0 replies; 15+ messages in thread
From: PFC @ 2008-06-24 11:25 UTC (permalink / raw)
To: bijayant4u@yahoo.com; pgsql-performance
> SELECT relpages, reltuples FROM pg_class WHERE relname ='users';
> relpages | reltuples
> ----------+-----------
> 54063 | 2307
> (1 row)
This is a horribly bloated table.
> The Output of query on the old server which is fast
>
> relpages | reltuples
> ----------+-----------
> 42 | 1637
This is a healthy table.
You need to clean up the users table.
For this the easiest way is either to VACUUM FULL or CLUSTER it. CLUSTER
will be faster in your case. Use whatever index makes sense, or even the
PK.
> 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.
Use CLUSTER.
It is blocking so your traffic will suffer during the operation, which
should not take very long. Since you have very few rows, most of the
needed time will be reading the table from disk. I would suggest to do it
right now.
CLUSTER users_pk ON users;
Then, configure your autovacuum so it runs often enough. On a small table
like this (once cleaned up) VACUUM will be very fast, 42 pages should take
just a couple tens of ms to vacuum, so you can do it often.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Postgresql is very slow
2008-06-23 11:06 Postgresql is very slow bijayant kumar <bijayant4u@yahoo.com>
2008-06-23 13:50 ` Re: Postgresql is very slow tv@fuzzy.cz
2008-06-24 05:48 ` Re: Postgresql is very slow bijayant kumar <bijayant4u@yahoo.com>
2008-06-24 07:51 ` Re: Postgresql is very slow Scott Marlowe <scott.marlowe@gmail.com>
@ 2008-06-24 09:33 ` Ian Barwick <barwick@gmail.com>
2 siblings, 0 replies; 15+ messages in thread
From: Ian Barwick @ 2008-06-24 09:33 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: bijayant4u@yahoo.com; tv@fuzzy.cz; pgsql-performance
2008/6/24 Scott Marlowe <scott.marlowe@gmail.com>:
> On Mon, Jun 23, 2008 at 11:48 PM, bijayant kumar <bijayant4u@yahoo.com> wrote:
(...)
>> 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.
>
> Definitely need a vacuum full on this table, likely followed by a reindex.
This is a LIKE query with a wildcard at the start of the string to
match, reindexing won't help much.
Ian Barwick
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Postgresql is very slow
2008-06-23 11:06 Postgresql is very slow bijayant kumar <bijayant4u@yahoo.com>
@ 2008-06-23 16:06 ` Gregory S. Youngblood <greg@tcscs.com>
3 siblings, 0 replies; 15+ messages in thread
From: Gregory S. Youngblood @ 2008-06-23 16:06 UTC (permalink / raw)
To: bijayant4u@yahoo.com; pgsql-performance
> System Specification:
> OS :- CentOs 5
> Postgresql 8.1.8
> RAM :- 1 GB
> SWAP 2 GB
[Greg says]
How much memory is actually free, can you include the output from the command "free" in your reply? What else runs on this server? What is the system load before and during your query?
While it's more likely the other comments about vacuum will be the ultimate cause, performance will also be degraded, sometimes significantly, if your system has too many other things running and you are actively using swap space.
Greg
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2008-06-25 05:41 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-06-23 11:06 Postgresql is very slow bijayant kumar <bijayant4u@yahoo.com>
2008-06-23 12:35 ` Jan de Visser <jdevisser@digitalfairway.com>
2008-06-23 13:36 ` Jeremy Harris <jgh@wizmail.org>
2008-06-23 13:50 ` tv@fuzzy.cz
2008-06-24 05:48 ` bijayant kumar <bijayant4u@yahoo.com>
2008-06-24 07:51 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-06-24 07:52 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-06-24 08:17 ` tv@fuzzy.cz
2008-06-24 09:17 ` bijayant kumar <bijayant4u@yahoo.com>
2008-06-24 10:02 ` tv@fuzzy.cz
2008-06-24 12:12 ` bijayant kumar <bijayant4u@yahoo.com>
2008-06-25 05:41 ` bijayant kumar <bijayant4u@yahoo.com>
2008-06-24 11:25 ` PFC <lists@peufeu.com>
2008-06-24 09:33 ` Ian Barwick <barwick@gmail.com>
2008-06-23 16:06 ` Gregory S. Youngblood <greg@tcscs.com>
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