Обсуждение: vacuumdb question/problem

Hello,

I am new to this list and hope I have chosen the appropriate group to ask this question.

We are running version 8.2.6 of postgres and I am trying to run a full vacuum on a single table in our database. I started the vacuum about 24 hours ago and it is still running.  Within 2-3 hrs of running, the following info was returned:

INFO:  vacuuming "public.pecrsep"
INFO:  "pecrsep": found 33781 removable, 10389467 nonremovable row versions in 35318465 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 1633 to 1637 bytes long.
There were 130850585 unused item pointers.
Total free space (including removable row versions) is 271020146144 bytes.
30292740 pages are or will become empty, including 0 at the end of the table.
35318465 pages containing 271020146144 free bytes are potential move destinations.
CPU 236.49s/90.56u sec elapsed 3954.18 sec.
INFO:  index "pecrsep_time_ind" now contains 10389467 row versions in 400823 pages
DETAIL:  33781 index row versions were removed.
7202 index pages have been deleted, 7202 are currently reusable.
CPU 3.08s/6.87u sec elapsed 73.42 sec.
INFO:  index "pecrsep_pk" now contains 10389467 row versions in 294694 pages
DETAIL:  30179 index row versions were removed.
46 index pages have been deleted, 46 are currently reusable.
CPU 2.52s/9.90u sec elapsed 49.47 sec.

Since then, the process has continued to run (for about 20 hrs) without any additional information being returned.

The question I have about the vacuumdb is, does it scan through the table and identify what actions it will take...return the info listed above, THEN do the actual vacuum?  Or did it complete the vacuum before it returned the information?

The table I am running the full vacuum on has been accumulating data for a couple years and a full vacuum has never been run (to my knowledge). In addition, over the last 6 months I posted over 200 million records to this table.  Thus, I am not sure if the process is hung or if it is just taking a long time since the table has never had a full vacuum and has had a ton of records posted recently.

Any thoughts are appreciated. Should I let it continue to run or kill the process?  It was suggested to me that I may want to kill the process and run a CLUSTER on the table...then re-run the full vacuum.

I realize we are on a old version of postgres, but I am locked into this with no chance to upgrade.

Thanks in advance.

On Fri, Jul 15, 2011 at 5:10 PM, David Ondrejik <David.Ondrejik@noaa.gov> wrote:

> Since then, the process has continued to run (for about 20 hrs) without any
> additional information being returned.

Probably locked behind another long running task that is holding a buffer pin.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

  Simon - thanks for the response. I checked all the processes and
nothing appears to be holding it up.  Any other advice?

Simon Riggs said the following on 7/15/2011 12:21 PM:
> On Fri, Jul 15, 2011 at 5:10 PM, David Ondrejik<David.Ondrejik@noaa.gov>  wrote:
>
>> Since then, the process has continued to run (for about 20 hrs) without any
>> additional information being returned.
> Probably locked behind another long running task that is holding a buffer pin.
>

Simon Riggs <simon@2ndQuadrant.com> writes:
> On Fri, Jul 15, 2011 at 5:10 PM, David Ondrejik <David.Ondrejik@noaa.gov> wrote:
>> Since then, the process has continued to run (for about 20 hrs) without any
>> additional information being returned.

> Probably locked behind another long running task that is holding a buffer pin.

That's possible, or it could be busy vacuuming some (really large?)
index.  Is the process actually busy, as in consuming CPU time according
to top or other process monitoring tool?

            regards, tom lane

  Tom,

It was not consuming any CPU time. I found that another process on the
machine failed. That process was trying to write to a different table
(not the one I was vacuuming) in the database and that table was locked
(not sure why).  It produced thousands of errors which caused the log
files to grow large enough that the entire disk was filled, and this
brought everything to a halt.

So I had to kill my process, recover disk space and get the machine back
in working condition for the weekend. I guess I will attempt to do the
full vacuum again next week.

I am still wondering how the vacuum process actually works. When it
throws the output lines that show how many rows are
recoverable/nonremovable, does this mean that the vacuum has completed?
Or do those output lines show the vacuum has scanned through the
database...found what what it is going to recover and then takes on the
vacuum process?

Thanks,
Dave

Tom Lane said the following on 7/15/2011 1:12 PM:
> Simon Riggs<simon@2ndQuadrant.com>  writes:
>> On Fri, Jul 15, 2011 at 5:10 PM, David Ondrejik<David.Ondrejik@noaa.gov>  wrote:
>>> Since then, the process has continued to run (for about 20 hrs) without any
>>> additional information being returned.
>> Probably locked behind another long running task that is holding a buffer pin.
> That's possible, or it could be busy vacuuming some (really large?)
> index.  Is the process actually busy, as in consuming CPU time according
> to top or other process monitoring tool?
>
>             regards, tom lane

David Ondrejik <David.Ondrejik@noaa.gov> writes:
> I am still wondering how the vacuum process actually works. When it
> throws the output lines that show how many rows are
> recoverable/nonremovable, does this mean that the vacuum has completed?

No, that's just the end of the first pass over the table.  After that it
cleans out dead index entries, and then it sets about moving rows down
to fill free space.

As far as your original question is concerned: if you have reason to
think there's a lot of bloat, CLUSTER would be the thing to use.
Old-style VACUUM FULL is seriously inefficient when there's a lot of
rows that have to be moved.

            regards, tom lane

David Ondrejik <David.Ondrejik@noaa.gov> wrote:

> So I had to kill my process, recover disk space and get the
> machine back in working condition for the weekend. I guess I will
> attempt to do the full vacuum again next week.

Why do you think that you need a VACUUM FULL?  That is only needed
as an extreme measure in rather unusual circumstances.  We run
hundreds of PostgreSQL databases, and have not used VACUUM FULL for
years.  On the other hand, we VACUUM ANALYZE most databases in their
entirety every night.  (Be very careful not to confuse VACUUM FULL
with vacuum of an entire database.)

On the other hand, the failed attempt at VACUUM FULL may have
bloated your indexes, so you might want to REINDEX the table next
week, followed by a VACUUM ANALYZE (without the FULL).

-Kevin

Thanks to everyone for their response and help.  I still have some more questions that hopefully someone can help me with as I have not yet been able to solve my vacuumdb problem.

The posting of data to the table in question is extremely slow...yesterday I saw that it took over 6 min to post just 124 rows of data. That is just not acceptable. Additionally, we have about 9,000 to 11,000 products that come in daily (some contain one row of data...others may be several hundred lines) and are posted to the database. The system used to run with maybe 10-100 products in the queue (that is before I posted over 200 million rows). Yesterday, there were over 25,000 products in the queue waiting to be processed - which is bad for our operational use.

I think the table got even more bloated when I tried to do the vacuum full last week and had to kill the process - it never completed.

From the info I received from previous posts, I am going to change my game plan. If anyone has thoughts as to different process or can confirm that I am on the right track, I would appreciate your input.

1. I am going to run a CLUSTER on the table instead of a VACUUM FULL. But I have a few questions that are not clear from the info I have found on the internet regarding this process.  The table name is 'pecrsep', and the database is hd_ob92rha. It has multiple columns in the table, but I am choosing the 'obsdate' as my index (I believe 'index' refers to the name of a column in the table?).  Some of the columns are listed:

  lid  | pe1 | pe2 | dur | idur | t | s | e | p |  obstime   | z0000 | z0015 | z0030 | z0045 | z0100 | z0115 | z0130 ... etc

Anyway, if I interpret the info correctly the first time a CLUSTER is run, I have to run something like:

CLUSTER obsdate ON pecrsep (CLUSTER indexname ON tablename)

Is it true that the first time I run a CLUSTER, I must provide an 'indexname' or can I just run: CLUSTER pecrsep (CLUSTER tablename)? Or is it better to provide an indexname?

2. I know there are a lot of variables such as table size, etc. Are we looking at something that will take couple hours...or 24+ hours? 

