Oracle 10g and turning off/on hyperthreading while db is up

[TwoProc]

Anyone have experience with running Oracle 10g and flipping hyperthreading off/on while the database is up? From the tuning folks at HP, they said that they *thought* it should be OK, but didn't know for sure.

I could easily believe that it's possible that Oracle knows which procs are real and which ones are HT, and I could just as well believe that it has no idea. And, I think that the truth is that it's probably the latter.

I'm going to do a test tonight, but I'd rather not risk trashing my test database if it's already known by one/some of you out there that it's not a good idea.

As for why, we've seen in testing that our big ugly night jobs run better with HT on, and our daytime work (OLTP) is better with it off. So, since it's dynamic, we're talking about possibilities, including turning on HT at night but while the system is up (not shutting down to do it). Of course, it affects other things in Oracle as well, but those things are all dynamic as well within Oracle so that's less of an issue.

Our concern is whether or not the Oracle instance would be OK with losing 1/2 the amount of processors while running given that those procs are HT procs (if it knows the diff).

Thanks!

[Alzhy · Accepted solution]

TowProc sir,

The fact that HP-UX and Oracle is agnostic to dynamic CPU changes (whetehr the're real cores or threads) I do not think does not matter.

The OS does not distinguish between a Thread Resoruce or a realy CPU core -- both appears as normal "CPUs" to the OS.

We have vPars and we routinely increase/decrease CPUs on the fly all the time. Now there could be certain Oracle params that are cognizant of the CPU changes but I think it is all transparent and self adjusts.

[TwoProc]

Thanks for the reply Alzy - however, isn't there are restricted percentage of change that the Oracle db seems to be OK with? Like 30% or so, for decreasing?

[Steven E. Protter]

Shalom,

I'm not sure you can flip hyper-threading on and off while the db is running.

I would not recommend doing this outside a maintenance window. I've seen the switch for this in the Ignite system replication interface at my last contract. We left it set to off.

I was eager to try out this feature too, but could only do so in a maintenance window.

If you have found a way to do this on the fly, please share. I would not risk doing this on a hot system. There is risk involved here.

SEP

[Alzhy]

I have boosted CPU count (which a CPU thread is really on HT systems) from 1 to 32 "cpus" and Oracle has no complaints at all. On an Oracle Data Warehouse (10GR2 based) - the parallelism simply adjusts to the number of processing units the OS now provides it.

Is this a Tukwilla system?

FWIW, I believe whatever threading or CPU changes the OS support should boil down to the DB. We've been running Oracle DBs with widely varying online dynamic CPU allocations since day 1 -- with no ill effects.

But do test.

[TwoProc]

Well, it was a lot of fun.

I've just went in and flipped it on one of our large test systems with 24 processors real, hyperthreading = 48.

It seems to work just fine. I flipped it back and forth about a dozen times, the only thing we saw a little note about the change in the Oracle alert log, but no complaints.

I checked the number of processors Oracle was holding up, and it didn't change before and after.

kctune lcpu_attr=1 turns on hyperthreading
kctune lcpu_attr=0 turns it off.

If you look at top with hyperthreading on you'll see 24 procs, and if you turn on HT,the screen doubles in length show 48 procs. Interesting to see that when 24 procs are in use (sans HT) you see cpus numbered to 48, but only even numbered cpus.

When you turn on HT, you see all cpus.

So, from this alone, the OS clearly is informed about which cpus got turned on, and what type they are.

Anyways, no ill effects thus far from flipping it.

I'm about to launch a load scenario with Mercury tools with 250 users and flip it while running...

I'll put feedback here.

[TwoProc]

Alzy, as for what it is:

It's a new Tukwila system, running HPUX 11.31.

[Alzhy]

TwoProc,

Let us know how HT works out and if it is a GOOD thing for Oracle Databases (considering most oracle processes are not highly threaded processes).

On Linux (Nehalems), it seems HT does not matter a lot for Oracle workloads but for custom biilt C/C++ Apps that are not -lpthread compiled and expects REAL heavy weight CPUs instead of thread cpus -- HT can actually be bad.

For plane jane standard mid-tier apps -- ie. WebLogic, SAP, Java, JBOSS, etc.. HT is a performance boost. Ditto with HyperVisor loads like vMware and KVM (possibly Hyper-V too).

[Steven E. Protter]

Shalom,

Based on this part of your post:

kctune lcpu_attr=1 turns on hyperthreading
kctune lcpu_attr=0 turns it off.


Which I did not know about, I'd say you could flip this using kctune.

See this:
orashp03:/root#kctune | grep lcpu
lcpu_attr 0 0 Imm (auto disabled)

This parameter can be changed without recompiling the kernel.

I'd say you can safely flip it on a running system. That may not fully enable the feature.

I would not do it on a production system without a maintenance window and user notification.

It brings fear to my heart.

I'd do it on a test system and see if anything breaks first.

SEP

[TwoProc]

Alzhy - it certainly looks like that for a box with users on it (OLTP) - the best config is HT turned off. For a server without users actually using screens on it, the best config is HT turned on. Which is what we plan to go with.

Steven, yeah those kctunes are the commands we're using, and I've flipped the system a couple of dozen times and it seems not to bother it all.

For a fun stress test, I'm about to kick off the test, and with a little sleep while loop, change the HT on and off every 60 seconds during a heavy load (40% cpu load) First just to see if Oracle can handle that craziness, and secondly to see if I can see any performance problems that come up during a switch to either more or less HT processors.

We do see the process vxfsd pop up to the top of the list for a couple of seconds every time we make the switch, which I find odd.

Let you know more as I know more ...

Thanks for the expert input and the soundboard for discussion on this!