Having problems with your account or logging in?
A lot of changes are happening in the community right now. Some may affect you. READ MORE HERE
Micro Focus Expert
Micro Focus Expert
195 views

(DP) Support Tip: DP 9.x - IDB "bloat" error / pg_reorg tool info

Jump to solution

(DP) Support Tip: DP 9.x - IDB "bloat" error / pg_reorg tool info

Issue :

- Upgraded to Data protector V9.06 from 7.
- Enabled alerts for the IDB

and now, they are alarmed on a lot of these entries from Ob2EventLog.txt

Warning 06/23/2016 11:07 OMNITRIG DbReorgNeeded "[138:762] Fragmentation of the table dp_catalog_object_datastream in the column objver_seq_id detected."

Warning 06/27/2016 12:30 OMNITRIG DbReorgNeeded "[138:763] Fragmentation of the index dp_catalog_object_version_data_protection_index detected."
Warning 06/27/2016 12:30 OMNITRIG DbReorgNeeded "[138:763] Fragmentation of the index dp_catalog_object_version_object_access_index detected."
Warning 06/27/2016 12:30 OMNITRIG DbReorgNeeded "[138:763] Fragmentation of the index dp_catalog_objver_backup_name_idx detected."
Warning 06/27/2016 12:30 OMNITRIG DbReorgNeeded "[138:763] Fragmentation of the index dp_catalog_objver_pit_ux_idx detected."
Warning 06/27/2016 12:30 OMNITRIG DbReorgNeeded "[138:763] Fragmentation of the index dp_catalog_objver_restore_graph_idx detected."
Warning 06/27/2016 12:30 OMNITRIG DbReorgNeeded "[138:763] Fragmentation of the index dp_catalog_objver_object_idx detected."
Warning 06/27/2016 12:30 OMNITRIG DbReorgNeeded "[138:763] Fragmentation of the index dp_object_uk detected."
Warning 06/27/2016 12:30 OMNITRIG DbReorgNeeded "[138:763] Fragmentation of the index dp_catalog_object_name_idx detected."
Warning 06/27/2016 12:30 OMNITRIG DbReorgNeeded "[138:763] Fragmentation of the index dp_catalog_position_medium_idx detected."
Warning 06/27/2016 12:30 OMNITRIG DbReorgNeeded "[138:763] Fragmentation of the index dp_catalog_position_objver_idx detected."
Warning 06/27/2016 12:30 OMNITRIG DbReorgNeeded "[138:763] Fragmentation of the index dp_catalog_position_seqacc_med_detail_catalog_exists_index detected."
Warning 06/27/2016 12:30 OMNITRIG DbReorgNeeded "[138:763] Fragmentation of the index position_pk detected."
Warning 06/27/2016 12:30 OMNITRIG DbReorgNeeded "[138:763] Fragmentation of the index datastream_pk detected."
Warning 06/27/2016 12:30 OMNITRIG DbReorgNeeded "[138:763] Fragmentation of the index dp_catalog_objdatstr_btag_idx detected."
Warning 06/27/2016 12:30 OMNITRIG DbReorgNeeded "[138:763] Fragmentation of the index objver_session_pk detected."
Warning 06/27/2016 12:30 OMNITRIG DbReorgNeeded "[138:763] Fragmentation of the index dp_catalog_objversession_session_idx detected."

Warning 06/27/2016 12:30 OMNITRIG DbReorgNeeded "[138:724] Bloat of the table dp_catalog_object detected."
Warning 06/27/2016 12:30 OMNITRIG DbReorgNeeded "[138:724] Bloat of the table dp_catalog_object_datastream detected."
Warning 06/27/2016 12:30 OMNITRIG DbReorgNeeded "[138:724] Bloat of the table dp_catalog_object_versession detected."
Warning 06/27/2016 12:30 OMNITRIG DbReorgNeeded "[138:724] Bloat of the table dp_catalog_object_version detected."
Warning 06/27/2016 12:30 OMNITRIG DbReorgNeeded "[138:724] Bloat of the table dp_catalog_object_version_data_protection_index detected."
Warning 06/27/2016 12:30 OMNITRIG DbReorgNeeded "[138:724] Bloat of the table dp_catalog_position_seqacc_med detected."
Warning 06/27/2016 12:30 OMNITRIG DbReorgNeeded "[138:724] Bloat of the table dp_catalog_position_seqacc_med_detail_catalog_exists_index detected."


From the Trouble.txt file :

MESSAGE:
[138:724] Bloat of the table p detected.

DESCRIPTION:
Data Protector Internal Database tables should never grow ("bloat") due to unreclaimed dead rows.
According to bloat threshold, hp recommends to reorganize the table.

ACTION:
To reorganize a table, use the pg_reorg tool.

 

MESSAGE:
[138:762] Fragmentation of the table p in the column p detected.

DESCRIPTION:
According to table fragmentation threshold, hp recommends to reorganize the table on the stated column.

ACTION:
Use the pg_reorg tool to reorganize the stated table.

 

MESSAGE:
[138:763] Fragmentation of the index p detected.

DESCRIPTION:
According to index fragmentation threshold, hp recommends to reorganize the index.

ACTION:
Run the following Data Protector command to reorganize an index:

omnidbutil -reindex -index <IndexName>

 

 

RECOMMENDATION - Check the Postgres logs and if it don't show anything to be worried about, do nothing and ignore these reorg "warnings".

Run IDB check (omnidbcheck -extended) and IDB backup to ensure IDB is good.


Postgres typically doesn't need any special "reorg" or "bloat" removal steps because it has autovacuum doing it continuously where it matters.

The tables and indices get "bloated" over time but that doesn't mean it's worth "reorg-ing" them.

The -chkreorg code looks at correlation between the order of column values and their physical order and thinks near-zero correlation means "fragmentation". It's impossible to have all columns highly correlated to the physical order on disk unless the columns are highly correlated between each other - which would be nice but it's very unlikely in a real-world database (i.e. physically reordering the table by one column order would disorder the table for other columns etc). High correlation is only useful for queries returning large result sets where we'd want sequential table scan to result in a nearly sequential disk read to maximize I/O throughput. However this is not the typical query scenario in DP - the typical query scenario is an index scan returning a small result set.

No need to do anything because whatever actually is worth doing, is already being done automatically via autovacuum.

Labels (1)
0 Likes
1 Solution

Accepted Solutions
Knowledge Partner Knowledge Partner
Knowledge Partner

Re: (DP) Support Tip: DP 9.x - IDB "bloat" error / pg_reorg tool info

Jump to solution

The Trouble.txt is located in C:\Program Files\OmniBack\help\enu.

This kind of alerts can be configured using Data Protector GUI => Reporting => Notifications => Add Notification.

Regards,
Sebastian Koehler

---
Please use the Like button below, if you find this post useful.
0 Likes
2 Replies
Absent Member.. KloppYNWA69 Absent Member..
Absent Member..

Re: (DP) Support Tip: DP 9.x - IDB "bloat" error / pg_reorg tool info

Jump to solution

how do you enable IDB alerts and does this create trouble.txt ?

0 Likes
Knowledge Partner Knowledge Partner
Knowledge Partner

Re: (DP) Support Tip: DP 9.x - IDB "bloat" error / pg_reorg tool info

Jump to solution

The Trouble.txt is located in C:\Program Files\OmniBack\help\enu.

This kind of alerts can be configured using Data Protector GUI => Reporting => Notifications => Add Notification.

Regards,
Sebastian Koehler

---
Please use the Like button below, if you find this post useful.
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.