Michael Krafick [@mkrafick]
Sr. Database Administrator, Atlanta
Session Code: D05
Wed, May 14, 2014 (08:00 AM - 09:00 AM) | Platform: DB2 for LUW
Database & Server Environment
•
P6 570
•
4 CPU (8 Logical) CPU
•
32G Memory
•
1.2T over 4 HDISKS
•
AIX 6.0 | DB2 9.7
•
Server oversized for drastic growth
DB only 50G at moment
• Isolated active log directory
• 7300 connections, 4500 used at any time
• 14 separate application servers
• 350,000 transactions at peak hour
• 2.8M daily transactions
• 6300 unique users
3rd Party Software
• Sales and service software for banking
• 1600+ locations
• 10,500+ teammates
• Acts as a "Caching" system for this tool, queries mainframe backend
• First DB2 environment implementation
•
Data disk utilization doubles from 30-40% to 60-80%
with volume
•
Active log disk utilization 20% higher than data disk, can jump to 90-100%
•
Overall database BP Hit Ratio is 70-80%
•
No locking, lock escalations, or deadlocks
Root Cause
•
Database Agnostic Design
•
“Plopped in place” (same BP, tablespace, etc)
•
Some best practices not in place for OLTP
•
Our customization changed database behavior
•
Critical table contains a blob field (XML Data)
•
2012 IDUG pre-conference seminar:
DB2 for LUW Performance Workshop
•
Performance “How To’s” on DBI Website
http://www.dbisoftware.com/blog/db2_performance.php?cat=39
• GET SNAPSHOT FOR DATABASE ON <DBNAME>
• MONREPORT.DBSUMMARY Procedure
What is a BLOB?
•
Binary Large Object (BLOB)
•
Store large objects
•
Documents
•
Pictures
•
Voice
•
Mixed media
•
Like its name sake, can be big and slow
•
LOB data is not kept in the buffer pool but is read from disk
•
Additional I/O to fetch, insert, or update
•
32k Row Definition Limit
How Does DB2 Natively Handle Lobs?
•
Stored in a location separate from the table row that references them
*Graphic from IBM Info Center
CREATE TABLE EMPLOYEE
(EMPNO CHAR(6) NOT NULL PRIMARY KEY, FIRSTNME VARCHAR(12) NOT NULL,
MIDINIT CHAR(1) NOT NULL WITH DEFAULT, LASTNAME VARCHAR(15) NOT NULL,
WORKDEPT CHAR(3), PHONENO CHAR(4),
PHOTO BLOB(10M) NOT NULL NOT LOGGED COMPACT) IN RESOURCE
Example taken from: “LOB Column Considerations” in InfoCenter
How Does DB2 Natively Handle Lobs?
Example:
PHOTO BLOB(10M) NOT NULL NOT LOGGED COMPACT
• Create LOB “as small as possible” when COMPACT
• Default is NOT COMPACT
• Performance impact when using COMPACT feature
•
“It can be impractical (and sometimes impossible) to include large data objects in base table rows” (32k limit)
*Quote and Graphic from IBM Info Center
Affect of BLOB (20 Minute interval)
• Effect of Bottleneck Table - < 5000 Rows, flushed nightly
• Direct reads = 233754
• Direct writes = 174318
(8715 Minute)
• Direct write elapsed time (ms) = 24593
• Update/Insert/Delete statements executed = 93176
• Log pages written = 88708
• Number write log IOs = 78827
• DB2 logs each time it manipulates a LOB
• Do you really need the data if recovery is needed?
• Option: NOT LOGGED
Does not log changes to BLOBs
Stop the Note Taking and Chit Chat
For example:
CREATE TABLE EMPLOYEE
(EMPNO CHAR(6) NOT NULL PRIMARY KEY, FIRSTNME VARCHAR(12) NOT NULL,
MIDINIT CHAR(1) NOT NULL WITH DEFAULT, LASTNAME VARCHAR(15) NOT NULL,
WORKDEPT CHAR(3), PHONENO CHAR(4),
PHOTO BLOB(10M) NOT NULL NOT LOGGED) IN RESOURCE
Example taken from: “LOB Column Considerations” in InfoCenter
•
ROLLFORWARD RECOVERY
LOB data will be placed with binary zeros
•
Even if NOT LOGGED, LOB columns are shadowed to allow changes to be rolled back
• Shadowing is a recovery technique in which current
storage page contents are never overwritten. That is, old, unmodified pages are kept as "shadow" copies. These
copies are discarded when they are no longer needed to support a transaction rollback. [*IBM Info Center]
Stop the Note Taking and Chit-Chat (PT 3)
•
If the LOB column is larger than 1 GB, logging must be turned off.
•
As a rule of thumb, you might not want to log LOB columns larger than 10 MB.
•
Take this into account on readable HADR standby.
•
Break into it's own tablespace and BP
•
Consideration: Is your LOB only a few K?
•
Improve speed, place small LOBs in formatted rows
•
Benefit: LOBs can be brought into memory
•
Benefit: LOBs are eligible for Row Compression
Side Note: Separate Tablespaces in Recovery
• Break into it's own tablespace and BP
• LOBs and long data can elongate restore times
• Tablespace level restore:
Consider restoring only what you need, do you need the LOB’s?
• NOT LOGGED option also improves speed
• 1 Step – Estimate Proper Size
• Command:
ADMIN_EST_INLINE_LENGTH--(--column-name--)--->
Do the Two-Step
• Results of estimate:
•
Number: Size estimate for column length
•
NULL: Inputs are NULL
•
-1: Can not be inlined (Length)
•
-2: Inserted before 9.7, can not be determined
• May want to ORDER BY or MAX results
(Watch Row Size Limits)
Table 1. Row size limits
Page size Row size limit Inline length limit
4K 4005 4001
8K 8101 8097
16K 16 293 16 289
32K 32 677 32 673
*Graphic from IBM Info Center
Do the Two-Step
•
2
ndStep – ALTER COLUMN and REORG Command:
ALTER TABLE <table> ALTER COLUMN <colname> SET INLINE LENGTH <value>
Command:
REORG TABLE <tablename> …. LONGLOBDATA
• The INLINE will not take affect unless you REORG (LONGLOBDATA) or UPDATE
From Info Center: This parameter is required when converting existing LOB data into inline LOB data
• No real benefit during routine REORG process*
(Time consuming and doesn't improve clustering)
*LONGLOBDATA option on tables with XML columns will reclaim unused space and shrink the XML object.
Put on After Burners!
• NO FILE SYSTEM CACHING vs. FILE SYSTEM CACHING
From Info Center: Allows you to enable/disable non-buffered I/O on specific TS while avoid dependency on physical layout
• Allows DBM to decide which is best to used buffered or non- buffered
• Used in CREATE TABLESPACE or ALTER TABLESPACE statements
• Use STMM on BP or increase by 10-20 percent and retest till optimized
Command:
Db2look –d <dbname> -l
I have caching? Yeah, uh ..
Command:
db2 "get snapshot for tablespaces on <dbname>" | grep -p caching
•
BLOBs need FS caching to improve performance
•
Normal Tables (and INLINE BLOBs) do not
•
If you reverse it, it could be the difference between:
A N D
Results – Performance Load Test
• Direct reads = 233754 73471 68% Improvement
• Direct writes = 174318 14008 91% Improvement
• Direct write requests = 16213 262 98% Improvement
• Direct write elapsed time (ms) = 24593 1622 93% Improvement
• Log pages written = 88708 53642 Overall work drops
• Log write time (sec.ns) = 89 76 Overall work drops
• Number write log IOs = 78827 48354 Overall work drops
• Total Transactions Processed - 9% increase
• 20% of Total Transactions improved by 15-16%
• <1% (Quote "Very Very Few") of transactions
slowed down slightly
Results - Production
• SQL exceeding 2 seconds on basic FETCH Drops from thousands to <50
• 97.93% drop - Execution time for UPDATE statements
Per 100 executions against problem table (0.436s to 0.009s)
• 81.7% drop - Execution time for SELECT statements
Per 100 executions against problem table (0.656s to 0.120s)
• ZERO Lock Escalations per 1000 transactions
• Write Rate: 70 % Improvement | Read Rate: 20% Improvement [How often we have to spin disk]
• Write Transfer Size – 2.25x Improvement [More volume at one time]
• Read Transfer Size – 25% Improvement [More volume at one time]
• 10-20% drop in disk utilization
Brand Spanking New developerWorks Article
www.ibm.com/developerworks/data/library/techarticle/dm-1403taming/index.html
• Prashant Sogarwal
• Stephanie Knight-Baker
• Scott Hayes
Wait! There's more - Order now!
• Regular guest blogger at Db2Commerce.com
• Attack of the BLOB!
• 10 Minute Triage – Pt 1 and 2
• DB2 and HACMP Failover
• Automatic Storage Tablespaces (AST): Compare and Contrast to DMS
• Row and Column Access Control (Column Masking)
Sr. Database Administrator, Atlanta [email protected]
Twitter: @mkrafick
Session Code: D05 Attack of the Blob!
(Managing BLOBs Within a DB2 Database)
Please fill out your session evaluation before leaving!
