Thread: slower every day

slower every day

From
G u i d o B a r o s i o
Date:
Dear all,

  I am currently experiencing troubles with the performance of my critical's database.

  The problem is the time that the postgres takes to perform/return a query. For example, trying the \d <tablename>
commandtakes between 4 or 5 seconds. This table is very big, but I am not asking for the rows, only asking the table
schema,so...why is this so slow?!?!? My last administrative action into this table was a reindex to all the indexes via
theBKI in standalone mode. I thought I suceed, but this was las saturday. Today I am in the same situation again. 

  The only change that I've done was a highest level of debug in the conf file (loggin lot of stuff).
  I understand that this could lack on performance, but when I've changed the .conf file to the usual .conf file (with
lessdebug), and pg_ctl reload(ed) it, it goes on debuging as in the first state, in the higher level. Is this a known
issue? 

  My conclusion is that I can aquire high levels of debug while the server is running, editing the .conf file, and
pg_reload(ing)it, but I can go back then, unless I pg_restart the server. Is this ok? 

Some info
-----------------------------------------------------------
PostgreSQL 7.4.2
[postgres@lmnukmis02 data]$ pg_config --configure
'--enable-thread-safety' '--with-perl'
Intel(R) Xeon(TM) MP CPU 2.80GHz
Linux 2.4.24-ck1 #5 SMP Fri Mar 12 23:41:51 GMT 2004 i686 unknown
RAM 4 Gb.
-----------------------------------------------------------


Thanks, Guido.




Re: slower every day

From
Shridhar Daithankar
Date:
On Wednesday 01 Sep 2004 3:36 pm, G u i d o B a r o s i o wrote:
> Dear all,
>
>   I am currently experiencing troubles with the performance of my
> critical's database.
>
>   The problem is the time that the postgres takes to perform/return a
> query. For example, trying the \d <tablename> command takes between 4 or 5
> seconds. This table is very big, but I am not asking for the rows, only
> asking the table schema, so...why is this so slow?!?!? My last
> administrative action into this table was a reindex to all the indexes via
> the BKI in standalone mode. I thought I suceed, but this was las saturday.
> Today I am in the same situation again.

Is this database vacuumed and analyzed recently? I would suggest database-wide
vacuum full analyze.

If your queries are getting slower, then checking the explain analyze output
is a good starting point. To see queries issued by psql, start it as psql -E.

HTH

 Shridhar