Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id C54589FBC97 for ; Wed, 2 May 2007 12:12:08 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 35542-07 for ; Wed, 2 May 2007 12:12:01 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from py-out-1112.google.com (py-out-1112.google.com [64.233.166.180]) by postgresql.org (Postfix) with ESMTP id E7CD49FBC91 for ; Wed, 2 May 2007 12:12:00 -0300 (ADT) Received: by py-out-1112.google.com with SMTP id a73so132749pye for ; Wed, 02 May 2007 08:11:58 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding; b=N3xZImI/zYgauFXdfKyXYEibZ/scv+hQDzLklUF/NBenVjo0m9KBkzvIQiMafF38t+ISF7o1BHT0s9scRDQ5SGWiKH/Fv36lUEUkGLsSzJ5tlbvlyASRWN0b543Zc9g96kIrqL0qgkTLQ+ZZsxUbNAqyU22+wpciiLtcOokDHis= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding; b=Fo14WUxoUwT2DE8PWSLCHI58QsVFzuhXb5rkkcmbGmEYREZ2VgnDVl9HrM897dPEQYl2kjpEuSULtmDfh7g/d0NfhUge9b3aWJTsDIHVm/BqgN0DLbYnnjzGrKIS4ZlRoTFBMx81at2h0QUuZf2Li74Hqwx4ZDa52tqmD1m0UhY= Received: by 10.35.80.20 with SMTP id h20mr1433344pyl.1178118718005; Wed, 02 May 2007 08:11:58 -0700 (PDT) Received: from ?192.168.0.102? ( [66.168.24.69]) by mx.google.com with ESMTP id 38sm3843434nzk.2007.05.02.08.11.56; Wed, 02 May 2007 08:11:56 -0700 (PDT) Message-ID: <4638AA6E.2090506@gmail.com> Date: Wed, 02 May 2007 10:12:46 -0500 From: Peter Koczan User-Agent: Thunderbird 1.5.0.10 (X11/20070306) MIME-Version: 1.0 To: Gabriele Bartolini CC: pgsql-admin@postgresql.org Subject: Re: VACUUM FULL ANALYSE hanging References: <910CF843580B3C40A25CD0D04B3908E213050E@exchange4.comune.prato.local> In-Reply-To: <910CF843580B3C40A25CD0D04B3908E213050E@exchange4.comune.prato.local> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/20 X-Sequence-Number: 25340 I've noticed in my own experiments and experiences with VACUUM FULL that it tries to reindex all the indexes to compress them. While a good idea, this unfortunately takes a *long* time. You should check two things. First, the server CPU usage should be high (~100% if on a single core). Second, check the contents of the pg_locks view. It should hold exclusive locks on all the indexes (though it's ordered by oid, so you might have to check pg_class or something else to get the actual table/index name). If it's truly hanging/deadlocking, the locks won't be granted, and the CPU usage will be low. In my experiences, doing a dump/restore was far faster, but this method creates downtime. (e.g. a 10 GB database took 2 hours to restore, while reindexing/vacuuming full was still on the same table after 4 hours). If anyone can shed some light onto why reindexing/vacuuming full takes so long, I'd like to know. Peter Gabriele Bartolini wrote: > Hi guys, > > I am having problems with freeing disk space after a massive delete > operation on a table that had approximately 80 million record. I > ran the following command, by setting the vacuum memory to > approximately a GigaByte: > > SET vacuum_mem TO 1024000 > VACUUM FULL ANALYSE VERBOSE oltp.requests > > Here is what I get: > > There were 34221203 unused item pointers. > Total free space (including removable row versions) is 8969616624 bytes. > 1129827 pages are or will become empty, including 0 at the end of the > table. > 1337307 pages containing 8964065020 free bytes are potential move > destinations. > CPU 16.03s/9.12u sec elapsed 219.47 sec. > INFO: index "requests_pkey" now contains 20075362 row versions in > 327419 pages > DETAIL: 8211835 index row versions were removed. > 217782 index pages have been deleted, 217782 are currently reusable. > CPU 5.38s/12.53u sec elapsed 100.80 sec. > INFO: index "idx_oltp_requests_access_date" now contains 20075362 row > versions in 491725 pages > DETAIL: 8211835 index row versions were removed. > 426501 index pages have been deleted, 426501 are currently reusable. > CPU 14.96s/13.47u sec elapsed 200.91 sec. > INFO: index "idx_oltp_requests_access_time" now contains 20075362 row > versions in 343915 pages > DETAIL: 8211835 index row versions were removed. > 213612 index pages have been deleted, 213612 are currently reusable. > CPU 6.32s/14.03u sec elapsed 111.24 sec. > INFO: index "idx_oltp_requests_referer" now contains 20075362 row > versions in 470822 pages > DETAIL: 8211835 index row versions were removed. > 376873 index pages have been deleted, 376873 are currently reusable. > CPU 18.85s/17.18u sec elapsed 265.25 sec. > INFO: index "idx_oltp_requests_session" now contains 20075362 row > versions in 611141 pages > DETAIL: 8211835 index row versions were removed. > 478827 index pages have been deleted, 478827 are currently reusable. > CPU 16.83s/14.33u sec elapsed 258.47 sec. > INFO: index "idx_oltp_requests_status_code" now contains 20075362 row > versions in 690337 pages > DETAIL: 8211835 index row versions were removed. > 600953 index pages have been deleted, 600953 are currently reusable. > CPU 34.37s/24.44u sec elapsed 297.21 sec. > INFO: index "idx_oltp_requests_url" now contains 20075362 row > versions in 336075 pages > DETAIL: 8211835 index row versions were removed. > 73821 index pages have been deleted, 73821 are currently reusable. > CPU 17.06s/28.14u sec elapsed 319.16 sec. > But here, the command simply hangs. > > The table description is: > > htminer=> \d oltp.requests > Tabella "oltp.requests" > Colonna | Tipo | Modificatori > --------------------+-----------------------------+-------------------- > id_request | integer | not null > id_session | integer | > access_time | timestamp(0) with time zone | not null > request_method | numeric(2,0) | not null default 1 > http_version_major | numeric(1,0) | not null default 1 > http_version_minor | numeric(1,0) | not null default 1 > status_code | numeric(3,0) | not null > bytes | integer | > time_taken | numeric(3,0) | > id_url | integer | not null > id_referer | integer | > content_language | character(2) | > dwell_time | smallint | not null default 1 > request_type | numeric(1,0) | > Indici: > "requests_pkey" PRIMARY KEY, btree (id_request), tablespace > "htminer_oltp" > "idx_oltp_requests_access_date" btree (date_trunc('day'::text, > timezone('UTC'::text, access_time))), tablespace "htminer_oltp" > "idx_oltp_requests_access_time" btree (access_time), tablespace > "htminer_oltp" > "idx_oltp_requests_referer" btree (id_referer), tablespace > "htminer_oltp" > "idx_oltp_requests_session" btree (id_session, status_code), > tablespace "htminer_oltp" > "idx_oltp_requests_status_code" btree (status_code), tablespace > "htminer_oltp" > "idx_oltp_requests_url" btree (id_url), tablespace "htminer_oltp" > Vincoli di controllo: > "requests_bytes_check" CHECK (bytes >= 0) > "requests_dwell_time_check" CHECK (dwell_time >= 0) > "requests_id_request_check" CHECK (id_request > 0) > "requests_request_method_check" CHECK (request_method > 0::numeric) > "requests_request_type_check" CHECK (request_type > 0::numeric) > Vincoli di integrità referenziale > "requests_id_referer_fkey" FOREIGN KEY (id_referer) REFERENCES > oltp.urls(id_url) ON UPDATE CASCADE ON DELETE CASCADE > "requests_id_session_fkey" FOREIGN KEY (id_session) REFERENCES > oltp.sessions(id_session) ON UPDATE CASCADE ON DELETE CASCADE > "requests_id_url_fkey" FOREIGN KEY (id_url) REFERENCES > oltp.urls(id_url) ON UPDATE CASCADE ON DELETE CASCADE > Tablespace: "htminer_oltp" > Should I drop the indexes and re-create them? > > Thanks, > Gabriele > > -- > Gabriele Bartolini - Istruttore Informatico - Comune di Prato > Sistema Informativo - Servizi di E-Government e Open-Source > g.bartolini@comune.prato.it - > www.comune.prato.it - www.htminer.it > >