Re: [HACKERS] One vacuum full is not enough.

[Gaetano Mendola]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Hannu Krosing wrote:
 Ühel kenal päeval (teisipäev, 8. märts 2005, 00:52+0100), kirjutas
 Gaetano Mendola:
 
Hi all,
running a 7.4.5 it happen to me with another table
where a single vacuum full was not freeing enough pages,
here the verbose vacuum full, as you can see only at
the end:  truncated 8504 to 621 pages.

I use pg_autovacuum and it's not enough. I'll schedule
again a nightly vacuum full.
 
 
 You may have too few fsm pages, so new inserts/updates don't use all the 
 pages freed by vacuums.
 

Is not this the case:


 max_fsm_pages  | 200
 max_fsm_relations  | 1000

and when I was doing the vacuum full these settings were above the
real needs.



Regards
Gaetano Mendola




-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.5 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFCMA++7UpzwH2SGd4RAi0hAJwLBpSWlDTQoAWglK8Dg/IoY3fb8QCfTjKU
wxDSc2VG7B5pRPfCfQqxRtk=
=Ce+9
-END PGP SIGNATURE-


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [HACKERS] One vacuum full is not enough.

[Christopher Browne]

[EMAIL PROTECTED] (Gaetano Mendola) wrote:
 Hi all,
 running a 7.4.5 it happen to me with another table
 where a single vacuum full was not freeing enough pages,
 here the verbose vacuum full, as you can see only at
 the end:  truncated 8504 to 621 pages.

 I use pg_autovacuum and it's not enough. I'll schedule
 again a nightly vacuum full.

That doesn't follow as a legitimate inference.

It is fairly well certain that what you are suffering from are some
long running transactions that prevent dead tuples from being vacuumed
out.

That indicates that your focus on VACUUM FULL is a focus on a red
herring.

You can see that pretty easily; you're seeing VACUUM FULL requests not
doing the trick because the old transaction prevents _ANY_ kind of
vacuum from clearing out tuples that were 'killed' after that
transaction started.

The problem isn't particularly with your vacuum policy; it is with the
transaction handling behaviour in your application.  No vacuum policy
will ever really be enough until you can get the long running
transactions under control.
-- 
(format nil [EMAIL PROTECTED] cbbrowne acm.org)
http://linuxdatabases.info/info/lsf.html
I've run  DOOM more in  the last  few days than  I have the  last few
months.  I just love debugging ;-) -- Linus Torvalds

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] One vacuum full is not enough.

[Hannu Krosing]

Ühel kenal päeval (teisipäev, 8. märts 2005, 00:52+0100), kirjutas
Gaetano Mendola:
 Hi all,
 running a 7.4.5 it happen to me with another table
 where a single vacuum full was not freeing enough pages,
 here the verbose vacuum full, as you can see only at
 the end:  truncated 8504 to 621 pages.

 I use pg_autovacuum and it's not enough. I'll schedule
 again a nightly vacuum full.

You may have too few fsm pages, so new inserts/updates don't use all the pages 
freed by vacuums.

-- 
Hannu Krosing [EMAIL PROTECTED]

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

[HACKERS] One vacuum full is not enough.

[Gaetano Mendola]

Hi all,
running a 7.4.5 it happen to me with another table
where a single vacuum full was not freeing enough pages,
here the verbose vacuum full, as you can see only at
the end:  truncated 8504 to 621 pages.

I use pg_autovacuum and it's not enough. I'll schedule
again a nightly vacuum full.

Regards
Gaetano Mendola


