Обсуждение: Vacuum Problem

Hello all,

I have a problem. The following message keeps appearing in logs :

---------------------------------------------------------------------------------------------------------------------------
WARNING:  database "data_base" must be vacuumed within 2606182 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in
"data_base".

---------------------------------------------------------------------------------------------------------------------------

Last week I have run : 'vacuumdb -vz -U postgres -ddata_base'
Today it's still listed in 'ps aux'

---------------------------------------------------------------------------------------------------------------------------
postgres  2113  1.0  1.9  26256 20132 ?        D    Nov26 130:14
postgres: postgres data_base [local] VACUUM
root       2146  0.0  0.1   4616  1508 tty1     Ss   Nov28   0:00 -bash
root      2176  0.0  0.0   4648   996 tty1     S+   Nov28   0:00
vacuumdb -vz -U postgres -ddata_base
postgres  2177  0.0  0.3  10260  3572 ?        S    Nov28   7:12
postgres: postgres data_base [local] VACUUM waiting

---------------------------------------------------------------------------------------------------------------------------

In pg_stat_activity I can see two lines mentionning 'vacuum verbose analyze'

What can I do ??
I really need help

Cedric

--
Usama Munir Dar http://linkedin.com/in/usamadar
Consultant Architect
Cell:+92 321 5020666
Skype: usamadar

Usama Dar a écrit :
>
>
> On Dec 5, 2007 12:53 PM, Cedric BUSCHINI <cbuschini@carax.com
> <mailto:cbuschini@carax.com>> wrote:
>
>     Hello all,
>
>     I have a problem. The following message keeps appearing in logs :
>
---------------------------------------------------------------------------------------------------------------------------
>     WARNING:  database "data_base" must be vacuumed within 2606182
>     transactions
>     HINT:  To avoid a database shutdown, execute a full-database VACUUM in
>     "data_base".
>
---------------------------------------------------------------------------------------------------------------------------
>
>
>     Last week I have run : 'vacuumdb -vz -U postgres -ddata_base'
>     Today it's still listed in 'ps aux'
>
---------------------------------------------------------------------------------------------------------------------------
>
>     postgres  2113  1.0  1.9  26256 20132 ?        D    Nov26 130:14
>     postgres: postgres data_base [local] VACUUM
>     root       2146  0.0  0.1   4616  1508 tty1     Ss   Nov28   0:00
>     -bash
>     root      2176  0.0  0.0   4648   996 tty1     S+   Nov28   0:00
>     vacuumdb -vz -U postgres -ddata_base
>     postgres  2177  0.0  0.3  10260  3572 ?        S    Nov28   7:12
>     postgres: postgres data_base [local] VACUUM waiting
>
---------------------------------------------------------------------------------------------------------------------------
>
>
>     In pg_stat_activity I can see two lines mentionning 'vacuum
>     verbose analyze'
>
>     What can I do ??
>     I really need help
>
>
> it seems to me that you need to vacuum more frequently, and the hint
> seems to point you to a vacuum full for now, try "vacuumdb -avz", but
> beaware it can be time & resource consuming.
So your advice is to stop the running vacuum and run 'vacuum -avz' ?
My actual question is the running processes are doing something or not ?

On Wed, 2007-12-05 at 11:04 +0100, Cedric BUSCHINI wrote:
> Usama Dar a écrit :
> >
> >
> > On Dec 5, 2007 12:53 PM, Cedric BUSCHINI <cbuschini@carax.com
> > <mailto:cbuschini@carax.com>> wrote:
> >
> >     Hello all,
> >
> >     I have a problem. The following message keeps appearing in logs :
> >
---------------------------------------------------------------------------------------------------------------------------
> >     WARNING:  database "data_base" must be vacuumed within 2606182
> >     transactions
> >     HINT:  To avoid a database shutdown, execute a full-database VACUUM in
> >     "data_base".
> >
---------------------------------------------------------------------------------------------------------------------------
> >
> >
> >     Last week I have run : 'vacuumdb -vz -U postgres -ddata_base'
> >     Today it's still listed in 'ps aux'
> >
---------------------------------------------------------------------------------------------------------------------------
> >
> >     postgres  2113  1.0  1.9  26256 20132 ?        D    Nov26 130:14
> >     postgres: postgres data_base [local] VACUUM
> >     root       2146  0.0  0.1   4616  1508 tty1     Ss   Nov28   0:00
> >     -bash
> >     root      2176  0.0  0.0   4648   996 tty1     S+   Nov28   0:00
> >     vacuumdb -vz -U postgres -ddata_base
> >     postgres  2177  0.0  0.3  10260  3572 ?        S    Nov28   7:12
> >     postgres: postgres data_base [local] VACUUM waiting
> >
---------------------------------------------------------------------------------------------------------------------------
> >
> >
> >     In pg_stat_activity I can see two lines mentionning 'vacuum
> >     verbose analyze'
> >
> >     What can I do ??
> >     I really need help
> >
> >
> > it seems to me that you need to vacuum more frequently, and the hint
> > seems to point you to a vacuum full for now, try "vacuumdb -avz", but
> > beaware it can be time & resource consuming.
> So your advice is to stop the running vacuum and run 'vacuum -avz' ?
> My actual question is the running processes are doing something or not ?
>

