Thread: 3-days-long vacuum of 20GB table
This autovacuum has been hammering my server with purely random i/o for half a week. The table is only 20GB and the i/o subsystem is good for 250MB/s sequential and a solid 5kiops. When should I expect it to end (if ever)? current_query: VACUUM reuters.value query_start: 2008-04-15 20:12:48.806885-04 think=# select * from pg_class where relname = 'value'; -[ RECORD 1 ]--+--------------------------------- relname | value relfilenode | 191425 relpages | 1643518 reltuples | 1.37203e+08 # find -name 191425\* ./16579/191425 ./16579/191425.1 ./16579/191425.10 ./16579/191425.11 ./16579/191425.12 ./16579/191425.13 ./16579/191425.14 ./16579/191425.15 ./16579/191425.16 ./16579/191425.17 ./16579/191425.18 ./16579/191425.19 ./16579/191425.2 ./16579/191425.3 ./16579/191425.4 ./16579/191425.5 ./16579/191425.6 ./16579/191425.7 ./16579/191425.8 ./16579/191425.9 # vmstat 1 procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu---- r b swpd free buff cache si so bi bo in cs us sy id wa 0 1 30336 46264 60 7882356 0 0 250 299 1 1 6 2 87 5 0 1 30336 47412 60 7881308 0 0 2896 48 944 4861 3 2 71 24 0 2 30336 46696 60 7882188 0 0 816 4 840 5019 1 0 75 24 0 1 30336 49228 60 7879868 0 0 1888 164 971 5687 1 1 74 24 0 1 30336 49688 60 7878916 0 0 2640 48 1047 5751 1 0 75 23 autovacuum | on autovacuum_vacuum_cost_delay | -1 autovacuum_vacuum_cost_limit | -1 vacuum_cost_delay | 0 vacuum_cost_limit | 200 vacuum_cost_page_dirty | 20 vacuum_cost_page_hit | 1 vacuum_cost_page_miss | 10
"Jeffrey Baker" <jwbaker@gmail.com> writes:
> This autovacuum has been hammering my server with purely random i/o
> for half a week. The table is only 20GB and the i/o subsystem is good
> for 250MB/s sequential and a solid 5kiops. When should I expect it to
> end (if ever)?
What have you got maintenance_work_mem set to? Which PG version
exactly?
regards, tom lane
On Fri, Apr 18, 2008 at 10:03 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Jeffrey Baker" <jwbaker@gmail.com> writes: > > This autovacuum has been hammering my server with purely random i/o > > for half a week. The table is only 20GB and the i/o subsystem is good > > for 250MB/s sequential and a solid 5kiops. When should I expect it to > > end (if ever)? > > What have you got maintenance_work_mem set to? Which PG version > exactly? This is 8.1.9 on Linux x86_64, # show maintenance_work_mem ; maintenance_work_mem ---------------------- 16384
On Fri, Apr 18, 2008 at 10:32 AM, Jeffrey Baker <jwbaker@gmail.com> wrote: > > On Fri, Apr 18, 2008 at 10:03 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > "Jeffrey Baker" <jwbaker@gmail.com> writes: > > > This autovacuum has been hammering my server with purely random i/o > > > for half a week. The table is only 20GB and the i/o subsystem is good > > > for 250MB/s sequential and a solid 5kiops. When should I expect it to > > > end (if ever)? > > > > What have you got maintenance_work_mem set to? Which PG version > > exactly? > > This is 8.1.9 on Linux x86_64, > > # show maintenance_work_mem ; > maintenance_work_mem > ---------------------- > 16384 That appears to be the default. I will try increasing this. Can I increase it globally from a single backend, so that all other backends pick up the change, or do I have to restart the instance? -jwb
On Fri, Apr 18, 2008 at 10:34 AM, Jeffrey Baker <jwbaker@gmail.com> wrote: > > On Fri, Apr 18, 2008 at 10:32 AM, Jeffrey Baker <jwbaker@gmail.com> wrote: > > > > On Fri, Apr 18, 2008 at 10:03 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > > "Jeffrey Baker" <jwbaker@gmail.com> writes: > > > > This autovacuum has been hammering my server with purely random i/o > > > > for half a week. The table is only 20GB and the i/o subsystem is good > > > > for 250MB/s sequential and a solid 5kiops. When should I expect it to > > > > end (if ever)? > > > > > > What have you got maintenance_work_mem set to? Which PG version > > > exactly? > > > > This is 8.1.9 on Linux x86_64, > > > > # show maintenance_work_mem ; > > maintenance_work_mem > > ---------------------- > > 16384 > > That appears to be the default. I will try increasing this. Can I > increase it globally from a single backend, so that all other backends > pick up the change, or do I have to restart the instance? I increased it to 1GB, restarted the vacuum, and system performance seems the same. The root of the problem, that an entire CPU is in the iowait state and the storage device is doing random i/o, is unchanged: r b swpd free buff cache si so bi bo in cs us sy id wa 1 1 30328 53632 60 6914716 0 0 904 2960 1216 4720 1 1 74 23 0 1 30328 52492 60 6916036 0 0 1152 1380 948 3637 0 0 75 24 0 1 30328 49600 60 6917680 0 0 1160 1420 1055 4191 1 1 75 24 0 1 30328 49404 60 6919000 0 0 1048 1308 1133 5054 2 2 73 23 0 1 30328 47844 60 6921096 0 0 1552 1788 1002 3701 1 1 75 23 At that rate it will take a month. Compare the load generated by create table foo as select * from bar: r b swpd free buff cache si so bi bo in cs us sy id wa 2 2 30328 46580 60 6911024 0 0 145156 408 2006 10729 52 8 17 23 3 1 30328 46240 60 6900976 0 0 133312 224 1834 10005 23 12 42 23 1 3 30328 60700 60 6902056 0 0 121480 172 1538 10629 22 14 32 32 1 2 30328 49520 60 6914204 0 0 122344 256 1408 14374 13 17 41 28 1 2 30328 47844 60 6915960 0 0 127752 248 1313 9452 16 15 42 27 That's rather more like it. I guess I always imagined that VACUUM was a sort of linear process, not random, and that it should proceed at sequential scan speeds. -jwb
Jeffrey Baker escribió: > That's rather more like it. I guess I always imagined that VACUUM was > a sort of linear process, not random, and that it should proceed at > sequential scan speeds. It's linear for the table, but there are passes for indexes which are random in 8.1. That code was rewritten by Heikki Linnakangas to do linear passes for indexes in 8.2 AFAIR. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
"Jeffrey Baker" <jwbaker@gmail.com> writes:
> I increased it to 1GB, restarted the vacuum, and system performance
> seems the same. The root of the problem, that an entire CPU is in the
> iowait state and the storage device is doing random i/o, is unchanged:
Yeah, but you just reduced the number of index scans that will be needed
by a factor of 1GB/16MB. Hang in there ...
regards, tom lane
Jeffrey Baker wrote: > On Fri, Apr 18, 2008 at 10:32 AM, Jeffrey Baker <jwbaker@gmail.com> wrote: >> # show maintenance_work_mem ; >> maintenance_work_mem >> ---------------------- >> 16384 > > That appears to be the default. I will try increasing this. Can I > increase it globally from a single backend, so that all other backends > pick up the change, or do I have to restart the instance? You can change it in the config file, and send postmaster the HUP signal, which tells all backends to reload the file. "killall -HUP postmaster" or similar. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com