table index compression
Post on 09-Apr-2018
224 Views
Preview:
TRANSCRIPT
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 1/70
Presentation prepared by:•Matt Huras - IBM
An Insider's Look at theNext Release of DB2 :New Kernel Features
Presented by:•Camp Hill – Dwaine Snow
•Dallas – Bill Minor
•Austin - Matt Huras
•Minneapolis – Paul Zikopolous
•Kansas City – Matt Huras
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 2/70
2
Agenda• Compression Advancements
• Indexes
• Temp Data
• LOBs
• Storage Management• Automatic Storage : Rebalance Improvements
• Automatic Storage : Easier Adoption
• Reclaimable Storage
• Table Maintenance• Sparse MDC Tables
• Online Table Move
• Online (Inplace) Table Reorganization Improvements
• Table Partitioning• Partitioned Indexes
• Currently Committed Isolation
• High Availability Disaster Recovery• Active Standby
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 3/70
3
Disclaimer
• This presentation describes technology IBM may, ormay not, deliver in a future deliverable(s) of DB2 LUW
• Presentation of this material is not a commitment thatthe technology will be delivered in the future
• This information is dynamic in nature due to the stateof development. It is possible that information may bemodified or added prior to presentation at IDUG.
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 4/70
4
Agenda• Compression Advancements
• Indexes
• Temp Data
• LOBs
• Storage Management• Automatic Storage : Rebalance Improvements
• Automatic Storage : Easier Adoption
• Reclaimable Storage
• Table Maintenance• Online Table Move
• Online Table Reorganization Improvements
• Sparse MDC Tables
• Table Partitioning• Partitioned Indexes
• Currently Committed Isolation
• High Availability Disaster Recovery• Active Standby
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 5/70
5
Index Compression : Overview
• Three different compression techniques applies to index pages1. Slot directory compression
2. “RIDlist” compression
3. Prefix key compression
• Index pages stored compressed on disk and in bufferpool
• As with table compression
• New INDEX compression attribute• CREATE INDEX … COMPRESS YES
• ALTER INDEX … COMPRESS YES
• An INDEX will be compressed when created/reorg’d/loaded, if:• The index compress attribute is YES, or,
• The index compress attribute is not set, and the table compression attribute is YES
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 6/70
6
Index Compression : Existing Page Format
Page Header
Fixed Slot Directory (maximum size reserved)
1055, 1056
3011, 3025, 3026, 3027, 3029, 3033, 3035, 3036, 3037
3009, 3012, 3013, 3015, 3016, 3017, 3109
6008, 6009, 6010, 6011
An index key RIDlist (the IDs of the records in the table with this key)
DALY, M, AL
COLE, F, KIM
COLE, F, KIRA
DALY, M, ANTHONY
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 7/70
7
Index Compression : Variable Slot Directory
Page Header
Var Slot Dir
1055, 1056
3011, 3025, 3026, 3027, 3029, 3033, 3035, 3036, 3037
3009, 3012, 3013, 3015, 3016, 3017, 3109
6008, 6009, 6010, 6011
An index key RIDlist (the IDs of the records in the table with this key)
Saved Space
DALY, M, AL
COLE, F, KIM
COLE, F, KIRA
DALY, M, ANTHONY
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 8/70
8
Index Compression : RIDlist Compression
Page Header
Var Slot Dir
1055, 1
3011, 14, 1, 1, 2, 4, 2, 1, 1
3009, 3, 1, 2, 1, 1, 92
6008, 1, 1, 1
An index key Compressed RIDlist
DALY, M, AL
DALY, M, ANTHONY
COLE, F, KIM
COLE, F, KIRA
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 9/70
9
Index Compression : Prefix Compression
Page Header
Var Slot Dir
1055, 1
3011, 14, 1, 1, 2, 4, 2, 1, 1
3009, 3, 1, 2, 1, 1, 92
6008, 1, 1, 1
An index key Compressed RIDlist
DALY, M, AL
COLE, F, KIM
COLE, F, KIRA
DALY, M, ANTHONY
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 10/70
10
Index Compression : Prefix Compression
Page Header
Var Slot Dir
RA 3011, 14, 1, 1, 2, 4, 2, 1, 1
L
NTHONY
3009, 3, 1, 2, 1, 1, 92
6008, 1, 1, 1
CompressedKey
CompressedRIDlist
COLE, F, KI
M 1055, 1
DALY, M, A 0, 2
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 11/70
11
Index Compression : Sample Data
Index compression typically mosteffective with•
Non-unique indexes (moreeffective RID list compression)• Larger keys (more effective key
compression)
0
10
20
30
40
50
60
7080
TPC-DS
SAP BW (1st cust.)
SAP BW (2nd cust.)
SAP R/3
Compression Ratio (%)(higher is better)
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 12/70
12
• How much space could I save by compressing theindexes on table T1 ?
Index Compression : Monitoring
SELECT index_name, pages_saved_percent, compress_attr, index_compressedFROM TABLE SYSPROC.ADMIN_GET_INDEX_COMPRESS_INFO
(‘T’, ‘myschema’, ‘T1’, ‘’, ‘’)) AS T
INDEX_NAME PERCENT_PAGES_SAVED COMPRESS_ATTR INDEX_COMPRESSED------------------- -------------------------------------- --------------------------- -------------------------------
INDEX1 57 N N
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 13/70
13
• How much space did I save by compressing INDEX1?
Index Compression : Monitoring
ALTER INDEX index1 COMPRESS YESREORG INDEXES ON TABLE t1RUNSTATS ON TABLE t1SELECT index_name, pages_saved_percent, compress_attr FROM SELECTindex_name, pages_saved_percent, compress_attr, index_compressed
FROM TABLE SYSPROC.ADMIN_GET_INDEX_COMPRESS_INFO(‘T’, ‘myschema’, ‘T1’, ‘’, ‘’)) AS T
INDEX_NAME PERCENT_PAGES_SAVED COMPRESS_ATTR INDEX_COMPRESSED------------------- -------------------------------------- --------------------------- -------------------------------
INDEX1 58 Y Y
Actual savings
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 14/70
14
• Fewer index levels• Fewer logical and physical I/Os for key search (insert, delete,
select)
• Better bufferpool hit ratio
• Fewer index leaf pages• Fewer logical and physical I/Os for index scans
• Fewer splits
• Better bufferpool hit ratio
• Tradeoff• Some additional CPU cycles needed for compress /
decompress
• 0-10% in early measurements
• Typically outweighed by reduction in I/O resulting in higheroverall throughput
Index Compression : Performance Attributes
0
20
40
60
80
100
INSERTS QUERY 1 QUERY 2
Relative Performance
(% Elapsed Time)(lower is better)
Uncompressed
Compressed
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 15/70
15
Temp Compression : Overview
• Compression techniques closely modeled after existing tablecompression
•
Dictionary based compression
• Temp tables will be compressed automatically, if DB2 installationis licensed for data compression
• No need to turn on explicitly
• Applies to both “internal” and “external” temps
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 16/70
16
Temp Compression : Details
• Internal temp tables
• These are temp tables automatically created by DB2 in order to runan access plan
• Examples:
• ‘System’ temps (often used for temporary results)
• Spilling sorts
• Eligible for compression if the optimizer estimates a large enough
• Data size (eg. 100 MB)
• Row size (eg. 20 bytes)
• Actually compressed at run-time when
• ‘System’ temps : after enough data has been inserted to create compressiondictionary, subsequent rows inserted into the temp will be compressed
• Spilling sorts : dictionary created on first spill; all rows will be compressed
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 17/70
17
Temp Compression : Details
• External temp tables
• These are temp tables explicitly created by a user or application
• Examples:
• Declared Global Temporary Table• Created Global Temporary Table
• Use an algorithm very similar to automatic dictionary creation forregular tables
1) CREATE
Note that, unlike regulartables, the compression
dictionary is not stored in thetable (it is only stored in
memory for temp tables, sinceit does’nt need to be
persisted).
2) INSERT..
Internal threshold designed asbest compromise b/w
compression ratio, anddictionary build speed.
Default ~1 MB
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 18/70
18
Temp Compression : Sample Data (TPC-DS)
0
50
100
Not Compressed
Compessed
Temp Space (GB)
(lower is better)
Elapsed Time (min)
(lower is better)
170
175
180
18556%reduction
5% better
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 19/70
19
LOB Compression
• Enabled through LOB “Inlining”
• Normally LOBs are not stored in the base row
• They are stored in a separate storage object
• An “inlined” LOB is one that is stored directly in the base row
Data Page in
Table Object
Segment in
LOB Object
Row
Actual LOBData (eg.JPG)
LOBDescriptor
John 15 xyz 5
CREATE TABLE (NAME CHAR(20), AGE INT, PICTURE BLOB(10MB), SERVICE INT)
Data Page inTable Object
Row
John 15 Actual
LOB Data
(eg. JPG) 5
Non-inlined LOB Case Inlined LOB Case
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 20/70
20
LOB Inlining : Advantages
• Compression
• Inlined LOBs are in the base data row, and therefore can be compressed withDB2’s row compression
• Performance
• Inlined LOBs require do not require an extra I/O to be accessed
• Usage Recommendations
• Consider inlining LOBs that have one or more of the following characteristics:
• Frequently accessed
• Often small enough to fit entirely on a data page
• Compressible (i.e. not random data, not already compressed)
• Keep in mind:
• Potential growth in table size
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 21/70
21
LOB Inlining : Details
• Specify maximum INLINE LENGTH on CREATE or ALTER TABLE
• CREATE TABLE … PICTURE BLOB(10MB) INLINE LENGTH 10000
• ALTER TABLE … ALTER COLUMN PICTURE SET INLINE LENGTH 10000
• LOBs <= than inline length will be stored directly in the row in the data page
• DB2 will search for a page with sufficient space
• There can be a mixture of inlined and non-inlined LOBs within a single page and within asingle row
• INLINE LENGTH limits
• Implicit Inlining
• LOBs can be inlined even if there is no INLINE LENGTH set !
• Occurs when a LOB’s actual length is <= it’s maximum descriptor size based on the declaredmaximum length of the LOB (see speaker notes)
• Otherwise space would be consumed both in data page (for descriptor) and LOB object (actual LOB)
326733267732k
162891629316k
809781018k400140054k
INLINE LENGTH limitRow size limitPage size
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 22/70
22
Implicit Inlining
Data Page inTable Object
Segment inLOB Object
Row
LOB
LOBDescriptor
John 15 xyz 5
Data Page inTable Object
Row
Non-inlined LOB in 9.5 Implicitly Inlined LOB
John 15 xyz 5
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 23/70
23
LOB Inlining : Example Savings
0
10000
20000
30000
40000
50000
60000
70000
No Compression Data Compression LOBs Inlined LOBs Inlined plus Data
Compression T o t a l p h y s i c
a l s t o r a g e ( K B s )
15616331524633665536Total (KBs)
10752108801075210880Indexes (KBs)
1281283033630336LOB storage(KBs)
473622144524824320Base table (KBs)
Compressionplus InlinedLOBs
No Compression
LOBs Inlined
DataCompression
No Compression
All LOBs in this table
are smaller than thedefault inline length forthe LOB columns andtherefore all are inlined
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 24/70
24
Agenda• Compression Advancements
• Indexes
• Temp Data
• LOBs
• Storage Management• Automatic Storage : Rebalance Improvements
• Automatic Storage : Easier Adoption
• Reclaimable Storage
• Table Maintenance• Sparse MDC Tables
• Online Table Move
• Online Table Reorganization Improvements
• Table Partitioning•
Partitioned Indexes
• Currently Committed Isolation
• High Availability Disaster Recovery• Active Standby
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 25/70
25
Automatic Storage Rebalance : Motivation
• Let’s review what occurs when paths are added to your automaticstorage pool,…
C0 C1
Two storage pathsand a table space hasa container on each
ALTER DBADD p3, p4
New storage pathsnot used by the
table space
immediately
TSgrows
p1 and p2 become full
C0 C1
p1 p2 p1 p2
C0 C1
p1 p2p3 p4 p3 p4
TSgrows
DB2 automaticallycreates a new stripe
set on p3 and p4
C0 C1
p1 p2 p3 p4
C2 C3
• Works fine, and is automatic,… but,…• What if you wanted to stripe your data across all 4 devices immediately
(perhaps to gain more I/O parallelism) ?
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 26/70
26
Automatic Storage Rebalance : REBALANCE
C0 C1
Two storage pathsand a table space hasa container on each
New storage pathsnot used by the
table spaceimmediately
C1
p1 p2 p1 p2 p3 p4
ALTER DATABASE …ADD p3, p4
ALTER TABLESPACE myts REBALANCE
REBALANCE causes DB2to create equal-sized
containers in new paths, andredistribute extents to them
p1 p2 p3 p4
ALTER TABLESPACE ...REBALANCE
C0 C1 C2 C3
If table space is not growingrapidly, consider REDUCingit to make space available
for other table spaces
p1 p2 p3 p4
C0 C1 C2 C3
ALTER TABLESPACE ...REDUCE
(optional)
C0
High Water Mark
High Water Mark
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 27/70
27
• If storage path is “Not In Use” then it is immediately removed from
database
• If storage path is “In Use” then:
•Storage path state is changed to “Drop Pending”
• Affected table spaces are marked as “An Underlying Storage Path(s) has been Dropped”
• A “Drop Pending” storage path is removed when no automaticstorage table space is using it:
•
Requires REBALANCE
Dropping Storage Paths
ALTER DATABASE DROP STORAGE PATH ON ‘path1’,…
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 28/70
28
Dropping Storage Paths : Example
C2
p1 p2
C3
p3
C1 C2
p1 p2
C3
p3
C1
p1
C3
p3
C1
ALTER TABLESPACE ..REBALANCE
ALTER DATABASE …DROP p2
Drop Pending
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 29/70
29
Tablespace name = USERSPACE1
Tablespace ID = 2
...Rebalancer Mode = No Rebalancing
Underlying storage path has been dropped = No | Yes
Minimum Recovery Time =
...
Automatic storage path = /storagePath1 Node number = 0State = In Use | Not In Use | Drop PendingFile system ID = 64768Storage path free space (bytes) = 66725142528
File system used space (bytes) = 50100731904File system total space (bytes) = 317068410880
New tablespace snapshot field
New database snapshot field
Monitoring
SELECT DISTINCT(A.TBSP_NAME)FROM SYSIBMADM.SNAPTBSP A, SYSIBMADM.SNAPTBSP_PART B WHERE A.TBSP_ID = B.TBSP_ID AND
A.TBSP_CONTENT_TYPE IN ('ANY','LARGE') ANDB.PATH_DROPPED = 1
SQL to find tablespaces using dropped paths
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 30/70
30
Agenda• Compression Advancements
• Indexes
• Temp Data
• LOBs
• Storage Management• Automatic Storage : Rebalance Improvements
• Automatic Storage : Easier Adoption
• Reclaimable Storage
• Table Maintenance• Sparse MDC Tables
• Online Table Move
• Online Table Reorganization Improvements
• Table Partitioning•
Partitioned Indexes• Currently Committed Isolation
• High Availability Disaster Recovery• Active Standby
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 31/70
31
Adopting Automatic Storage : Motivation
• DB2 automatic storage …• Collapses storage management tasks for any number of tablespaces down to
a single point of management
• Significantly reduces human costs
• Is IBM’s strategic direction going forward for DB2 LUW
• However, adopting the technology was not simple
• Required database to be created as an automatic storage database
CREATE DATABASE mydb
CONNECT to mydb
CREATE TABLESPACE mytsMANAGED BY AUTOMATIC STORAGE
> Error !
CREATE DATABASE mydb AUTOMATICSTORAGE YES on D:, E:, F:
CONNECT to mydb
CREATE TABLESPACE mytsMANAGED BY AUTOMATIC STORAGE
> Success
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 32/70
32
Adopting Automatic Storage : Made Simpler
1. Can convert non-AS database to AS• ALTER DATABASE ADD STORAGE now supported on a non-AS database
• Creates a storage pool for the database
• Allows creation of new automatic storage tablespaces
• Also allows existing DMS tablespaces to be converted to automatic storage (next bullet)
2. ALTER TABLESPACE enhanced to convert a DMS tablespace to anautomatic storage tablespace•
ALTER TABLESPACE … MANAGED BY AUTOMATIC STORAGE• Causes all new growth to come from the automatic storage paths
• Old (file or raw) containers can be removed via the DROP and REBALANCE enhancementspreviously covered
3. REDIRECTED RESTORE enhanced to allow restoration from DMStable spaces to AUTOMATIC STORAGE table spaces• SET TABLESPACE CONTAINERS … USING AUTOMATIC STORAGE
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 33/70
33
Agenda• Compression Advancements
• Indexes
• Temp Data
• LOBs
• Storage Management• Automatic Storage : Rebalance Improvements
• Automatic Storage : Easier Adoption
• Reclaimable Storage
• Table Maintenance• Sparse MDC Tables
• Online Table Move
• Online Table Reorganization Improvements
• Table Partitioning•
Partitioned Indexes• Currently Committed Isolation
• High Availability Disaster Recovery• Active Standby
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 34/70
34
• CREATE TABLESPACE ts1
• CREATE TABLE t1 IN ts1 …
• LOAD t1 …
• CREATE TABLESPACE ts2
• CREATE DATABASE … ON /p1, /p2 /p1 /p2
Reclaimable Storage : Motivation
• CREATE TABLE t2 IN ts1 …
• LOAD t2 …
• CREATE TABLE t3 IN ts2 …
• LOAD t3 …
• DROP TABLE t1
• LOAD t3 …Trapped Free
Space
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 35/70
35
Reclaimable Storage : Overview
• ALTER TABLESPACE .. REDUCE enhanced to free “trapped freespace”
• Works by:• Moving used extents from higher addresses in the tablespace to unused lower addresses
• Lowering high water mark accordingly
• Shrinking/removing containers to return space back to the Automatic Storage paths
• MAX frees as much space as possible• Can also specify a target size to free up
• Runs in the background• Works in batches, committing free extents as it progresses
• STOP option terminates a background REDUCE operation
ALTER TABLESPACE <tsname> REDUCE --+-----------------------------+---'-- <size> --+-------------+--+--'
| +----- K -----+ |
| +----- M -----+ |
| +----- G -----+ |
| '-- PERCENT --' |
'--- MAX ---------------------'
'--- STOP --------------------'
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 36/70
36
• CREATE TABLESPACE ts1
• CREATE TABLE t1 IN ts1 …
• LOAD t1 …
• CREATE TABLESPACE ts2
• CREATE DATABASE … ON /p1, /p2 /p1 /p2
Reclaimable Storage : Benefit
• CREATE TABLE t2 IN ts1 …
• LOAD t2 …
• CREATE TABLE t3 IN ts2 …
• LOAD t3 …
• DROP TABLE t1
• LOAD t3 …Trapped Free
Space
• ALTER TABLESPACE ts2 REDUCE MAX
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 37/70
37
Reclaimable Storage : Examples
DROP TABLE 2DROP TABLE 3
Internal tablespace metadata extents
Table 1
Table 2
Table 3Extent that is allocated to tablespace, but not to a table
ALTERTABLESPACE …
REDUCE MAX
R l i bl S E l
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 38/70
38
Reclaimable Storage : Examples
DROP TABLE 2DROP TABLE 3
Internal tablespace metadata extents
Table 1
Table 2
Table 3Extent that is allocated to tablespace, but not to a table
ALTERTABLESPACE …
REDUCE nn
R l i bl S M i i
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 39/70
39
• Tablespace snapshot
• New MON_GET_EXTENT_MOVEMENT_STATUS()
Tablespace Type = Database managed space
Using automatic storage = Yes
Using reclaimable storage = Yes...
Tablespace State = 0x'00000010‘
Detailed explanation: Extent movement in progress
TBSP_NAME TBSP_ID .... NUM_EXTENTS_MOVED NUM_EXTENTS_LEFT TOTAL_MOVE_TIME
--------- ------- ----------------- ---------------- ---------------
USERSPACE1 2 -1 -1 -1
TS1 3 4000 2000 60000
• Very rough estimate for remaining time as (LEFT/MOVED) * TIME
• 30000 milli-seconds (30 secs) in this example for TS1
Reclaimable Storage : Monitoring
A d
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 40/70
40
Agenda• Compression Advancements
• Indexes
• Temp Data
• LOBs
• Storage Management• Automatic Storage : Rebalance Improvements
• Automatic Storage : Easier Adoption
• Reclaimable Storage
• Table Maintenance• Sparse MDC Tables• Online Table Move
• Online Table Reorganization Improvements
• Table Partitioning•
Partitioned Indexes• Currently Committed Isolation
• High Availability Disaster Recovery• Active Standby
S MDC T bl M ti ti
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 41/70
41
Sparse MDC Tables : Motivation
NW,2004 SW,2004 SW,2005
Region
Year
DELETE FROM Sales
WHERE Region = SWAND Year = 2004
Table Object
NW,2004 SW,2005
Region
Year
• These pages and storage are still assigned to the MDC table
• How can this storage be reused elsewhere in the table space?• If you have a maintenance window : Classic REORG
• If not,…. ?
Sparse MDC Tables
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 42/70
42
Sparse MDC Tables
NW,2004 SW,2005
Region
Year
Table Object
REORG TABLE SalesRECLAIM EXTENTS ONLY
NW,2004 SW,2005
Region
Year
What’s Going at the Table Space Level ?
REORG TABLE SalesRECLAIM EXTENTS ONLY
Extent is freedback to table
space & can beused by other
tables !
S MDC T bl
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 43/70
43
• Very fast !
• Not really a reorg
• No copy of the table created, no copy phase, etc
• Done in-place with no data movement, minimallogging
• Find the empty blocks in block map• Mark them as unallocated in the MDC table’s block map
• Mark them as unallocated in the table space SMPs
Sparse MDC Tables
REORG TABLE <mdc table name> RECLAIM EXTENTS ONLY
[ ALLOW { WRITE | READ | NO } ACCESS ]
S MDC T bl
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 44/70
44
• When should you run RECLAIM EXTENTS ONLY ?
• Use new RECLAIMABLE_SPACE column
returned from ADMIN_GET_TAB_INFO()function
Sparse MDC Tables
SELECT reclaimable_space as R FROM TABLE
SYSPROC.ADMIN_GET_TAB_INFO_V97 ( ‘db2user1’, ‘emp’)) AS T
SELECT tabschema, tabname, reclaimable_spaceFROM sysibmadm.admintabinfoWHERE reclaimable_space > 10,000,000GROUP BY tabschema, tabname
Get tables with over 10 MB of reclaimable space
Get reclaimable space for a specific table
Online Table Move : Motivation
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 45/70
45
Online Table Move : Motivation
• Now what ?• If you have a large enough maintenance window
• UNLOAD, re CREATE, re LOAD
• But what if you have no maintenance windows ?
CONNECT to mydb
CREATE TABLESPACE myts … PAGESIZE 4K
CREATE TABLE mytable IN myts (C1 CHAR(8), C2 CHAR(8), C3 CHAR(8),…C500 CHAR(8))
ALTER TABLE mytable ADD (C501 CHAR(8))> Error !
• Initial definition
• Six months later you need to add a column …
Online Table Move : Data Flow
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 46/70
46
… cnc2c1 … cnc2c1
… cnc2c1
TARGET
TABLE
SOURCE
TABLE
STAGINGTABLE
INSERT
DELETE
UPDATE
REPLAY PHASE
valuekeytabnametabschema
Online table move control table
SYSTOOLS.ADMIN_MOVE_TABLE
Keys ofrow changed
by onlineworkoad
captured viatriggers
Rows withkeys present
in stagingtable are
re-copiedfrom source
table
Online Table Move : Data Flow
COPY PHASE
INSERT
DELETE
UPDATE
OnlineWorkload
1 INIT PHASE
2
3
4 SWAP PHASE
Create triggers,target,staging tables
Rename Target -> Source
Online Table Move : Example Use Cases
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 47/70
47
• Online REORG or Redistribute
• Online table compression
• Online change pagesize
• Online conversion to LARGE tablespaces
• Move data/index/long data to new/different tablespaces
• Support for schema change:
•
Add or remove columns, change columndatatypes, enlarge columns• Add/change MDC dimensions, range partitioning
or partitioning key
Online Table Move : Example Use Cases
Online Table Reorg : Review
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 48/70
48
Online Table Reorg : Review
TIME
Move rows from end of table, filling up holes atthe start
• Space-reclaiming inplacereorg does simultaneousbackward and forwardtable scans
• Moves rows from end oftable to ‘holes’ foundstarting at beginning oftable
• When the scans meet, atable S lock is taken andone final pass is taken topick up any newly insertedrows at the end of thetable, followed by atruncate
Online Table Reorg Concurrency : Motivation
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 49/70
49
Online Table Reorg Concurrency : Motivation
OnlineReorgIssued
DrainExisting(Wait forexistingaccesses toend, whileletting new
accessesstart)
Move rows to re-establish clusteringand/or reclaim free space
… if Truncate requested …Waitfor Slock
DrainExisting
Trun-cate
SLockRel-
eased
- Duration of existing write transactions
- Time taken for final row movement phase and truncate phase
No new write
accessesduring this
period
Time period dependent on …
Online Table Reorg Concurrency :
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 50/70
50
• Final pass of row movement made significantly more efficient• Up to twice as fast
• REORG … PAUSE enhanced when invoked during truncatephase• Truncates pages it can – i.e. the pages freed up ‘so far’ in final pass• Releases S lock• REORG … RESUME will re-acquire S lock and continue final pass
•
REORG … STOP will terminate reorg
• Usage recommendations:• If you find the S lock is holding up your workload, use REORG… PAUSE to
reclaim currently available space, and resume full table access•
At a later time, check current table size via ADMIN_GET_TAB_INFO()• If satisfied with space reclaimed:
• REORG … STOP
• Else• REORG … RESUME
Online Table Reorg Concurrency :Improvements
Agenda
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 51/70
51
Agenda
• Compression Advancements• Indexes
• Temp Data
• LOBs
• Storage Management• Automatic Storage : Rebalance Improvements
• Automatic Storage : Easier Adoption
• Reclaimable Storage
• Table Maintenance• Sparse MDC Tables• Online Table Move
• Online Table Reorganization Improvements
• Table Partitioning• Partitioned Indexes
• Currently Committed Isolation
• High Availability Disaster Recovery• Active Standby
Table Partitioning Improvements : Motivation
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 52/70
© 2009 IBM Corporation52
Jan
Table Partitioning Improvements : Motivation
Feb Mar Apr MayJan JuneALTER TABLE
DETACHPARTITION
JAN
ALTER TABLEATTACH
JUNE
LOADJune
CREATETABLE
JUNE
Global Index (order_id)
AutomaticAsynchronous
Index Cleanup
SET INTEGRITY
COMMIT
SQL
Key Improvement : Partitioned Indexes
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 53/70
53
tbsp3tbsp2tbsp1
t1.p1 t1.p2 t1.p3
CREATE TABLE t1(c1 INT)INDEX IN tbsp4PARTITION BY RANGE(a)(STARTING FROM (1) ENDING(34) IN tbsp1,ENDING(67) IN tbsp2,
ENDING(100) IN tbsp3)
CREATE INDEX i1(c1)
tbsp4
i1
tbsp3tbsp2tbsp1
t1.p1 t1.p2 t1.p3
i1
CREATE TABLE t1(c1 INT)PARTITION BY RANGE(a)(STARTING FROM (1) ENDING(34) IN tbsp1
INDEX IN tbsp4,ENDING(67) IN tbsp2
INDEX IN tbsp5,ENDING(100) IN tbsp3
INDEX IN tbsp6)
CREATE INDEX i1(c1) PARTITIONED
Key Improvement : Partitioned Indexes
tbsp4 tbsp5 tbsp6
Partitioned Indexes : Benefits
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 54/70
54
Partitioned Indexes : Benefits
• Storage• Partitioned indexes do not store partition identifier in each key
• 2 byte savings per RID
• Each partitioned index will be a fraction of the size of an alternative non-partitiioned index• In some cases, may be able to use REGULAR (small RID) tablespaces for indexes
• Another 2 byte savings per RID
• Performance• Storage savings typically leads to performance savings
• Less I/O bandwidth and memory consumption
• However, be aware that partitioned indexes will (in general) not provide ‘order’, eg:
•
• Several advantages in Utility and DDL processing
SELECT * … ORDER BY partitioned_index_key // can’t use partitioned index for order; may need sort // (unless of course part. index key is prefixed by table part key)
SELECT * … WHERE table_part_key=X … // can use partitioned index instead of sortORDER BY partitioned_index_key
Partitioned Indexes : Utilities / DDL
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 55/70
55
Partitioned Indexes : Utilities / DDL
• Partition-level index REORG
• Reorganizes indexes on specified partition only
• All partitions (including target partition) available for read/write access
• Partition-level table REORG
• Reorganizes specified partition and it’s partitioned indexes
• In the above example, target partition available for read access• All other partitions available for read/write access
• Important note: if non-partititoned indexes also exist, they will also be reorganized (which willrestrict availability of all partitions)
• Statistics• New SYSINDEXPARTITIONS catalog table added to contain per-index-partition statistics
• Also, new statistics added to SYSDATAPARTITIONS to help determine when/if partitioning leveltable reorg would be useful
• RUNSTATS continues to operate on table level
REORG INDEXES ALL FOR TABLE xyz … ON DATA PARTITION jan … ALLOW WRITE ACCESS
REORG TABLE xyz … ON DATA PARTITION jan … ALLOW READ ACCESS
Partitioned Indexes : Utilities / DDL (cont’d)
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 56/70
56
Partitioned Indexes : Utilities / DDL (cont d)
• ATTACH
• If required partitioned indexes have already been created on the table about to be attached
• They are just quickly “linked in” as the index partition for this new data partition
• SET INTEGRITY will not perform index maintenance operations for such indexes (eliminatesassociated logging) !
• Otherwise, they will be created automatically
• Hint: use the REQUIRE MATCHING INDEXES to ensure automatic creation does not occur
• DETACH
• Partitioned indexes for the detached partition quickly “unlinked” from the range partitionedtable, and become regular indexes on the detached partition (now just a regular table)
• No Async Index Cleanup necessary for Partitioned indexes
ALTER TABLE … ATTACH PARTITION…. REQUIRE MATCHING INDEXES
Partitioned Indexes : Streamlined Roll-in
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 57/70
© 2009 IBM Corporation57
JuneFeb Mar Apr May
ALTER TABLEATTACH
JUNESET INTEGRITY
LOAD
CREATE TABLEJUNE
CREATE INDEX
Part’d
Index
Part’d
Index
Part’d
Index
Part’d
Index Index
No Global Index to Maintain
June
Part’d
Partitioned Indexes : Streamlined Roll-out
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 58/70
© 2009 IBM Corporation58
Jan Feb Mar Apr MayJan
ALTER TABLEDETACH
PARTITIONJAN
Part’dIndex
SQL
Part’dIndex
Part’dIndex
Part’dIndex
Part’dIndex
Z lock removed
Agenda
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 59/70
59
g
• Compression Advancements• Indexes
• Temp Data
• LOBs
• Storage Management• Automatic Storage : Rebalance Improvements
• Automatic Storage : Easier Adoption
• Reclaimable Storage
• Table Maintenance
• Sparse MDC Tables• Online Table Move
• Online Table Reorganization Improvements
• Table Partitioning• Partitioned Indexes
• Currently Committed Isolation
• High Availability Disaster Recovery• Active Standby
Currently Committed Isolation : Motivation
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 60/70
60
SELECT * FROM EMP
EMPID NAME OFFICE SALARY
6354 Smith A1/21 43
> wait
22D2/18Baum5456205
33X1/03Tata1325104
21AA/00AA/00 C3/46C3/46Jones783696
43A1/21Smith6354
77 1111Y2/11Y2/11ChanChan42454245
48
salaryofficenameempidrowid
EMP
Uncommitted insert
Uncommitted update
Uncommitted delete
Currently Committed Isolation : Output
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 61/70
61
22D2/18Baum5456
33X1/03Tata1325
21AA/00AA/00 C3/46C3/46Jones7836
43A1/21Smith6354
1111Y2/11Y2/11ChanChan42454245
salaryofficenameempidrowid
EMP
SELECT * FROM EMP
Uncommitted insert
Uncommitted update
Uncommitted delete
205
104
96
77
48
EMPID NAME OFFICE SALARY
6354 Smith A1/21 437836 Jones AA/00 211325 Tata X1/03 33
5456 Baum D2/18 22
> SUCCESS
DB2 returns currently committed data without
waiting for locks ! (Delete and Update undone; Insert skipped.)
Currently Committed Isolation : How it Works
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 62/70
62
Locklist
X(D)205
X(U)96
X(I)77
lockrowid
INS:Emp,1,6354,Smith,A1/21, 43INS:Emp,4,1325,Tata,X1/03,33
Log Archive
Active Log Files
INS: Emp,1,4245,Chan,Y2/11,11
Log Buffer
Uncommitted INSERTed data is skipped.
For uncommitted DELETEs and UPDATEs, when encountering a lock which would otherwise conflict, DB2 uses new information in the lock manager to reconstruct and
return the previously committed data from the log buffer or log file.
DEL: Emp,5,5456,Baum,D2/18
-
22D2/18Baum5456
33X1/03Tata1325
21AA/00AA/00 C3/46C3/46Jones7836
43A1/21Smith6354
1111Y2/11Y2/11ChanChan42454245
salaryofficenameempidrowid
EMPlog ref
205
104
96
77
48
UPD: Emp,3,7836,Chan,AA/00C3/46
Currently Committed : Internals & Usage Notes
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 63/70
63
• Log-based implementation : simple & fast• No need for rollback segments !
• Currently committed data typically reconstructed from memory (log buffer)
• Exception: updates/deletes from mass update transactions that spill log buffer• Active logs read from storage in this case
• Fallback to traditional locking
• If the currently committed data is unavailable (or not available quickly), DB2 will fall back to thetraditional locking behavior
• Examples
• Currently committed data is only available from an archived log (as may be the case with infinite logging)
• Updater held table lock (not row lock)
• Usage hints & tips• Consider increasing your log buffer size
• Options for evaluating log buffer size• New Cur Commit Total Log Reads, Cur Commit Disk Log Reads outputs from db2pd -logs
• Existing num_log_data_found_in_buffer, num_log_read_io monitor elements
• Consider increasing lock list size (or using AUTOMATIC setting)• To avoid escalation to table locks (disables currently committed behavior for the table)
• Be aware of potential for increase in log space consumption if CC enabled• First update to a given row in a transaction logs entire row image
Currently Committed Isolation : Externals
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 64/70
64
CUR_COMMIT database configuration parameter
ON : cursor stability scans return currently committed data (default for new dbs)AVAILABLE : cursor stability scans return currently committed data if explicitly requestedDISABLED : disabled (default for upgraded databases)
BIND option
>--+-------------------------------------------------------------+-->
'--CONCURRENTACCESSRESOLUTION--+--USE CURRENTLY COMMITTED--+--'
'-- WAIT FOR OUTCOME---------‘
CLI, JCC
SQL_ATTR_CONCURRENT_ACCESS_RESOLOUTION = 1
concurrentAccessResolution property
Notes• CC does not apply to target table of write, catalogs, RCT, “integrity scans”
• DB2 needs to hold the right to update the row in this case• Target table of write still exhibit “skip uncommitted inserts” aspect of CC
• CC extends “skip uncommitted inserts” to Read Stability isolation as well
Currently Committed Isolation : Example
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 65/70
65
UPDATE T1 SET COL1=? WHERE COL2=? UPDATE T2 SET COL1=? WHERE COL2=?
SELECT COL1, COL5 FROM T1WHERE COL5=? AND COL2=?
SELECT COL1, COL3 FROM T2WHERE COL2 > ?
• Typical Result without Currently Committed : ?
• Deadlock possible : dependent on
• Timing of execution of each application
• Values being selected at any point in time
• The number of rows affected (selecttivity) of selects and updates
• The access plan chosen (eg. Index vs tasble scan)
• Result without Currently Committed : Success
• Deadlock no longer possible
APPLICATION A APPLICATION B
Currently Committed Isolation : Semantics
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 66/70
66
Can additional rowsbe included in theresult set if a query isrun a 2nd time in atransaction ?
Can a query returnuncommitted data ?
NNNRepeatable Read
Y
Y
N
N
N
N
Read Stability
with CC
YY
YY
NN
Cursor Stabilitywith CC
YYYUncommitted Read
Phantoms ?Non-
repeatable
Reads ?Can rows bedropped from theresult set if a queryis run a 2nd time in atransaction ?
Dirty Reads
?
Isolation
Agenda
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 67/70
67
• Compression Advancements• Indexes
• Temp Data
• LOBs
• Storage Management• Automatic Storage : Rebalance Improvements
• Automatic Storage : Easier Adoption
• Reclaimable Storage
• Table Maintenance•
Sparse MDC Tables• Online Table Move
• Online Table Reorganization Improvements
• Table Partitioning• Partitioned Indexes
• Currently Committed Isolation
• High Availability Disaster Recovery• Active Standby
HADR Reads on Standby : MotivationOLTP Clients
Read enabled Standby in
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 68/70
68
• Standby provides High Availability, Disaster Recovery & allows read-only workloads• Improve resource utilization• Decrease total cost of ownership
OLTP Clients
ClientsDatabase Logs
Clients
Reporting/DSS/BIworkloads
perpetual Rollforward
HADR Communication
Resource Utilization
StandbyPrimary
HADR Reads on Standby : Overview
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 69/70
69
• Standby can be opened for read access• Enabled via : DB2_HADR_ROS = ON
• Supports all synchronization modes• ASYNC
• NEAR SYNC
• FULL SYNC
• Isolation level is Uncommitted Read• Can force applications to use UR : DB2_STANDBY_ISO = UR
• If this registry variable is not set, an application requesting an isolation level otherthan UR will fail with SQL1773N
• Writers blocked• Error SQL1773N
8/8/2019 Table Index Compression
http://slidepdf.com/reader/full/table-index-compression 70/70
Matt Huras
IBM
huras@ca.ibm.comdwsnow@us.ibm.com
paulz@ca.ibm.combminor@ca.ibm.com
top related