On 26/11/2007, Elmer <sebastian.franek@gmail.com> wrote: On 23 Lis, 10:23, peterachi...@gmail.com ("Peter Childs") wrote:
> Yes, however....
>
> found x removable
>
> have just been found and are now unused, so on the next run this number will
> be added to the unused unless they get used again in the mean time.
>
> The number ie the unused is the number of tuples left in the free space map
> unused since the last vacuum. If its high it may be worth clustering or
> running vacuum full but only if you don't think you table will never or
> unlikely to grow (insert) or change (update) by less than that number of
> records before you next run vacuum.
>
> Generally only worry if the number is very very high (over 10000).
>
> The best way of understanding the numbers is to run vacuum at regular
> intervals and compare the output.
>
> Peter.
Thank you for your explanation - it's not easy to get help in this
subject... But how it is possible that new unused.
You wrote:
> The number ie the unused is the number of tuples left in the free space map unused since the last vacuum.
This is important information for me but I still can't understand why
this number keeps growing.Correct me if I wrong but if there is 17000
unused tuples in free space map, they should be used in first place
for creating new tuples versions. This should cause that next 17000
operations (consists of INSERT,UPDATE,DELETE) would use fsm for row
version creation instead of creating entirely new tuples at the end of
table file. If I understand it correct number of unused item pointers
should shrink between vacuums (but it still grows)...
Hmm that should have been number of tuples left in the free space map at the start of the vacuum. So if you run a second vacuum in quick succession the number under removable will have been added the the unused value to become the new unused value.
If you have a growing table unused should be 0 (or shrinking)
If you have a busy table thats not vacuumed often enough unused should be 0 but removable will be high (so long as you vacuum at regular intervals)
If you have a shrinking table unused will grow.
If you have a steady table that was larger at some point the past eg an update to the whole table. unused will be large
If unused is the amount of "bloat" you have but is only useful depending on when you last vacuumed if you vacuumed twice within a couple of seconds the second number is not very useful.
If I'm wrong I'm sure someone will correct me.
Peter Childs