pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedTable health
9+ messages / 5 participants
[nested] [flat]
* Table health
@ 2023-10-17 17:53 Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2023-10-17 19:15 ` Re: Table health Ron <ronljohnsonjr@gmail.com>
2023-10-18 06:52 ` Re: Table health Laurenz Albe <laurenz.albe@cybertec.at>
2023-10-18 12:40 ` Re: Table health Tomek <tomekphotos@gmail.com>
2023-10-18 14:13 ` Re: Table health Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 4 replies; 9+ messages in thread
From: Rajesh Kumar @ 2023-10-17 17:53 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
I have been asked to check table health of specific big table.
What are list of things I have to check?
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Table health
2023-10-17 17:53 Table health Rajesh Kumar <rajeshkumar.dba09@gmail.com>
@ 2023-10-17 19:15 ` Ron <ronljohnsonjr@gmail.com>
3 siblings, 0 replies; 9+ messages in thread
From: Ron @ 2023-10-17 19:15 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Table health
2023-10-17 17:53 Table health Rajesh Kumar <rajeshkumar.dba09@gmail.com>
@ 2023-10-18 06:52 ` Laurenz Albe <laurenz.albe@cybertec.at>
3 siblings, 0 replies; 9+ messages in thread
From: Laurenz Albe @ 2023-10-18 06:52 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Table health
2023-10-17 17:53 Table health Rajesh Kumar <rajeshkumar.dba09@gmail.com>
@ 2023-10-18 12:40 ` Tomek <tomekphotos@gmail.com>
2023-10-18 13:37 ` Re: Table health Rajesh Kumar <rajeshkumar.dba09@gmail.com>
3 siblings, 1 reply; 9+ messages in thread
From: Tomek @ 2023-10-18 12:40 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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?
>
--
--
Polecam moje mojego autorstwa na FB
<https://www.facebook.com/tomasz.main/photos_albums;
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Table health
2023-10-17 17:53 Table health Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2023-10-18 12:40 ` Re: Table health Tomek <tomekphotos@gmail.com>
@ 2023-10-18 13:37 ` Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2023-10-18 14:02 ` Re: Table health Tomek <tomekphotos@gmail.com>
2023-10-18 14:11 ` Re: Table health Ron <ronljohnsonjr@gmail.com>
0 siblings, 2 replies; 9+ messages in thread
From: Rajesh Kumar @ 2023-10-18 13:37 UTC (permalink / raw)
To: Tomek <tomekphotos@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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?
>>
>
>
> --
> --
> Polecam moje mojego autorstwa na FB
> <https://www.facebook.com/tomasz.main/photos_albums;
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Table health
2023-10-17 17:53 Table health Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2023-10-18 12:40 ` Re: Table health Tomek <tomekphotos@gmail.com>
2023-10-18 13:37 ` Re: Table health Rajesh Kumar <rajeshkumar.dba09@gmail.com>
@ 2023-10-18 14:02 ` Tomek <tomekphotos@gmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: Tomek @ 2023-10-18 14:02 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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 :-) ).
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?
>>>
>>
>>
>> --
>> --
>> Polecam moje mojego autorstwa na FB
>> <https://www.facebook.com/tomasz.main/photos_albums;
>>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Table health
2023-10-17 17:53 Table health Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2023-10-18 12:40 ` Re: Table health Tomek <tomekphotos@gmail.com>
2023-10-18 13:37 ` Re: Table health Rajesh Kumar <rajeshkumar.dba09@gmail.com>
@ 2023-10-18 14:11 ` Ron <ronljohnsonjr@gmail.com>
2023-10-19 04:30 ` Re: Table health Rajesh Kumar <rajeshkumar.dba09@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Ron @ 2023-10-18 14:11 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
* 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?
>
>
>
> --
> --
> Polecam moje mojego autorstwa na FB
> <https://www.facebook.com/tomasz.main/photos_albums;
>
--
Born in Arizona, moved to Babylonia.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Table health
2023-10-17 17:53 Table health Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2023-10-18 12:40 ` Re: Table health Tomek <tomekphotos@gmail.com>
2023-10-18 13:37 ` Re: Table health Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2023-10-18 14:11 ` Re: Table health Ron <ronljohnsonjr@gmail.com>
@ 2023-10-19 04:30 ` Rajesh Kumar <rajeshkumar.dba09@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Rajesh Kumar @ 2023-10-19 04:30 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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?
>>>
>>
>>
>> --
>> --
>> Polecam moje mojego autorstwa na FB
>> <https://www.facebook.com/tomasz.main/photos_albums;
>>
>
> --
> Born in Arizona, moved to Babylonia.
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Table health
2023-10-17 17:53 Table health Rajesh Kumar <rajeshkumar.dba09@gmail.com>
@ 2023-10-18 14:13 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
3 siblings, 0 replies; 9+ messages in thread
From: Alvaro Herrera @ 2023-10-18 14:13 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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)
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2023-10-19 04:30 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-10-17 17:53 Table health Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2023-10-17 19:15 ` Ron <ronljohnsonjr@gmail.com>
2023-10-18 06:52 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-10-18 12:40 ` Tomek <tomekphotos@gmail.com>
2023-10-18 13:37 ` Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2023-10-18 14:02 ` Tomek <tomekphotos@gmail.com>
2023-10-18 14:11 ` Ron <ronljohnsonjr@gmail.com>
2023-10-19 04:30 ` Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2023-10-18 14:13 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
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