Hi All,
I am struggling because of the update performance of db2 . Is it possible to improve update performance using the bufferpool size of tablespace?? I read it in some document but they didn't say how to change or how to relate a tablespace with the perticular table.Also can we disable logging in db2 level.(not the NOT LOGGED INITIALLY!! any other tech) .
Also How to improve my db2 database update performance its becoming poorer every day!!!!.
Advance Thanks And Hoping all will help me to improve the db2 update perf.
Vallish
6 8677
What DB2 environment do you mean? LUW or z/OS. I'm mostly z/OS, so I will give that view.
In z/OS you generally specify the Tablespace when defining the Table. You can also find by Selecting from SYSIBM.SYSTABLES there is a column TSNAME.
As to improving INSERT performance, check your Freespace and Freepage. Also how many Indexes are defined. Each Index needs to be updated for every INSERT. In DB2 V8 and V9 there are Muliple Inserts available.
The NO LOGGING is only available from DB2 V9 (z/OS) and can be switched by issuing the ALTER TABLESPACE LOGGED / NOT LOGGED. This can leave the Tablespace in Copypending status, so it needs to be tested.
What DB2 environment do you mean? LUW or z/OS. I'm mostly z/OS, so I will give that view.
In z/OS you generally specify the Tablespace when defining the Table. You can also find by Selecting from SYSIBM.SYSTABLES there is a column TSNAME.
As to improving INSERT performance, check your Freespace and Freepage. Also how many Indexes are defined. Each Index needs to be updated for every INSERT. In DB2 V8 and V9 there are Muliple Inserts available.
The NO LOGGING is only available from DB2 V9 (z/OS) and can be switched by issuing the ALTER TABLESPACE LOGGED / NOT LOGGED. This can leave the Tablespace in Copypending status, so it needs to be tested.
I am using db2 8.1 version in suse Linux .
Hi,
increasing the bufferpool size is always the first attempt and usually shows significant impact, esp. if you started a new database with default parameters. But increase the bufferpool with caution and check the resukting performance because every database has a point when adding memory to the bufferpool doesn't show much effect any more.
Another parameter you may play with is CHNGPGS_THRESH which usually is 60%. Try setting it to 80 which means the page cleaners are starting their work later. Additionally, you should increase their numbers (NUM_IOCLEANERS) so the db keeps using asynchronous page cleaning on the bufferpool once CHNGPGS_THRESH is reached. This can have high impact on databases with many write operations.
If your table gets lots of inserts but few updates, then the APPEND option could be interesting for you. It tells DB2 to write new records always to the end of the table and never to look for free space in between existing records (e.g. space freed after deleting records). Use "ALTER TABLE schema.tablename APPEND ON/OFF" to enable/disable this option.
Regards,
Bernd
I think otherwise.Setting CHNGPGS_THRESH to higer value may not be beneficiel in this case where we have frequent updates.This is because the DB has to wait until 80% of bufferpool to be filled up before activating num_iocleaners.Setting to a lower value might be beneficial becuse the dirty pages can be cleared well in advance.I would say you can try 20%.
Wiht Regards
Siva
IBM Certified DB2 DBA
Hi all,
Thanks for your valuable suggestion.
In my Scenario i need to do both read and update the database (not necessarily same record ).I am getting good read performance.But Update performance is very poor. I will try the thrashing parameter.
Thanks
Vallish
Please try the following:
CREATE REGULAR TABLESPACE MYTBSP PAGESIZE 16 K MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 16 OVERHEAD 10.5 PREFETCHSIZE 16 TRANSFERRATE 0.14 BUFFERPOOL MYDEFBPOOL DROPPED TABLE RECOVERY ON;
CREATE TABLE TAB1 (...........) IN MYTBSP
This most of the time improves ur performance
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Ed Wong |
last post by:
I am looking for some published paper regarding database performance
tunning performance strategies. This is for academic purpose so it
needs not to be any commerical database specific. It will...
|
by: bjarne |
last post by:
Willy Denoyette wrote;
> ... it
> was not the intention of StrousTrup to the achieve the level of efficiency
> of C when he invented C++, ...
Ahmmm. It was my aim to match the performance...
|
by: fritz-bayer |
last post by:
Hi,
today I noticed by accident that our mysql server occassionaly runs out
of connections. This is very bad and I did not notice it before. I have
now increase the maximum connections from the...
|
by: rmli |
last post by:
Expert tunning Database
http://quickdba.blogspot.com/2006/05/oracle-expert-tuning-secrets_22.html
|
by: Raj |
last post by:
Hi all,
I need help tunning the following query
Table scan on table Fact.sale_Current_2005
1.select key,sale_amt,dt,c4,c5,c6,c7 from Fact.sale_Current_view
where dt>= (select (min(Dt))...
|
by: keysac |
last post by:
Hi ,
My belwo script takes long time to run. What can i do about its performance tunning. This scripts read values from table t_emp . It search for that empid
ie comp_guid across the database . ie...
|
by: tpramarao |
last post by:
I want know how to implement index tunning by using index tunning wizard.can any body help me regarding this problem.
thanks,
pattabi
|
by: vijaysonar29 |
last post by:
Dear all ,
As I am going through maintainance phase of my project ,currently working on oracle9i,I nees some Articles,docs,whitepaper,weblink,pdf etc regarding SQL tunning,Anything which is...
|
by: bravo |
last post by:
hi
using mysql 4.1 i wish to update records using batch update but for a batch of 100, records and table having only 5000 records execution time is approx 2.4 sec i want to know whether this time...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
| |