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 [postg...@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.marl...@gmail.com> > On Tue, Aug 17, 2010 at 2:28 PM, Alexandre de Arruda Paes > > <adald...@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. >
