Postgres Pro
Downloads
Home   >   mailing lists

Fwd: Vacuum Full + Cluster + Vacuum full = non removable dead rows - Mailing list pgsql-performance

From Alexandre de Arruda Paes
Subject Fwd: Vacuum Full + Cluster + Vacuum full = non removable dead rows
Date
Msg-id AANLkTikz1VQFLw0dm54cUtQ2_eWmg=R6HVKu2nUUA5+b@mail.gmail.com
Whole thread Raw
In response to Vacuum Full + Cluster + Vacuum full = non removable dead rows  (Alexandre de Arruda Paes <adaldeia@gmail.com>)
Responses Re: Fwd: Vacuum Full + Cluster + Vacuum full = non removable dead rows
List pgsql-performance
Tree view
I'm forwarding again this email to list, because Me and Scoot unfortunately was talking alone. (thanks Scott)

>So what do:
>select * from pg_stat_activity where current_query ilike '%transaction%';
>and
>select * from pg_stat_activity where now()-current_query > '1 minute'::interval;
>say?
>You should really avoid vacuum full, and stick to vacuum (plain).  At
>least until you can get the tuples to be freed up.  Each time you run
>it you bloat your indexes.

To clarify:

This is a production server with lots of connection and the commands above returns a lot of rows, but nothing related with this table (see bellow).
I know the problem with VACUUM FULL and bloated Indexes, but I don't understand why the table that is not in use by nobody, cant be vacuumed or clustered to avoid dead tuples.
Single VACUUM cant recover this dead tuples too.
 
I see an opened transaction (this is a tomcat servlet webpage), but killing this transaction does not help the VACUUM:

<webpa 192.168.1.1 2010-08-17 18:36:40.459 BRT 243345>LOG:  execute S_1: BEGIN
<webpa 192.168.1.1 2010-08-17 18:36:40.459 BRT 243346>LOG:  duration: 0.010 ms
<webpa 192.168.1.1 2010-08-17 18:36:40.459 BRT 243347>LOG:  duration: 0.362 ms
<webpa 192.168.1.1 2010-08-17 18:36:40.460 BRT 243348>LOG:  duration: 0.703 ms
<webpa 192.168.1.1 2010-08-17 18:36:40.460 BRT 243349>LOG:  execute <unnamed>: SELECT TP93usuari, TP93Objeto, TP93Ca251, TP93Nm0805, TP93Nm0804, TP93Ca501, TP93Ca2001, TP93Nm1521, TP93Nm0803, TP93Ca253, TP93Nm1522, TP93Nm0801, TP93Nm0802, TP93Chave FROM TP93T WHERE (TP93usuari = $1) AND (TP93Objeto = 'PC0658PP') AND (TP93Ca251 >= $2) ORDER BY TP93Chave
<webpa 192.168.1.1 2010-08-17 18:36:40.460 BRT 243350>DETAIL:  parameters: $1 = 'WEBCLIENTE          ', $2 = '                    '
<webpa 192.168.1.1 2010-08-17 18:36:40.469 BRT 243351>LOG:  duration: 9.302 ms

[postgres@servernew logs]$ psql carmen
psql (8.4.4)
Type "help" for help.

carmen=# select * from vlocks where relname='tp93t'; select * from pg_stat_activity where usename='webpa';

 datname | relname | virtualtransaction |      mode       | granted | usename |        substr         |          query_start          |       age       | procpid
---------+---------+--------------------+-----------------+---------+---------+-----------------------+-------------------------------+-----------------+---------
 carmen  | tp93t   | 25/4319            | AccessShareLock | t       | webpa   | <IDLE> in transaction | 2010-08-17 18:36:40.460657-03 | 00:01:09.455456 |    1917
(1 row)


 datid | datname | procpid | usesysid  | usename |     current_query     | waiting |          xact_start           |          query_start          |         backend_start         | client_addr | client_port
