pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedslower 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>
2004-09-01 10:25 ` Re: slower every day Shridhar Daithankar <shridhar@frodo.hserus.net>
2004-09-01 10:28 ` Re: slower every day Peter Eisentraut <peter_e@gmx.net>
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: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>
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:06 slower every day G u i d o B a r o s i o <gbarosio@uolsinectis.com.ar>
@ 2004-09-01 10:28 ` Peter Eisentraut <peter_e@gmx.net>
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>
2004-09-01 14:28 ` Re: slower every day Michael Paesold <mpaesold@gmx.at>
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 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>
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 11:58 G u i d o B a r o s i o <gbarosio@uolsinectis.com.ar>
2004-09-01 12:27 ` Re: [ADMIN] slower every day Richard Huxton <dev@archonet.com>
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 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>
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 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