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

Search Discussions

  • Usama Dar at Dec 5, 2007 at 9:35 am

    On Dec 5, 2007 12:53 PM, Cedric BUSCHINI 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.


    Cedric

    ---------------------------(end of broadcast)---------------------------
    TIP 3: Have you checked our extensive FAQ?

    http://www.postgresql.org/docs/faq
    --
    Usama Munir Dar http://linkedin.com/in/usamadar
    Consultant Architect
    Cell:+92 321 5020666
    Skype: usamadar
  • Cedric BUSCHINI at Dec 5, 2007 at 10:05 am
    Usama Dar a écrit :

    On Dec 5, 2007 12:53 PM, Cedric BUSCHINI 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 ?
  • Brad Nicholson at Dec 6, 2007 at 2:38 pm

    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
    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.
  • Cedric BUSCHINI at Dec 7, 2007 at 9:30 am

    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
    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
  • Brad Nicholson at Dec 7, 2007 at 4:18 pm

    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
    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.
  • Plu tard at Dec 12, 2007 at 9:33 am
    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!
    http://club.live.com/chicktionary.aspx?icid=chick_wlhmtextlink1_dec

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedDec 5, '07 at 7:53a
activeDec 12, '07 at 9:33a
posts7
users4
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase