Discussion:
[Autovacuum] Issue to understand some logs
(too old to reply)
Baptiste LHOSTE
2012-12-17 15:57:35 UTC
Permalink
Hi everybody,

I have trouble understanding some logs of postgreSQL.

Here you can find a log from the auto-vacuum process :

2012-12-17 16:38:58 CET LOG: automatic vacuum of table "flows.public.agg_t344_outgoing_a41_src_net_and_dst_net_f5": index scans: 0
pages: 0 removed, 59820 remain
tuples: 0 removed, 5801752 remain
system usage: CPU 0.93s/0.95u sec elapsed 423.07 sec

I am right to conclude that there is 5801752 available tuples in the previous table ?

If true, I am very confused because our table can not exceed 2592000 tuples (We delete old ones).

To be sure I tried to run the following query to count availables tuples :

select count(*) from agg_t344_outgoing_a41_src_net_and_dst_net_f5;
count
---------
2584398
(1 row)

Can someone give me a clue ?

Best regards,

Baptiste.

---
Baptiste LHOSTE
***@alaloop.com

ALALOOP S.A.S. - Technopole Izarbel - 64210 Bidart
Téléphone : +33 (0) 5 59 41 51 10
www.alaloop.com
--
Sent via pgsql-admin mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Guillaume Lelarge
2012-12-17 16:02:53 UTC
Permalink
Post by Baptiste LHOSTE
Hi everybody,
I have trouble understanding some logs of postgreSQL.
2012-12-17 16:38:58 CET LOG: automatic vacuum of table "flows.public.agg_t344_outgoing_a41_src_net_and_dst_net_f5": index scans: 0
pages: 0 removed, 59820 remain
tuples: 0 removed, 5801752 remain
system usage: CPU 0.93s/0.95u sec elapsed 423.07 sec
I am right to conclude that there is 5801752 available tuples in the previous table ?
If true, I am very confused because our table can not exceed 2592000 tuples (We delete old ones).
select count(*) from agg_t344_outgoing_a41_src_net_and_dst_net_f5;
count
---------
2584398
(1 row)
Can someone give me a clue ?
It could be dead rows, still visible for other transactions.
--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com
--
Sent via pgsql-admin mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Baptiste LHOSTE
2012-12-17 16:10:18 UTC
Permalink
Post by Guillaume Lelarge
It could be dead rows, still visible for other transactions.
Ok but in this case, why the automatic vacuum task of the autovacuum process does not delete theses dead rows ?

Best regards,

Baptiste

---
Baptiste LHOSTE
***@alaloop.com

ALALOOP S.A.S. - Technopole Izarbel - 64210 Bidart
Téléphone : +33 (0) 5 59 41 51 10
www.alaloop.com
--
Sent via pgsql-admin mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Guillaume Lelarge
2012-12-17 16:18:39 UTC
Permalink
Post by Baptiste LHOSTE
Post by Guillaume Lelarge
It could be dead rows, still visible for other transactions.
Ok but in this case, why the automatic vacuum task of the autovacuum process does not delete theses dead rows ?
As I said, because they are still visible to other transactions. Try to
see if you have long-lasting transactions.
--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com
--
Sent via pgsql-admin mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Baptiste LHOSTE
2012-12-17 16:35:27 UTC
Permalink
Post by Guillaume Lelarge
As I said, because they are still visible to other transactions. Try to
see if you have long-lasting transactions.
How can I do that ? I check running query in pg_stat_activity, but there is no query on that table.

Best regards,

Baptiste.

---
Baptiste LHOSTE
***@alaloop.com

ALALOOP S.A.S. - Technopole Izarbel - 64210 Bidart
Téléphone : +33 (0) 5 59 41 51 10
www.alaloop.com
--
Sent via pgsql-admin mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Baptiste LHOSTE
2012-12-17 16:40:38 UTC
Permalink
I run a select on the pg_stat_all_tables and it returns that there is 0 n_dead_tup.

I am really confused.

Best regards,

Baptiste.

---
Baptiste LHOSTE
***@alaloop.com

ALALOOP S.A.S. - Technopole Izarbel - 64210 Bidart
Téléphone : +33 (0) 5 59 41 51 10
www.alaloop.com
--
Sent via pgsql-admin mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Tom Lane
2012-12-17 17:01:05 UTC
Permalink
Post by Baptiste LHOSTE
Post by Guillaume Lelarge
As I said, because they are still visible to other transactions. Try to
see if you have long-lasting transactions.
How can I do that ? I check running query in pg_stat_activity, but there is no query on that table.
Does "select * from pg_prepared_xacts" find anything?

regards, tom lane
--
Sent via pgsql-admin mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Baptiste LHOSTE
2012-12-17 17:26:24 UTC
Permalink
Post by Tom Lane
Does "select * from pg_prepared_xacts" find anything?
Yes indeed, so I rollback our old prepared transactions.

I will check tomorrow, and I will let you know.

Best regards,

Baptiste.

---
Baptiste LHOSTE
***@alaloop.com

ALALOOP S.A.S. - Technopole Izarbel - 64210 Bidart
Téléphone : +33 (0) 5 59 41 51 10
www.alaloop.com
--
Sent via pgsql-admin mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Baptiste LHOSTE
2012-12-19 08:34:46 UTC
Permalink
Thanks both of you for your help.

The autovacuum process did the work yesterday.

Best regards,

Baptiste

---
Baptiste LHOSTE
***@alaloop.com

ALALOOP S.A.S. - Technopole Izarbel - 64210 Bidart
Téléphone : +33 (0) 5 59 41 51 10
www.alaloop.com
--
Sent via pgsql-admin mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Loading...