Thread: slower every day
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.
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/
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 onesthat actually are commented. Although it's my fault to have 2 (tow) lines for the same issue, and that I should realizethat 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
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 Iexpected 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 onesthat actually are commented. Although it's my fault to have 2 (tow) lines for the same issue, and that I should realizethat 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
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?
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 incomingDBA trying to put things in order. A temporary patch, will be updating documentation, encouraging administrators to use the SHOW ALL; command in the psqlenv, 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 lastsaturday, confused me. This is not a trivial problem, but in conjunction with other small problems could become a bigone. 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? >