slower every day

Поиск
Список
Период
Сортировка
Искать

slower every day

От:
G u i d o B a r o s i o <gbarosio@uolsinectis.com.ar>
Дата:
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  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.

  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 less debug), 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

От:
Peter Eisentraut <peter_e@gmx.net>
Дата:
Am Mittwoch, 1. September 2004 12:06 schrieb G u i d o B a r o s i o:
>   The problem is the time that the postgres takes to perform/return a
> query. For example, trying the \d  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.

Do you regularly vacuum and analyze the database?

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

Re: slower every day

От:
G u i d o B a r o s i o <gbarosio@uolsinectis.com.ar>
Дата:
The solution appeared as something I didn't know

  On the .conf file

Previous situation:

#log_something=false
log_something=true

Worst situation 
#log_something=false
#log_something=true 

Nice situation
log_something=false
#log_something=true


Ok, the problem was that I assumed that commenting a value on
the conf file will set it up to a default (false?). I was wrong.
My server was writting tons of log's.

Is this the normal behavior for pg_ctl reload? It seems that looks for new values, remembering the last state on the ones that actually are commented. Although it's my fault to have 2 (tow) lines for the same issue, and that I should realize that this is MY MISTAKE, the log defaults on a reload, if commented, tend to be the last value entered?

Regards,
Guido


> Am Mittwoch, 1. September 2004 12:06 schrieb G u i d o B a r o s i o:
> >   The problem is the time that the postgres takes to perform/return a
> > query. For example, trying the \d  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.
> 
> Do you regularly vacuum and analyze the database?
> 
> -- 
> Peter Eisentraut
> http://developer.postgresql.org/~petere/
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend

Re: slower every day

От:
G u i d o B a r o s i o <gbarosio@uolsinectis.com.ar>
Дата:
Again me, 

   To make it easier.

Situation A:
log_something = true

Situation B: 
# log_something = 

Situation C:
log_something = false

After the pg_ctl reload:

Situation B = Situation A
Situation C <> (Situation A || Situation B)

Is this the expected behavior?

Conclusion:

If you comment a line on the conf file, and reload it, will remain in the last state. (either wast true or false, while I expected a default)

Regards

> The solution appeared as something I didn't know
> 
>   On the .conf file
> 
> Previous situation:
> 
> #log_something=false
> log_something=true
> 
> Worst situation 
> #log_something=false
> #log_something=true 
> 
> Nice situation
> log_something=false
> #log_something=true
> 
> 
> Ok, the problem was that I assumed that commenting a value on
> the conf file will set it up to a default (false?). I was wrong.
> My server was writting tons of log's.
> 
> Is this the normal behavior for pg_ctl reload? It seems that looks for new values, remembering the last state on the ones that actually are commented. Although it's my fault to have 2 (tow) lines for the same issue, and that I should realize that this is MY MISTAKE, the log defaults on a reload, if commented, tend to be the last value entered?
> 
> Regards,
> Guido
> 
> 
> > Am Mittwoch, 1. September 2004 12:06 schrieb G u i d o B a r o s i o:
> > >   The problem is the time that the postgres takes to perform/return a
> > > query. For example, trying the \d  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.
> > 
> > Do you regularly vacuum and analyze the database?
> > 
> > -- 
> > Peter Eisentraut
> > http://developer.postgresql.org/~petere/
> > 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 8: explain analyze is your friend
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

Re: slower every day

От:
"Michael Paesold" <mpaesold@gmx.at>
Дата:
This issue was resently discussed on hackers. It is a known issue, not very
convinient for the user. Nevertheless it is not fixed in 8.0, but will
perhaps be addressed in the next major release.
(Remembering, it was a non-trivial thing to change.)

Best Regards,
Michael Paesold

G u i d o B a r o s i o wrote:

> The solution appeared as something I didn't know
>
>   On the .conf file
>
> Previous situation:
>
> #log_something=false
> log_something=true
>
> Worst situation
> #log_something=false
> #log_something=true
>
> Nice situation
> log_something=false
> #log_something=true
>
>
> Ok, the problem was that I assumed that commenting a value on
> the conf file will set it up to a default (false?). I was wrong.
> My server was writting tons of log's.
>
> Is this the normal behavior for pg_ctl reload? It seems that looks for new
values, remembering the last state on the ones that actually are commented.
Although it's my fault to have 2 (tow) lines for the same issue, and that I
should realize that this is MY MISTAKE, the log defaults on a reload, if
commented, tend to be the last value entered?

Re: slower every day

От:
G u i d o B a r o s i o <gbarosio@uolsinectis.com.ar>
Дата:
Thanks for the reply,

  Been reading hackers of Aug 2004 and found the threads. It's a common habit to create two lines on the configuration files, in order to maintain the copy of the default conf file. I guess this should be the worst scenery for a freshly incoming DBA trying to put things in order. 

  A temporary patch, will be updating documentation, encouraging administrators to use the SHOW ALL; command in the psql env, to confirm that changes where made.

  In my case, a 1.2 gig file was written, performance was on the floor. And my previous situation, a reindex force task last saturday, confused me. This is not a trivial problem, but in conjunction with other small problems could become a big one.

  Good habits when touching conf files & using the SHOW ALL to confirm that changes where made will help until this is patched. 

  Thanks for Postgres, 

Regards, Guido.


> This issue was resently discussed on hackers. It is a known issue, not very
> convinient for the user. Nevertheless it is not fixed in 8.0, but will
> perhaps be addressed in the next major release.
> (Remembering, it was a non-trivial thing to change.)
> 
> Best Regards,
> Michael Paesold
> 
> G u i d o B a r o s i o wrote:
> 
> > The solution appeared as something I didn't know
> >
> >   On the .conf file
> >
> > Previous situation:
> >
> > #log_something=false
> > log_something=true
> >
> > Worst situation
> > #log_something=false
> > #log_something=true
> >
> > Nice situation
> > log_something=false
> > #log_something=true
> >
> >
> > Ok, the problem was that I assumed that commenting a value on
> > the conf file will set it up to a default (false?). I was wrong.
> > My server was writting tons of log's.
> >
> > Is this the normal behavior for pg_ctl reload? It seems that looks for new
> values, remembering the last state on the ones that actually are commented.
> Although it's my fault to have 2 (tow) lines for the same issue, and that I
> should realize that this is MY MISTAKE, the log defaults on a reload, if
> commented, tend to be the last value entered?
> 

FAQ