Question After-imaging File Size

[BigSlick]

OE10.2BSp07 64-bit Windows 64-bit.

Hi all,

I have a question regarding after-image file sizes; basically i'd essentially like to reduce the size of the AI files that we transport to DR. Is this at all possible?

Our process runs every 15 minutes and over a course of a 24hour period the total file sizes can be between 8GB and 25GB, with the database only growing in size per day of around 0.7GB.

Database block size is 4096.
Size is 1.5TB

AI Log

♀01/20/16        Status: AI Log
15:47:06

After-image begin date:            10/11/15 13:41
After-image new date:              01/20/16 15:45
After-image open date:             01/20/16 15:45
After-image generation number:     8500
Number of after-image extents:     4
Current after-image extent:        11
Number of AI buffers:              100
After-image block size:            4096 bytes
After-image log size:              10036      K

BI Log

♀01/20/16        Status: BI Log
15:47:59

Before-image cluster age time:     0 seconds
Before-image block size:           4096 bytes
Before-image cluster size:         262128 kb (268419072 bytes)
Number of before-image extents:    2
Before-image log size (kb):        8388152
Bytes free in current cluster:     32724045 (13 %)
Last checkpoint was at:            01/20/16 15:23
Number of BI buffers:              100
Full buffers:                      1

Hope this is enough info to go off.

Thanks in advance!

 

[Rob Fitzpatrick]

The size of AI extents is a function of transaction activity, so I don't think you'll be able to make any meaningful improvement, short of optimizing the application(s).

On another note, I see you're using the maximum BI cluster size but your BI and AI block size is relatively small (4 KB). I always use the maximum block sizes of 16 KB for AI and BI. Increasing it may help a bit in reducing transaction-processing overhead. I don't know if it's a measurable amount, but I don't see a downside to increasing it.

Note that changing this (a) requires a brief downtime window and (b) increases the memory used by your AI/BI buffers, but it's a very small amount.

Do you use the AI file management daemon?

 

[BigSlick]

Thanks Rob - I did think that would be the case. I saw something which suggested to reduce the bibufs but i wasnt too sure about the implications.

The issue we are getting is that the AI files are taking >15 mins to roll forward every now and again. This causes a backlog and i'm not too happy about that due to the implications that could occur.

I'll look at increasing the block size then, see if that helps.

Yes.

♀01/20/16        Activity: Summary
16:33:54        10/18/15 09:50 to 01/20/16 15:46 (2262 hrs 56 min)

Event                  Total  Per Sec |Event                  Total  Per Sec

Commits             3626595K    455.9 |DB Reads           51384444K   6458.9
Undos              16599070       2.0 |DB Writes           1230725K    154.7
Record Reads         750175M  96558.0 |BI Reads             164748K     20.7
Record Updates      1018358K    128.0 |BI Writes            480206K     60.4
Record Creates       747627K     94.0 |AI Writes            426822K     53.7
Record Deletes       340153K     42.8 |Checkpoints            6634       0.0
Record Locks       13050572K   1640.4 |Flushed at chkpt    4197424       0.5
Record Waits           5830       0.0 |Active trans             28

Rec Lock Waits     0 %    BI Buf Waits      0 %    AI Buf Waits      0 %
Writes by APW     97 %    Writes by BIW    67 %    Writes by AIW    99 %
DB Size:        1466 GB   BI Size:       8191 MB   AI Size:          9 MB
Empty blocks:5656880      Free blocks:   1134      RM chain:     94271
Buffer Hits       97 %    Primary Hits     97 %    Alternate Hits    0 %

301 Servers, 648 Users (6 Local, 642 Remote, 52 Batch), 1 Apws

 

[Rob Fitzpatrick]

The implication for you of increasing the BI and AI block sizes is additional shared memory size. You currently use 100 * 4096 bytes (400 KB) for -bibufs and another 400 KB for -aibufs. Increasing the block sizes to 16 KB each would increase memory usage from 800 KB to 3.1 MB; not a big deal.

But I wouldn't expect a change in block size to help with roll forward time. If rolling forward is taking longer than the AI extent switch interval then that's obviously a problem. How do your DR/hot spare system and database compare with production? Same hardware, OS, patch levels, OE version and service pack, database configuration, storage hardware? It sounds like DR may be under-powered in some way.

 

