SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-27-2008, 01:09 PM
| ||||
| ||||
 V9 Compression & Log Use I have been testing compression for update operations. Can anyone tell me why I require more log for an update of a compressed table than I do for the same table that is not compressed ? I tried an update for the same number of rows for two copies of a table, one compressed and one not. The compressed UOW exceeds my log allocation while the non-compressed does not. Thanks      |
|
02-27-2008, 01:09 PM
| |||
| |||
| Re: V9 Compression & Log Use mike_dba wrote: > I have been testing compression for update operations. Can anyone > tell me why I require more log for an update of a compressed table > than I do for the same table that is not compressed ? > > I tried an update for the same number of rows for two copies of a > table, one compressed and one not. The compressed UOW exceeds my log > allocation while the non-compressed does not. Huh? That's odd. The log records remain compressed. Simply speaking you should see a similar compression ration for the logs as for the table. Cheers Serge -- Serge Rielau DB2 Solutions Development IBM Toronto Lab |
|
02-27-2008, 01:09 PM
| |||
| |||
| Re: V9 Compression & Log Use On Dec 5, 11:26 am, Serge Rielau <srie...@ca.ibm.com> wrote: > mike_dba wrote: > > I have been testing compression for update operations. Can anyone > > tell me why I require more log for an update of a compressed table > > than I do for the same table that is not compressed ? > > > I tried an update for the same number of rows for two copies of a > > table, one compressed and one not. The compressed UOW exceeds my log > > allocation while the non-compressed does not. > > Huh? That's odd. The log records remain compressed. Simply speaking you > should see a similar compression ration for the logs as for the table. > > Cheers > Serge > > -- > Serge Rielau > DB2 Solutions Development > IBM Toronto Lab I was thinking that maybe the updated column had some immense compression on it (the table went from 22 Gb to 9 Gb). And the update changed the dictionary and maybe there was no entry for the new value in the dictionary so this caused some trickle down effect and caused additional logging. But the fact that it fit into my logs for non- compressed data is puzzling. I am updating a 10 byte column in a 559 byte wide row. There are 72 million rows to update. The log started at 8 Gb and was increased to 12 Gb and still wouldn't fit. |
|
02-27-2008, 01:09 PM
| |||
| |||
| Re: V9 Compression & Log Use mike_dba wrote: > On Dec 5, 11:26 am, Serge Rielau <srie...@ca.ibm.com> wrote: >> mike_dba wrote: >>> I have been testing compression for update operations. Can anyone >>> tell me why I require more log for an update of a compressed table >>> than I do for the same table that is not compressed ? >>> I tried an update for the same number of rows for two copies of a >>> table, one compressed and one not. The compressed UOW exceeds my log >>> allocation while the non-compressed does not. >> Huh? That's odd. The log records remain compressed. Simply speaking you >> should see a similar compression ration for the logs as for the table. >> >> Cheers >> Serge >> >> -- >> Serge Rielau >> DB2 Solutions Development >> IBM Toronto Lab > > I was thinking that maybe the updated column had some immense > compression on it (the table went from 22 Gb to 9 Gb). And the update > changed the dictionary and maybe there was no entry for the new value > in the dictionary so this caused some trickle down effect and caused > additional logging. But the fact that it fit into my logs for non- > compressed data is puzzling. > > I am updating a 10 byte column in a 559 byte wide row. There are 72 > million rows to update. The log started at 8 Gb and was increased to > 12 Gb and still wouldn't fit. > I do have a thought. Could it be that you do have the dictionary, but the data is NOT compressed. Then when you do the UPDATE DB2 compresses the page in question which, of course, requires to log the whole page. So what you are logging is not the update, but the piecemeal compression of the table. Make sure you run REORG after getting the dictionary Cheers Serge -- Serge Rielau DB2 Solutions Development IBM Toronto Lab |
|
02-27-2008, 01:09 PM
| |||
| |||
| Re: V9 Compression & Log Use On Dec 5, 1:24 pm, Serge Rielau <srie...@ca.ibm.com> wrote: > mike_dba wrote: > > On Dec 5, 11:26 am, Serge Rielau <srie...@ca.ibm.com> wrote: > >> mike_dba wrote: > >>> I have been testing compression for update operations. Can anyone > >>> tell me why I require more log for an update of a compressed table > >>> than I do for the same table that is not compressed ? > >>> I tried an update for the same number of rows for two copies of a > >>> table, one compressed and one not. The compressed UOW exceeds my log > >>> allocation while the non-compressed does not. > >> Huh? That's odd. The log records remain compressed. Simply speaking you > >> should see a similar compression ration for the logs as for the table. > > >> Cheers > >> Serge > > >> -- > >> Serge Rielau > >> DB2 Solutions Development > >> IBM Toronto Lab > > > I was thinking that maybe the updated column had some immense > > compression on it (the table went from 22 Gb to 9 Gb). And the update > > changed the dictionary and maybe there was no entry for the new value > > in the dictionary so this caused some trickle down effect and caused > > additional logging. But the fact that it fit into my logs for non- > > compressed data is puzzling. > > > I am updating a 10 byte column in a 559 byte wide row. There are 72 > > million rows to update. The log started at 8 Gb and was increased to > > 12 Gb and still wouldn't fit. > > I do have a thought. > Could it be that you do have the dictionary, but the data is NOT > compressed. Then when you do the UPDATE DB2 compresses the page in > question which, of course, requires to log the whole page. > So what you are logging is not the update, but the piecemeal compression > of the table. > > Make sure you run REORG after getting the dictionary > > Cheers > Serge > -- > Serge Rielau > DB2 Solutions Development > IBM Toronto Lab- Hide quoted text - > > - Show quoted text - I just double checked : alter table schema.fact_tbl_comp COMPRESS YES; reorg table schema.fact_tbl_comp resetdictionary; runstats ... update --> sql0964 log full |
|
02-27-2008, 01:09 PM
| |||
| |||
| Re: V9 Compression & Log Use On Dec 5, 2:55 pm, mike_dba <michaelaaldr...@yahoo.com> wrote: > On Dec 5, 1:24 pm, Serge Rielau <srie...@ca.ibm.com> wrote: > > > > > > > mike_dba wrote: > > > On Dec 5, 11:26 am, Serge Rielau <srie...@ca.ibm.com> wrote: > > >> mike_dba wrote: > > >>> I have been testing compression for update operations. Can anyone > > >>> tell me why I require more log for an update of a compressed table > > >>> than I do for the same table that is not compressed ? > > >>> I tried an update for the same number of rows for two copies of a > > >>> table, one compressed and one not. The compressed UOW exceeds my log > > >>> allocation while the non-compressed does not. > > >> Huh? That's odd. The log records remain compressed. Simply speaking you > > >> should see a similar compression ration for the logs as for the table. > > > >> Cheers > > >> Serge > > > >> -- > > >> Serge Rielau > > >> DB2 Solutions Development > > >> IBM Toronto Lab > > > > I was thinking that maybe the updated column had some immense > > > compression on it (the table went from 22 Gb to 9 Gb). And the update > > > changed the dictionary and maybe there was no entry for the new value > > > in the dictionary so this caused some trickle down effect and caused > > > additional logging. But the fact that it fit into my logs for non- > > > compressed data is puzzling. > > > > I am updating a 10 byte column in a 559 byte wide row. There are 72 > > > million rows to update. The log started at 8 Gb and was increased to > > > 12 Gb and still wouldn't fit. > > > I do have a thought. > > Could it be that you do have the dictionary, but the data is NOT > > compressed. Then when you do the UPDATE DB2 compresses the page in > > question which, of course, requires to log the whole page. > > So what you are logging is not the update, but the piecemeal compression > > of the table. > > > Make sure you run REORG after getting the dictionary > > > Cheers > > Serge > > -- > > Serge Rielau > > DB2 Solutions Development > > IBM Toronto Lab- Hide quoted text - > > > - Show quoted text - > > I just double checked : > > alter table schema.fact_tbl_comp COMPRESS YES; > reorg table schema.fact_tbl_comp resetdictionary; > runstats ... > update --> sql0964 log full- Hide quoted text - > > - Show quoted text - Do you know if subsequent Inserts after the reorg simply use the existing dictionary or add entries to the dictionary ? Do you know the effect of Not Logged Initially on compression ? I believe that you may have pointed my in the right direction with regard to reorg. I'll test some more. |
|
02-27-2008, 01:09 PM
| ||||
| ||||
| Re: V9 Compression & Log Use mike_dba wrote: > Do you know if subsequent Inserts after the reorg simply use the > existing dictionary or add entries to the dictionary ? > Do you know the effect of Not Logged Initially on compression ? Once the dictionary is created it remains static. NLI has no impact on compression and vice versa. Cheers Serge -- Serge Rielau DB2 Solutions Development IBM Toronto Lab |
Linear Mode
