pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
slower every day
8+ messages / 5 participants
[nested] [flat]

* slower every day
@ 2004-09-01 10:06  G u i d o B a r o s i o <gbarosio@uolsinectis.com.ar>
  0 siblings, 2 replies; 8+ messages in thread

From: G u i d o B a r o s i o @ 2004-09-01 10:06 UTC (permalink / raw)
  To: pgsql-performance; +Cc: pgsql-admin@postgresql.org

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.

  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.






^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: slower every day
@ 2004-09-01 10:25  Shridhar Daithankar <shridhar@frodo.hserus.net>
  parent: G u i d o B a r o s i o <gbarosio@uolsinectis.com.ar>
  1 sibling, 0 replies; 8+ messages in thread

From: Shridhar Daithankar @ 2004-09-01 10:25 UTC (permalink / raw)
  To: gbarosio@uolsinectis.com.ar; +Cc: pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: slower every day
@ 2004-09-01 10:28  Peter Eisentraut <peter_e@gmx.net>
  parent: G u i d o B a r o s i o <gbarosio@uolsinectis.com.ar>
  1 sibling, 0 replies; 8+ messages in thread

From: Peter Eisentraut @ 2004-09-01 10:28 UTC (permalink / raw)
  To: gbarosio@uolsinectis.com.ar; +Cc: pgsql-performance; pgsql-admin@postgresql.org

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 <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.

Do you regularly vacuum and analyze the database?

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



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: slower every day
@ 2004-09-01 10:57  G u i d o B a r o s i o <gbarosio@uolsinectis.com.ar>
  0 siblings, 1 reply; 8+ messages in thread

From: G u i d o B a r o s i o @ 2004-09-01 10:57 UTC (permalink / raw)
  To: pgsql-performance; pgsql-admin@postgresql.org

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 <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.
> 
> 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




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: slower every day
@ 2004-09-01 11:58  G u i d o B a r o s i o <gbarosio@uolsinectis.com.ar>
  0 siblings, 1 reply; 8+ messages in thread

From: G u i d o B a r o s i o @ 2004-09-01 11:58 UTC (permalink / raw)
  To: pgsql-performance; pgsql-admin@postgresql.org; +Cc: pgsql-hackers@postgresql.org

Again me, 

   To make it easier.

Situation A:
log_something = true

Situation B: 
# log_something = <anything>

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 <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.
> > 
> > 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




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: [ADMIN] slower every day
@ 2004-09-01 12:27  Richard Huxton <dev@archonet.com>
  parent: G u i d o B a r o s i o <gbarosio@uolsinectis.com.ar>
  0 siblings, 0 replies; 8+ messages in thread

From: Richard Huxton @ 2004-09-01 12:27 UTC (permalink / raw)
  To: gbarosio@uolsinectis.com.ar; +Cc: pgsql-performance; pgsql-hackers@postgresql.org

G u i d o B a r o s i o wrote:
> 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)

Yes, that's correct. No, you're not the only one to have been caught out 
by this.

-- 
   Richard Huxton
   Archonet Ltd



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: slower every day
@ 2004-09-01 14:28  Michael Paesold <mpaesold@gmx.at>
  parent: G u i d o B a r o s i o <gbarosio@uolsinectis.com.ar>
  0 siblings, 0 replies; 8+ messages in thread

From: Michael Paesold @ 2004-09-01 14:28 UTC (permalink / raw)
  To: gbarosio@uolsinectis.com.ar; pgsql-performance; pgsql-admin@postgresql.org

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?




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: slower every day
@ 2004-09-01 15:07  G u i d o B a r o s i o <gbarosio@uolsinectis.com.ar>
  0 siblings, 0 replies; 8+ messages in thread

From: G u i d o B a r o s i o @ 2004-09-01 15:07 UTC (permalink / raw)
  To: mpaesold@gmx.at; pgsql-performance; pgsql-admin@postgresql.org; +Cc: pgsql-hackers@postgresql.org

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?
> 




^ permalink  raw  reply  [nested|flat] 8+ messages in thread


end of thread, other threads:[~2004-09-01 15:07 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-09-01 10:06 slower every day G u i d o B a r o s i o <gbarosio@uolsinectis.com.ar>
2004-09-01 10:25 ` Shridhar Daithankar <shridhar@frodo.hserus.net>
2004-09-01 10:28 ` Peter Eisentraut <peter_e@gmx.net>
2004-09-01 10:57 Re: slower every day G u i d o B a r o s i o <gbarosio@uolsinectis.com.ar>
2004-09-01 14:28 ` Michael Paesold <mpaesold@gmx.at>
2004-09-01 11:58 Re: slower every day G u i d o B a r o s i o <gbarosio@uolsinectis.com.ar>
2004-09-01 12:27 ` Richard Huxton <dev@archonet.com>
2004-09-01 15:07 Re: slower every day G u i d o B a r o s i o <gbarosio@uolsinectis.com.ar>

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