[TheMadDBA]

If you look in the database log file for the warm database I am pretty sure you will see that most of the time is spend processing the BI file and not the AI apply itself.

Like Rob says you need to look into the hardware... especially the disks.

This is a known issue with OE that I am still waiting to be fixed. Part of the issue is massively multi threaded writes to the AI and still a single thread doing the apply.

 

[BigSlick]

Thanks TheMadDBA. You are right and i'm having flashbacks of pointing this out to management!

[2016/01/22@15:17:38.260+0000] P-5456       T-5808  I RFUTIL   : (5326)  Begin Physical Redo Phase at 917448 . 
[2016/01/22@15:23:27.184+0000] P-5456       T-5808  I RFUTIL   : (7161)  Physical Redo Phase Completed at blk 1027729 off 3794 upd 653607.

 

[TheMadDBA]

Check out the disks that serve the BI file and make sure they are as fast as possible. I have had success in the past keeping the file system caching turned on for the BI file system but turned off for the rest of the files systems. This keeps more and more of the BI file in memory and reduces the Redo phase.

Obviously test these things out before just doing them in production. YMMV depending on OS version, file system settings, OE version and options.

I have tried almost every combination of parameters to get the apply to go faster but no luck. Even with all of its flaws OE Replication does seem to do a much better job of keeping up... assuming you never ever use sync mode.

 

[cj_brandt]

Do you compress the ai log before transferring to DR ?

You can check the db log of the DR database to see where the time is being spent - is it processing the BI or AI.

Keeping your active transactions as small as possible helps apply AI logs faster. We have some character screens in our application that allow a user to open an active transaction and then leave for lunch. Before we addressed that issue, we had a lot of issues with applying AI logs on DR.

 

[BigSlick]

Do you compress the ai log before transferring to DR ?

No, at least i think not. Is there a param i can check?

This is one of the latest entries in the DR database:

               Mon Jan 25 13:06:31 2016
[2016/01/25@13:06:31.024+0000] P-7084       T-6628  I RFUTIL   : (451)   Roll forward session begin for AI_Processing on CON:. 
[2016/01/25@13:06:31.046+0000] P-7084       T-6628  I RFUTIL   : (5326)  Begin Physical Redo Phase at 0 . 
[2016/01/25@13:16:48.260+0000] P-7084       T-6628  I RFUTIL   : (7161)  Physical Redo Phase Completed at blk 272392 off 2843 upd 293568. 
[2016/01/25@13:16:48.283+0000] P-7084       T-6628  I RFUTIL   : (13547) At end of Physical redo, transaction table size is 32768. 
[2016/01/25@13:16:53.082+0000] P-7084       T-6628  I RFUTIL   : (660)   Beginning roll forward of after-image file E:\AI\Customer.ai9. 
[2016/01/25@13:16:53.105+0000] P-7084       T-6628  I RFUTIL   : (1633)  After-image dates for this after-image file: 
[2016/01/25@13:16:53.105+0000] P-7084       T-6628  I RFUTIL   : (1640)      Last AIMAGE BEGIN Sun Oct 11 13:41:54 2015 
[2016/01/25@13:16:53.106+0000] P-7084       T-6628  I RFUTIL   : (1641)      Last AIMAGE NEW Mon Jan 25 09:30:49 2016 
[2016/01/25@13:16:53.106+0000] P-7084       T-6628  I RFUTIL   : (1642)      This is aimage file number 8852 since the last AIMAGE BEGIN. 
[2016/01/25@13:16:53.107+0000] P-7084       T-6628  I RFUTIL   : (1643)      This file was last opened for output on Mon Jan 25 09:30:49 2016. 
[2016/01/25@13:24:31.628+0000] P-7084       T-6628  I RFUTIL   : (15109) At Database close the number of live transactions is 8. 
[2016/01/25@13:24:31.648+0000] P-7084       T-6628  I RFUTIL   : (1634)  1361376 notes were processed. 
[2016/01/25@13:24:31.648+0000] P-7084       T-6628  I RFUTIL   : (3785)  13 in-flight transactions. 
[2016/01/25@13:24:31.649+0000] P-7084       T-6628  I RFUTIL   : (1635)  42637 transactions were started. 
[2016/01/25@13:24:31.649+0000] P-7084       T-6628  I RFUTIL   : (11138) 42642 transactions were completed. 
[2016/01/25@13:24:31.650+0000] P-7084       T-6628  I RFUTIL   : (1636)  At the end of the .ai file, 8 transactions were still active. 
[2016/01/25@13:24:31.652+0000] P-7084       T-6628  I RFUTIL   : (662)   Roll forward completed. 
[2016/01/25@13:24:31.759+0000] P-7084       T-6628  I RFUTIL   : (334)   rfutil -C roll forward session end.