First off  - what version of Postgres?

My guess is, if you are getting the warning about the database needing
to be vacuumed in x transactions, you probably have a fairly high
traffic DB that is not getting vacuumed often enough.  That means vacuum
is going to have a whole lot of work to do.  That's going to take a
while.

A couple of things to check.

Do you have any of the vacuum_cost_delay stuff on?  This will make
vacuums go slower, but they will take less IO.  If that is on, you might
want to turn the values down or off, but be prepared to see your
database IO usage go through the roof.

What is your maintenance_work_mem set to?  Bumping this value may
increase the speed that your vacuum will run.  You would have to restart
the vacuum after changing the setting though.

--
Brad Nicholson  416-673-4106
Database Administrator, Afilias Canada Corp.

Brad Nicholson a écrit :
> On Wed, 2007-12-05 at 11:04 +0100, Cedric BUSCHINI wrote:
>
>> Usama Dar a écrit :
>>
>>> On Dec 5, 2007 12:53 PM, Cedric BUSCHINI <cbuschini@carax.com
>>> <mailto:cbuschini@carax.com>> wrote:
>>>
>>>     Hello all,
>>>
>>>     I have a problem. The following message keeps appearing in logs :
>>>
---------------------------------------------------------------------------------------------------------------------------
>>>     WARNING:  database "data_base" must be vacuumed within 2606182
>>>     transactions
>>>     HINT:  To avoid a database shutdown, execute a full-database VACUUM in
>>>     "data_base".
>>>
---------------------------------------------------------------------------------------------------------------------------
>>>
>>>
>>>     Last week I have run : 'vacuumdb -vz -U postgres -ddata_base'
>>>     Today it's still listed in 'ps aux'
>>>
---------------------------------------------------------------------------------------------------------------------------
>>>
>>>     postgres  2113  1.0  1.9  26256 20132 ?        D    Nov26 130:14
>>>     postgres: postgres data_base [local] VACUUM
>>>     root       2146  0.0  0.1   4616  1508 tty1     Ss   Nov28   0:00
>>>     -bash
>>>     root      2176  0.0  0.0   4648   996 tty1     S+   Nov28   0:00
>>>     vacuumdb -vz -U postgres -ddata_base
>>>     postgres  2177  0.0  0.3  10260  3572 ?        S    Nov28   7:12
>>>     postgres: postgres data_base [local] VACUUM waiting
>>>
---------------------------------------------------------------------------------------------------------------------------
>>>
>>>
>>>     In pg_stat_activity I can see two lines mentionning 'vacuum
>>>     verbose analyze'
>>>
>>>     What can I do ??
>>>     I really need help
>>>
>>>
>>> it seems to me that you need to vacuum more frequently, and the hint
>>> seems to point you to a vacuum full for now, try "vacuumdb -avz", but
>>> beaware it can be time & resource consuming.
>>>
>> So your advice is to stop the running vacuum and run 'vacuum -avz' ?
>> My actual question is the running processes are doing something or not ?
>>
>>
>
> First off  - what version of Postgres?
>
> My guess is, if you are getting the warning about the database needing
> to be vacuumed in x transactions, you probably have a fairly high
> traffic DB that is not getting vacuumed often enough.  That means vacuum
> is going to have a whole lot of work to do.  That's going to take a
> while.
>
> A couple of things to check.
>
> Do you have any of the vacuum_cost_delay stuff on?  This will make
> vacuums go slower, but they will take less IO.  If that is on, you might
> want to turn the values down or off, but be prepared to see your
> database IO usage go through the roof.
>
> What is your maintenance_work_mem set to?  Bumping this value may
> increase the speed that your vacuum will run.  You would have to restart
> the vacuum after changing the setting though.
>
>
Brad,

It's a 8.1.5
About these settings, these are both off ...
Should I turn them on ?

Because of the message, the database isn't used ...


--

 Cedric BUSCHINI
    - CARAX -
  IT Department

Phone : + 33 1 4006 9864
fax   : + 33 1 4006 9865

