From: pavan95 <pavan.postgresdba@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: 8.2 Autovacuum BUG ?
Date: Wed, 31 Jan 2018 23:18:00 -0700 (MST)
Message-ID: <1517465880209-0.post@n3.nabble.com> (raw)
In-Reply-To: <CACh9nsYFjrF9xPuQSkc_ffDz4-7CMHabLfZrMs5AXMEzsZwt6Q@mail.gmail.com>
References: <1516776879945-0.post@n3.nabble.com>
<CAGTBQpa-mTQ9qA18xVimrgTmrXZnFAnqKpzHYwCNtc=GEEd+5w@mail.gmail.com>
<1516794633075-0.post@n3.nabble.com>
<CAGTBQpbDRmAaiveO2bDOtXhdBJBqcgvfzmp69=XKDJ3mKcQCHg@mail.gmail.com>
<CACh9nsYDjR817FpH+gs_81WtcOSRyc_0SU_VZM9haOm_b4wOGg@mail.gmail.com>
<20180124151920.vonyeexv7qqledjy@alvherre.pgsql>
<1516852035576-0.post@n3.nabble.com>
<20180125213010.2tdsha5npaegvzpo@alvherre.pgsql>
<CACh9nsY=c=-Ne_NU-jrT+qAL9hRbUM3USOCr_nRiPskDeAYWkg@mail.gmail.com>
<CACh9nsYFjrF9xPuQSkc_ffDz4-7CMHabLfZrMs5AXMEzsZwt6Q@mail.gmail.com>
Hi all,
Regarding this archive log generation found one observation.
A table named abc_table id found to be archived every 9'th and 39'th minute.
We are able to find number of tuples deleted from the pg_stat_user_tables
view.
But to my wonder the number of tuple inserts are shown 0. How can there be
any delete without any inserts.
It was found that the table is having 2060 rows, where in which all rows are
getting deleted in every 9'th and 39'th minute of an hour. It implies that
those deleted should be inserted before the delete operation.
Also performed vacuum freeze on that table before 9'th minute of an hour it
generated 36 archive logs, and when I tried to do the same operation after
9'th minute(say 11'th minute of the same hour), it is generating the same
number of archive logs.
This is possible only if the entire table gets updated/recreated. Now my
final doubt is why the tuple inserts in pg_stat_user_tables is showing 0,
when corresponding deletes are existing?
Please find the below outputs FYR.
--Steps performed on production server:--
--1. Found Count Of Rows in Production
--******************************************
prod_erp=# select count(*) from abc_table;;
count
-------
2060
(1 row)
--2. Issued 'Select pg_stat_reset();'
--3. Before Delete Statements (Before JAN 31'st 2018 14:09 Hrs)
--****************************************************************
Issued:
select * from pg_stat_user_tables where relname ='abc_table';
-[ RECORD 1 ]-----+----------------------------
relid | 550314
schemaname | public
relname | abc_table
seq_scan | 2
seq_tup_read | 4120
idx_scan | 0
idx_tup_fetch | 0
n_tup_ins | 0
n_tup_upd | 0
n_tup_del | 0
n_tup_hot_upd | 0
n_live_tup | 0
n_dead_tup | 0
last_vacuum |
last_autovacuum |
last_analyze |
last_autoanalyze |
vacuum_count | 0
autovacuum_count | 0
analyze_count | 0
autoanalyze_count | 0
--4. After Delete Statements (Before JAN 31'st 2018 14:09 Hrs)
--****************************************************************
select * from pg_stat_user_tables where relname ='abc_table';
-[ RECORD 1 ]-----+----------------------------
relid | 550314
schemaname | public
relname | abc_table
seq_scan | 3
seq_tup_read | 6180
idx_scan | 2060
idx_tup_fetch | 2060
n_tup_ins | 0
n_tup_upd | 0
n_tup_del | 2060
n_tup_hot_upd | 0
n_live_tup | 0
n_dead_tup | 0
last_vacuum |
last_autovacuum |
last_analyze |
last_autoanalyze |
vacuum_count | 0
autovacuum_count | 0
analyze_count | 0
autoanalyze_count | 0
--5. After Delete Statements (Before JAN 31'st 2018 14:39 Hrs)
--****************************************************************
select * from pg_stat_user_tables where relname ='abc_table';
-[ RECORD 1 ]-----+----------------------------
relid | 550314
schemaname | public
relname | abc_table
seq_scan | 4
seq_tup_read | 8240
idx_scan | 4120
idx_tup_fetch | 4120
n_tup_ins | 0
n_tup_upd | 0
n_tup_del | 4120
n_tup_hot_upd | 0
n_live_tup | 0
n_dead_tup | 0
last_vacuum |
last_autovacuum |
last_analyze |
last_autoanalyze |
vacuum_count | 0
autovacuum_count | 0
analyze_count | 0
autoanalyze_count | 0
--6. After Delete Statements (Before JAN 31'st 2018 15:09 Hrs)
--****************************************************************
select * from pg_stat_user_tables where relname ='abc_table';
-[ RECORD 1 ]-----+----------------------------
relid | 550314
schemaname | public
relname | abc_table
seq_scan | 5
seq_tup_read | 10300
idx_scan | 6180
idx_tup_fetch | 6180
n_tup_ins | 0
n_tup_upd | 0
n_tup_del | 6180
n_tup_hot_upd | 0
n_live_tup | 0
n_dead_tup | 0
last_vacuum |
last_autovacuum |
last_analyze |
last_autoanalyze |
vacuum_count | 0
autovacuum_count | 0
analyze_count | 0
autoanalyze_count | 0
As said above if we compare n_tup_del value in steps 4,5,6 it says us that
entire table is getting deleted(correct me if I'm wrong), but n_tup_ins is
0.
Regards,
Pavan
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pavan.postgresdba@gmail.com
Subject: Re: 8.2 Autovacuum BUG ?
In-Reply-To: <1517465880209-0.post@n3.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox