monitoring tuple_count vs dead_tuple_count

From: Mariel Cherkassky <mariel(dot)cherkassky(at)gmail(dot)com>
To: pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: monitoring tuple_count vs dead_tuple_count
Date: 2019-06-23 14:24:53
Message-ID: CA+t6e1nkgonSYNCjWucP1yz1NxeeyTgL0cnAACL6KgyJVcgWiA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi,
I wrote a script that monitored the size of a specific table of mine(dead
tuples_mb vs live tuples_mb). The script run a query on pg_stattuple every
15 minutes : select * from pg_stattuple('table_name'). I know that every
night there is a huge delete query that deletes most of the table`s
content. In addition, I set the following parameters for the table :
toast.autovacuum_vacuum_scale_factor=0,
toast.autovacuum_vacuum_threshold=10000,
toast.autovacuum_vacuum_cost_limit=10000,
toast.autovacuum_vacuum_cost_delay=5

After a week of monitoring I generates a csv of the results and I created a
graph from that data. However, the graph that I created confused me very
much.
A small sample of all the data that I gathered :
date toasted_live_tup_size_MB toasted_dead_tup_size_mb
6/16/19 0:00 58.8537941 25.68760395
6/16/19 0:15 8.725102425 25.02167416
6/16/19 0:30 8.668716431 25.08410168
6/16/19 0:45 8.810066223 24.94327927
6/16/19 1:00 8.732183456 25.02435684
6/16/19 1:15 8.67656517 20.01097107
6/16/19 1:30 9.573832512 20.76298809
6/16/19 1:45 9.562319756 20.7739706
6/16/19 2:00 9.567030907 21.01560402
6/16/19 2:15 9.576253891 70.62042999
6/16/19 2:30 9.715950966 492.2445602
6/16/19 2:45 9.59837532 801.455843
6/16/19 3:00 9.599774361 1110.201434
6/16/19 3:15 9.606671333 1402.255548
6/16/19 3:30 9.601698875 1698.487226
6/16/19 3:45 9.606934547 2003.051514
6/16/19 4:00 9.600641251 2307.625901
6/16/19 4:15 9.61320591 2612.196963
6/16/19 4:30 9.606646538 2916.773588
6/16/19 4:45 9.61294651 3221.337314
6/16/19 5:00 9.607636452 3525.914713
6/16/19 5:15 5.447218895 3826.313025
6/16/19 5:30 9.621054649 4130.883012
6/16/19 5:45 11.48730659 4433.29188
6/16/19 6:00 7.311745644 4742.039024
6/16/19 6:15 12.31321144 5135.994677
6/16/19 6:30 12.12382507 5671.512811
6/16/19 6:45 8.029448509 6171.677253
6/16/19 7:00 7.955677986 6666.846472
6/16/19 7:15 12.21173954 7161.934807
6/16/19 7:30 7.96325779 7661.273341
6/16/19 7:45 12.20623493 8156.362462
6/16/19 8:00 7.960205078 8655.704986
6/16/19 8:15 12.13819695 33.60424519
6/16/19 8:30 12.21746635 57.87192154
6/16/19 8:45 12.2179966 33.52415848
6/16/19 9:00 12.14417744 33.60204792
6/16/19 9:15 12.21954441 26.85134888

As you can see in this example, The size of the dead rows from 2am until
8am increased while there isnt any change in the size of the live rows.
During that time I know that there were a delete query that run and deleted
a lot of rows. That is why I'm confused here, if more dead rows are
generated because of a delete, it means that number of live_tuples should
be decreased but it doesnt happen here. Any idea why ?

Browse pgsql-performance by date

  From Date Subject
Next Message AminPG Jaffer 2019-06-23 15:07:56 Re: Incorrect index used in few cases..
Previous Message Rick Otten 2019-06-23 14:21:40 materialized view refresh of a foreign table