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?
>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:>least until you can get the tuples to be freed up. Each time you run
>it you bloat your indexes.
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)
(0 rows)
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 pagesCPU 0.00s/0.00u sec elapsed 0.00 sec.
DETAIL: 19126 dead row versions cannot be removed yet.
carmen=# VACUUM FULL verbose 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@xxxxxxxxx>
On Tue, Aug 17, 2010 at 2:28 PM, Alexandre de Arruda Paes
<adaldeia@xxxxxxxxx> 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:You should really avoid vacuum full, and stick to vacuum (plain). At
>
> carmen=# VACUUM FULL verbose tp93t;
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.
