Thread: 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
On Dec 5, 2007 12:53 PM, Cedric BUSCHINI <cbuschini@carax.com> wrote:
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.
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
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.
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!