We are using 8.2.9 Windows 64 edtion, in one of our projects we need
to recreate a few lager tables which have many millions of rows each,
we have used concurrent Java threads to read from data source and do
batch inserts to the target tables concurrently, to speed up the
inserts we have used the following configuration policies:
1. Use large page size (32K) and extend size (256) on the target db,
2. Use large buffer pools for the tablespaces which contains the large
tables on the target db.
3. Set num_cleaners to 4 to speed up moving data from bufferpool to
disks on the target db, we have 4 4-core CPU on the server.
4. Set chngpgs_thresh to 80%, using this value we mean avoiding DB2 to
moving data from bufferpool to disk too frequtenlly, because when
doing these inserts we have not other readers on the target.
5. Use lage page size(32K) and extend size(256) and prefetch size
(256) on the source db.
6. Use lage bufferpool on the source db.
7. Set num_ioservers to 4, we have 4 4-core cpu on the source db.
We are not sure about the chngpgs_thresh value, any suggestions? 3 3570
"Amber" <gu**********@g mail.comwrote in message
news:89******** *************** ***********@e39 g2000hsf.google groups.com...
We are using 8.2.9 Windows 64 edtion, in one of our projects we need
to recreate a few lager tables which have many millions of rows each,
we have used concurrent Java threads to read from data source and do
batch inserts to the target tables concurrently, to speed up the
inserts we have used the following configuration policies:
1. Use large page size (32K) and extend size (256) on the target db,
2. Use large buffer pools for the tablespaces which contains the large
tables on the target db.
3. Set num_cleaners to 4 to speed up moving data from bufferpool to
disks on the target db, we have 4 4-core CPU on the server.
4. Set chngpgs_thresh to 80%, using this value we mean avoiding DB2 to
moving data from bufferpool to disk too frequtenlly, because when
doing these inserts we have not other readers on the target.
5. Use lage page size(32K) and extend size(256) and prefetch size
(256) on the source db.
6. Use lage bufferpool on the source db.
7. Set num_ioservers to 4, we have 4 4-core cpu on the source db.
We are not sure about the chngpgs_thresh value, any suggestions?
Instead of modifying the chngpgs_thresh, set the
DB2_USE_ALTERNA TE_PAGE_CLEANIN G to ON (with db2set).
In the db cfg, set you logbuffsz to about 256. This requires that you
increase DBHEAP by same amount since it is part of DBHEAP memory.
On Jul 14, 11:42*am, "Mark A" <nob...@nowhere .comwrote:
"Amber" <guxiaobo1...@g mail.comwrote in message
news:89******** *************** ***********@e39 g2000hsf.google groups.com...
We are using 8.2.9 Windows 64 edtion, in one of our projects we need
to recreate a few lager tables which have *many millions of rows each,
we have used concurrent Java threads to read from data source and do
batch inserts to the target tables concurrently, to speed up the
inserts we have used the following configuration policies:
1. Use large page size (32K) and extend size (256) on the target db,
2. Use large buffer pools for the tablespaces which contains the large
tables on the target db.
3. Set num_cleaners to 4 to speed up moving data from bufferpool to
disks on the target db, we have 4 *4-core CPU on the server.
4. Set chngpgs_thresh to 80%, using this value we mean avoiding DB2 to
moving data from bufferpool to disk too frequtenlly, because when
doing these inserts we have not other readers on the target.
5. Use lage page size(32K) and extend size(256) and prefetch size
(256) on the source db.
6. Use lage bufferpool on the source db.
7. Set num_ioservers to 4, we have 4 4-core cpu on the source db.
We are not sure about the chngpgs_thresh value, any suggestions?
Instead of modifying the chngpgs_thresh, set the
DB2_USE_ALTERNA TE_PAGE_CLEANIN G to ON (with db2set).
In the db cfg, set you logbuffsz to about 256. This requires that you
increase DBHEAP by same amount since it is part of DBHEAP memory.- Hide quoted text -
- Show quoted text -
This has become a performance tuning thread, but I'd use a cursor load
instead, great speed, no space usage..
On Jul 14, 5:10*am, Amber <guxiaobo1...@g mail.comwrote:
We are using 8.2.9 Windows 64 edtion, in one of our projects we need
to recreate a few lager tables which have *many millions of rows each,
we have used concurrent Java threads to read from data source and do
batch inserts to the target tables concurrently, to speed up the
inserts we have used the following configuration policies:
1. Use large page size (32K) and extend size (256) on the target db,
2. Use large buffer pools for the tablespaces which contains the large
tables on the target db.
3. Set num_cleaners to 4 to speed up moving data from bufferpool to
disks on the target db, we have 4 *4-core CPU on the server.
4. Set chngpgs_thresh to 80%, using this value we mean avoiding DB2 to
moving data from bufferpool to disk too frequtenlly, because when
doing these inserts we have not other readers on the target.
5. Use lage page size(32K) and extend size(256) and prefetch size
(256) on the source db.
6. Use lage bufferpool on the source db.
7. Set num_ioservers to 4, we have 4 4-core cpu on the source db.
We are not sure about the chngpgs_thresh value, any suggestions?
If you are using SMS tablespace, I highly recommend enabling multipage
file allocation. Formatting one page at a time is a big performance
hit. See http://publib.boulder.ibm.com/infoce...e/r0002054.htm.
HTH,
Norm This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Jean-David Beyer |
last post by:
When initially populating a database, it runs slower that I would have
supposed. Checking here and there, especially with iostat, reveals that
most of the time is spent writting the logfiles. In other words, I am not
compute limited, but I am not IO limited either (in terms of bytes/second
to the drives). The system is in IO-WAIT state most of the time, so I
assume the process is really seek limited.
It is true that my method of...
|
by: ML |
last post by:
We have a large batch type update process that the user needs to launch from
a button on an ASP.NET webpage. The process basically does some queries
against SQL Server, loops through the result set, and process the data and
inserts new data to the database.
The problem is that this process can take several minutes to run due to the
amount of data being handled. Because of this if the user closes the
webpage or goes back to a previous...
|
by: Sezai YILMAZ |
last post by:
Hello
I need high throughput while inserting into PostgreSQL. Because of that I
did some PostgreSQL insert performance tests.
------------------------------------------------------------
-- Test schema
create table logs (
logid serial primary key,
ctime integer not null,
|
by: Schraalhans Keukenmeester |
last post by:
I have a substantial random stream of (numerical, long int) data (coming
from several SCADA sources) that needs to be passed thru a function that
tallies the occurrences of several different bit patterns in each number
(or in fact 32 bit row, of which a few (leftmost) are bogus).
Speediness of execution is high on the priority list.
I thought of a solution along these lines, avoiding relatively slow
switch/case or several if/elseif...
|
by: Private Pyle |
last post by:
We are prototyping an application that is inserting 500 rows into a
table. One method is to using batching and make one database call to
insert all 500 rows, the other method is to make 500 database calls
insert 1 row each time. The problem is that the batching method is
about 20% slower. Does anyone have any insights as to why this is the
case? I was expecting the batching to be faster.
DB2 9 on AIX 5.3 64 bit.
Thanks!
| |
by: Paul |
last post by:
I have a process that I want to speed up. It first was written in
Microsoft Access then converted to VB.NET. I would like to hear some
suggestions on how to speed it up. The process is to match names using
a Soundex function. It reads through a table of about 20,000 records
and matches it with a table of about 9,000 records. That is all done
in code. The tables are stored in SQL Server 2005.
Soundex is a numerical formula to...
|
by: charles.lobo |
last post by:
Hi,
I have recently begun using templates in C++ and have found it to be
quite useful. However, hearing stories of code bloat and assorted
problems I decided to write a couple of small programs to check. What I
expected was that there would be minor code bloat and some speed
improvement when using templates. However...
I wrote a basic list container (using templates), and a list container
(using virtual derived classes). I also tried...
|
by: Frank Swarbrick |
last post by:
We have a batch process that inserts large numbers (100,000 - 1,000,000) of
records into a database each day. (DL/I database.) We're considering
converting it to a DB2 table. Currently we have logic in place that, prior
to inserting any data, reads the first input record and checks to see if it
already exists in the table. If the record already exists there are two
options:
1) Don't continue, because you already ran this job today!
2)...
|
by: palgre |
last post by:
Hi ALL,
I am working in an windows based application using SQL Server 2000 as
database. There are few tables (refer parent tables) in the
application which are uploaded by a seprate application.
My application fetches data from parent tables and put data in
separate tables (chils tables) of application.
I am using dataset to fetch data from parent tables and insert/update
data in child table.
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |