Thread: Vacuum verbose output?
Is there any documentation on the output of VACUUM VERBOSE please? I've found some mention from Tom of some of the fields in a previous message here... --------------------------------------------------------------- Looking at the code: Tup # tuples remaining after vacuum Vac # tuples removed by vacuum Keep # dead tuples kept because some xact can still see 'em Unused # unused item pointers "Keep" is included in the "Tup" total. The unused item pointers may get recycled later; it looks like that hasn't been determined yet at the point where these stats are printed. --------------------------------------------------------------- but I'd be interested to understand more of NOTICE: Pages 371: Changed 3, reaped 364, Empty 0, New 0; Tup 8180: Vac 1786, Keep/VTL 0/0, UnUsed 6285, MinLen 115, MaxLen 648; Re-using: Free/Avail. Space 622460/621672; EndEmpty/Avail. Pages 0/350. in a little detail. The application is a database where the data in the tables are essentially write-once-read-many, and a tuple stays active for about 3 hours before being deleted/archived. With a regular, simple VACUUM, the number of Pages increases steadily until a VACUUM FULL is performed. But a write-lock on the tables is a real pain, as the entire population mechanism has to be suspended, and it backs up very quickly. In order to understand the space and performance implications of how often we do a VACUUM FULL rather than simple VACUUM, I'd like to understand the output. Example (just successive VACUUM output, longer entries are VACUUM FULL): NOTICE: Pages 371: Changed 3, reaped 364, Empty 0, New 0; Tup 8180: Vac 1786, Keep/VTL 0/0, UnUsed 6285, MinLen 115, MaxLen 648; Re-using: Free/Avail. Space 622460/621672; EndEmpty/Avail. Pages 0/350. NOTICE: Pages 342: Changed 2, Empty 0; Tup 8747: Vac 609, Keep 0, UnUsed 5350. NOTICE: Pages 342: Changed 1, Empty 0; Tup 8443: Vac 732, Keep 0, UnUsed 5572. NOTICE: Pages 342: Changed 0, Empty 0; Tup 8325: Vac 838, Keep 0, UnUsed 5614. NOTICE: Pages 342: Changed 4, Empty 0; Tup 8197: Vac 868, Keep 0, UnUsed 5729. NOTICE: Pages 353: Changed 1, Empty 0; Tup 9159: Vac 413, Keep 0, UnUsed 5508. NOTICE: Pages 354: Changed 1, Empty 0; Tup 8780: Vac 765, Keep 0, UnUsed 5552. NOTICE: Pages 354: Changed 1, Empty 0; Tup 8335: Vac 1179, Keep 0, UnUsed 5597. NOTICE: Pages 354: Changed 1, Empty 0; Tup 8746: Vac 349, Keep 0, UnUsed 6016. NOTICE: Pages 361: Changed 1, Empty 0; Tup 9084: Vac 691, Keep 0, UnUsed 5515. NOTICE: Pages 361: Changed 2, Empty 0; Tup 8632: Vac 899, Keep 0, UnUsed 5785. NOTICE: Pages 361: Changed 0, Empty 0; Tup 8229: Vac 1111, Keep 0, UnUsed 5981. NOTICE: Pages 361: Changed 2, Empty 0; Tup 8563: Vac 329, Keep 0, UnUsed 6429. NOTICE: Pages 363: Changed 4, Empty 0; Tup 8992: Vac 822, Keep 0, UnUsed 5580. NOTICE: Pages 363: Changed 1, reaped 341, Empty 0, New 0; Tup 8411: Vac 1136, Keep/VTL 0/0, UnUsed 5851, MinLen 115, MaxLen 804; Re-using: Free/Avail. Space 428616/426644; EndEmpty/Avail. Pages 0/327. NOTICE: Pages 351: Changed 1, reaped 340, Empty 0, New 0; Tup 7987: Vac 1297, Keep/VTL 0/0, UnUsed 5583, MinLen 115, MaxLen 804; Re-using: Free/Avail. Space 477064/475712; EndEmpty/Avail. Pages 0/321. NOTICE: Pages 337: Changed 0, Empty 0; Tup 8334: Vac 690, Keep 0, UnUsed 5481. It grows steadily until a VACUUM FULL is performed, when it shrinks back. If we never do a VACUUM FULL, will it just keep on growing, and does performance suffer as it does so? Thanks Julian Scarfe
hello, is it possible to estimate the time to restore a database from a dump ? the dump is 85 Mo, the base is 850 Mo, we have 512 Mo RAM and a pentium 3 1.13 GHz. we are running for about 16 hours (20:00 CPU time) but it's really slow, and the database is actually 365 Mo restored. cheers, pascal.
"Julian Scarfe" <julian.scarfe@ntlworld.com> writes:
> The application is a database where the data in the tables are essentially
> write-once-read-many, and a tuple stays active for about 3 hours before
> being deleted/archived. With a regular, simple VACUUM, the number of Pages
> increases steadily until a VACUUM FULL is performed.
What do you have the FSM parameters in postgresql.conf set to? My guess
is you need to increase 'em.
regards, tom lane
On Tue, 2003-01-14 at 04:46, Julian Scarfe wrote: > Is there any documentation on the output of VACUUM VERBOSE please? AFAIK your best bet is to search the mailing archives or look through the source :-( > > NOTICE: Pages 371: Changed 3, reaped 364, Empty 0, New 0; Tup 8180: Vac > 1786, Keep/VTL 0/0, UnUsed 6285, MinLen 115, MaxLen 648; Re-using: > Free/Avail. Space 622460/621672; EndEmpty/Avail. Pages 0/350. > > in a little detail. > All from memory, probably wrong on a couple, so take this with a grain of salt. Pages = the number of files on the system involved in this table. Changed = the number of pages that had to be modified. Reaped mean the number of pages that were made compleltely blank, ready for reuse. Empty = pages that are currently empty, ready for reuse. New = pages that had to be created as a result of vacuuming? > The application is a database where the data in the tables are essentially > write-once-read-many, and a tuple stays active for about 3 hours before > being deleted/archived. With a regular, simple VACUUM, the number of Pages > increases steadily until a VACUUM FULL is performed. But a write-lock on > the tables is a real pain, as the entire population mechanism has to be > suspended, and it backs up very quickly. > Sounds like you need to run vacuum at least every 3 hours. > In order to understand the space and performance implications of how often > we do a VACUUM FULL rather than simple VACUUM, I'd like to understand the > output. > > Example (just successive VACUUM output, longer entries are VACUUM FULL): <snip output> ISTM that at times your vacuuming regularly and actually recovering space, but then there are times when your space grows rather quickly. This is either an indication that you need to time your vacuums better (to coincide with large batch inserts or deletes) or you need to vacuum more often, to recover more space between batches. I should also say that it might also be an indication that your free space map settings aren't set appropriately, make sure max_fsm_relation and max_fsm_pages are set high enough. Without looking at your complete database there's no way to know for sure. (Although max_fsm_relations is almost assuredly to low). > > It grows steadily until a VACUUM FULL is performed, when it shrinks back. > If we never do a VACUUM FULL, will it just keep on growing, and does > performance suffer as it does so? > The thing with vacuum is at some point you should reach a condition where space on the disk increases only when the total number of inserts is greater than the total number of deletions over a given time frame. If it increases when inserts is less than deletes, your not vacuuming enough. If you can reach this level of "symbiosis" with regular vacuums, you should never have to do vacuum full. Robert Treat
On 14/1/03 15:42, "Tom Lane" <tgl@sss.pgh.pa.us> wrote: > "Julian Scarfe" <julian.scarfe@ntlworld.com> writes: >> The application is a database where the data in the tables are essentially >> write-once-read-many, and a tuple stays active for about 3 hours before >> being deleted/archived. With a regular, simple VACUUM, the number of Pages >> increases steadily until a VACUUM FULL is performed. > > What do you have the FSM parameters in postgresql.conf set to? My guess > is you need to increase 'em. Currently set at the defaults, max_fsm_relations is 100, max_fsm_pages is 10000. What are the implications/effects of increasing them? BTW, one of my tables is much bigger, turning over at a similar rate (though I'm much less concerned about performance for that one). Typical VACUUM FULL looks like: NOTICE: Pages 94279: Changed 315, reaped 53522, Empty 0, New 0; Tup 2753324: Vac 7907, Keep/VTL 0/0, UnUsed 277009, MinLen 135, MaxLen 1248; Re-using: Free/Avail. Space 8569160/3380588; EndEmpty/Avail. Pages 0/5802. Thanks Julian
Julian Scarfe <julian.scarfe@ntlworld.com> writes:
> On 14/1/03 15:42, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
>> What do you have the FSM parameters in postgresql.conf set to? My guess
>> is you need to increase 'em.
> Currently set at the defaults, max_fsm_relations is 100, max_fsm_pages is
> 10000.
100 is definitely too small for max_fsm_relations; we've bumped that
default to 1000 for the next release. Given that you've got a
nearly-100k-page table, 10000 for max_fsm_pages is probably much too
small also.
> What are the implications/effects of increasing them?
Shared memory usage gets bigger. I think the max_fsm_pages multiplier
is 6 bytes per slot, so you could make it a million, or ten, without
stressing modern machines. I'd try 1000/1000000 for starters.
Don't forget you need a postmaster restart to make these changes take
effect.
regards, tom lane
Robert Treat <xzilla@users.sourceforge.net> writes:
> The thing with vacuum is at some point you should reach a condition
> where space on the disk increases only when the total number of inserts
> is greater than the total number of deletions over a given time frame.
> If it increases when inserts is less than deletes, your not vacuuming
> enough. If you can reach this level of "symbiosis" with regular vacuums,
> you should never have to do vacuum full.
It's simpler than that: the vacuum frequency determines the amount of
overhead you have to tolerate. Judging from the numbers Julian quoted
us to start with, he's got the frequency set so that vacuum reclaims
about 10% of the tuples on each run. That seems like a good ballpark to
me: 10% space overhead is quite reasonable.
If space usage increases when the total amount of live data is staying
about the same, then the problem has got to be that the space getting
reclaimed by vacuum isn't being re-used effectively. And the only cause
for that, AFAIK, is the FSM settings being too small to keep track of
all the free space in the database.
regards, tom lane
On 14/1/03 16:10, "Tom Lane" <tgl@sss.pgh.pa.us> wrote: > Julian Scarfe <julian.scarfe@ntlworld.com> writes: >> On 14/1/03 15:42, "Tom Lane" <tgl@sss.pgh.pa.us> wrote: >>> What do you have the FSM parameters in postgresql.conf set to? My guess >>> is you need to increase 'em. > >> Currently set at the defaults, max_fsm_relations is 100, max_fsm_pages is >> 10000. > > 100 is definitely too small for max_fsm_relations; we've bumped that > default to 1000 for the next release. Given that you've got a > nearly-100k-page table, 10000 for max_fsm_pages is probably much too > small also. > >> What are the implications/effects of increasing them? > > Shared memory usage gets bigger. I think the max_fsm_pages multiplier > is 6 bytes per slot, so you could make it a million, or ten, without > stressing modern machines. I'd try 1000/1000000 for starters. OK, so what's the up-side? :-) Will this improve performance, or change disk space requirements, or something else? What's the rule of thumb, what are the criteria for setting these parameters? Unfortunately, I don't have enough of a grasp of the internals to understand the implications of the changes -- is there a particular section of the doc that would help me, or do I have to look at the source? At the moment, I don't have a good mental model of what's going on. Thanks again Julian
Robert Thanks for that. On 14/1/03 15:54, "Robert Treat" <xzilla@users.sourceforge.net> wrote: > ISTM that at times your vacuuming regularly and actually recovering > space, but then there are times when your space grows rather quickly. > This is either an indication that you need to time your vacuums better > (to coincide with large batch inserts or deletes) or you need to vacuum > more often, to recover more space between batches. We vacuum about every hour, immediately after a large batch delete. Inserts are spread across the hour with a couple of peaks. [FWIW tuple "life" (insert to subsequent delete) is about 3 hours on that table, about 12-24 hours on other tables where performance is important, and 30 days on the rather larger but less performance critical large archive tables.] I've got limited data at the moment -- I need to collect more to see. >> It grows steadily until a VACUUM FULL is performed, when it shrinks back. >> If we never do a VACUUM FULL, will it just keep on growing, and does >> performance suffer as it does so? >> > > The thing with vacuum is at some point you should reach a condition > where space on the disk increases only when the total number of inserts > is greater than the total number of deletions over a given time frame. > If it increases when inserts is less than deletes, your not vacuuming > enough. If you can reach this level of "symbiosis" with regular vacuums, > you should never have to do vacuum full. OK, that's a helpful way of looking at it. Thanks. Julian
Julian Scarfe <julian.scarfe@ntlworld.com> writes:
> OK, so what's the up-side? :-)
> Will this improve performance, or change disk space requirements, or
> something else?
It'll keep the system from leaking free space in tables, which is what's
causing your tables to bloat. You need an FSM slot for each page that
has useful free space on it, else the system will forget about that
free space.
> What's the rule of thumb, what are the criteria for setting
> these parameters?
Right at the moment I don't think there is any direct way to discover
how big max_fsm_pages needs to be. An upper bound is
select sum(relpages) from pg_class where relkind in ('r','t');
(actually you have to sum over all databases in your installation).
But in most scenarios this is probably overkill, as large tables tend
not to have turnover in every page.
I suppose we should think about adding some more numbers to the VACUUM
output so that one can determine what's happening with the FSM.
regards, tom lane
On 14/1/03 16:32, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
> Julian Scarfe <julian.scarfe@ntlworld.com> writes:
>> OK, so what's the up-side? :-)
>
>> Will this improve performance, or change disk space requirements, or
>> something else?
>
> It'll keep the system from leaking free space in tables, which is what's
> causing your tables to bloat. You need an FSM slot for each page that
> has useful free space on it, else the system will forget about that
> free space.
>
>> What's the rule of thumb, what are the criteria for setting
>> these parameters?
>
> Right at the moment I don't think there is any direct way to discover
> how big max_fsm_pages needs to be. An upper bound is
> select sum(relpages) from pg_class where relkind in ('r','t');
> (actually you have to sum over all databases in your installation).
> But in most scenarios this is probably overkill, as large tables tend
> not to have turnover in every page.
In my case,
[Sorry about the false start.]
On 14/1/03 16:32, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
> Julian Scarfe <julian.scarfe@ntlworld.com> writes:
>> OK, so what's the up-side? :-)
>
>> Will this improve performance, or change disk space requirements, or
>> something else?
>
> It'll keep the system from leaking free space in tables, which is what's
> causing your tables to bloat. You need an FSM slot for each page that
> has useful free space on it, else the system will forget about that
> free space.
OK, makes sense.
>> What's the rule of thumb, what are the criteria for setting
>> these parameters?
>
> Right at the moment I don't think there is any direct way to discover
> how big max_fsm_pages needs to be. An upper bound is
> select sum(relpages) from pg_class where relkind in ('r','t');
> (actually you have to sum over all databases in your installation).
> But in most scenarios this is probably overkill, as large tables tend
> not to have turnover in every page.
In my case, for this, the only seriously active database
=> select sum(relpages) from pg_class where relkind in ('r','t');
sum
--------
858596
(1 row)
So it looks like your guess of 1000000 is pretty much spot on.
We'll see how that goes.
Thanks for the help
Julian
Hi, On Tue, Jan 14, 2003 at 12:19:39PM +0100, pascal bérest wrote: > is it possible to estimate the time to restore a database from a dump ? > the dump is 85 Mo, the base is 850 Mo, we have 512 Mo RAM and a > pentium 3 1.13 GHz. No idea. > we are running for about 16 hours (20:00 CPU time) but it's really > slow, and the database is actually 365 Mo restored. Did You disabled fsync for restore? so long, -tb -- Thomas Beutin tb@laokoon.IN-Berlin.DE Beam me up, Scotty. There is no intelligent live down in Redmond.
At 12:19 PM 1/14/03 +0100, pascal bérest wrote: >is it possible to estimate the time to restore a database from a dump ? >the dump is 85 Mo, the base is 850 Mo, we have 512 Mo RAM and a pentium 3 >1.13 GHz. >we are running for about 16 hours (20:00 CPU time) but it's really slow, >and the database is actually 365 Mo restored. Well you could look at your iostat values (to check IO bandwidth usage). Might give you some clues, you should have a good guesstimate after a few minutes into the restore into a test db. Hope that helps, Link.
On 14 Jan 2003 10:54:29 -0500, Robert Treat <xzilla@users.sourceforge.net> wrote: >Changed = the number of pages that had to be modified. This counts only pages changed due to heap tuple header updates (FrozenTransactionId, hint bits). It does not count page changes due to removal of dead tuples, cf. Julian's log: | NOTICE: Pages 342: Changed 0, Empty 0; Tup 8325: Vac 838, Keep 0, UnUsed 5614. | [...] | NOTICE: Pages 361: Changed 0, Empty 0; Tup 8229: Vac 1111, Keep 0, UnUsed 5981. I don't know whether this is intentional or just an oversight. If it is the latter, let me know and I'll send a patch. Servus Manfred
>>>>> "TL" == Tom Lane <tgl@sss.pgh.pa.us> writes: TL> 100 is definitely too small for max_fsm_relations; we've bumped that TL> default to 1000 for the next release. Given that you've got a TL> nearly-100k-page table, 10000 for max_fsm_pages is probably much too TL> small also. >> What are the implications/effects of increasing them? TL> Shared memory usage gets bigger. I think the max_fsm_pages multiplier TL> is 6 bytes per slot, so you could make it a million, or ten, without TL> stressing modern machines. I'd try 1000/1000000 for starters. Just a followup to this tip, Tom... I implemented this change last week (set max_fsm_relations = 500000), during my switchover from RAID5 to RAID10. After my dump/reload and vacuum analyze, things settled down to a good clip. And my nightly vacuum analyze runs have held to a pretty constant time. Usually after a full vacuum or reload the nightly vacuums start taking longer after about 4 or 5 days. (I also vacuum some tables hourly, every few hours, etc. depending on activity). So basically, this one optimization has significantly improved my disk usage by not making so many empty pages, and keeps performance good by not having sparse tables. My largest table has 115M rows in it (two integers and a char(4)). Thanks for the tip! -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D. Khera Communications, Inc. Internet: khera@kciLink.com Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/