Is there a way to monitor the CLUSTER process to ensure its working?

Is there any way to get output to give an estimate as to how much of the CLUSTER has completed...and how much is left to run?

3. With the info from previous posts, I am certainly rethinking the use of VACUUM FULL.

After I run the CLUSTER, should I REINDEX the table, or is that redundant to the CLUSTER?

If I run the REINDEX, I would do it just for the single table: REINDEX TABLE pecrsep;

Any idea as to how long this may take (i.e 1-2 hours or 24+ hours)?

4. If either/both the CLUSTER or REINDEX are successful, that would indicate that I don't need to run a VACUUM FULL...correct?

5. Then I would run a VACUUM ANALYZE as suggested.

Does this sound like an appropriate plan?

After I do all this on the single table, should I repeat something similar for the whole database? Or should I just attack the largest tables?

One last question, can anyone recommend a good postgres book for me to purchase? I guess I should repeat that we are still running version 8.2.6 of postgres on Linux machines. Is there an appropriate book for this version available?

Thanks again for your help...I hope I didn't ask too many questions, but the database is in poor shape and I need to get it working more efficiently quickly.

Best Regards,
Dave

David Ondrejik <David.Ondrejik@noaa.gov> wrote:

> The posting of data to the table in question is extremely
> slow...yesterday I saw that it took over 6 min to post just 124
> rows of data. That is just not acceptable. Additionally, we have
> about 9,000 to 11,000 products that come in daily (some contain
> one row of data...others may be several hundred lines) and are
> posted to the database. The system used to run with maybe 10-100
> products in the queue (that is before I posted over 200 million
> rows). Yesterday, there were over 25,000 products in the queue
> waiting to be processed - which is bad for our operational use.

Sounds like bloat.

> I think the table got even more bloated when I tried to do the
> vacuum full last week and had to kill the process - it never
> completed.

VACUUM FULL can increase bloat if it doesn't complete.  Even when it
completes it tends to bloat indexes.

> From the info I received from previous posts, I am going to change
> my game plan. If anyone has thoughts as to different process or
> can confirm that I am on the right track, I would appreciate your
> input.
>
> 1. I am going to run a CLUSTER on the table instead of a VACUUM
> FULL.

If you have room for a second copy of your data, that is almost
always much faster, and less prone to problems.

> But I have a few questions that are not clear from the info I have
> found on the internet regarding this process.  The table name is
> 'pecrsep', and the database is hd_ob92rha. It has multiple columns
> in the table, but I am choosing the 'obsdate' as my index (I
> believe 'index' refers to the name of a column in the table?).

No, it refers to the name of an index.  For example, you might have
an index to support your primary key declaration named
"pecrsep_pkey".  If you are using psql, type

\d pecrsep

When I do that for our "Party" table in our statewide copy of the
data, I see these lines near the bottom:

Indexes:
    "Party_pkey" PRIMARY KEY, btree ("countyNo", "caseNo",
"partyNo") CLUSTER
    "Party_EAccountNo" btree ("countyNo", "eAccountNo")
    "Party_SearchName" btree ("searchName", "countyNo")

So I could choose to cluster this table using "Party_pkey",
"Party_EAccountNo", or "Party_SearchName".  The primary key has been
set as the default if I don't specify an index.

> Anyway, if I interpret the info correctly the first time a CLUSTER
> is run, I have to run something like:
>
> CLUSTER obsdate ON pecrsep (CLUSTER /indexname/ ON /tablename/)

For 8.2 that is correct.  (In more recent versions that syntax is
deprecated in favor of a new wording which is more natural for most
people.)

> Is it true that the first time I run a CLUSTER, I must provide an
> 'indexname' or can I just run: CLUSTER pecrsep (CLUSTER
> tablename)? Or is it better to provide an indexname?

If an index name has been set for a default on that table, and
that's the index you want to use, it really doesn't matter.

> 2. I know there are a lot of variables such as table size, etc.
> Are we looking at something that will take couple hours...or 24+
> hours?

