Re: Monitoring DB size

[semab tariq]

Hi Manuel

Sorry for the late reply saw this email just today. Anyways how about using
something like?

postgres=# SELECT
postgres-# pg_size_pretty(pg_database_size('postgres')) AS
database_size,
postgres-# pg_size_pretty(pg_total_relation_size('accounts')) AS
table_size,
postgres-# pg_size_pretty(tuple_len) AS live_tuple_size,
postgres-# pg_size_pretty(dead_tuple_len) AS dead_tuple_size,
postgres-# pg_size_pretty(free_space) AS free_space
postgres-# FROM
postgres-# pgstattuple('accounts');
 database_size | table_size | live_tuple_size | dead_tuple_size |
free_space
---++-+-+
 8500 kB   | 40 kB  | 80 bytes| 80 bytes| 7988 bytes
(1 row)


Thanks and regards
Semab


On Tue, Jul 16, 2024 at 4:38 PM Shenavai, Manuel 
wrote:

> Thanks for the suggestion. I think this will not help us to differentiate
> between live tuples, dead tuples and free space.
>
>
>
> Best regards,
>
> Manuel
>
>
>
> *From:* Torsten Förtsch 
> *Sent:* 15 July 2024 18:59
> *To:* Shenavai, Manuel 
> *Cc:* pgsql-general@lists.postgresql.org
> *Subject:* Re: Monitoring DB size
>
>
>
> Slightly different approach than you might expect. For larger DBs you'd
> likely want to exclude base and instead use pg_database_size() in addition.
>
>
>
> postgres(2454884) =# create temp table xx(dir text, sz bigint);
> CREATE TABLE
> Time: 2.587 ms
> postgres(2454884) =# copy xx(sz, dir) from program 'du -s *';
>
> COPY 21
> Time: 3.793 ms
> postgres(2454884) =# select * from xx;
> dir  |  sz
> --+---
> base | 26280
> global   |   568
> pg_commit_ts |12
> pg_dynshmem  | 4
> pg_logical   |16
> pg_multixact |28
> pg_notify| 4
> pg_replslot  | 4
> pg_serial| 4
> pg_snapshots | 4
> pg_stat  | 4
> pg_stat_tmp  | 4
> pg_subtrans  |12
> pg_tblspc| 4
> pg_twophase  | 4
> PG_VERSION   | 4
> pg_wal   | 16392
> pg_xact  |12
> postgresql.auto.conf | 4
> postmaster.opts  | 4
> postmaster.pid   | 4
> (21 rows)
>
> Time: 0.282 ms
>
>
>
> On Mon, Jul 15, 2024 at 4:42 PM Shenavai, Manuel 
> wrote:
>
> Hi everyone,
>
>
>
> we currently capture the db size (pg_database_size) which gives the “Disk
> space used by the database with the specified name”. Is it possible to
> further split this data how much space is occupied by live tuples, dead
> tuples and free space?
>
>
>
> We would like to have something like:
>
> DB Size: 200 MB, live tuple 100MB, Dead Tuple: 20 MB, free space 80MB
>
>
>
> Is this possible?
>
>
>
> Best regards,
>
> Manuel
>
>

Re: autovacuum freeze recommendations at table level

[semab tariq]

Hi Durga

*autovacuum_freeze_max_age* specifies the maximum age (in transactions)
that a table's tuples can reach before a vacuum is forced to prevent
transaction ID wraparound. when the age of the oldest tuple in the table
exceeds this value, an autovacuum is triggered to freeze the tuples.
*Recommendation = 2000 -> 15000 *

*autovacuum_multixact_freeze_max_age *It is similar to above, but applies
to multi-transaction IDs (used for shared row locks). when the age of the
oldest multi-transaction ID exceeds this value, an autovacuum is triggered
to freeze the multi-transaction IDs.
*Recommendation = 2000 -> 15000*

*autovacuum_freeze_min_age* specifies the minimum age (in transactions)
that a tuple must reach before it is considered for freezing. Lowering this
value can cause more frequent freezing, which can increase the overhead of
autovacuum.
*Recommendation = 0 -> 5000*

Thanks, Semab

On Sun, Aug 11, 2024 at 11:12 AM Durgamahesh Manne <
maheshpostgr...@gmail.com> wrote:

> Hi  Respected Team,
>
> Could you please let me know that how this freeze parameters work
> Update query runs on table  through which data being modified daily in
> this case
> Total records in table is about 20lakhs
> current setting for this table is
> Access method: heap
> if it reaches > 0.1*200+1000 = 2,10,000 as per the formula autovacuum
> triggers
> Options: fillfactor=85, autovacuum_vacuum_cost_delay=0,
> autovacuum_vacuum_cost_limit=3000, parallel_workers=6,
> autovacuum_vacuum_scale_factor=0.1, autovacuum_vacuum_threshold=1000,
> autovacuum_freeze_max_age=2000,
> autovacuum_multixact_freeze_max_age=2000, autovacuum_freeze_min_age=0
>
> How autovacuum freeze parameters work.Give me some recommendations to
> improve the performance better than now
> Ex :ALTER TABLE table SET (
>autovacuum_freeze_max_age = 2000,(2 crores)
>autovacuum_multixact_freeze_max_age = 2000,(2 crores)
>autovacuum_freeze_min_age = 0
> );
> Regards,
> Durga Mahesh
>