On Fri, 2007-12-07 at 10:30 +0100, Cedric BUSCHINI wrote:
> Brad Nicholson a écrit :
> > On Wed, 2007-12-05 at 11:04 +0100, Cedric BUSCHINI wrote:
> >
> >> Usama Dar a écrit :
> >>
> >>> On Dec 5, 2007 12:53 PM, Cedric BUSCHINI <cbuschini@carax.com
> >>> <mailto:cbuschini@carax.com>> wrote:
> >>>
> >>>     Hello all,
> >>>
> >>>     I have a problem. The following message keeps appearing in logs :
> >>>
---------------------------------------------------------------------------------------------------------------------------
> >>>     WARNING:  database "data_base" must be vacuumed within 2606182
> >>>     transactions
> >>>     HINT:  To avoid a database shutdown, execute a full-database VACUUM in
> >>>     "data_base".
> >>>
---------------------------------------------------------------------------------------------------------------------------
> >>>
> >>>
> >>>     Last week I have run : 'vacuumdb -vz -U postgres -ddata_base'
> >>>     Today it's still listed in 'ps aux'
> >>>
---------------------------------------------------------------------------------------------------------------------------
> >>>
> >>>     postgres  2113  1.0  1.9  26256 20132 ?        D    Nov26 130:14
> >>>     postgres: postgres data_base [local] VACUUM
> >>>     root       2146  0.0  0.1   4616  1508 tty1     Ss   Nov28   0:00
> >>>     -bash
> >>>     root      2176  0.0  0.0   4648   996 tty1     S+   Nov28   0:00
> >>>     vacuumdb -vz -U postgres -ddata_base
> >>>     postgres  2177  0.0  0.3  10260  3572 ?        S    Nov28   7:12
> >>>     postgres: postgres data_base [local] VACUUM waiting
> >>>
---------------------------------------------------------------------------------------------------------------------------
> >>>
> >>>
> >>>     In pg_stat_activity I can see two lines mentionning 'vacuum
> >>>     verbose analyze'
> >>>
> >>>     What can I do ??
> >>>     I really need help
> >>>
> >>>
> >>> it seems to me that you need to vacuum more frequently, and the hint
> >>> seems to point you to a vacuum full for now, try "vacuumdb -avz", but
> >>> beaware it can be time & resource consuming.
> >>>
> >> So your advice is to stop the running vacuum and run 'vacuum -avz' ?
> >> My actual question is the running processes are doing something or not ?
> >>
> >>
> >
> > First off  - what version of Postgres?
> >
> > My guess is, if you are getting the warning about the database needing
> > to be vacuumed in x transactions, you probably have a fairly high
> > traffic DB that is not getting vacuumed often enough.  That means vacuum
> > is going to have a whole lot of work to do.  That's going to take a
> > while.
> >
> > A couple of things to check.
> >
> > Do you have any of the vacuum_cost_delay stuff on?  This will make
> > vacuums go slower, but they will take less IO.  If that is on, you might
> > want to turn the values down or off, but be prepared to see your
> > database IO usage go through the roof.
> >
> > What is your maintenance_work_mem set to?  Bumping this value may
> > increase the speed that your vacuum will run.  You would have to restart
> > the vacuum after changing the setting though.
> >
> >
> Brad,
>
> It's a 8.1.5
> About these settings, these are both off ...
> Should I turn them on ?

No.  Turning them on will make your vacuum go slower.  You don't want
this.

> Because of the message, the database isn't used ...

First, what is your maintenance_work_mem set to?  If you have a decent
amount of memory, you'll want to try setting it high while doing this
vacuum - to something like 100000.  Setting this value up and re-running
the vacuum might get you past this problem.

If you pipe the output from your vacuum command to a file, you'll be
able to see what it is doing.  It would also be really useful for folks
to help you troubleshoot the problem if you could post the verbose
vacuum output so we could see exactly what vacuum is working on.

However, If my earlier theory is correct - lots of updates/deletes and
not enough routine vacuuming being done, then you have a whole lot of
dead tuples in that database.  Which presents a different problem -
database bloat.

Ideally, you would want to identify if your tables are bloated or not
and act accordingly.  The output from vacuum would tell tell this.

However, if the database is not being used, then you have another other
option.

You can use the cluster command to clear out the dead tuples a lot
quicker that the vacuum will.  Cluster will take an access exclusive
lock on the table, and psychically reorganize the data on the disk.  It
will also get rid of all the dead tuples, and compact the physical
layout of your DB.  You will still need to vacuum the whole database to
deal with the "You must vacuum the database" issue, but the vacuum would
go faster.

