Thread: Postgres performance problem
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi, Note: I have already vacumm full. It does not solve the problem. I have a postgres 8.1 database. In the last days I have half traffic than 4 weeks ago, and resources usage is twice. The resource monitor graphs also shows hight peaks (usually there is not peaks) The performarce is getting poor with the time. Im not able to find the problem, seems there is not slow querys ( I have log_min_duration_statement = 5000 right now, tomorrow I ll decrease it ) Server is HP, and seems there is not hardware problems detected. Any ideas to debug it? -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFG0rh5Io1XmbAXRboRAuaTAJ4tDVHUMN5YRBdWxT//kPAfBvYqRACgvLst rJF3dmxzWHDOWB8yQwTyvpw= =2ic9 -----END PGP SIGNATURE-----
Attachment
> Hi, > > Note: I have already vacumm full. It does not solve the problem. > > I have a postgres 8.1 database. In the last days I have half traffic > than 4 weeks ago, and resources usage is twice. The resource monitor > graphs also shows hight peaks (usually there is not peaks) > > The performarce is getting poor with the time. > > Im not able to find the problem, seems there is not slow querys ( I have > log_min_duration_statement = 5000 right now, tomorrow I ll decrease it ) > > Server is HP, and seems there is not hardware problems detected. > > Any ideas to debug it? Hi, first of all: let us know the exact version of PG and the OS. If performance is getting worse, there ususally is some bloat envolved. Not vacuuming aggressivly enough, might be the most common cause. Do you autovacuum or vacuum manually? Tell us more... Bye, Chris.
SO: CentOS release 4.3 (Final) (kernel: 2.6.9-34.0.1.ELsmp) Postgres: 8.1.3 I had some problems before with autovacuum. So, Each day I crontab execute: vacuumdb -f -v --analyze reindex database vacadb I saw logs (the output of vacuum and reindex) and there is no errors. If u need more info, I ll be pleased to tell it here ... Chris Mair escribió: >> Hi, >> >> Note: I have already vacumm full. It does not solve the problem. >> >> I have a postgres 8.1 database. In the last days I have half traffic >> than 4 weeks ago, and resources usage is twice. The resource monitor >> graphs also shows hight peaks (usually there is not peaks) >> >> The performarce is getting poor with the time. >> >> Im not able to find the problem, seems there is not slow querys ( I have >> log_min_duration_statement = 5000 right now, tomorrow I ll decrease it ) >> >> Server is HP, and seems there is not hardware problems detected. >> >> Any ideas to debug it? > > Hi, > > first of all: let us know the exact version of PG and the OS. > > If performance is getting worse, there ususally is some bloat > envolved. Not vacuuming aggressivly enough, might be the most > common cause. Do you autovacuum or vacuum manually? > Tell us more... > > > Bye, > Chris. > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > >
In response to Chris Mair <chris@1006.org>: > > Hi, > > > > Note: I have already vacumm full. It does not solve the problem. To jump in here in Chris' defense, regular vacuum is not at all the same as vacuum full. Periodic vacuum is _much_ preferable to an occasional vacuum full. The output of vacuum verbose would have useful information ... are you exceeding your FSM limits? Try a reindex on the database. There may be some obscure corner cases where reindex makes a notable improvement in performance. > > I have a postgres 8.1 database. In the last days I have half traffic > > than 4 weeks ago, and resources usage is twice. The resource monitor > > graphs also shows hight peaks (usually there is not peaks) Resource monitor graphs? That statement means nothing to me, therefore I don't know if the information they're providing is useful or accurate, or even _what_ it is. What, exactly, are these graphs monitoring? You might want to provide your postgresql.conf. Have you considered the possibility that the database has simply got more records and therefore access takes more IO and CPU? > > The performarce is getting poor with the time. > > > > Im not able to find the problem, seems there is not slow querys ( I have > > log_min_duration_statement = 5000 right now, tomorrow I ll decrease it ) > > > > Server is HP, and seems there is not hardware problems detected. > > > > Any ideas to debug it? > > Hi, > > first of all: let us know the exact version of PG and the OS. > > If performance is getting worse, there ususally is some bloat > envolved. Not vacuuming aggressivly enough, might be the most > common cause. Do you autovacuum or vacuum manually? > Tell us more... > > > Bye, > Chris. > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > > > > > > -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ wmoran@collaborativefusion.com Phone: 412-422-3463x4023 **************************************************************** IMPORTANT: This message contains confidential information and is intended only for the individual named. If the reader of this message is not an intended recipient (or the individual responsible for the delivery of this message to an intended recipient), please be advised that any re-use, dissemination, distribution or copying of this message is prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any errors or omissions in the contents of this message, which arise as a result of e-mail transmission. ****************************************************************
Just a random thought/question... Are you running else on the machine? When you say "resource usage", do you mean hd space, memory, processor, ??? What are your values in top? More info... Cheers Anton On 27/08/2007, Bill Moran <wmoran@collaborativefusion.com> wrote: > In response to Chris Mair <chris@1006.org>: > > > > Hi, > > > > > > Note: I have already vacumm full. It does not solve the problem. > > To jump in here in Chris' defense, regular vacuum is not at all the same > as vacuum full. Periodic vacuum is _much_ preferable to an occasional > vacuum full. > > The output of vacuum verbose would have useful information ... are you > exceeding your FSM limits? > > Try a reindex on the database. There may be some obscure corner > cases where reindex makes a notable improvement in performance. > > > > I have a postgres 8.1 database. In the last days I have half traffic > > > than 4 weeks ago, and resources usage is twice. The resource monitor > > > graphs also shows hight peaks (usually there is not peaks) > > Resource monitor graphs? That statement means nothing to me, therefore > I don't know if the information they're providing is useful or accurate, > or even _what_ it is. What, exactly, are these graphs monitoring? > > You might want to provide your postgresql.conf. > > Have you considered the possibility that the database has simply got more > records and therefore access takes more IO and CPU? > > > > The performarce is getting poor with the time. > > > > > > Im not able to find the problem, seems there is not slow querys ( I have > > > log_min_duration_statement = 5000 right now, tomorrow I ll decrease it ) > > > > > > Server is HP, and seems there is not hardware problems detected. > > > > > > Any ideas to debug it? > > > > Hi, > > > > first of all: let us know the exact version of PG and the OS. > > > > If performance is getting worse, there ususally is some bloat > > envolved. Not vacuuming aggressivly enough, might be the most > > common cause. Do you autovacuum or vacuum manually? > > Tell us more... > > > > > > Bye, > > Chris. > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 3: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/docs/faq > > > > > > > > > > > > > > > -- > Bill Moran > Collaborative Fusion Inc. > http://people.collaborativefusion.com/~wmoran/ > > wmoran@collaborativefusion.com > Phone: 412-422-3463x4023 > > **************************************************************** > IMPORTANT: This message contains confidential information and is > intended only for the individual named. If the reader of this > message is not an intended recipient (or the individual > responsible for the delivery of this message to an intended > recipient), please be advised that any re-use, dissemination, > distribution or copying of this message is prohibited. Please > notify the sender immediately by e-mail if you have received > this e-mail by mistake and delete this e-mail from your system. > E-mail transmission cannot be guaranteed to be secure or > error-free as information could be intercepted, corrupted, lost, > destroyed, arrive late or incomplete, or contain viruses. The > sender therefore does not accept liability for any errors or > omissions in the contents of this message, which arise as a > result of e-mail transmission. > **************************************************************** > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match > -- echo '16i[q]sa[ln0=aln100%Pln100/snlbx]sbA0D4D465452snlbxq' | dc This will help you for 99.9% of your problems ...
Bill Moran escribió: > In response to Chris Mair <chris@1006.org>: > >>> Hi, >>> >>> Note: I have already vacumm full. It does not solve the problem. > > To jump in here in Chris' defense, regular vacuum is not at all the same > as vacuum full. Periodic vacuum is _much_ preferable to an occasional > vacuum full. > > The output of vacuum verbose would have useful information ... are you > exceeding your FSM limits? I think its ok. There is not warning messages on vacuum verbose in the last year. (I save all logs) > > Try a reindex on the database. There may be some obscure corner > cases where reindex makes a notable improvement in performance. I do it all days. (i know it is not necessary all days, but I should avoid problems) > >>> I have a postgres 8.1 database. In the last days I have half traffic >>> than 4 weeks ago, and resources usage is twice. The resource monitor >>> graphs also shows hight peaks (usually there is not peaks) > > Resource monitor graphs? That statement means nothing to me, therefore > I don't know if the information they're providing is useful or accurate, > or even _what_ it is. What, exactly, are these graphs monitoring? I should have explain that. I use "sar" command, I get 1 minute per 5 minutes, and I show the result in a graph. I have been using this one year, and use to work very well due traffic / resources usage . In last days started to show thinks that should not. By the way, the server is two intel dual processor 4Gb ram. It is only database server. > > You might want to provide your postgresql.conf. > I have tested these values. It makes sqls faster. I didn't change it in the last 10 months, The values I changed are: max_connections = 500 autovacuum = off shared_buffers = 24576 work_mem = 3072 maintenance_work_mem = 65536 wal_buffers = 1024 checkpoint_segments = 12 checkpoint_warning = 30 effective_cache_size = 225000 random_page_cost = 2 log_min_duration_statement = 1000 > Have you considered the possibility that the database has simply got more > records and therefore access takes more IO and CPU? Not possible. All database is in RAM memory. iostat is quiet. And if that was the problem, it should be in another way. Not in few weeks with half traffic. (I also have checked a possible attack, automatic "sing up" or thinks like that ... nothing found) By other way, there is not "slow sqls". (ok, there is a few slow sqls but are known slow sqls) I saw once a postgres sql server 8.1 that had poor performance with sql s that involve one table because someone change a column data type and seems it didn't work well. There is not that kind of changes in my database for 2 months at least, but ... is there any chance that database is being corrupted? Maybe dump database , delete it and restore it again may solve the problem ? > >>> The performarce is getting poor with the time. >>> >>> Im not able to find the problem, seems there is not slow querys ( I have >>> log_min_duration_statement = 5000 right now, tomorrow I ll decrease it ) >>> >>> Server is HP, and seems there is not hardware problems detected. >>> >>> Any ideas to debug it? >> Hi, >> >> first of all: let us know the exact version of PG and the OS. >> >> If performance is getting worse, there ususally is some bloat >> envolved. Not vacuuming aggressivly enough, might be the most >> common cause. Do you autovacuum or vacuum manually? >> Tell us more... >> >> >> Bye, >> Chris. >> >> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 3: Have you checked our extensive FAQ? >> >> http://www.postgresql.org/docs/faq >> >> >> >> >> >> > >
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi ... Seems its solved. But the problem is not found. As you may know, I do a vacuum full and a reindex database each day. I have logs that confirm that its done and I can check that everything was fine. So, this morning, I stopped the website, I stopped database, started it again. (I was around 200 days without restarting), then I vacuum database and reindex it (Same command as everyday) . Restart again, and run again the website. Now seems its working fine. But I really does not know where is the problem. Seems vacuum its not working fine? Maybe database should need a restart? I really don't know. Does someone had a similar problem? Thanks in advance, Ruben Rubio -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFG1pLMIo1XmbAXRboRAqgQAKCkWcZYE8RDppEVI485wDLnIW2SfQCfV+Hj e8PurQb2TOSYDPW545AJ83c= =dQgM -----END PGP SIGNATURE-----
Attachment
On Thu, Aug 30, 2007 at 11:50:04AM +0200, Ruben Rubio wrote: > As you may know, I do a vacuum full and a reindex database each day. I > have logs that confirm that its done and I can check that everything was > fine. > > So, this morning, I stopped the website, I stopped database, started it > again. (I was around 200 days without restarting), then I vacuum > database and reindex it (Same command as everyday) . Restart again, and > run again the website. > > Now seems its working fine. But I really does not know where is the > problem. Seems vacuum its not working fine? Maybe database should need > a restart? I really don't know. No, it sounds to me like you just weren't vacuuming aggressively enough to keep up with demand. -- Decibel!, aka Jim Nasby decibel@decibel.org EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
Attachment
Perhaps you had a long-running transaction open (probably a buggy or hung application) that was preventing dead rows from being cleaned up. Restarting PG closed the offending connection and rolled back the transaction, which allowed vacuum to clean up all the dead rows. If you're not running regular VACUUMs at all but are instead exclusively running VACUUM FULL, then I don't think you would see warnings about running out of fsm enties, which would explain why you did not notice the bloat. I haven't confirmed that though, so I might be wrong. -- Mark Lewis On Thu, 2007-08-30 at 11:50 +0200, Ruben Rubio wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > > > Hi ... > > Seems its solved. But the problem is not found. > > As you may know, I do a vacuum full and a reindex database each day. I > have logs that confirm that its done and I can check that everything was > fine. > > So, this morning, I stopped the website, I stopped database, started it > again. (I was around 200 days without restarting), then I vacuum > database and reindex it (Same command as everyday) . Restart again, and > run again the website. > > Now seems its working fine. But I really does not know where is the > problem. Seems vacuum its not working fine? Maybe database should need > a restart? I really don't know. > > Does someone had a similar problem? > > Thanks in advance, > Ruben Rubio > > > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.4.6 (GNU/Linux) > Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org > > iD8DBQFG1pLMIo1XmbAXRboRAqgQAKCkWcZYE8RDppEVI485wDLnIW2SfQCfV+Hj > e8PurQb2TOSYDPW545AJ83c= > =dQgM > -----END PGP SIGNATURE----- > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org
Decibel! wrote: > On Thu, Aug 30, 2007 at 11:50:04AM +0200, Ruben Rubio wrote: >> As you may know, I do a vacuum full and a reindex database each day. I >> have logs that confirm that its done and I can check that everything was >> fine. >> >> So, this morning, I stopped the website, I stopped database, started it >> again. (I was around 200 days without restarting), then I vacuum >> database and reindex it (Same command as everyday) . Restart again, and >> run again the website. >> >> Now seems its working fine. But I really does not know where is the >> problem. Seems vacuum its not working fine? Maybe database should need >> a restart? I really don't know. > > No, it sounds to me like you just weren't vacuuming aggressively enough > to keep up with demand. Actually , I think it sounds like a stray long-lived transaction. Ruben - vacuum can't recover rows if another transaction might be able to see them. So, if you have a connection that issues BEGIN and sits there for 200 days you can end up with a lot of bloat in your database. Now, there's no way to prove that since you've restarted the database-server, but keep an eye on it. -- Richard Huxton Archonet Ltd
On Aug 30, 2007, at 2:08 PM, Mark Lewis wrote: > If you're not running regular VACUUMs at all but are instead > exclusively > running VACUUM FULL, then I don't think you would see warnings about > running out of fsm enties, which would explain why you did not notice > the bloat. I haven't confirmed that though, so I might be wrong. If you run vacuum full, your pages should be full so there should be very small if any number of pages in the free space map. Thus, there would be no warnings.