Обсуждение: Overload after some minutes, please help!
Hi all, we are struggling for some time now with PostgreSQL 8.1.4 and the situation is pretty critical so please help with whatever comes to your mind. We even did an upgrade from version 7.4.13, tried different vacuum configurations and optimized the configuration. There is a table called tableregistrations where per day about 1 million rows are INSERTed 20000 SELECTs should be performed on it 10000 UPDATEs should be performed where about 100 rows are updated with each execution 10000 DELETEs should be performed every 10 seconds in such a way that the table constantly contains about 20000 entries. I appended examples for these statements and the table structure. A vaccum of the whole database is performed every 10 minutes with cron, autovacuum is enabled too. We also tried to vaccum every minute which improved the performance somewhat but it just defers the freeze situation: After starting the system such a DELETE statement takes 300-500ms but after about 15 minutes the DELETE statements take about 45 seconds to complete and top shows that the postgres process which performs the DELETE takes almost 100% of one CPU. Some minutes later the database is unusable (all tables of the database are affected) and if the application is stopped, the load is still as high as before. The SELECT statements then also hang. All in all there are about 3 million other statements executed per day on other tables which are rather simple and don't hang in the database as the statements for the tableregistration do. System Specification: Dual Xeon Server machines (Dell PowerEdge 2850) using Heartbeat1. Each server has only one SCSI harddisk, no RAID configuration is used. - Each Cluster has 4 drbd Devices, one for the PostgreSQL data - Two of these clusters are using the same PostgreSQL installation to share the data - OS: Debian Sarge with postgresql 8.1.4 Other tasks are also performed on the system like logrotate of some GB sized files which need one CPU for some time. Is it possible that this is just too much load for the database on such a hardware setup? Where could i see what is constantly getting worse over time so that the DELETE statements take so long after some minutes? thx, Peter
Вложения
On Thu, 19 Oct 2006, Peter Bauer wrote: > A vaccum of the whole database is performed every 10 minutes with > cron What is the command you use? Maybe you are vaccuming with "-f" and locking the whole table. Regards Tometzky -- ...although Eating Honey was a very good thing to do, there was a moment just before you began to eat it which was better than when you were... Winnie the Pooh
Hi, its just a vacuumdb --all. We already learned that full vacuums are evil because the database was carrupted after some time. Regards, Peter 2006/10/19, Tomasz Ostrowski <tometzky@batory.org.pl>: > On Thu, 19 Oct 2006, Peter Bauer wrote: > > > A vaccum of the whole database is performed every 10 minutes with > > cron > > What is the command you use? Maybe you are vaccuming with "-f" and > locking the whole table. > > Regards > Tometzky > -- > ...although Eating Honey was a very good thing to do, there was a > moment just before you began to eat it which was better than when you > were... > Winnie the Pooh >
"Peter Bauer" <peter.m.bauer@gmail.com> writes: > There is a table called tableregistrations where per day about > 1 million rows are INSERTed > 20000 SELECTs should be performed on it > 10000 UPDATEs should be performed where about 100 rows are updated > with each execution > 10000 DELETEs should be performed every 10 seconds > in such a way that the table constantly contains about 20000 entries. > A vaccum of the whole database is performed every 10 minutes with > cron, autovacuum is enabled too. That's not *nearly* enough given that level of row turnover. You need to be vacuuming that table about once a minute if not more often, and you need to be sure that there aren't any long-running transactions that would prevent vacuum from removing dead rows. Try a VACUUM VERBOSE after the system has gotten into a slow state to get more info about exactly what's happening. regards, tom lane
Peter Bauer wrote: > - Two of these clusters are using the same PostgreSQL installation to > share the data Just checking - you're not sharing the same data files between two machines here, are you? Because that's not good. -- Richard Huxton Archonet Ltd
Hi, the drbd device can only be active and mounted on one machine, so the other is just in standby. Regards, Peter 2006/10/19, Richard Huxton <dev@archonet.com>: > Peter Bauer wrote: > > - Two of these clusters are using the same PostgreSQL installation to > > share the data > > Just checking - you're not sharing the same data files between two > machines here, are you? Because that's not good. > > -- > Richard Huxton > Archonet Ltd >
thank you, so we will perform the tests with such a vacuum configuration, br, Peter 2006/10/19, Tom Lane <tgl@sss.pgh.pa.us>: > "Peter Bauer" <peter.m.bauer@gmail.com> writes: > > There is a table called tableregistrations where per day about > > 1 million rows are INSERTed > > 20000 SELECTs should be performed on it > > 10000 UPDATEs should be performed where about 100 rows are updated > > with each execution > > 10000 DELETEs should be performed every 10 seconds > > in such a way that the table constantly contains about 20000 entries. > > > A vaccum of the whole database is performed every 10 minutes with > > cron, autovacuum is enabled too. > > That's not *nearly* enough given that level of row turnover. You need > to be vacuuming that table about once a minute if not more often, and > you need to be sure that there aren't any long-running transactions that > would prevent vacuum from removing dead rows. > > Try a VACUUM VERBOSE after the system has gotten into a slow state to > get more info about exactly what's happening. > > regards, tom lane >
On Thu, Oct 19, 2006 at 01:57:56PM +0200, Peter Bauer wrote: In the update statement, don't wrap the ID values in quotes. At best it's extra work; at worse it will fool the planner into not using the index. > shared_buffers = 1000 # min 16 or max_connections*2, 8KB each This is *way* too small for what you're trying to do. Try a minimum of 10% of memory, and 50% of memory may be a better idea. > #temp_buffers = 1000 # min 100, 8KB each > #max_prepared_transactions = 5 # can be 0 or more > # note: increasing max_prepared_transactions costs ~600 bytes of shared memory > # per transaction slot, plus lock space (see max_locks_per_transaction). > work_mem = 20480 # min 64, size in KB, Making that active might help a lot, but beware of running the machine out of memory... > #max_fsm_pages = 20000 # min max_fsm_relations*16, 6 bytes each Probably needs to get increased. > #bgwriter_delay = 200 # 10-10000 milliseconds between rounds > #bgwriter_lru_percent = 1.0 # 0-100% of LRU buffers scanned/round > #bgwriter_lru_maxpages = 5 # 0-1000 buffers max written/round > #bgwriter_all_percent = 0.333 # 0-100% of all buffers scanned/round > #bgwriter_all_maxpages = 5 # 0-1000 buffers max written/round The background writer might need to be tuned more aggressively. > #checkpoint_warning = 30 # in seconds, 0 is off I'd set that closer to 300 to make sure you're not checkpointing a lot, though keep in mind that will impact failover time. > effective_cache_size = 44800 # typically 8KB each The machine only has 1/2G of memory? > #autovacuum_naptime = 60 # time between autovacuum runs, in secs I'd drop that to 30. > #autovacuum_vacuum_threshold = 1000 # min # of tuple updates before > # vacuum > #autovacuum_analyze_threshold = 500 # min # of tuple updates before > # analyze > #autovacuum_vacuum_scale_factor = 0.4 # fraction of rel size before > # vacuum > #autovacuum_analyze_scale_factor = 0.2 # fraction of rel size before > # analyze I'd cut the above 4 in half. -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
thank you very much, we will test it br, Peter 2006/10/19, Jim C. Nasby <jim@nasby.net>: > On Thu, Oct 19, 2006 at 01:57:56PM +0200, Peter Bauer wrote: > In the update statement, don't wrap the ID values in quotes. At best > it's extra work; at worse it will fool the planner into not using the > index. > > > shared_buffers = 1000 # min 16 or max_connections*2, 8KB each > > This is *way* too small for what you're trying to do. Try a minimum of > 10% of memory, and 50% of memory may be a better idea. > > > #temp_buffers = 1000 # min 100, 8KB each > > #max_prepared_transactions = 5 # can be 0 or more > > # note: increasing max_prepared_transactions costs ~600 bytes of shared memory > > # per transaction slot, plus lock space (see max_locks_per_transaction). > > work_mem = 20480 # min 64, size in KB, > > Making that active might help a lot, but beware of running the machine > out of memory... > > > #max_fsm_pages = 20000 # min max_fsm_relations*16, 6 bytes each > > Probably needs to get increased. > > > #bgwriter_delay = 200 # 10-10000 milliseconds between rounds > > #bgwriter_lru_percent = 1.0 # 0-100% of LRU buffers scanned/round > > #bgwriter_lru_maxpages = 5 # 0-1000 buffers max written/round > > #bgwriter_all_percent = 0.333 # 0-100% of all buffers scanned/round > > #bgwriter_all_maxpages = 5 # 0-1000 buffers max written/round > > The background writer might need to be tuned more aggressively. > > > #checkpoint_warning = 30 # in seconds, 0 is off > > I'd set that closer to 300 to make sure you're not checkpointing a lot, > though keep in mind that will impact failover time. > > > effective_cache_size = 44800 # typically 8KB each > > The machine only has 1/2G of memory? > > > #autovacuum_naptime = 60 # time between autovacuum runs, in secs > > I'd drop that to 30. > > > #autovacuum_vacuum_threshold = 1000 # min # of tuple updates before > > # vacuum > > #autovacuum_analyze_threshold = 500 # min # of tuple updates before > > # analyze > > #autovacuum_vacuum_scale_factor = 0.4 # fraction of rel size before > > # vacuum > > #autovacuum_analyze_scale_factor = 0.2 # fraction of rel size before > > # analyze > > I'd cut the above 4 in half. > > -- > Jim Nasby jim@nasby.net > EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) >
Hi all, for further investigation we seperated the sub-SELECT from the DELETE statement and it looks like the SELECT is usually finished in some 100 milliseconds but after some minutes it suddenly takes some minutes. Peter 2006/10/20, Peter Bauer <peter.m.bauer@gmail.com>: > Hi all, > > we have a theory for the root of all evil which causes a reproducable > deadlock which is not detected by Postgre: > > The DELETE statement contains a select which waits for a sharelock > (according to pg_locks and pg_stat_activity) on rows locked by the > UPDATE statement. The UPDATE itself waits to get a lock for some rows > which are exclusively locked by the DELETE statement (got from its > sub-SELECT). > > What do you think about this theory? > > thx, > Peter > > 2006/10/19, Peter Bauer <peter.m.bauer@gmail.com>: > > thank you very much, we will test it > > > > br, > > Peter > > > > 2006/10/19, Jim C. Nasby <jim@nasby.net>: > > > On Thu, Oct 19, 2006 at 01:57:56PM +0200, Peter Bauer wrote: > > > In the update statement, don't wrap the ID values in quotes. At best > > > it's extra work; at worse it will fool the planner into not using the > > > index. > > > > > > > shared_buffers = 1000 # min 16 or max_connections*2, 8KB each > > > > > > This is *way* too small for what you're trying to do. Try a minimum of > > > 10% of memory, and 50% of memory may be a better idea. > > > > > > > #temp_buffers = 1000 # min 100, 8KB each > > > > #max_prepared_transactions = 5 # can be 0 or more > > > > # note: increasing max_prepared_transactions costs ~600 bytes of shared memory > > > > # per transaction slot, plus lock space (see max_locks_per_transaction). > > > > work_mem = 20480 # min 64, size in KB, > > > > > > Making that active might help a lot, but beware of running the machine > > > out of memory... > > > > > > > #max_fsm_pages = 20000 # min max_fsm_relations*16, 6 bytes each > > > > > > Probably needs to get increased. > > > > > > > #bgwriter_delay = 200 # 10-10000 milliseconds between rounds > > > > #bgwriter_lru_percent = 1.0 # 0-100% of LRU buffers scanned/round > > > > #bgwriter_lru_maxpages = 5 # 0-1000 buffers max written/round > > > > #bgwriter_all_percent = 0.333 # 0-100% of all buffers scanned/round > > > > #bgwriter_all_maxpages = 5 # 0-1000 buffers max written/round > > > > > > The background writer might need to be tuned more aggressively. > > > > > > > #checkpoint_warning = 30 # in seconds, 0 is off > > > > > > I'd set that closer to 300 to make sure you're not checkpointing a lot, > > > though keep in mind that will impact failover time. > > > > > > > effective_cache_size = 44800 # typically 8KB each > > > > > > The machine only has 1/2G of memory? > > > > > > > #autovacuum_naptime = 60 # time between autovacuum runs, in secs > > > > > > I'd drop that to 30. > > > > > > > #autovacuum_vacuum_threshold = 1000 # min # of tuple updates before > > > > # vacuum > > > > #autovacuum_analyze_threshold = 500 # min # of tuple updates before > > > > # analyze > > > > #autovacuum_vacuum_scale_factor = 0.4 # fraction of rel size before > > > > # vacuum > > > > #autovacuum_analyze_scale_factor = 0.2 # fraction of rel size before > > > > # analyze > > > > > > I'd cut the above 4 in half. > > > > > > -- > > > Jim Nasby jim@nasby.net > > > EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) > > > > > >
Hi all, we have a theory for the root of all evil which causes a reproducable deadlock which is not detected by Postgre: The DELETE statement contains a select which waits for a sharelock (according to pg_locks and pg_stat_activity) on rows locked by the UPDATE statement. The UPDATE itself waits to get a lock for some rows which are exclusively locked by the DELETE statement (got from its sub-SELECT). What do you think about this theory? thx, Peter 2006/10/19, Peter Bauer <peter.m.bauer@gmail.com>: > thank you very much, we will test it > > br, > Peter > > 2006/10/19, Jim C. Nasby <jim@nasby.net>: > > On Thu, Oct 19, 2006 at 01:57:56PM +0200, Peter Bauer wrote: > > In the update statement, don't wrap the ID values in quotes. At best > > it's extra work; at worse it will fool the planner into not using the > > index. > > > > > shared_buffers = 1000 # min 16 or max_connections*2, 8KB each > > > > This is *way* too small for what you're trying to do. Try a minimum of > > 10% of memory, and 50% of memory may be a better idea. > > > > > #temp_buffers = 1000 # min 100, 8KB each > > > #max_prepared_transactions = 5 # can be 0 or more > > > # note: increasing max_prepared_transactions costs ~600 bytes of shared memory > > > # per transaction slot, plus lock space (see max_locks_per_transaction). > > > work_mem = 20480 # min 64, size in KB, > > > > Making that active might help a lot, but beware of running the machine > > out of memory... > > > > > #max_fsm_pages = 20000 # min max_fsm_relations*16, 6 bytes each > > > > Probably needs to get increased. > > > > > #bgwriter_delay = 200 # 10-10000 milliseconds between rounds > > > #bgwriter_lru_percent = 1.0 # 0-100% of LRU buffers scanned/round > > > #bgwriter_lru_maxpages = 5 # 0-1000 buffers max written/round > > > #bgwriter_all_percent = 0.333 # 0-100% of all buffers scanned/round > > > #bgwriter_all_maxpages = 5 # 0-1000 buffers max written/round > > > > The background writer might need to be tuned more aggressively. > > > > > #checkpoint_warning = 30 # in seconds, 0 is off > > > > I'd set that closer to 300 to make sure you're not checkpointing a lot, > > though keep in mind that will impact failover time. > > > > > effective_cache_size = 44800 # typically 8KB each > > > > The machine only has 1/2G of memory? > > > > > #autovacuum_naptime = 60 # time between autovacuum runs, in secs > > > > I'd drop that to 30. > > > > > #autovacuum_vacuum_threshold = 1000 # min # of tuple updates before > > > # vacuum > > > #autovacuum_analyze_threshold = 500 # min # of tuple updates before > > > # analyze > > > #autovacuum_vacuum_scale_factor = 0.4 # fraction of rel size before > > > # vacuum > > > #autovacuum_analyze_scale_factor = 0.2 # fraction of rel size before > > > # analyze > > > > I'd cut the above 4 in half. > > > > -- > > Jim Nasby jim@nasby.net > > EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) > > >
"Peter Bauer" <peter.m.bauer@gmail.com> writes: > we have a theory for the root of all evil which causes a reproducable > deadlock which is not detected by Postgre: > The DELETE statement contains a select which waits for a sharelock > (according to pg_locks and pg_stat_activity) on rows locked by the > UPDATE statement. The UPDATE itself waits to get a lock for some rows > which are exclusively locked by the DELETE statement (got from its > sub-SELECT). > What do you think about this theory? Not much. It's been years since anyone found a bug in the deadlock detector; if you want us to believe you have an undetected deadlock, you'll need more evidence than an unsupported assertion. regards, tom lane
> its just a vacuumdb --all. We already learned that full vacuums are > evil because the database was carrupted after some time. Wait a sec... vacuum full maybe evil in the 'locks stuff and takes long to run'-sense, but it should definitly NOT corrupt your database. Are you sure there's no issues on the hardware / system administration side of things? Bye, Chris.
Hi, we had these problems with Version 7.4.7, you can find the old thread here: http://archives.postgresql.org/pgsql-general/2006-09/msg00079.php br, Peter 2006/10/21, Chris Mair <chrisnospam@1006.org>: > > > its just a vacuumdb --all. We already learned that full vacuums are > > evil because the database was carrupted after some time. > > Wait a sec... > vacuum full maybe evil in the 'locks stuff and takes long to run'-sense, > but it should definitly NOT corrupt your database. > > Are you sure there's no issues on the hardware / system administration > side of things? > > Bye, Chris. > > > > > > >
Hi all, here comes an update: Currently a loadtest is running since 4 hours which did not cause any problems so far. There are about 200000 rows inserted, updated and deleted per hour. We made column_indexes for location1, location2, location3, register, type and multi_column_index for isbackup, callednumber, physicalnumber uus1, sourcemask and priority. Additionally a BEGIN; LOCK table tableregistrations IN EXCLUSIVE mode; before the DELETE statement and a END afterwards. vacuumdb for tableregistrations is running with 10sec of sleep in between and the suggested changes of Jim to the postgresql.conf so autovacuum should run properly too. All other loadtests (no locking or no indexing) ended up in very high load and an unusable system after max. one hour because of the very long running sub-SELECT of the DELETE statement. So i think that sometimes there were deadlocks between these 3 statements which were detected and reported by Postgre (not sure if it could be resolved). This should be solved by locking the whole table. Additionally the sub-SELECT took so lang that vacuum couldnt clean up the dead rows caused by the UPDATEs and the next runtime of it was extremely high which lead to a unrecoverable situation because there was constant load. Is this a reasonable assumption or impossible nonsense? thx, Peter 2006/10/21, Peter Bauer <peter.m.bauer@gmail.com>: > Hi, > > we had these problems with Version 7.4.7, you can find the old thread here: > http://archives.postgresql.org/pgsql-general/2006-09/msg00079.php > > br, > Peter > > 2006/10/21, Chris Mair <chrisnospam@1006.org>: > > > > > its just a vacuumdb --all. We already learned that full vacuums are > > > evil because the database was carrupted after some time. > > > > Wait a sec... > > vacuum full maybe evil in the 'locks stuff and takes long to run'-sense, > > but it should definitly NOT corrupt your database. > > > > Are you sure there's no issues on the hardware / system administration > > side of things? > > > > Bye, Chris. > > > > > > > > > > > > > > >
> All other loadtests (no locking or no indexing) ended up in very high > load and an unusable system after max. one hour because of the very > long running sub-SELECT of the DELETE statement. > > So i think that sometimes there were deadlocks between these 3 > statements which were detected and reported by Postgre (not sure if it > could be resolved). This should be solved by locking the whole table. > Additionally the sub-SELECT took so lang that vacuum couldnt clean up > the dead rows caused by the UPDATEs and the next runtime of it was > extremely high which lead to a unrecoverable situation because there > was constant load. > > Is this a reasonable assumption or impossible nonsense? Sounds more likely to me, since you didn't originally have indexes that you were getting a long seqscan for your DELETE statement which was running while the sub-select was waiting. If you were running the DELETE statement multiple times during a load test you likely ran into a table bloat issues because of all the dead rows. Joshua D. Drake > > thx, > Peter > > 2006/10/21, Peter Bauer <peter.m.bauer@gmail.com>: >> Hi, >> >> we had these problems with Version 7.4.7, you can find the old thread >> here: >> http://archives.postgresql.org/pgsql-general/2006-09/msg00079.php >> >> br, >> Peter >> >> 2006/10/21, Chris Mair <chrisnospam@1006.org>: >> > >> > > its just a vacuumdb --all. We already learned that full vacuums are >> > > evil because the database was carrupted after some time. >> > >> > Wait a sec... >> > vacuum full maybe evil in the 'locks stuff and takes long to >> run'-sense, >> > but it should definitly NOT corrupt your database. >> > >> > Are you sure there's no issues on the hardware / system administration >> > side of things? >> > >> > Bye, Chris. >> > >> > >> > >> > >> > >> > >> > >> > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
Peter Bauer wrote: > Hi all, > > for further investigation we seperated the sub-SELECT from the DELETE > statement and it looks like the SELECT is usually finished in some 100 > milliseconds but after some minutes it suddenly takes some minutes. Explain analyzes before and after should give some insight into what's happening. It looks like the query plan changes after some minutes. What I think is happening (but w/o any proof I'm just guessing) is that the planner starts with statistics from a relatively empty table (or something similar statistics-wise) and never gets the opportunity to update its statistics with information about the newly inserted data. If that's the case, calling ANALYSE regularly should show improvement (I'm not sure if autovacuum also analyses - but if so, not frequently enough). It may also be that the statistics do not match the number of records and the data you have. Playing with the statistics size may show improvement. A quick question for the experts: Is the statistics size equivalent to a "sample" as known in statistical analysis? If so, there are mathematics to calculate the required sample size that should at least give people some idea what size to set it to. Regards, -- Alban Hertroys alban@magproductions.nl magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World //