Check out the documentation for cluster
http://www.postgresql.org/docs/8.1/interactive/sql-cluster.html

--
Brad Nicholson  416-673-4106
Database Administrator, Afilias Canada Corp.

I seem to be having a similar problem. I'm trying to run a vacuum full on one
of my development machines and it seems really slow.

Take a look at this 'vmstat 1'  output. It seems to be pegged in iowait:

 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa
 1 11  34664  55388   6652 1604836    0    0     0     0 1247 3202  1  4  0 95
 0 11  34664  55760   6652 1604836    0    0     0     0 1182 3165  1  4  0 95
 0 11  34664  56256   6652 1604836    0    0     0     0 1227 3296  0  6  0 94
 0 12  34664  56752   6652 1604836    0    0     0     0 1367 3814  1  4  0 95
 0 12  34664  57372   6652 1604836    0    0     0     0 1174 3173  0  3  0 97
 0 12  34664  57868   6652 1604836    0    0     0     0 1229 3169  1  4  0 95
 0 12  34664  58240   6652 1604836    0    0     0     0 1282 3469  0  5  0 95
 0 12  34664  58736   6652 1604836    0    0     0     0 1007 2428  1  4  0 95
 0 12  34664  59232   6652 1604836    0    0     0     0 1246 3205  1  6  0 93
 0 12  34664  59728   6652 1604836    0    0     0     0 1072 2895  1  3  0 96
 0 12  34664  60100   6652 1604836    0    0     0     0 1182 3149  1  4  0 95
 0 12  34664  60720   6652 1604836    0    0     0     0 1232 3352  0  8  0 92
 0 12  34664  61092   6652 1604836    0    0     0     0 1166 2836  1  4  0 95
 0 12  34664  61464   6652 1604836    0    0     0     0 1059 2696  0  3  0 97
 6 12  34664  61960   6652 1604836    0    0     0     0 1066 2471  1  4  0 95
 0 12  34664  62580   6652 1604836    0    0     0     0 1309 3624  1  6  0 93
 0 12  34664  62952   6652 1604836    0    0     0     0 1121 2885  0  4  0 96
 0 12  34664  63324   6652 1604836    0    0     0     0  925 1999  1  5  0 94
 0 12  34664  63944   6652 1604836    0    0     0     0 1102 2938  1  2  0 97
 0 12  34664  64440   6652 1604836    0    0     0    32 1016 2609  1  1  0 98
 0 12  34664  64796   6652 1604836    0    0     0     0 1020 2684  3  6  0 91
procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa
 7 12  34664  65244   6652 1604836    0    0     0     0 1094 3137 10  4  0 86
 0 12  34664  65740   6652 1604836    0    0     0  1144 1135 2932  2  5  0 93
 3 12  34664  64872   6652 1604836    0    0     0  4800 1029 3182 33  9  0 58
 0  9  34664  65740   6652 1604836    0    0     0  6932  814 1931  5  5  0 90
 4  5  34664  65120   6664 1606056    0    0     8  3184  809  813 35 14  0 51
 0  6  34664  45344   6684 1606232    0    0   152 164320 1216  612 19 17  0 64
 1 12  34664  44912   6688 1607888    0    0    44     0  690  899 31 15  0 54
 0 13  34664  44264   6696 1608220    0    0    68     0  983 1508  3  6  0 91
 0 13  34664  43832   6696 1608532    0    0    72     0 1052 1415  6  5  0 89

Is that normal? Does it look like a hardware misconfiguration of some sort?

Running Pg 8.2.5 on Ubuntu 7.04 with 2GB ram.
I have two IDE disks running software RAID 1.

postgresql.conf is hasn't been changed much except I increased
effective_cache_size to 1G. I also tried raising maintenance_work_mem
based on a previous message in this thread, but it didn't seem to make
any improvement.

/sbin/hdparm /dev/hda:
 multcount    = 16 (on)
 IO_support   =  1 (32-bit)
 unmaskirq    =  1 (on)
 using_dma    =  1 (on)
 keepsettings =  0 (off)
 readonly     =  0 (off)
 readahead    = 256 (on)
 geometry     = 24792/255/63, sectors = 398297088, start = 0

I have each disk on a separate IDE channel, each configured as master.
One cable has a DVD on it too set as slave.

I have a similar machine, also with two disk RAID 1, and it seems to run
vacuum full much faster. It's on CentOS 4 with Pg 8.2.4. Same config
except it's using effective_cache_size of 2GB with 4GB total ram.

Any ideas for what to investigate?

Thanks.





D

---

Don't get caught with egg on your face. Play Chicktionary! Check it out!