Troubleshooting on site : how a simple Db2 SQL INSERT statement causes a high GETPAGES rate and how to fix it.
One shop I visit had an interesting problem the other day. I thought I would share it with you, all as it really was cool.
The game is afoot!
A few days into this brand new year the production machine started to die…they swapped in a bunch of CPs and ZiiPs to try and help but it didn’t. So now the hunt was on – Who was killing the machine and how?
The usual suspects?
Using our WLX (WorkLoadExpert) software, it was quickly found that a simple SQL INSERT statement appeared to be the cause. It was never in the old top 10 “bad guys” list, but was currently at around number four – and rising up the charts faster than Elvis!
First thought were…
- cross member invalidation, when multiple members insert in a data-sharing group,
- or a bad usage of INSERT into SELECT syntax.
Deerstalker hat switched to „ON“
Deerstalker hat mode was quickly switched on and the DBA group started looking at *why* a simple INSERT could cause such a headache… Then someone noticed that an ALTER had been done… At first, it was thought that this could cause the problem but the ALTER timestamp was 03:43 in the morning! No DBA was working at that time. “Aha!” that is the Db2 Database Maintenance size checker and corrector that ALTERs PRIQTY and SECQTY. (Even though they use the sliding scale and allocate with -1 and -1, go figure…)
PBG pains
I happened to be on site for another reason and started looking around. I noticed that the tablespace in question was a PBG with MAXPARTITION 99 and was at the 45th Partition. This partition had been created at 03:43 and was the ALTER event recorded in SYSIBM.SYSCOPY.
Compress me till it hurts!
Now to add some spice to the story, this table space is set to COMPRESS YES. So it would be good to know if the compression dictionary was copied from partition 44 or not… After looking through various documents and also browsing the VSAM cluster, it became apparent that Db2 creates the dictionary “on-the-fly” after a “Db2-determined number of inserts” – I would love to know that number!
It gets fishy…
All of this was actually a red herring in the grand scheme of things as then the Programming Development Team happened to mention that a brand new logging process had been written that was “faster better cheaper.” It had been rolled out to production after passing all tests with flying colours.
The smoking gun…
Using WLX, it was possible to see that for every insert about 500 GETPAGES were being done. Now, on this table, there are *no* indexes. It really is a just a container that gets dumped out every now and again. So I looked at the DDL and saw that the PBG space was indeed created with PCTFREE 0 and FREEPAGE 0 and also with MEMBER CLUSTER, a so-called MC00 space,
but APPEND YES was missing from the table definition and *this* was the reason for the high number of GETPAGES. It was reading all the SPACEMAP pages instead of just inserting at end.
More roads to Rome
Now another way of speeding this all up, would be to ALTER the tablespace to be TRACKMOD NO which would save a lot of time as the SPACEMAP updates are then not done. However, at this firm, they do a ton of Incremental Image Copies on this space, so removing this was not an option.
Listserv Helps
From listserv comes this info:
“APPEND YES avoids ’scanning‘ the table space part for free space before actually extending the dataset beyond its current high used RBA. How much does this save? The cost of getpages for each of your table space bit maps. If this is mostly a busy insert only table then the bitmaps are most likely in the buffer pool and will not require a physical I/O. APPEND YES will not avoid the get pages to the calculated home page because of the need to check for duplicates.”
Now it was an extremely busy table but the system was also under extreme stress, so it really was rereading the spacemaps. The DBA group scheduled an ALTER to APPEND YES and hope that if this level of INSERTS happens again that the system will not stop!
And finally
Here’s a last bit of info from IBM’s John Campbell all about this:
TRACKMOD
< NO> – is recommended if do not require incremental COPY
- Db2 does not keep track of updated pages
- Less space map page updates which will improve performance
- Less data sharing overhead
- Can be altered via ALTER TABLESPACE DDL
New APPEND option is provided for INSERT in V9 NFM
- CREATE/ALTER TABLE … APPEND YES
Can relieve high get pages during space search
- APPEND search at the end of table space quickly
- Not going through looking for deleted space
- Table space size will tend to grow
With high number of concurrent inserts, APPEND could cause bottleneck on the last space map page
- Using MEMBER CLUSTER option together with APPEND
- to relieve the contention at the end
Hindsight is always good isn’t it?
I hope your new year is off to a better start. As usual any queries or criticism gladly accepted!
TTFN
Roy Boxwell