Before I gave up on VACUUM FULL as an overall bad idea for us, I had
left a VACUUM FULL on a large table (which had some bloat) running
when I left work Friday, and found it still running Monday morning.
I canceled the VACUUM FULL, noticed it was now *further* bloated,
and used CLUSTER.  In our case, with that table, it finished in a
few hours.

> Is there a way to monitor the CLUSTER process to ensure its
> working?

I guess you could look at the generated files, but I've never heard
of it failing, short of running the data area out of space.

> Is there any way to get output to give an estimate as to how much
> of the CLUSTER has completed...and how much is left to run?

If you calculated an estimate of the non-bloated heap space you
could watch the files being created for the new copy as it runs.
Remember that all the indexes also need to be built after the heap
is populated.

> 3. With the info from previous posts, I am certainly rethinking
> the use of VACUUM FULL.

Good.

> After I run the CLUSTER, should I REINDEX the table, or is that
> redundant to the CLUSTER?

That would be redundant.  At completion of the CLUSTER, the table
has brand new, freshly built indexes.

> Any idea as to how long this may take (i.e 1-2 hours or 24+
> hours)?

If you know the speed of your drives, you can set a lower bound on
it, I guess.  *At least* as much time as it takes to read the entire
table through the specified index, plus the time to sequentially
write the un-bloated heap, plus the time to build all indexes on the
table.  Caching effects can make this tricky to estimate.

> 4. If either/both the CLUSTER or REINDEX are successful, that
> would indicate that I don't need to run a VACUUM FULL...correct?

REINDEX would not eliminate heap bloat.

> 5. Then I would run a VACUUM ANALYZE as suggested.

Be sure to run autovacuum.  You probably want to run it with
settings more aggressive than the 8.2 defaults.  We generally
supplement that with a nightly or weekly VACUUM ANALYZE VERBOSE.
That was especially important prior to 8.4, as you would get
information at the end of the listing to help determine when you
should adjust the free space manager (fsm) allocations.  (In 8.4 and
later such allocations are automatic, so you no longer need to worry
about setting these high enough to prevent ongoing performance
degeneration.)

> After I do all this on the single table, should I repeat something
> similar for the whole database? Or should I just attack the
> largest tables?

All tables which are being modified should be regularly vacuumed and
analyzed.  CLUSTER is only indicated for correcting bloat and
certain particular usage patterns (where there is an index on which
a number of consecutive rows are frequently accessed).

> One last question, can anyone recommend a good postgres book for
> me to purchase?

Yes.  "PostgreSQL 9.0 High Performance" and "PostgreSQL 9
Administration Cookbook":

http://www.postgresql.org/docs/books/

> I guess I should repeat that we are still running version
> 8.2.6 of postgres on Linux machines.

It would still be a good idea to update to 8.2.recent (which doesn't
require a conversion or introduce changes in behavior beyond bug and
security fixes).  Once you get things stabilized you should probably
get 9.0 or 9.1 on your radar.  (At this point I wouldn't consider
just going to 8.3 or 8.4 anymore.)  Much better performance, many
cool new features,, and keeping within the support window all make
it worthwhile.

> Is there an appropriate book for this version available?

The books I mentioned above do cover older versions.

-Kevin

  I think I see a (my) fatal flaw that will cause the cluster to fail.


>>  From the info I received from previous posts, I am going to change
>> my game plan. If anyone has thoughts as to different process or
>> can confirm that I am on the right track, I would appreciate your
>> input.
>>
>> 1. I am going to run a CLUSTER on the table instead of a VACUUM
>> FULL.
Kevin Grittner stated:
> If you have room for a second copy of your data, that is almost
> always much faster, and less prone to problems.

I looked at the sizes for the tables in the database and the table I am
trying to run the cluster on is 275G and I only have 57G free.  I don't
know how much of that 275G has data in it and how much is empty to allow
for a second copy of the data. I am guessing the cluster would fail due
to lack of space.

Are there any other options??

If I unload the table to a flat file; then drop the table from the
database; then recreate the table; and finally reload the data - will
that reclaim the space?