-------+---------+---------+-----------+---------+-----------------------+---------+-------------------------------+-------------------------------+-------------------------------+-------------+-------------
 16745 | carmen  |    1917 | 750377993 | webpa   | <IDLE> in transaction | f       | 2010-08-17 18:36:40.459531-03 | 2010-08-17 18:36:40.460657-03 | 2010-08-17 18:36:09.917687-03 | 192.168.1.1 |       39027
(1 row)

carmen=# select * from vlocks where usename='webpa';

 datname |  relname   | virtualtransaction |      mode       | granted | usename |        substr         |          query_start          |       age       | procpid
---------+------------+--------------------+-----------------+---------+---------+-----------------------+-------------------------------+-----------------+---------
 carmen  | tp93t_pkey | 25/4319            | AccessShareLock | t       | webpa   | <IDLE> in transaction | 2010-08-17 18:36:40.460657-03 | 00:01:16.618563 |    1917
 carmen  | tp93t      | 25/4319            | AccessShareLock | t       | webpa   | <IDLE> in transaction | 2010-08-17 18:36:40.460657-03 | 00:01:16.618563 |    1917
 carmen  |            | 25/4319            | ExclusiveLock   | t       | webpa   | <IDLE> in transaction | 2010-08-17 18:36:40.460657-03 | 00:01:16.618563 |    1917
(3 rows)

-----------------------------------------------------------------------------------------------

OK, I will kill the backend and run vacuum:

carmen=# select pg_terminate_backend(1917);
 pg_terminate_backend
----------------------
 t
(1 row)

carmen=# select * from vlocks where relname='tp93t'; select * from pg_stat_activity where usename='webpa';

 datname | relname | virtualtransaction | mode | granted | usename | substr | query_start | age | procpid
---------+---------+--------------------+------+---------+---------+--------+-------------+-----+---------
(0 rows)

 datid | datname | procpid | usesysid  | usename |     current_query     | waiting |          xact_start           |          query_start          |         backend_start         | client_addr | client_port
-------+---------+---------+--
---------+---------+-----------------------+---------+-------------------------------+-------------------------------+-------------------------------+-------------+-------------
(0 rows)


carmen=# VACUUM verbose tp93t;
INFO:  vacuuming "public.tp93t"
INFO:  index "tp93t_pkey" now contains 5592 row versions in 103 pages
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:  "tp93t": found 0 removable, 19336 nonremovable row versions in 4887 out of 4887 pages
DETAIL:  19126 dead row versions cannot be removed yet.


carmen=# VACUUM FULL verbose tp93t;
INFO:  vacuuming "public.tp93t"
INFO:  "tp93t": found 0 removable, 19336 nonremovable row versions in 4887 pages
DETAIL:  19126 dead row versions cannot be removed yet.
Nonremovable row versions range from 1853 to 2029 bytes long.
There were 210 unused item pointers.
(...)







2010/8/17 Scott Marlowe <scott.marlowe@gmail.com>
On Tue, Aug 17, 2010 at 2:28 PM, Alexandre de Arruda Paes

<adaldeia@gmail.com> wrote:

So what do:
select * from pg_stat_activity where current_query ilike '%transaction%';
and
select * from pg_stat_activity where now()-current_query > '1 minute'::interval;
say?

> And its the dead rows is growing:
>
> carmen=# VACUUM FULL verbose tp93t;

You should really avoid vacuum full, and stick to vacuum (plain).  At
least until you can get the tuples to be freed up.  Each time you run
it you bloat your indexes.

> INFO:  vacuuming "public.tp93t"
> INFO:  "tp93t": found 1309 removable, 313890 nonremovable row versions in
> 78800 pages
> DETAIL:  312581 dead row versions cannot be removed yet.
> Nonremovable row versions range from 1845 to 2032 bytes long.
> There were 3014 unused item pointers.


--
To understand recursion, one must first understand recursion.


pgsql-performance by date:

Previous
From: Hannes Frederic Sowa
Date:
Subject: Re: Very poor performance
Next
From: Tom Lane
Date:
Subject: Re: Fwd: Vacuum Full + Cluster + Vacuum full = non removable dead rows