DB2 V9.1 (Linux) on Single Partition
RAID-10
--------


Hi,

In one of my projects, I am observing that the INSERT PERFORMANCE is degrading every other day, with the TABLEs being loaded with more and more volume.

The insert operation is being performed by around 32 Threads of (Java Application) and we have kept the tables with APPEND_ON. There are around 3 Indexes on average in each table and all the tables are COMPRESSED.

There are around 100% asynch writes happening and the BP hit for both Data and INDEX tablespaces are 99.99%.

Still on day 1 while we started the INSERT rate in the DB was around 6000/second and on day 7, the INSERT rate has come down to around 4100/sec.

The tables are REORGed/Runstat every morning at 3 AM (est).

Thanks and please suggest,
Jayanta
DB2 UDB Consultant
Morgan-Stanley / Headstrong.

What are you doing to get the performance back to the 6000/second? By the way you worded your question it appears that you do. My first thought was of fragmentation due to your index keys, but you said you are reorging on a daily basis. So, leads us back to my question as to what clears it up for you to get your optimum insert rates? Then I would take a look into why that clears things up for you.

Dave Nance

Thanks, definitely I will update once we get back to that INSERT rate. All possible best practices we tried to implement seemed to have worked well but seemed the more and more records are being INSERTed is slowing down the INSERT. APPEND_ON, ASYNCH WRITE, Better I/O with RAID-10, Lesser INDEXes and no CLUSTERed ones, REORGs, COMPRESSION, ALTERNATE_PAGE_CLEANING, LINUX_AIO and all such required parameters are tuned. I don't see anything is missed in terms of getting the best INSERT performance. The throughput requirement is quite high, we need to handle around 3.5 Million transactions in an hour. I just wonder if anyone have come across such such situation of having degraded INSERT performance after having the tables fully loaded.

Thanks,
Jayanta
DB2 UDB Consultant
Morgan-Stanley / Headstrong.

So, you are saying that you have not gotten back to the rate of 6000/second as yet, that's just where you started and now you are falling off further and further from that rate? You mention you are doing daily reorgs. Are you reorging the table or the indexes? What parms are you using for the reorgs? Are your inserts in ascending order or random? What indexes are on the table?
It looks like you all have covered the bases so far for this table. Are there other issues slowing you down CPU performance, locking, etc...

Dave Nance

Hi All,
seems have got into some depth for the Slower INSERT issue. The reason behind the same is pointing towards the PAGE-Fragmentation happening. In fact we REORG/RUNSTAT once in a day, still the same was happening with much higher OVERFLOW ACCESSes. After further looking into the problem, got to see that the issue is with COMPRESSION ENABLEd in three of the tables which are undergoing very agressive PAGE FRAGMENTATION as there are INSERT/UPDATE in the ratio of 2:1 in those few tables. Moreover have removed CLUSTERING from few INDEXes residing in those tables. So the following two modifications have been made to have better INSERT performance:

1> Removing COMPRESSION from three tables which are undergoing heavy INSERT and UPDATE.
2> Removing CLUSTERed INDEXes.

Thanks,
Jayanta