Kevin - thanks for the book recommendation.  Will order it tomorrow.

Thanks again for all the technical help!

Dave

Dave,

You're on the right track now, however, unloading the table to a flat file using pg_dump may create a very large flat
file. Make sure you use compression ("-Fc" or pipe the dump through gzip, which will use two CPU's, one for pg_dump and
onefor gzip) on the dump file to minimize its size.  If the dump is successful you don't have to drop the table, you
canjust truncate it and that will recover the used space and hand it back to the file system.  Truncate is very fast,
butthen again so is drop table.  Be careful.  You can then use pg_restore to put the data  back into the original table
andthat will reclaim the space. 

Take what Kevin said earlier about autovacuum and possible scheduled vacuum analyze verbose jobs to make dead space
reusablevery, very seriously.  Upgrading to 8.4 will remove any need to manually manage the free space map in 8.2 and
isworth it, particularly to get a nicer version of autovacuum, although there are differences in automatic casting of
datatype between 8.2 and 8.4 (and 9.x) that you should test before making a wholesale commitment to upgrading.  The
fixesto your code aren't hard, but need to be done for you to get consistent results pre- and post-upgrade. 

Finally, if there are natural partitions to the data in that table consider using PostgreSQL's partition feature.  What
thosepartitions are depends entirely on your use case(s).  If the technique fits, you would be able to drop and create
newpartitions to clear out and populate data quite quickly.  Check
out http://www.postgresql.org/docs/8.4/interactive/ddl-partitioning.html fordetails of partitioning in PG 8.4. 

Good luck!

Bob Lunney


----- Original Message -----
From: David Ondrejik <David.Ondrejik@noaa.gov>
To: pgsql-admin <pgsql-admin@postgresql.org>
Cc:
Sent: Thursday, July 21, 2011 2:12 PM
Subject: Re: [ADMIN] vacuumdb question/problem

I think I see a (my) fatal flaw that will cause the cluster to fail.


>>  From the info I received from previous posts, I am going to change
>> my game plan. If anyone has thoughts as to different process or
>> can confirm that I am on the right track, I would appreciate your
>> input.
>>
>> 1. I am going to run a CLUSTER on the table instead of a VACUUM
>> FULL.
Kevin Grittner stated:
> If you have room for a second copy of your data, that is almost
> always much faster, and less prone to problems.

I looked at the sizes for the tables in the database and the table I am trying to run the cluster on is 275G and I only
have57G free.  I don't know how much of that 275G has data in it and how much is empty to allow for a second copy of
thedata. I am guessing the cluster would fail due to lack of space. 

Are there any other options??

If I unload the table to a flat file; then drop the table from the database; then recreate the table; and finally
reloadthe data - will that reclaim the space? 

Kevin - thanks for the book recommendation.  Will order it tomorrow.

Thanks again for all the technical help!

Dave


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

David Ondrejik <David.Ondrejik@noaa.gov> wrote:

> I think I see a (my) fatal flaw that will cause the cluster to
> fail.

> Kevin Grittner stated:
>> If you have room for a second copy of your data, that is almost
>> always much faster, and less prone to problems.
>
> I looked at the sizes for the tables in the database and the table
> I am trying to run the cluster on is 275G and I only have 57G
> free.  I don't know how much of that 275G has data in it and how
> much is empty to allow for a second copy of the data. I am
> guessing the cluster would fail due to lack of space.

Seems quite likely although not certain, depending (as you said) on
the level of bloat.

> Are there any other options??
>
> If I unload the table to a flat file; then drop the table from
> the database; then recreate the table; and finally reload the data
> - will that reclaim the space?

Yeah, but you don't necessarily need to go quite that far.  You can
pg_dump an individual table with the -t option (to some medium where
you have room), and then drop and restore the table.  It would be
prudent to make very sure of your dump of the table before dropping
it, of course.  I'm just paranoid enough to probably make sure I
have an up-to-date PITR-style dump, too, before issuing the DROP
TABLE command.

-Kevin