OE and SQL tuning parameters (-omsize in particular)

#1
This is a continuation of Answered - Can (SQL) UPDATE STATISTICS hamper performance of ABL AFTER the fact - NO! that took a new direction.

Current omsize is 1024, database objects are 2623. Below are samples from protop and promon for approximately the same 60s interval.

Protop
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqLatch Activityqqqqqqqqqqqqqqqqqqqqqqqqqqqqqk
x Latch Type Holder QHolder Requests Waits Wait% x
x qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq x
x OM Spin 617 -1 3,022 0 99.99% x

Promon
----- Locks ----- ------ Busy ------ Naps ---------- Spins ----------- ----- Nap Max -----
Owner Total /Sec /Sec Pct /Sec /Sec /Lock /Busy Total HWM
OM -- 542689 9044 0 0.0 0 0 0 0 0 0

Will gather and present more stats before increasing the omsize.
 
#2
While you don't have any obvious pressing problems (hardly any waits) it is a "best practice" to set it high enough to not have to worry about it. I'd go with -omsize 3072 or 4096. There's nothing magic about those values -- i just like powers of 2. Either value will give you a nice pad for future expansion.

The kbase cannot be bothered to say exactly how much memory omsize uses: "memory used for omsize is very small" but I seem to recall Rich Banville saying it was less than 100 bytes per entry at a talk once.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
#3
This is the kind of thing you can estimate with a little trial and effect. This was done in 11.6.3 64-bit, on Linux.

Code:
proserve demo -omsize 1:

04/09/18        Status: Shared Resources
18:13:30

Primary object cache (-omsize):    Total: 13 Used: 13 Pinned: 0
Secondary object cache (-omsize):  Total: 13 Used: 13 Pinned: 0
Shared memory allocated:            17476K

proserve demo -omsize 1013:

Primary object cache (-omsize):    Total: 1013 Used: 233 Pinned: 0
Secondary object cache (-omsize):  Total: 1013 Used: 0 Pinned: 0
Shared memory allocated:            17684K  (17476 + 208)

proserve demo -omsize 2013:

Primary object cache (-omsize):    Total: 2013 Used: 233 Pinned: 0
Secondary object cache (-omsize):  Total: 2013 Used: 0 Pinned: 0
Shared memory allocated:            17892K  (17684 + 208)

proserve demo -omsize 3013:

Primary object cache (-omsize):    Total: 3013 Used: 233 Pinned: 0
Secondary object cache (-omsize):  Total: 3013 Used: 0 Pinned: 0
Shared memory allocated:            18100K  (17892 + 208)
It turns out the smallest possible value you can have for -omsize is 13, even if you specify less. If you specify 0 the DB won't start. Every increase of 1000 increases shared memory size by 208 KB (212,922 bytes), so each entry takes about 213 bytes of memory. Pretty inexpensive.

A few years ago at a conference, I was in a DB-tuning workshop. One of the exercises demonstrated a measurable overhead on read performance to having -omsize tuned too low. Was it a contrived test? Maybe. Does every environment suffer from significant latch contention? Definitely not. Is this the "lowest-hanging fruit" for optimization in any given environment? Probably not.

But I don't see that as a reason not to tune it anyway. I have seen the effect of setting it in my clients' databases: OM latch locks go from many millions to n, where n is the number of storage objects. As I see it, latch locks are work and work isn't free, so why do it if you don't have to?

The nice thing about -omsize is it's easy to tune. There is no art to it like with -spin or some other parameters. There is no downside to tuning it appropriately, apart from a small shared memory size cost. So I don't worry about how much it helps me. I just set it and spend my time thinking about things that merit more thought. Maybe in a future release, Progress will automagically set the right value and this discussion will be moot. But until then:
  • Count the storage objects (e.g.: select count(*) from _storageobject; )
  • If count <= 1024 (the default value), you don't need to set -omsize.
  • If count > 1024, set -omsize <count>.
It is worth noting that -omsize sets the size (# of entries) of two caches, as shown in promon above: the primary object cache and the secondary object cache. If your primary cache is large enough to hold all the storage objects, they are accessed without latching. If it isn't large enough, it is filled and the remainder are cached in the secondary cache. In this case an LRU list is maintained and processes must lock the OM latch to update it. If objects are added to the database online, the are always added to the secondary cache, even if there are empty slots in the primary. Access to the secondary cache always involves LRU/latch access.

This is my recollection from past conferences, but IANADBEP (I am not a DB engine programmer). :)
 
Last edited:
#4
Thanks again guys!
I'm just holding off so I can gather more stats. (Didn't know about the hidden menu last week so I had to revise my collection script). I should have 24 hours worth by 12pm today. So I will make the change then. And go from there.
 
#7
Ok - here are the results. Not much of a difference. But I learned a lot in the process and will continue the tuning quest one step at a time.
 

Attachments

#9
Yep fewer than 3072:
select count(*) from _storageobject
2,623

However, R&D 1|13
Primary object cache (-omsize): Total: 1024 Used: 1024 Pinned: 0
Secondary object cache (-omsize): Total: 3072 Used: 1051 Pinned: 0

I didn't restart, just upped the size online, but omsize startup parameter is reported as 3072
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
#10
Oh, okay, sorry; my mistake. That's something important that I obviously forgot. Resizing online only resized the secondary cache, so you are still doing lots of latch locking as a result. You have an LRU for both caches.

If you were to restart with -omsize 3072, I would expect a total of 2623 latch locks for OM, not the tens of millions you've shown. Then you would have all the objects cached in the primary, with room to spare, and the secondary would be empty.
 
#12
BINGO!
----- Locks ----- ------ Busy ------ Naps ---------- Spins ----------- ----- Nap Max -----
Owner Total /Sec /Sec Pct /Sec /Sec /Lock /Busy Total HWM
04/13/18 20:00:02 OM -- 279949087 77742 0 0.0 0 0 0 0 0 0
04/13/18 21:00:02 OM -- 159044683 44166 0 0.0 0 0 0 0 0 0
04/13/18 22:00:02 OM -- 25560135 7098 0 0.0 0 0 0 0 0 0
04/13/18 23:00:02 OM -- 62198 17 0 0.0 0 0 0 0 0 0
....
04/16/18 05:00:03 OM -- 0 0 0 0.0 0 0 0 0 0 0
04/16/18 06:00:02 OM -- 0 0 0 0.0 0 0 0 0 0 0
04/16/18 07:00:02 OM -- 0 0 0 0.0 0 0 0 0 0 0
04/16/18 08:00:02 OM -- 0 0 0 0.0 0 0 0 0 0 0
04/16/18 09:00:02 OM -- 0 0 0 0.0 0 0 0 0 0 0
04/16/18 10:00:02 OM -- 0 0 0 0.0 0 0 0 0 0 0

Again Many Thanks!
 
Top