Обсуждение: Vacuum taking an age
Hi, I have a pretty "live" table: rows being inserted and updated more than once 1 per second, though far, far more inserts than updates. There are currently over 3 million rows. It has not been vacuumed for months. Now a vacuum on that table takes hours, and I have not let it complete because it stays running into our daily busy time... but I've been told its necessary because the table is slowing down. I have begun a cron job which will do a daily analyze, and am thinking of a weekly vacuum... Please advise on the best way to keep this table maintained, even if it means regularly taking the service offline early on Sunday morning... thanks in advance Brian -- Brian Modra Land line: +27 23 5411 462 Mobile: +27 79 183 8059 6 Jan Louw Str, Prince Albert, 6930 Postal: P.O. Box 2, Prince Albert 6930 South Africa
If you're using the version 8.1 and after, you should consider using the auto-vacuum daemon that is the best way to do it: http://www.postgresql.org/docs/8.1/static/maintenance.html Pascal; Brian Modra a écrit : > Hi, > > > I have a pretty "live" table: rows being inserted and updated more > than once 1 per second, though far, far more inserts than updates. > > There are currently over 3 million rows. > > It has not been vacuumed for months. > > Now a vacuum on that table takes hours, and I have not let it complete > because it stays running into our daily busy time... but I've been > told its necessary because the table is slowing down. > > I have begun a cron job which will do a daily analyze, and am thinking > of a weekly vacuum... > Please advise on the best way to keep this table maintained, even if > it means regularly taking the service offline early on Sunday > morning... > > thanks in advance > Brian > >
On Jan 3, 2008 6:48 AM, Brian Modra <epailty@googlemail.com> wrote: > Hi, > > > I have a pretty "live" table: rows being inserted and updated more > than once 1 per second, though far, far more inserts than updates. > > There are currently over 3 million rows. > > It has not been vacuumed for months. How many rows per second? 1? all of them? Kinda makes a difference. If it was 1 a second updated for 3 months that's about 7million dead rows. If it was all 3million, then that's 7million * 3million dead rows, also know as a whole bunch of rows. Either way, you probably have a table so terribly bloated that a regular vacuum will not help you in terms of speeding it up. Regular vacuums are like brushing your teeth three times a day. If you've forgotten for three months, brushing them once isn't likely to fix all the cavities you've got. Same thing here. You'll either need a vacuum full or a cluster. Cluster is often faster. Or you can try selecting everything into a temp table, truncating the real table, and inserting the data back in. Truncation will remove all rows, dead or otherwise. The advantage is that it's often faster to truncate / reload than it is to vacuum full. If you have indexes, you might want to drop them while re-inserting and then recreated them.
On 03.01.2008, at 05:48, Brian Modra wrote: > I have a pretty "live" table: rows being inserted and updated more > than once 1 per second, though far, far more inserts than updates. Not that busy ;-) > It has not been vacuumed for months. Not good. > Now a vacuum on that table takes hours, and I have not let it complete > because it stays running into our daily busy time... but I've been > told its necessary because the table is slowing down. > > I have begun a cron job which will do a daily analyze, and am thinking > of a weekly vacuum... > Please advise on the best way to keep this table maintained, even if > it means regularly taking the service offline early on Sunday > morning... Two things you can consider: 1. Cluster the table with one of the indexes. This will be really fast, but is not transaction-safe as far as I remember for 8.2.x. 2. Use autovaccum to vacuum / analyze your database all the time. That will keep the size small and the stats up to date. cug -- http://www.event-s.net
Hi, thanks for your reply. The number of rows per second has been increasing rapidly, but its averaging about 1 row per second, and a far smaller number of updates. So maybe there are not such a huge number of dead rows. I hope that a normal vacuum will clean it up. Total number of rows is about 3 million. Last night before I got your reply, I noticed that the number of shared memory buffers was only 1000, so I increased shmmax and when I restart the server next, its number of buffers will be 10000. The server has 8GB of memory, so that will only be a small proportion of its total memory. I have not restarted postgres yet because a vacuum is still running. Maybe I should kill that and restart postgres? The reason I increased this is because I noticed if I did a partial count of rows (e.g. those inserted with a timestamp after midnight last night), then the first time takes about 17 seconds, and the second time 1/4 second. I started a vacuum on the table yesterday, and its still running. I guess thats because the table is live. I am pretty sure that if I take it offline, then the vacuum will complete relatively quickly. Am I right? (I don't want to take it offline unless I really need to.) On 04/01/2008, Scott Marlowe <scott.marlowe@gmail.com> wrote: > On Jan 3, 2008 6:48 AM, Brian Modra <epailty@googlemail.com> wrote: > > Hi, > > > > > > I have a pretty "live" table: rows being inserted and updated more > > than once 1 per second, though far, far more inserts than updates. > > > > There are currently over 3 million rows. > > > > It has not been vacuumed for months. > > How many rows per second? 1? all of them? Kinda makes a difference. > > If it was 1 a second updated for 3 months that's about 7million dead > rows. If it was all 3million, then that's 7million * 3million dead > rows, also know as a whole bunch of rows. > > Either way, you probably have a table so terribly bloated that a > regular vacuum will not help you in terms of speeding it up. Regular > vacuums are like brushing your teeth three times a day. If you've > forgotten for three months, brushing them once isn't likely to fix all > the cavities you've got. Same thing here. You'll either need a > vacuum full or a cluster. Cluster is often faster. Or you can try > selecting everything into a temp table, truncating the real table, and > inserting the data back in. Truncation will remove all rows, dead or > otherwise. The advantage is that it's often faster to truncate / > reload than it is to vacuum full. If you have indexes, you might want > to drop them while re-inserting and then recreated them. > -- Brian Modra Land line: +27 23 5411 462 Mobile: +27 79 183 8059 6 Jan Louw Str, Prince Albert, 6930 Postal: P.O. Box 2, Prince Albert 6930 South Africa
On Fri, Jan 04, 2008 at 06:55:13AM +0200, Brian Modra wrote: > I started a vacuum on the table yesterday, and its still running. I > guess thats because the table is live. I am pretty sure that if I take > it offline, then the vacuum will complete relatively quickly. Am I > right? (I don't want to take it offline unless I really need to.) No. The vacuum on that table is going to take a long time, period. See upthread about not brushing teeth for 3 months. Anyway, I can't believe a plain vacuum is going to help you here. I'd perform CLUSTER or else dump the database and reload it. You're looking at a service outage, I think. A