Show Children Hide Children FtsRelease20TableFragmentationCern
Main FTS Pages
FtsRelease22
Install
Configuration
Administration
Procedures
Operations
Development
Previous FTSes
FtsRelease21
FtsRelease21
All FTS Pages
FtsWikiPages
Last Page Update
GavinMcCance
2008-09-01

FTS table fragmentation on FTS 1.5 and FTS 2.0

Scope:

This has been noted on FTS versions 1.5 and 2.0 when running that FTS 'history' cleanup tool described in FtsAdminTools15.

Impact:

  • Standard service operation is not affected (since FTS uses indices to find the blocks).
  • The table takes up more space than it should (since the fragmented blocks have a large unused portion).
  • Schema upgrades (in particular new index builds) take much longer than they should, since they require a full table scan which requires reading all the blocks into the DB buffer cache.

Resolution:

  • Before schema upgrade, the tables can be defragmented. The actual cause of the fragmentation is being understood with Oracle support.

Notice

Please do this in collaboration with your DBA. It is likely that most of these operations will require DBA priviliges.

Please do this in collaboration with the WLCG 3D project. Any question should be sumitted to grid-service-databases@cern.ch.

How to check for fragmentation

Check if any table is highly fragmented (>60%)

select a.owner, table_name, mb_used, mb_allocated,
round(100-((mb_used*100)/mb_allocated)) PCT_FRAGMENTED from 
  (select round((num_rows * avg_row_len)/1024/1024) MB_used, table_name, owner from dba_tables) a, 
  (select round(sum(bytes)/1024/1024) MB_allocated, segment_name, 
     owner from dba_segments group by segment_name, owner) b
   where a.table_name=b.segment_name 
      and a.owner=b.owner and mb_allocated>100 and a.owner not in 'SYS'
   order by 5 desc, 4 desc;

the likely candidates for high levels of fragmentation are t_file, t_job and t_transfer.

How to defragment

Check with grid-service-databases@cern.ch first!

Notes:

  1. Stop all daemons from accessing the database
  2. The SHRINK procedure requires ASSM tablespaces
  3. You will need to drop the timestamp function-based indices and recreate them afterwards

Basic procedure per table you want to fragment:

  1. Activate row movement on this table: alter table OWNER.TABLE_NAME enable row movement;
  2. Shrink it: alter table OWNER.TABLE_NAME shrink space;
  3. Re-gather stats: exec dbms_stats.gather_table_stats('OWNER','TABLE_NAME');

See FtsRelease20TableFragmentationCern for CERN procedure.

-- GavinMcCance - 18 Jul 2007

Edit | Attach | Watch | Print version | History: r3 < r2 < r1 | Backlinks | Raw View | WYSIWYG | More topic actions
Topic revision: r3 - 2008-09-01 - GavinMcCance
 
    • Cern Search Icon Cern Search
    • TWiki Search Icon TWiki Search
    • Google Search Icon Google Search

    LCG All webs login

This site is powered by the TWiki collaboration platform Powered by PerlCopyright & 2008-2019 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback