DB Consolidation and Shared Memory (SHMMAX)

[Alzhy]

We're planning a DB Consolidation where several large DBs will be hosted by one big but flexible HP-UX environment. Each DB instance will still however have its own Fibre CHannel Pair to the SAN. My question is with SHM requirements:

If say I'm consolidating 3 DB's with 10GB of SGA each. Am I to expect that my system should be prepared to provide 3 SHM mem areas/spaces of 10GB each for a total of 30GB allocation for SHMMAX alone? Or will 10GB be shared by all 3 DB instances?

Thanks.

[Don Morris_1]

shmmax is not the total of all SysV segments anyway -- it limits the size of any given segment. (shmmax * shmmni would be the limit on possible SysV shared memory space implicitly).
[man 5 shmmax (11.22 and later) or see docs.hp.com for the 11.11 man page]

So unless your DB environment will consolidate into a single 30Gb segment, leaving shmmax alone [presuming that it handles the 10Gb segment now] would be fine. As to exactly what it is going to do... I would expect that would depend on the DB program itself, not the OS.

[Alzhy]

Hmmm...
I've an existing server with 5 DB servers of SGA sizes 5 4 3 2 1 GB respectively. "ipcs -a " show 5 separate shared memory areas each sized equal to the SGAs.

So if I've 3 DB instanes each with 10GB of SGA... would it be reasonable to expect 3 x 10 GB SHM areas in use?

[Don Morris_1]

I would certainly expect that from what you've described, yes. [Assuming, of course, sufficient system resources (dev/fs or memory swap) exist to create 30Gb+ of virtual objects].

[Ben Dehner]

Each DB will have its own shared memory segment, so SHMMAX has to be large enough for the largest SGA on the system. The SGA is not shared by different database instances running on the system; each DB will have its own shared memory segment. If you are running 3 DB's with 10 GB of SGA each, then, yes, you will have a total allocation of 30 GB.

[Steven E. Protter]

Shalom,

10G and above the Oracle standard for SHMMAX is 100% of physical RAM. G-d forbid it try and use that much, but that is the required installation setting.

There is really little point in trying to run with a lower figure. The key issue here is SGA tuning not SHMMAX tuning. With regards to the issue of shared memory use alone.

SEP

[Hein van den Heuvel]

Nelson,

You main question has been mostly answered, but your raise so many more between the lines!

As indicated, you have to add the memory requirement for each SGA, and the SHMMAX should be as big as the biggest SGA to be expected.

>> Or will 10GB be shared by all 3 DB instances?

DB instances are VERY independent with very few touch points:
- The listener (if you choose it to be that way)
- The Oracle images (if you choose to run the exact same versions).

The images will be shared in memory, which will provide a minor relief: 100mb or so of shared text versus 100 mb on each box.

When run in the same box, the instances do share resources (memory, cpu, semaphors,...) and may influence each other adversly.

It's clear you realize this: "Each DB instance will still however have its own Fibre CHannel Pair to the SAN.".

That's obviously well intended, but I suspect it will become more of a missed optimization opportunity than a conflict avoidance tool over time.

Have you decided on a listener config already?
Unique listeners(ports) per instance?

Are you sure you want a single big box?
If it goes down, all will be down!
Does each DB require the other DB anyway, or can they each still perform a useful business function if either or both other instances are down?

Has a RAC style solution been considered?
Maybe even just a mostly-active on this node, hot-standby on the other node setup?
For a RAC solution, if you ever wanted to upgrade hpux on one of the boxes, or change the hardware, then you'd just pick a slowish time, bring up a reduced SGA version of all remaining instances on the surviving node, fail over the users, bring back the other node, start up instances in their full glory, fail back.

What mechanisme will you put in place to manage relative resource consumption between the instances? If one instance 'goes bezerk' spinning CPU, are the other twe allowed to suffer some? suffer a lot?

Yesterday I had the pleasure of attending a day session by (Ask) Tom Kyte.
There was a fun, mostly tongue in cheek, worst-practices presentation.
I'm pretty sure he referred to consolidation from both positive and negative perspective. I can't find a good slide on the positive effects just now, but the negative aspects are jokingly highlighted in slide 28 and 29 in --> OOW_practices.ppt in --> the October 2007 file noug.zip in -->
http://asktom.oracle.com/pls/asktom/f?p=100:8:122775462229225::NO


Hope this helps some,
Hein van den Heuvel (at gmail dot com)
HvdH Performance Consulting