Message-ID: <4418005A.70108@archonet.com>
Date: Wed, 15 Mar 2006 11:54:02 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 1.5 (X11/20051201)
MIME-Version: 1.0
To: =?ISO-8859-2?Q?Marcin_Ma=F1k?= <marcin.mank@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: VACUUM FULL hangs
References: <024d01c64818$9b5790e0$0c67a8c0@maniek>
In-Reply-To: <024d01c64818$9b5790e0$0c67a8c0@maniek>
Content-Type: text/plain; charset=ISO-8859-2; format=flowed
Content-Transfer-Encoding: quoted-printable

Marcin Ma=F1k wrote:
> Hello list.
>=20
> I recently tried to do a slony replica of my database, and doing it fal=
ied.
> I retried, and then it succeeded (why it failed is another story).
>=20
> This caused that in the replica there is a lot of dead tuples ( If i
> understand correctly, a failure in creating the replica means a HUGE ab=
orted
> transaction - and Slony should TRUNCATE the table, getting rid of dead
> tuples, but that is a subject for another list).
>=20
> so I did vacuum full verbose analyze (does it make sense ?)

Fair enough. If you want empty tables TRUNCATE is probably a better bet=20
though.

> This hanged on a (quite large) table:
>=20
> INFO:  vacuuming "public.calls"
> INFO:  "calls": found 7980456 removable, 3989705 nonremovable row versi=
ons
> in 296943 pages
> DETAIL:  0 dead row versions cannot be removed yet.
> Nonremovable row versions range from 136 to 224 bytes long.
> There were 891 unused item pointers.
> Total free space (including removable row versions) is 1594703944 bytes=
=2E
> 197958 pages are or will become empty, including 0 at the end of the ta=
ble.
> 212719 pages containing 1588415680 free bytes are potential move
> destinations.

OK, so there are 7.9 million removable rows and 3.9 million nonremovable =

so truncate isn't an option since you have data you presumably want to=20
keep. It estimates about 200,000 pages will become empty, but none of=20
them are at the end of the table. This represents 1.5GB of unused=20
disk-space.

I'm a bit puzzled as to how you managed to get so much free space at the =

start of the table. Did the replication work on the second try?

> CPU 7.25s/3.28u sec elapsed 144.95 sec.
> INFO:  index "calls_pkey" now contains 3989705 row versions in 8975 pag=
es
> DETAIL:  108927 index row versions were removed.
> 0 index pages have been deleted, 0 are currently reusable.
> CPU 0.35s/0.59u sec elapsed 39.03 sec.
> INFO:  index "calls_cli" now contains 3989705 row versions in 13504 pag=
es
> DETAIL:  108927 index row versions were removed.
> 0 index pages have been deleted, 0 are currently reusable.
> CPU 0.51s/0.60u sec elapsed 58.60 sec.
> INFO:  index "calls_dnis" now contains 3989705 row versions in 13600 pa=
ges
> DETAIL:  108927 index row versions were removed.
> 0 index pages have been deleted, 0 are currently reusable.
> CPU 0.60s/0.90u sec elapsed 27.05 sec.
> INFO:  index "calls_u" now contains 3989705 row versions in 23820 pages=

> DETAIL:  108927 index row versions were removed.
> 0 index pages have been deleted, 0 are currently reusable.
> CPU 0.92s/0.78u sec elapsed 80.51 sec.
> INFO:  index "calls_z" now contains 3989705 row versions in 13607 pages=

> DETAIL:  108927 index row versions were removed.
> 0 index pages have been deleted, 0 are currently reusable.
> CPU 0.60s/0.85u sec elapsed 39.77 sec.

It's done all the indexes (and seems to have done them quite quickly),=20
and is presumably working on the data now.

> It was hanging in this state for more than 3 hours, and I had to kill t=
he
> vacuum process.
>=20
>>From iostat I saw that there was continuous write activity, steadilly a=
bout
> 1.3 MB/s (the disk system can do about 40 MB/s), and there were iowait
> processes. There was no read activity.
>=20
> There were no other clients for that database (but there were clients i=
n
> other databases in the instance).

OK, so you might well be getting the vacuum writing one page, then WAL,=20
then vacuum, etc. That will mean the disk spends most of its time=20
seeking back and fore. How many disks do you have, and is the WAL on a=20
separate set of disks?

I think it's just taking a long time because you have so many pages to=20
move and not enough disk bandwidth. Of course the root of the problem is =

that you had so many dead rows after a failed replication, but you're=20
right and that's another email.

--=20
   Richard Huxton
   Archonet Ltd