Re: Postgresql is very slow

Hi,

Thanks for the reply. Many gentlemans have replied to my question, thanks to all of them. I have tried to answer all
questionsin 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.
Basicallyone 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
monththis 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
thenew 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
notachieve 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
estimatedtotal 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