# vacuum full verbose url;
INFO:  vacuuming public.url
INFO:  url: found 268392 removable, 21286 nonremovable row versions in 8563 
pages
DETAIL:  22 dead row versions cannot be removed yet.
Nonremovable row versions range from 104 to 860 bytes long.
There were 13924 unused item pointers.
Total free space (including removable row versions) is 63818404 bytes.
4959 pages are or will become empty, including 7 at the end of the table.
8296 pages containing 63753840 free bytes are potential move destinations.
CPU 0.33s/0.12u sec elapsed 9.55 sec.
INFO:  index url_pkey now contains 21286 row versions in 2343 pages
DETAIL:  268392 index row versions were removed.
886 index pages have been deleted, 886 are currently reusable.
CPU 0.11s/0.37u sec elapsed 2.68 sec.
INFO:  index idx_url_url now contains 297 row versions in 7412 pages
DETAIL:  268392 index row versions were removed.
6869 index pages have been deleted, 6869 are currently reusable.
CPU 1.02s/2.05u sec elapsed 59.89 sec.
INFO:  index idx_url_name now contains 297 row versions in 3277 pages
DETAIL:  268392 index row versions were removed.
2976 index pages have been deleted, 2976 are currently reusable.
CPU 0.40s/0.72u sec elapsed 27.05 sec.
INFO:  url: moved 2 row versions, truncated 8563 to 8550 pages
DETAIL:  CPU 0.40s/0.52u sec elapsed 28.05 sec.
INFO:  index url_pkey now contains 21287 row versions in 2343 pages
DETAIL:  1 index row versions were removed.
886 index pages have been deleted, 886 are currently reusable.
CPU 0.07s/0.04u sec elapsed 6.22 sec.
INFO:  index idx_url_url now contains 298 row versions in 7412 pages
DETAIL:  0 index row versions were removed.
6956 index pages have been deleted, 6956 are currently reusable.
CPU 0.37s/0.07u sec elapsed 14.30 sec.
INFO:  index idx_url_name now contains 298 row versions in 3277 pages
DETAIL:  0 index row versions were removed.
2979 index pages have been deleted, 2979 are currently reusable.
CPU 0.16s/0.04u sec elapsed 4.79 sec.
INFO:  vacuuming pg_toast.pg_toast_16730637
INFO:  pg_toast_16730637: found 0 removable, 0 nonremovable row versions in 0 
pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 0 to 0 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 0 bytes.
0 pages are or will become empty, including 0 at the end of the table.
0 pages containing 0 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index pg_toast_16730637_index now contains 0 row versions in 1 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM
empdb=# vacuum full verbose url;
INFO:  vacuuming public.url
INFO:  url: found 42 removable, 21286 nonremovable row versions in 8550 pages
DETAIL:  22 dead row versions cannot be removed yet.
Nonremovable row versions range from 104 to 860 bytes long.
There were 281879 unused item pointers.
Total free space (including removable row versions) is 63713588 bytes.
4948 pages are or will become empty, including 0 at the end of the table.
8289 pages containing 63705340 free bytes are potential move destinations.
CPU 0.39s/0.15u sec elapsed 5.93 sec.
INFO:  index url_pkey now contains 21286 row versions in 2343 pages
DETAIL:  42 index row versions were removed.
886 index pages have been deleted, 886 are currently reusable.
CPU 0.09s/0.03u sec elapsed 1.44 sec.
INFO:  index idx_url_url now contains 297 row versions in 7412 pages
DETAIL:  42 index row versions were removed.
6956 index pages have been deleted, 6956 are currently reusable.
CPU 0.18s/0.08u sec elapsed 7.30 sec.
INFO:  index idx_url_name now contains 297 row versions in 3277 pages
DETAIL:  42 index row versions were removed.
2979 index pages have been deleted, 2979 are currently reusable.
CPU 0.09s/0.02u sec elapsed 2.58 sec.
INFO:  url: moved 0 row versions, truncated 8550 to 8550 pages
DETAIL:  CPU 0.37s/0.23u sec elapsed 4.39 sec.
INFO:  vacuuming pg_toast.pg_toast_16730637
INFO:  pg_toast_16730637: found 0 removable, 0 nonremovable row versions in 0 
pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 0 to 0 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 0 bytes.
0 pages are or will become empty, including 0 at the end of the table.
0 pages containing 0 free bytes are potential move destinations.
CPU 0.00s/0.01u sec elapsed 0.00 sec.
INFO:  index pg_toast_16730637_index now contains 0 row versions in 1 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM