Register Login
Python Photoshop SAP Java PHP Android C++ Hadoop Oracle Interview Questions Articles Other

Restart-time Row Store reorganization Procedure

Updated May 18, 2018

How to Restart-time Row Store Reorganization?

What to do if Row store memory size is a lot bigger than the actual data size in row store and shows high fragmentation ratio.

Solution

Restart-time Row Store Reorganization

Restart-time row store reorganization procedure

1. Make a complete database backup

2. Upgrade database to Rev50 or later

3. Make sure to start up database at least once during upgrade.

4. Shutdown HANA database.

5. Set up configuration parameter in indexserver.ini via HANA Studio.

[row_engine] page_compaction_enable = true

6. Restart the database. Startup will take longer than normal startup when row store reorganization runs.

7. Check the memory fragmentation status after reorganization:

Rev.52 or higher

SELECT HOST, PORT, CASE WHEN (((SUM(FREE_SIZE) / SUM(ALLOCATED_SIZE)) > 0.30) AND SUM(ALLOCATED_SIZE) > TO_DECIMAL(10)*1024*1024*1024) THEN 'TRUE' ELSE 'FALSE' END "Row store Reorganization Recommended", TO_DECIMAL( SUM(FREE_SIZE)*100 / SUM(ALLOCATED_SIZE), 10,2) "Free Space Ratio in %",TO_DECIMAL( SUM(ALLOCATED_SIZE)/1048576, 10, 2) "Allocated Size in MB",TO_DECIMAL( SUM(FREE_SIZE)/1048576, 10, 2) "Free Size in MB" FROM M_RS_MEMORY WHERE ( CATEGORY = 'TABLE' OR CATEGORY = 'CATALOG' ) AND PORT LIKE '%03' GROUP BY HOST, PORT;

Rev. 50 or Rev. 51

SELECT HOST, PORT, CASE WHEN (((SUM(FREE_SIZE) / SUM(ALLOCATED_SIZE)) > 0.30) AND SUM(ALLOCATED_SIZE) > TO_DECIMAL(10)*1024*1024*1024) THEN 'TRUE' ELSE 'FALSE' END "Row store Reorganization Recommended", TO_DECIMAL( SUM(FREE_SIZE)*100 / SUM(ALLOCATED_SIZE), 10,2) "Free Space Ratio in %",TO_DECIMAL( SUM(ALLOCATED_SIZE)/1048576, 10, 2) "Allocated Size in MB",TO_DECIMAL( SUM(FREE_SIZE)/1048576, 10, 2) "Free Size in MB" FROM M_SHARED_MEMORY WHERE ( CATEGORY = 'TABLE' OR CATEGORY = 'CATALOG' ) AND PORT LIKE '%03' GROUP BY HOST, PORT;

8. Check Indexserver trace

Please check the indexserver trace files after row store reorganization is done.

Row store reorganization is performed in several phases when successful. It prints out "[RSReorg] start" message at the beginning, "[RSReorg] success" message at the end. When there are errors during reorganization, row store reorganization is stopped in that phase and all the changes are rolled back and database starts up to return to its original state before reorganization is run.

Successful case (Please note that the steps can be changed depending on the Revision)

  • i Row_Engine msglog.cc(00069) : [RSReorg] start
  • i Row_Engine msglog.cc(00069) : [RSReorg] Phase 1. collect target pages
  • i Row_Engine msglog.cc(00069) : [RSReorg] total 20 segments
  • i Row_Engine msglog.cc(00069) : [RSReorg] Phase 1. collected 24661 pages in 0.012204 sec
  • i Row_Engine msglog.cc(00069) : [RSReorg] Phase 2. check if version exists
  • i Row_Engine msglog.cc(00069) : [RSReorg] Phase 2. checked if version exists in 0.00077300000000000003 sec
  • i Row_Engine msglog.cc(00069) : [RSReorg] Phase 3. gather all pointer types
  • i Row_Engine msglog.cc(00069) : [RSReorg] Phase 3. gathered all pointer types in 0.002408 sec
  • i Row_Engine msglog.cc(00069) : [RSReorg] Phase 4. build pointer map
  • i Row_Engine msglog.cc(00069) : [RSReorg] Phase 4. built pointers map in 0.090515999999999999 sec
  • i Row_Engine msglog.cc(00069) : [RSReorg] Phase 5. update container infos
  • i Row_Engine msglog.cc(00069) : [RSReorg] Phase 5. updated container infos in 0. 033314000000000003 sec
  • i Row_Engine msglog.cc(00069) : [RSReorg] Phase 6. compute checksum of user tables
  • i Row_Engine msglog.cc(00069) : [RSReorg] Phase 6. computed checksum of user table contents and variable-length data in 2.180126 sec
  • i Row_Engine msglog.cc(00069) : [RSReorg] Phase 7. move pages
  • i Row_Engine msglog.cc(00069) : [RSReorg] Phase 7. moved 507 pages in 0.110776 sec
  • i Row_Engine msglog.cc(00069) : [RSReorg] Phase 8. update pointer references
  • i Row_Engine msglog.cc(00069) : [RSReorg] Phase 8. updated pointer references in 0.0049760000000000004 sec
  • i Row_Engine msglog.cc(00069) : [RSReorg] Phase 9. check metadata integrity
  • i Row_Engine msglog.cc(00069) : [RSReorg] Phase 9. checked metadata integrity in 0.023789000000000001 sec
  • i Row_Engine msglog.cc(00069) : [RSReorg] Phase 10. compare variable-length data checksum
  • i Row_Engine msglog.cc(00069) : [RSReorg] Phase 10. compared variable-length data checksum in 0.72106700000000001 sec
  • i Row_Engine msglog.cc(00069) : [RSReorg] Phase 11. compare user table contents checksum
  • i Row_Engine msglog.cc(00069) : [RSReorg] Phase 11. compared user table contents checksum in 1.01956 sec

