Resolved Before Image buffer waits & After Image buffer waits

[RealHeavyDude]

OpenEdge 11.3.3, Sun Solaris SPARC 64Bit ( Sun V490+ 1800 Mhz with 64 GB memory - database files residing on ZFS file systems on a SAN ).

I know - we should have migrated to a more decent hardware on RHEL a long time ago and I wish I could do something about the SAN but I can't. A migration budget has been cancelled now for 5 years in a row and I don't even want to know how many $$$ Oracle gets for the maintenance of those SPARC dinosaurs ...

Nevertheless, I finally found some time to have a look at some statistics of our production database.
So the before image and after image buffer waits caught my attention:

On average over the last year had

• A before image buffer wait every 0.2 seconds
• An after image buffer wait every 7 seconds

Now I wonder if I should do something about it, for example increasing the BI and AI buffers ( both of which I've already set to 750 )?


The database is about 500 GB large - you don't want to know how long an online backup takes ...

Startup parameters:

-S 47311                              # The service or port number used by a broker process on the host machine
-minport 8400                         # The lowest port number in a specified range of port numbers accessible to a client
-maxport 8499                         # The highest port number in a specified range of port numbers accessible to a client
-Mpb 35                               # The maximum number of servers that multiple brokers can start to serve remote users for any one protocol
-B 2800000                            # The number of blocks in the database buffers
-B2 20000                             # The number of blocks in the alternate buffer pool
-bibufs 750                           # The number of before-image buffers
-aibufs 750                           # The number of after-image buffers
-bithold 6000                         # The recovery log threshold
-tablerangesize 2500                  # Specifies the number of tables for which statistics are collected
-indexrangesize 7000                  # Specifies the number of indexes for which statistics are collected
-n 350                                # The maximum number of OpenEdge processes (users) on the database
-Mn 40                                # The maximum number of remote servers
-Ma 15                                # The maximum number of clients per remote server
-Mi 5                                 # The minimum number clients per reomte server
-L 50000                              # The number of entries in the record locking table
-Bpmax 250                            # The number of private buffers a connection can request through the private read-only buffers
-spin 5000                            # The number of times a process tries to acquire a latch before pausing
-prefetchDelay                        # Enable a delay when sending the first network message
-prefetchFactor 100                   # The percent of a message buffer to fill before sending it to a waiting remote client
-prefetchNumRecs 100                  # The number of prefetch records to put in a network message before sending the message to a waiting remote client
-Mm 8192                              # The network message buffer size. Must match between client and database server
-ssl                                  # Support for secure socket layer connections
-Mxs 156000                           # The size of the shared-memory overflow area in kilobytes
-lruskips 100                         # The number of times to access a buffer before moving it to the end of the LRU chain
-lru2skips 100                        # The number of times to access an alternate buffer before moving it to the end of the LRU chain
-omsize 8192                          # The size of the storage object cache ( _StorageObject )
-ServerType 4GL                       # Type of servers supported by the broker ( 4GL | SQL | both )

Thanks in Advance and Best Regards,
RealHeavyDude.

 

[Rob Fitzpatrick]

Are those busy buffer waits or empty buffer waits? If the former, there's nothing you can tune; it's just an indication of a high write workload. If the latter, increasing -bibufs/-aibufs can help, assuming your system can keep up. I assume you are running BIW and AIW.

You will get empty BI buffer waits when all of the BI buffers (except the current output buffer obviously) are on the modified queue. Those blocks are flushed to the BI file and put back on the free queue by the BIW, but in some cases also by an APW. So if your BI disk volume is slow or has high contention and your rate of writing BI notes is high and sustained then you could be using BI buffers faster than you are freeing them and ultimately run out (empty buffer wait), regardless of how many you have. Higher -bibufs will get you through short spikes but maybe not longer ones.

So if it is empty BI/AI buffer waits you are seeing, you might try to see if you can segregate those files on the SAN, if you haven't already.

On an unrelated note, I see TRS is 2500 and IRS is 7500. Is your schema that large that you need those values? If so, -omsize isn't large enough to cache all your _storageobject entries and you will be incurring a lot of OM latch activity which can throttle read throughput. Set -omsize larger than the record count in _storageobject. You can see OM latch activity in promon R&D | debghb | 6 | 11.

 

[Rob Fitzpatrick]

Also unrelated to the original question, the client/server params caught my eye.

-n 350 # The maximum number of OpenEdge processes (users) on the database
-Mn 40 # The maximum number of remote servers
-Ma 15 # The maximum number of clients per remote server
-Mi 5 # The minimum number clients per reomte server
-Mpb 35 # The maximum number of servers that multiple brokers can start to serve remote users for any one protocol

Mpb * Ma is 525, but -n is only 350.

 

[RealHeavyDude]

Hi Rob,

thanks for your valuable insights.

We are running APWs, AIW and BIW and data, before image and after image extents are segregated into their own dedicated file systems on the same SAN ( which is an EMC VMAX 20K with roughly 500 TB capacity ). Unfortunatley I don't have much control abount a more granular segregation other than all of these file systems are segregated into at least 2 LUNs. But that I was only able to achieve because I know somebody from the storage team who agreed to put some additional work into it.

Actually we have busy buffer waits. We have a transaction throughput somewhere between 30 and 40 GB worth of AI transaction notes every night during day end processing. So it looks like there is not much I can do about it.

-tablerangesize / -indexrangesize vs. -omsize: We have removed a lot of table before I introduced the -omsize parameter and I simply forgot to adjust -tablerangesize / -indexrangesize. I will adjust them accordingly.
select count(*) from _StorageObject = 6821
select count(*) from _File = 1700
select count(*) from _Index = 5249

Mpb * Ma is 525, but -n is only 350.

This is just a mistake of mine - thanks for pointing it out.


Thanks and Best Regards, RealHeavyDude.

 

[Rob Fitzpatrick]

It sounds like you're following good practices for write performance. Do you need to improve the performance of the day-end processing?

One other thought: are your AI extents fixed or variable?

 

[RealHeavyDude]

Actually at the moment we are able to fullfill our SLA so there is immediate need to improve the performance of the day-end processing. During the day-end processing we recieve some 100+ in-bound files which we need to import and then produce a handful of out-bound files which need to be delieverd to downstream systems before 08:00. Mostly the day-end processing is done 1 hour in advance of the delivery time.

One other thought: are your AI extents fixed or variable?

We use 8GB large fixed AI extents which are handled by the AI archiving daemon. Don't know wheter variable AI extents would be better on ZFS file system. Anyway - I am not allowed to have growing files on those file systems which are mounted under /db ( when we'll migrate to RHEL I will change the mountpoint to /pro and nobody will care anymore ... ). Additonally we take a full online backup daily.

I already thought of smaller AI extents or a shorter interval but our SAN is mirrored between an active and a passive data center in two different cities. Therefore, when the SAN would go down we would immediately switch to the passive data center. Therefore basically we rely on backups + AI mostly to cover software disasters.

Thanks, RealHeavyDude.

 

[RealHeavyDude]

Forgot to mention that we have a batch job in place which runs every Sunday and merges the results from a db analysis with the statistics accessible via VST.

 

[Rob Fitzpatrick]

We use 8GB large fixed AI extents which are handled by the AI archiving daemon. Don't know wheter variable AI extents would be better on ZFS file system.

My guess would be that with that many writes per day you're better off with fixed extents for AI, and trying to stay within your fixed extents in BI.

 

[TomBascom]

Did you mean to say "unable" to meet SLA?

If you are currently unable to meet your SLA I would find that to be a very strong argument for accelerating the migration to more capable hardware.

You seem to be doing the best that you can given the handicaps that you are required to operate with.

 

[RealHeavyDude]

I would almost say "unfortunately" we are able to fullfill our SLAs