Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cdeAe-0005jl-5E for pgsql-admin@arkaria.postgresql.org; Tue, 14 Feb 2017 14:33:32 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1cdeAd-0000ug-Oh for pgsql-admin@arkaria.postgresql.org; Tue, 14 Feb 2017 14:33:31 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cda92-0001eq-58 for pgsql-admin@postgresql.org; Tue, 14 Feb 2017 10:15:36 +0000 Received: from mail-wr0-x233.google.com ([2a00:1450:400c:c0c::233]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1cda8z-00023F-1E for pgsql-admin@postgresql.org; Tue, 14 Feb 2017 10:15:35 +0000 Received: by mail-wr0-x233.google.com with SMTP id k90so166147176wrc.3 for ; Tue, 14 Feb 2017 02:15:32 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=message-id:mime-version:to:from:subject:date:importance; bh=HC82DktN24Hy+2zdk/QUlKIdLk82r437qBt3I4OyYBo=; b=TbrOZWYZP0G14MYIg8IjvAkGROfExJLj5bM97K9fOr3EfAitlK41GoRMew+7YNLY2F SMUYuDuOTZHmP5bW/8jbxOF5Yx2B10FNoOrpZK1/nMn39WqQatZqW6fuTjZRWmCVeMrr Ycag9085Rp4UcDKvdXnK5IvjCaKnP06yFNGdpw5ud/B+0MptuvwIUUtV7anMW4Ucs9l4 qbFy/7Ztp2lJUq34792CCzdx0c9maIV4E1gKz6RFbrzUiR4lmm2ZWLBa/ojWf4SrBxha BxBhbZkFNDVcGK4gMVj8NjgTcPc/LsQLgLJ3uuXnRT6/jgK2oE9qI+NiWLAQzkHhUgcV 9X1g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:message-id:mime-version:to:from:subject:date :importance; bh=HC82DktN24Hy+2zdk/QUlKIdLk82r437qBt3I4OyYBo=; b=HdrJYU0kLZWF6rtKayGUK0t51N6Ztkr2yD6oMbbplSLSoMsW3DqaJix/sKIgRHumk/ gxzIo3htuE6/3M0SQdMk0WJLoRM0dWq6beUM2sly2tzb1rBzbQx7DnjKrGQU9DWj8sI+ g6n6FxD93ZTAMUDur6bKfW3FBufioGm3tjLBNz2GH3lp3Imc8W9BLyffu3lcRjwDRm3M tLLa4jD1DsjcT93ZUbmFKqpvYxLSSuRofb+ZMpbnsyaPjKpRvOqytqXZXsCJSUmAKz2h czWJA3oBaCV5so5k3bSAUJi2c53YN1C1oZwQo0i/P2yAyvuP6XJWd3dG/f17fL5FWDCD t1Xw== X-Gm-Message-State: AMke39ntaqDKLeqHxozeOsJx3gFvPXPiIGgTB+W1H8oGwhbVSzQvJHp9n6+0VNyguK4fDQ== X-Received: by 10.223.133.164 with SMTP id 33mr24597610wrt.39.1487067331832; Tue, 14 Feb 2017 02:15:31 -0800 (PST) Received: from ?IPv6:::ffff:172.31.2.92? (smtp.homegate.ch. [195.48.42.97]) by smtp.gmail.com with ESMTPSA id b10sm2811523wmi.34.2017.02.14.02.15.30 for (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Tue, 14 Feb 2017 02:15:31 -0800 (PST) Message-ID: <58a2d8c3.8a6d1c0a.dd92a.e25e@mx.google.com> MIME-Version: 1.0 To: "pgsql-admin@postgresql.org" From: Subject: (auto) vacuum mysterious Date: Tue, 14 Feb 2017 11:15:30 +0100 Importance: normal X-Priority: 3 Content-Type: multipart/alternative; boundary="_0896EACE-7B3B-410B-896A-BB2E1EAAE8C2_" X-Pg-Spam-Score: -2.0 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org --_0896EACE-7B3B-410B-896A-BB2E1EAAE8C2_ Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset="utf-8" Hi There=20 My Name is Martin And we are running PostgreSQL 9.5.3=20 We are doing a lot of deletes updates and Inserts on some tables Until now we didn=E2=80=99t Encounter so much Problems with the vacuumizer = but in the last view days we have the fact the vacuum cannot remove dead ro= ws anymore I know it is the case when there are other Long running Transactions runnin= g but this isn=E2=80=99t the case The vacuum Output Shows only that it cannot remove dead rows. But not why? Is there a way to see futher Information to see that? To see which session = is blocking the vacuum Job? Thanks Martin DETAIL: 2066873 dead row versions cannot be removed yet. There were 685985 unused item pointers. Skipped 0 pages due to buffer pins. 0 pages are entirely empty. CPU 1.75s/1.07u sec elapsed 2.84 sec. INFO: vacuuming "pg_toast.pg_toast_30145032" INFO: index "pg_toast_30145032_index" now contains 0 row versions in 1 pag= es DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "pg_toast_30145032": found 0 removable, 0 nonremovable row versions = in 0 out of 0 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. Skipped 0 pages due to buffer pins. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. VACUUM --_0896EACE-7B3B-410B-896A-BB2E1EAAE8C2_ Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="utf-8"

Hi There

 

My Name is Martin

And we are running PostgreSQL 9.5.3

&n= bsp;

We are doing a lot of deletes updates an= d Inserts on some tables

 

Until now we didn=E2=80=99t Encounter so much Problems with= the vacuumizer but in the last view days we have the fact the vacuum canno= t remove dead rows anymore

 

I know it is the case when there are other Long running T= ransactions running but this isn=E2=80=99t the case

 

 

The vacuum Output Shows only that it cannot remove dead rows.

=

But not why?

 =

Is there a way to see futher Information to see th= at? To see which session is blocking the vacuum Job?

 

Thanks Martin

 

 

 

 =

DETAIL:=C2=A0 2066873 dead row versions cannot be = removed yet.

There were 685985 unused item pointers= .

Skipped 0 pages due to buffer pins.

0 pages are entirely empty.

CPU 1.75s/= 1.07u sec elapsed 2.84 sec.

INFO:=C2=A0 vacuuming &= quot;pg_toast.pg_toast_30145032"

INFO:=C2=A0 i= ndex "pg_toast_30145032_index" now contains 0 row versions in 1 p= ages

DETAIL:=C2=A0 0 index row versions were remove= d.

0 index pages have been deleted, 0 are currently= reusable.

CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO:=C2=A0 "pg_toast_30145032": found 0 re= movable, 0 nonremovable row versions in 0 out of 0 pages

DETAIL:=C2=A0 0 dead row versions cannot be removed yet.

There were 0 unused item pointers.

Ski= pped 0 pages due to buffer pins.

0 pages are entire= ly empty.

CPU 0.00s/0.00u sec elapsed 0.00 sec.

=

VACUUM

= --_0896EACE-7B3B-410B-896A-BB2E1EAAE8C2_--