About Me

My photo
Singapore, Singapore, Singapore
I am currently working as a DB Consultant with Ashnik. We are into Open Source and Cloud based enterprise class solutions. Prior to this I was working as a DBA @ Misys India (Banking Services). Working on IBM DB2, Oracle Microsoft SQL Server. Selected as IBM Champion for Data Management 2011. I like bit of photography, cycling, poetry and I just love gadgets!!

Connect to me

See my profile on LinkedIn Visit my Ashnik Website

Optimize your Tablespaces and Bufferpools-2: Tablespace Sizing

Hey people...
So I hope the last post was informative... This post is going to be rather short... Not cause of the topic, but cause of my lack of knowledge on this matter... I could have very well knocked it off, but thought of giving some basics (to which my knowledge is restricted) from where people can carry on with google...

We will discuss sizing of tablespaces and their separation... We will wind up with little on temp tablespaces...

If as a DBA you tend to think, you can allocate any space, on your disk for your tablespaces and allow them to grow unlimited by any fraction as incremental size, and can sit back... You are damn wrong... Things that can cause problems...

1) If you allocate even initial size (which may be too huge) for all the tablespaces, the table spaces which has too frequent writes might face issues like allocation of new extents and which might be too scattered cause initial allocation was not thought properly...

2) Same applies if you think allocation for every tablespace should go at same pace... You might end up killing the space requirements for tablespaces which are too frequently accessed for writes... Moreover you will be facing issues with cost for storage (sooner or latter)...

3) Also you need to consider the fact that, some tables will have very sequential access and the tablespace having those tables, should have a more incremental size otherwise your data is going to scattered and prefetching suffers...

4) Static data and configuraiton data needs to have a medium allocation at starting and the increment window need not be too huge as very rarely you add new configurations to the system and very rarely you access configuration in sequence (well this might not be true always)...

Now wat is the way out??? This is something I have learnt from my seniors at work...

1) List the tables, and list the tablespaces they belog to. List the tables' row size and its cardinality as well

2) Sum up the above data per tablespace and see at what rate the data increases for every table

3) Combine all these inputs to decide, what should be the initial size (do not keep it same as current data's requirement at least keep 10%-50% extra space depending on rate at which data grows) and what should be the increment size...

This will really help you get rid of time lags for your write operations...

Next is to place all such tables together in a tablespace, where you think there will be sequential reads... Prefetching will benefit from this (as long as you remember to schedule a reorg at a proper frequency)... Tables which has static data which hardly gets updated/inserted should go into one tablespace (which should be reorganized at initial setup)... We will see more about bufferpool planing in upcoming posts...

Last... People tend to ignore the importance of system temporary tablespace and user temporary tablespace...

It is important to separate these two and explicitly create a new user temp tablespace, this will really help your sort operation which can have more freedom of "space" in system temp tablespaces... All other temp processing might as well benefit... Also, you should consider keeping a separate bufferpool for user temporary tablespace... Also, if you have not allocated enough space for you temp tablespace, it will try to reallocate/acquire space for sort operations, this will cause a delay in result for your sorting operations, which is quite frequent in application we see these days (patient enquiry sorted by admit date or if you remember the transaction order in your bank statement)...

So the mantra is to think wisely and group your tables in proper tablespaces, then size them and decide proper growth for them... Meanwhile do no forget the reorganization of tables and indexes (frequently) and of tablespaces at times... Meanwhile never ignore the importance of temp tablespaces...

I will be back with some tips on deciding proper strategy for your bufferpools and how to make them do the best according to your tablespace and tables contained by it...

Meanwhile I have got a some content on backup recovery... Planning to write it some time...

Optimize your Tablespaces and Bufferpools-1: Tablespace Design design

hey people... I am back after a long time... Office had kept me busy, and it will continue to do so for next few quarters... After all we have promised ourselves to build a world class product, which got to be best!!!

Well so getting back to db2... In this post I will discussing little bit on the physical database design...

Lets discuss the tablesapces first...

So lets start with pagesize... If u are really concerned about growth of your data, you should better go for a 32K pagesize, offering maximum storage... For configuration data and static data though you can choose to go with a 4k/8k pagesize and you might see some improvement in bufferpool and disk utilization (especially if you have a table with random access for records)...

Next is file-system caching... DB2 always caches the data (and caches it in more usable format for it db-engine) in bufferpools... So needless to say, if your OS is also caching the data, it is wasting both time and memory... So you better keep filesystem caching off (thank god it is default in 9.7)...

Exten Size is something which should be wisely thought out (esp when using automatic storage)... It is the amount of data/index pages which will be written to a container before going to the next container... If it is too low, you have too much of scattered data whereas if it is too high you have too much of I/O load... In case you do not specify extent size while creating the tablespace it will take it from dft_extent_sz db cfg... A thumb of rule is to use following formula:
RAID disk drives * Disk strip size

Prefetch Size is the no of pages for pre-fetching... It should not be smaller then extent size in any case, other wise you end up wasting I/O cycles... Most optimum suggestion is to keep it same as extent size, but if you are using multiplexing of data pages, it might be a good idea to keep it in multiples of extent size...

Overhead and transfer rate are two mostly ignored aspects of tablespace design and people tend to go with defaults... See generally hardware performances changes much rapidly and moreover organizations tend to change the hardware more frequently compared to s/w (at times it is vice versa)... Hence you transfer rate (which conveys to optimizer the speed @ which data will be tranferred from underlying container) and overhead (which conveys the other disk seek and various latencies related to disk) should be set properly... If they are wrong it might impact as much as deciding for a table scan instead of an index scan just cause the optimizer thinks the seek time will be too much for index pages...

At last let's try to answer the question on "If DMS/SMS/ASM???"... Well I will not bore with what's pros n cons of each as you can get lot of content on internet on this, and I do not want to copy paste stuff... :)
Well I advise going for Automatic Storage, as it is more easy to manage and handles parallelism more efficiently... It can utilize RAW devices more efficiently...

Lastly you can enable I/O parallelism with-in a tablespace (if it is spread across multiple containers)... for example If you have RAID 7 array you can set the below registry parameter using db2set

DB2_PARALLEL_IO=*:7


I shall continue the topic in next post... Where I will discuss on sizing and increment for tablespaces and also the temporary tablespaces...