11-17-08, 09:09 #1Registered User
- Join Date
- Nov 2007
Unanswered: Recommended tablespace extent size ?
I'm confused a bit trying to decicde right tablespace extent size.
8 POWER5 CPU's
32 Gb RAM
os AIX 5.3
For storage I use IBM FAST700 model 1742 SAN.
My storage config:
RAID-5 array (10 disks) for data
RAID-1 array (2 disks) for db logs
Segment Size 64Kb (what is "segment size" strip? stripe?)
Every source I read states that I should set tablespace extent size equal to or multiple of RAID stripe size.
!! BUT term 'stripe' varies depending on source I read.
According to IBM:
"The amount of contiguous data on each spindle in a RAID array is known as a “strip” and the amount of data across the array that comprises all the strips in a single array is called a “stripe”."
So in my case stripe size would be 64 x 9 = 576 Kb
But other sources state that stripe is simply amount of data put on single disk in RAID array. According to this my stripe size would be 64 Kb.
So I should choose my extent size according to piece of data put on single disk in array or according to sum of all 'strips' per RAID-5 array minus one ?
I have some large tablespace(120Gb) already configured with extent size of 64 Kb on disk I mentioned before (RAID-5 array (10 disks) ) - so it's eficient or not ?
Last edited by vilius; 11-17-08 at 11:08.
11-18-08, 03:29 #2Registered User
- Join Date
- Oct 2004
- DELHI INDIA
Thumb rule which we followed in different design is to Put around 32K extentsize with 16 K pagesize DMS Tablespace. For Index Tablespace with 4k Pagesize we kept the EXTENTSIZE as 16K.
It proved great on RAID-10 (QUAD-CORE) device and haven't faced any issues in terms of performance.
11-18-08, 09:10 #3:-)
Provided Answers: 1Originally Posted by vilius
- Join Date
- Jun 2003
- Toronto, Canada
Now, whether you want to match the DB2 extent size to the multiple of the RAID stripe size is a different question, and the answer will depend on how you use the retrieved data. In a data warehouse environment, where table scans are common, this could be a good idea because it will allow you to move data most efficiently between the disk and the bufferpools. In a typical OLTP system, where you most commonly access individual records by highly selective indexes, large extent size could actually decrease performance: you will be reading a lot of data that you never use, saturating the I/O subsytem and decreasing the bufferpool efficiency.
As always, the proof is in the pudding. Look at the tablespace snapshots to determine usage patterns and run a test with different extent sizes.---
"It does not work" is not a valid problem statement.