The folowings lines depending on the Revision.

1. (~Rev.51)

i Row_Engine msglog.cc(00069) :== Reorganization statistics ==

number of moved pages: 507

before reorganization

number of segments: 21

after reorganization

number of segments: 16

i Row_Engine msglog.cc(00069) : [RSReorg] success (507 pages)

2. Rev. 52 or higher

  • i Row_Engine msglog.cc(00069) : [RSReorg] RowStore memory reduced from 122432MB (1913 segments) to 46464MB (726 segments) by moving 54789 pages.
  • i Row_Engine msglog.cc(00069) : [RSReorg] success (54789 pages)
  • i Row_Engine msglog.cc(00069) : [RSReorg] finished in 657.60 sec
  • i Row_Engine msglog.cc(00069) : [RSReorg] make sure that free segments are deallocated
  • i Row_Engine msglog.cc(00069) : [RSReorg] trigger asynchronous savepoint

Failed case:

  • i Row_Engine msglog.cc(00069) : [RSReorg] 1,0,0,1048576,1,0
  • i Row_Engine msglog.cc(00069) : [RSReorg] start
  • i Row_Engine msglog.cc(00069) : [RSReorg] Phase 1. collect target pages
  • i Row_Engine msglog.cc(00069) : [RSReorg] total 20 segments
  • i Row_Engine msglog.cc(00069) : [RSReorg] Phase 1. collected 24661 pages in 0.012204 sec
  • i Row_Engine msglog.cc(00069) : [RSReorg] Phase 2. check if version exists
  • i Row_Engine msglog.cc(00069) : [RSReorg] Phase 2. checked if version exists in 0.00077300000000000003 sec
  • e Row_Engine msglog.cc(00069) : [RSReorg] failure: RSReorg failed to check correctness

9. Check catalog integrity

The following procedure call should run successfully and returns empty result set. CALL CHECK_CATALOG('CHECK_OBJECT_REFERENTIAL_INTEGRITY','','','');

10. Change the configuration parameter to disable row store reorganization in indexserver.ini

[row_engine] page_compaction_enable = false

Online Row Store Reorganization

Note: Be informed that online row store reorganization acquires exclusive table lock of a number of tables. Thus, update operations on those tables cannot be done until the online row store reorgainzation finishes reorganization of corresponding tables.

  • Make a complete database backup
  • Upgrade database to Rev64 or later
  • Execute Online Row Store Reorganization command

ALTER SYSTEM RELCAIM DATA SPACE

To see the progress, check the "Job Progress" Tab in "Performance" Tab of the HANA Studio

Please check the indexserver trace files after row store reorganization is done. The trace of Online Row Store Reorganization is separately managed in indexserver_<hostname>.<port>.row_store_reorg.<sequence>.trc

Successful case (Please note that the steps can be changed depending on the Revision)

[000000003545][752026] 2013-12-18 01:11:24.978133 i RowStoreReor OnlineReorganizer.cc(00231) : [OnlineReorg] online RS reorg started
[000000003545][-1] 2013-12-18 01:11:25.020654 i RowStoreReor OnlineReorganizer.cc(00120) : [OnlineReorg] inspecting segment fragmentation
[000000003545][-1] 2013-12-18 01:11:31.599009 i RowStoreReor OnlineReorganizer.cc(00128) : [OnlineReorg] inspection done.
[000000003545][-1] 2013-12-18 01:11:32.879678 i RowStoreReor OnlineReorganizer.cc(00164) : [OnlineReorg] 730 segments (46720 MB) could be freed.
[000000003545][-1] 2013-12-18 01:11:33.211998 i RowStoreReor OnlineReorgContainer.cc(00821) : [OnlineReorg] trying to obtain lock for SYS.P_CONSTRAINTS_(131759)

[000000003545][-1] 2013-12-18 01:12:00.913425 i RowStoreReor OnlineReorgContainer.cc(00875) : [OnlineReorg] possibly moved 42664 pages
[000000003545][-1] 2013-12-18 01:12:00.913579 i RowStoreReor OnlineReorganizer.cc(00268) : [OnlineReorg] online RS reorg finished: 27.702895000000002
[000000003545][752026] 2013-12-18 01:12:01.029016 i RowStoreReor OnlineReorganizer.cc(00289) : initiating page reclamation

Recommended Posts:
×