Обсуждение: Vacuum deadlocks?
I've got a reasonably busy system that logically needs vacuum on a better than daily basis, at least in a couple of the tables.There are times when the traffic is lower, but they're not necessarily predictably overnight, as there are some longrunning processes that people like to start as they leave in the afternoon. I'm running 7.2.1 on OSX 10.1.5 (dual 800, 768 megs of memory, the machine is serving as app/db/webserver) using Mark Liange'spackage from entropy.ch. an upgrade is planned, but he doesn't have newer packages for < 10.2, and I'm not planningto upgrade to 10.2 on this machine quite yet. Every so often (perhaps 1 time in 50) the vacuumdb -a --analyze command will deadlock the system, so that the clients showa status of either 'starting' or 'async-notify', or 'idle in transaction'. This happened roughly every other day whenI had vacuum running from an hourly cron, once a week from the every 3 hour cron, and now it's happened a couple of timeswhen I run it just after backups. What am I doing wrong here? Is this a known issue for older versions? Or should I be running the vacuums in a differentmanner? eric What am I doing wrong here?
Eric, > Every so often (perhaps 1 time in 50) the vacuumdb -a --analyze > command will deadlock the system, so that the clients show a status > of either 'starting' or 'async-notify', or 'idle in transaction'. > This happened roughly every other day when I had vacuum running from > an hourly cron, once a week from the every 3 hour cron, and now it's > happened a couple of times when I run it just after backups. > > What am I doing wrong here? Is this a known issue for older > versions? Or should I be running the vacuums in a different manner? I think there were some issues with VACUUM on 7.2.1. Were I you, the first thing I would try is an upgrade to 7.2.3, which will also fix some serious security issues and at least one backup/restore issue. Maybe someone else on the list will speak to your specific issue, or you can look at the release notes for 7.2.2 and 7.2.3. You could also upgrade to 7.3.1, but that's a more serious undertaking, as you may have some backward compatibility issues. Second, were you aware that you have the option of running a regular VACUUM or VACUUM <table-name> from a database connection without locking users out of the database? In high-transaction environments, I can run these every 5 -15 minutes. The vacuumdb commmand-line utility, I believe, does a VACUUM FULL which exclusively locks the database (and you do need to do periodically, just not frequently). See the online docs on the VACUUM command for more information. -Josh
Thanks Josh. > I think there were some issues with VACUUM on 7.2.1. Were I you, the > first thing I would try is an upgrade to 7.2.3, which will also fix > some serious security issues and at least one backup/restore issue. > Maybe someone else on the list will speak to your specific issue, or > you can look at the release notes for 7.2.2 and 7.2.3. Great. The packager's site is down. (I knew there was a reason for that nagging feeling that I should be building from sourceinstead of using a package. ) > Second, were you aware that you have the option of running a regular > VACUUM or VACUUM <table-name> from a database connection without > locking users out of the database? In high-transaction environments, > I can run these every 5 -15 minutes. The vacuumdb commmand-line > utility, I believe, does a VACUUM FULL which exclusively locks the > database (and you do need to do periodically, just not frequently). I looked at the docs and didn't see the mention that vacuumdb did a vacuum full. That would certainly explain the deadlocking. But it seems that that is not the case. from the 7.2.1 docs: -f Perform "full" vacuuming. -a Vacuum all databases. .. vacuumdb is a shell script wrapper around the backend command VACUUM via the PostgreSQL interactive terminal psql. Thereis no effective difference between vacuuming databases via this or other methods. eric
Eric, > vacuumdb is a shell script wrapper around the backend command VACUUM via the PostgreSQL interactive terminal psql. There is no effective difference between vacuuming databases via this or other methods. Gee, can you tell how often I've used the script? <grin> The only related fix between 7.2.1 and 7.2.3 I can find is on: http://www3.us.postgresql.org/users-lounge/docs/7.3/postgres/release-7-2-3.html " Prevent spinlock hangs on SMP PPC machines (Tomoyuki Niijima)" It would be worth either upgrading or consulting other OSX users to see if anyone else has observed this; the bug may still be present in current versions since our OSX community is rather small. I use PostgreSQL on OSX, but on an low-transaction database and have never obseved this bug, either in 7.2.1 or in 7.2.3. -- -Josh Berkus Aglio Database Solutions San Francisco
Josh Berkus <josh@agliodbs.com> writes: > The only related fix between 7.2.1 and 7.2.3 I can find is on: > http://www3.us.postgresql.org/users-lounge/docs/7.3/postgres/release-7-2-3.html > " Prevent spinlock hangs on SMP PPC machines (Tomoyuki Niijima)" Given that Eric is using SMP PPC hardware, I would strongly advise updating to get that patch. I'm not aware of any reasons for vacuuming to cause deadlocks, so I suspect that the spinlock bug must be the problem. regards, tom lane