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
Well,
Performance Engineering for Databases... it is not just about identifying and tuning the issues... or removing the bottlenecks... More than that it is avoiding those bottleneck to occur... More of guessing the issues beforehand rather than identifying it latter...

So it involves following...
Having a more scalable Physical design...
A more robust logical schema design...
A more performing application design...

Let me discuss in brief
To start with Physical Design...

PHYSICAL DESING
With DB2 you need to ensure following things in physical design
1) You have tablespaces distributed on different disks
2) With my personal experience I suggest usage of ASM spread over several disks
3) Always have indexes and Data on saperate disks
4) It is good to categorize table and indexes as below
i) Online Transaction Data
ii) Configuraiton Data
iii) Static data for your application to work and load
Place tables of above category in three different tablespaces and their indexes in three
different
5) Always create separate bufferpools for configuration & static data and transaction data
and indexes
6) Always create tablespaces with NO FS option to avoid double caching
7) Always better to use STMM for better memory management
8) Set a proper value for TRANSFERRATE and OVERHEAD for tablespaces, as they tell the optimizer about the time taken for seek and transfer of data from disk. A proper value can at times help the optimizer choose index scan over tables scan (say in a situation when table data may not be too scattered but index leaves are)

SCHEMA DESING
1) Avoid having foreign keys if your application takes care of integrity
2) Always index the columns used for
i) WHERE clause filter
ii) JOIN predicates
iii) SORT operation (ORDER BY, MAX(), MIN() etc)
3) Always keep the STATISTICS updated for all the tables to ensure index scans
4) If the cardinality of any table changes abruptly, consider making the table VOLATILE. this will help you ensure and index scan

APPLICATION DESIGN

1) Always use ANSI JOIN operator for joining tables. This avoids any accidental cross product
2) Try not to use too many sort operations in your query eg multiple MAX etc. instead see if you can use RANKING or windowing functions
3) Avoid doing JOINs with VIEWs, instead join with tables, this will avoid JOINing with any unnecessary underlaying table of view.
4) Avoid self joins and prefer case statements over self joins if possible
5) Use FETCH FIRST N rows to optimize selection for few rows if using a packaged application set OPTIMIZEFORNROWS in db2cli.ini.
6) As a thumb of rule, use parmeterized queries. When working with a packaged application you can set statement concentrator db cfg to force parameterization
7) When working with reporting tools like Crystal Report, use a single query to fetch the data. Never use links and joins provided by tool. The tool might be doing a full fetch and a join locally. This has an impact on IO and network as well.
8) Have a better concurrency in application

CONCURRENCY CONTROL
To improve the concurrency of the application use following:

1) Use Cursor Stability Isolation Level
2) Use LOCKSIZE row for all the tables
3) Use optimistic locking by using ROW CHANGE TIMESTAMP expression
4) Set following parameter
i) db2set db2_skipinterted=ON
ii) db2set db2_skipdeleted=ON
iii) db2set db2_evaluncommitted=ON
iv) db cfg cur_commit
5) Avoid long transaction and ensure the operations are committed regularly

I will wind up this post with a note that setting this right will always help you avoiding any performance issues in future....


I will try to come up with some SQL Tuning Tips...

1 comment:

  1. Hey there! Keep it up! This is a good read. I will be looking forward to visit your page again and for your other posts as well. Thank you for sharing your thoughts about performance engineering. I am glad to stop by your site and know more about performance engineering.
    One of the objectives of a peformance engineering is to eliminate late system deployment due to performance issues.
    Software Performance Engineering is a critical component of Enterprise Risk Management. In today’s complex and interdependent system environments, technology must provide an outstanding user experience for ten’s of thousands users. The new workloads supported by these systems has become increasingly volatile with tremendous variance in average and peak. Peak workloads are no longer simply 3 or 4 times the average. They can rapidly increase to 10 or 20 times the average. Many systems in place today were not designed to rapidly scale to 20X the average. The performance and quality of software or a system is not just a technical issue, but also reflects how well the business operates and performs on a daily basis. In essence, proactive performance engineering is enterprise risk management. PE ensures systems are able to scale with unexpected spikes in system demand whether driven by marketing events, or today's more volatile financial market behavior.

    ReplyDelete