Обсуждение: Table health
I have been asked to check table health of specific big table.
What are list of things I have to check?
On 10/17/23 12:53, Rajesh Kumar wrote: > I have been asked to check table health of specific big table. Are there suspected problems? > What are list of things I have to check? amcheck is what you want? -- Born in Arizona, moved to Babylonia.
On Tue, 2023-10-17 at 23:23 +0530, Rajesh Kumar wrote: > I have been asked to check table health of specific big table. > > What are list of things I have to check? Look at pg_stat_all_tables to see if the number of dead tuples is excessive and if autovacuum and autoanalyze are running as they should. Look at pg_class to see if "relfrozenxid" and "relminmxid" are far from the danger zone. Run the function pgstattuple_approx() to see if the table is bloated. Yours, Laurenz Albe
As a preliminary and often but good enough test, I recommend VACUUM FREEZE VERBOSE ...
In 95% of cases such simple check will satisfy your needs .
Of course amcheck tool is much better but it is an extension.
In 95% of cases such simple check will satisfy your needs .
Of course amcheck tool is much better but it is an extension.
wt., 17 paź 2023 o 19:59 Rajesh Kumar <rajeshkumar.dba09@gmail.com> napisał(a):
I have been asked to check table health of specific big table.What are list of things I have to check?
There is no problem with vaccum, i used to do it periodically...apart from vaccum i am asking things like table bloat, index bloat, duplicate index removal, unused index removal, cache hit ratio for table, cache hit ratio for index, .......what else?
Also, i saw cache hit ratio is only 2% for one big table and all other tables are around 9%. Is that a problem?
On Wed, 18 Oct, 2023, 6:10 PM Tomek, <tomekphotos@gmail.com> wrote:
As a preliminary and often but good enough test, I recommend VACUUM FREEZE VERBOSE ...
In 95% of cases such simple check will satisfy your needs .
Of course amcheck tool is much better but it is an extension.wt., 17 paź 2023 o 19:59 Rajesh Kumar <rajeshkumar.dba09@gmail.com> napisał(a):I have been asked to check table health of specific big table.What are list of things I have to check?--
OK :-) Now you are more specific.
previously I understood that it was about whether the table was damaged in some way.
So, :-) if you define how you understand "health of table" you will automatically get the list you ask about (sorry :-) ).
previously I understood that it was about whether the table was damaged in some way.
So, :-) if you define how you understand "health of table" you will automatically get the list you ask about (sorry :-) ).
I think that cache hit is important but should be not on this "check table health" list.
śr., 18 paź 2023 o 15:37 Rajesh Kumar <rajeshkumar.dba09@gmail.com> napisał(a):
There is no problem with vaccum, i used to do it periodically...apart from vaccum i am asking things like table bloat, index bloat, duplicate index removal, unused index removal, cache hit ratio for table, cache hit ratio for index, .......what else?Also, i saw cache hit ratio is only 2% for one big table and all other tables are around 9%. Is that a problem?On Wed, 18 Oct, 2023, 6:10 PM Tomek, <tomekphotos@gmail.com> wrote:As a preliminary and often but good enough test, I recommend VACUUM FREEZE VERBOSE ...
In 95% of cases such simple check will satisfy your needs .
Of course amcheck tool is much better but it is an extension.wt., 17 paź 2023 o 19:59 Rajesh Kumar <rajeshkumar.dba09@gmail.com> napisał(a):I have been asked to check table health of specific big table.What are list of things I have to check?--
* cache hit ratio for ... : does it matter? You don't control that anyway.
* unused index removal : table pg_stat_all_indexes
* duplicate index removal : this query shows all indices, including columns. Look for duplicates in table_name+index_columns:
select tbcl.relnamespace::regnamespace::text||'.'||tbcl.relname as table_name
, ndcl.relname as index_name
, array_agg(att.attname order by att.attnum) as index_columns
from pg_class ndcl
inner join pg_index nd
on (ndcl.oid = nd.indexrelid and ndcl.relkind = 'i')
inner join pg_class tbcl
on (nd.indrelid = tbcl.oid and tbcl.relkind = 'r')
inner join pg_attribute att
on att.attrelid = nd.indexrelid
where tbcl.relnamespace::regnamespace::text != 'pg_catalog'
group by tbcl.relnamespace::regnamespace::text||'.'||tbcl.relname
, ndcl.relname
order by 1, 2
;
On 10/18/23 08:37, Rajesh Kumar wrote:
There is no problem with vaccum, i used to do it periodically...apart from vaccum i am asking things like table bloat, index bloat, duplicate index removal, unused index removal, cache hit ratio for table, cache hit ratio for index, .......what else?Also, i saw cache hit ratio is only 2% for one big table and all other tables are around 9%. Is that a problem?On Wed, 18 Oct, 2023, 6:10 PM Tomek, <tomekphotos@gmail.com> wrote:As a preliminary and often but good enough test, I recommend VACUUM FREEZE VERBOSE ...
In 95% of cases such simple check will satisfy your needs .
Of course amcheck tool is much better but it is an extension.wt., 17 paź 2023 o 19:59 Rajesh Kumar <rajeshkumar.dba09@gmail.com> napisał(a):I have been asked to check table health of specific big table.What are list of things I have to check?--
--
Born in Arizona, moved to Babylonia.
Born in Arizona, moved to Babylonia.
On 2023-Oct-17, Rajesh Kumar wrote: > I have been asked to check table health of specific big table. > > What are list of things I have to check? Install extension pg_visibility and run functions pg_check_frozen and pg_check_visible on the table. I've seen a couple of cases of **serious** data recoverability issues that aren't discovered until much later, caused by pages having been improperly marked all-frozen in some distant past. I assume it's possible to have pages marked as all-visible as well, which would cause different issues. -- Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/ "Aprender sin pensar es inútil; pensar sin aprender, peligroso" (Confucio)
Thanks
On Wed, 18 Oct, 2023, 7:51 PM Ron, <ronljohnsonjr@gmail.com> wrote:
* cache hit ratio for ... : does it matter? You don't control that anyway.
* unused index removal : table pg_stat_all_indexes
* duplicate index removal : this query shows all indices, including columns. Look for duplicates in table_name+index_columns:
select tbcl.relnamespace::regnamespace::text||'.'||tbcl.relname as table_name
, ndcl.relname as index_name
, array_agg(att.attname order by att.attnum) as index_columns
from pg_class ndcl
inner join pg_index nd
on (ndcl.oid = nd.indexrelid and ndcl.relkind = 'i')
inner join pg_class tbcl
on (nd.indrelid = tbcl.oid and tbcl.relkind = 'r')
inner join pg_attribute att
on att.attrelid = nd.indexrelid
where tbcl.relnamespace::regnamespace::text != 'pg_catalog'
group by tbcl.relnamespace::regnamespace::text||'.'||tbcl.relname
, ndcl.relname
order by 1, 2
;On 10/18/23 08:37, Rajesh Kumar wrote:There is no problem with vaccum, i used to do it periodically...apart from vaccum i am asking things like table bloat, index bloat, duplicate index removal, unused index removal, cache hit ratio for table, cache hit ratio for index, .......what else?Also, i saw cache hit ratio is only 2% for one big table and all other tables are around 9%. Is that a problem?On Wed, 18 Oct, 2023, 6:10 PM Tomek, <tomekphotos@gmail.com> wrote:As a preliminary and often but good enough test, I recommend VACUUM FREEZE VERBOSE ...
In 95% of cases such simple check will satisfy your needs .
Of course amcheck tool is much better but it is an extension.wt., 17 paź 2023 o 19:59 Rajesh Kumar <rajeshkumar.dba09@gmail.com> napisał(a):I have been asked to check table health of specific big table.What are list of things I have to check?----
Born in Arizona, moved to Babylonia.