My plan is to attack the code shortly; always a fun job...

 

[Rob Fitzpatrick]

If AI extents are compressed it would be done outside of OpenEdge. So there isn't a parameter; you would have to check your automation.

 

[TheMadDBA]

Ten minutes for redo is awful.. 8 minutes for the apply isn't great either. Same advice for the BI redo as above... look into switching AI files more frequently and/or look into why you have so many transactions.

I wouldn't worry about compression unless you are having severe issues transferring the AI extents from prod to DR. Switching more often is probably a better use of your time... and better DR.

 

[Rob Fitzpatrick]

My plan is to attack the code shortly

Don't forget to attack the hardware/configuration of the DR system too.

Is all of your data in Type II storage areas?

 

[BigSlick]

The AI file in question is 150mb. The AI rolls every 15 minutes.

I think it must be the sheer amount of transactions.

I did wonder about compression and why i hadnt seen it, didnt know you meant via o/s.

I've queried the hardware on DR. The BI and DB are on the same disk, so i'm addressing that!

It's all type II storage areas, indexes are generally separated from tables too.

 

[Cringer]

"generally separated" could be a little worrying, but not the cause of your issues here. It's generally considered to be a very good idea to have your indexes completely separate from tables in terms of storage.

 

[BigSlick]

I left myself open for that one!

Large indexes are in there own area, smaller indexes have a few areas too. There is the odd exception where indexes are with tables. I'm aware of this and it will get addressed shortly.

In regards to separating indexes, if we have a table that has 4 x 40gb indexes, and 6 x 5-10gb indexes. Should each of the 4 x 40gb indexes have their own area or could they all be placed in one index area?

 

[Cringer]

This is the sort of question that generates hot debate at conferences. To an extent it's personal preference really. But for a more definitive answer I'll defer to the gurus that frequent the boards...

 

[TheMadDBA]

With Type II areas it really is mostly a matter of preference.. the one exception would be indexes that grow very (your own definition) quickly.

For 15 minutes on a large database those transactions aren't that bad. One system I used to admin would do several GB in 15 minutes with much larger peaks.

I would suggest focusing on the DR hardware and configuration. You are much more likely to see positive results working on that... at least in the short to medium term.

 

[BigSlick]

Hi all,

I thought i'd provide some form of update and a little bit more background to this. We use windows task scheduler to perform the roll forward and this is what is causing the headache. If I run the bat file interactively the process runs much faster than it does when run via task scheduler.

I've seen you can export then re-import the task to alter the priority. Maybe that's the next step to see if that helps - although this calls a bat file which calls rfutil so unless the priority is fed down the chain we're at a loss.

cheers

 

[TomBascom]

Apply the Linux patch...

I have heard that the "changing the priority" thing should work. But I have not tested it myself.

You've not been specific about what flavor of Windows this is. If you can't apply the Linux patch you might want to look into modernizing Windows.

 

[Rob Fitzpatrick]

I've seen you can export then re-import the task to alter the priority. Maybe that's the next step to see if that helps - although this calls a bat file which calls rfutil so unless the priority is fed down the chain we're at a loss.

I've been bitten by this. The issue is that when you create a task, the default priority is slightly lower than normal and the Task Scheduler UI doesn't surface a way to change that. But the XML export format does, thus the export/import workaround. So as long as you set it appropriately then I believe the child processes will also run with the default priority (but please test that!).