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!

[Hein van den Heuvel]

Alzhy ->>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).

Yes, concrete application based feedback is much welcomed, even if no two applications are alike.

Please note that the thread process comment IMHO is misleading/confusing. HT co-thread processors are full, independent, freely schedule-able entities.
They are available for any runnable thread, whether as only thread from fresh process or for a multi-threaded process.
You do NOT need multi-threaded applications to benefit from HT.
You do need lots of concurrently running threads any which way.
You also need a good bit of main memory stall s (= cache misses) to create 'micro-idle-time' to flip the threads.

TwoProc>> change the HT on and off every 60 seconds during a heavy load (40% cpu load)

Hmmm, I don't think that is a well constructed test for performance evaluation.
HT works best to create more total CPU throughput under high load... well over 50%, more like 80% (Such as tpc benchmarks :-)
Let's face it. If there is less than 50% CPU load then the OS can do best to not schedule anything on cpu where the co-cpu is already scheduled. In this case that would mean to frequently want to run many more then 24 cpus, 40+ or 60+

Turn up the volume to 11 (out of 10 :-).
For example, let's say that your suggested 250 user Mercury test creates 70% load on 24 non-HT CPU's. I expect those to use 40% or more CPU when switch to 48-HT... the price of more concurrency.
But now go to 400 users, approaching 100% CPU on 24-non. Typically response times will tank. Switch on HT and you'll find the system using 60 - 70% cpu... out of 48 with deteriorated but manageable response times.
And you may find you can add an other 50 users or so before approaching 90% cpu (out of 48) with acceptable response times.

The total throughput gain... in what would be overload situation for 24 CPU's is not unlikely to be 10% - 20%.
But it will not help didly-squat when using just 24 cpu's and may even hurt some, notably when there is lots of latch contention. (Cpu's actively spinning to wait for a memory flag to clear).

Hope this helps some
Hein van den Heuvel
HvdH Performance Consulting







Twoproc>> 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.

The latter. Each thread in an HT enviroment is as real or as unreal as the next.
They can not be distinguished operationally other then by number.

[Alzhy]

Alzhy ->>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).

Please note that the thread process comment IMHO is misleading/confusing. HT co-thread processors are full, independent, freely schedule-able entities.

Herr Hein, yours truly did not meant to "mislead" (as you allege) that highly threaded processes are a fit for HT processors. What I meant was since Oracle processes are not highly threaded - each Oracle fore/background process will have more access to "full independnet freely schedulable" processing entities with HT Threadin "ON". Now whether a "thread" processing entity has more "oomph" for Oracle (and some) processes pn REAL cores or THREAD processing entities is what I really am after as based on our experience there are certain processes that crunch logic much faster on REAL CPU cores.

But I do agree - there will be processing scenarios wherein the workload will have more processing capacity on HT Processors -- HT being a scheme of duping the OS into thinking there are more real CPUs that there are physical cores.

I have friends who work both for INTEL (HT-leaning) and AMD (historically insistent on real CORES). It is usually fun to have them both over a couple of b33rs. There were so many b33rs that flowed I could no longer remember what HT in Intel's approach or AMDs core persistence is all about.

But in the end -- it really all "depends Migz.

[TwoProc]

Hein, it's not a performance test to switch every 60 seconds (in fact the load is just there to generate some form of Oracle work, most any work would do). It's a durability test. I want to know if switching HT on and off is safe. I figure if I flip it off an on an insane amount for an extended period of time, and I've got no corruption issues, then its good to go.

Re: 250 users, not a magical number - it's all I've got for load testing licenses. It's the max I can push with the tool without spending lots more $$$, and I believe I can do a good job of load testing with that level of sim users. I can buy temporary user days, but I don't feel I need it for the goals of this particular test scenario.

As for getting the load higher than 40-50%, we do that by reducing and/or removing the timing waits in the scripts. No problem with that. The reason is that we simply cannot come up with a good Mercury test that simulates a room fool of folks doing their jobs. Even when we calculate the waits from sample users, from real user interactions, the load we then simulate WAY overloads the servers more than Mercury load runner does. The code being run is correct, the timing, even though averaged, is almost meaningless, either from empirical evidence, or from samples. When used, these loads are much higher than what actual users generate. I believe part of the blame is the Hawthorne effect, and the other part is that screen event logs and averages still give averages that are practically meaningless because the std deviation of number of items handled, the number of xxxx and xxxx and xxxx would have to be calculated, evaluated for lead/lag influences, stripped of coincident data with Durbin Watson tests, degrees of freedom for the above exploded... etc.

And, what I'd end up with is something like the average number of children per family in the US is 1.5. 1.5 is almost useless to simulate, you really need to simulate 0,1, and children, and use gaming to see if your distributions match real #'s.

We don't have that much time or $$$ resources to do that, when I can see the "real scenario" running right now from my monitors, and I can just tweek timing wait percentages until I feel I'm close enough to make value judgements - and I don't feel it's that difficult to do if looking at this process flow in its various mutations of its basic form for 12 years to judge its ability to satisfice a problem solving challenge... in other words, experience helps.

Anyways, thank you all for your valuable and esteemed input. Very gracious of all of you, thanks very much!

[Hein van den Heuvel]

TwoProc, Thanks for the follow up reply. Excellent.

>> it's not a performance test to switch every 60 seconds ... It's a durability test

We agree. Good work.

>> I believe part of the blame is the Hawthorne effect

Hmm, I don't think too many Oracle slaves will work harder because they sense they are being watched ;-)

>> Re: 250 users, not a magical number - it's all I've got for load testing licenses.

Been there, done that. Understood.
You can reduced think times to increase load, but it does not 'feel' the same as actually having more concurrent sessions unless the sessions are managed/funneled through a transaction monitor of sort anyway (tuxedo and the likes).


>> when I can see the "real scenario" running right now from my monitors,

And that's what we ended up doing. The load graphs for a given day in the week were relatively predictable/comparable. The effect of HT could be judged from there.

In the case studied where interactively used systems were configured NOT to run anywhere near max capacity, HT was deemed to hurt more then help. Predictability and crisp understanding of the performance graphs played a large role. It seems ashame to leave the potential power on the table. But oh well. I will not hesitate to run it on for 'throughput' / high-load batch application, much as you concluded.

Alzhy>> Herr Hein, yours truly did not meant to "mislead" (as you allege)

Ouch, that sounds intense.
Make that 'Mijnheer Hein' if you must :-).
'van' = Dutch/Belgian, 'von' = German.
The way I read it, which may have been the way others read it also, it seemed to say that you needed a multithreaded application to really exploit HT. The way I understand it, you just need lots of threads ready to run from however many jobs they come, as long as there are often more than the original config can offer. Many Oracle usages have plenty of foreground and background jobs to keep those CPUs occupied.

>> HT being a scheme of duping the OS into thinking there are more real CPUs

No duping. Real CPUs with their own contexts. They just do not get to run all the time. They only get a change to run when their co-thread has to wait for main memory. For some applications that is 'all the time'. For others it is infrequent. "It depends!"

Peace,
Hein.

[TwoProc]

I said that I'd update this thread regarding our findings, and so pardon the length of the posting while I follow through.

We're going to start with the system with hyperthreading turned off during the day, and on at night for big, ugly batch processing. We've done a lot of testing and verifying of databases, and switching hyperthreading off and on doesn't hurt the running database a bit.

Currently, we're going to leave the max_parallel_servers setting in the init.ora at actual cpu count, and don't plan to double it just because we now "virtually" have twice that. We don't do many parallel queries anyway, much less without specifying the degree of parallelism explicity. So, this will only have impact when we kick off something large with unspecified parallelism, like a gather stats on a whole schema for example, wherein we're happy to let the parallelism value float on some of the medium sized schemas.

FWIW - for our purposes the conversion ratio for processing on Tukwilla coming from PA-8800 chips is safe to say double for a nice fully cached query coming from memory blocks in Oracle. I've seen cases where its much faster than this, and I've seen some cases where it's not quite up to double. Overall, throughout our simulations, double (well half depending on how look at it) is a good rule of thumb for what we're doing.

Another setting that we've found very important in tuning: setting the sched_no_age policy to have a value of 178 (and allowing it by setting privileges). This has dramatically lowered latch waits for buffers, enabling processes to really start running/responding well. Also, we've found that turning off NUMA in Oracle 10g was really necessary (due to bugs AND inefficiencies as per Oracle). We also settled on setting the machine to 37.5% local memory(ILM) and leaving the rest for SLM. We may reduce the allocation to ILM after watching it run for a week or so live for production data.

We've found that setting multiblock read count to both 0 and 16 end up being almost the same for us. Setting it zero and letting it float loads up the cpu just a bit more than hard setting it to 16. Keep in mind that Oracle usually recommends 8. We plan to set it to zero at night and let it float to adjust on its own for whatever types of jobs its running, and set it to 16 during the day to stabilize cpu consumption and make it a bit lower overall, and a bit more predictable.

We noticed that file I/O wasn't quite matched up, it improved when we set the file system block size to be the same as the tablespace block size on data directories. This was one of those, "well duh" moments, obviously - but sure enough, we missed it on initial setup.

Many of you have seen me, over the years, go on about the "lotsa lun" theory vs the "big lun" theory of using storage arrays. You'll be happy to note that I've agreed to compromises on this, in which I'm not using just a few humongous luns, but, I am using luns that are many, many times the sizes that I used to. We've checked the scsi queue depth and made sure we're OK for this, and it looks great. I've not come up with a name for my compromise on lun sizing, so I don't know what to call it yet. :-)

What I didn't compromise on. R5, and others. Nope, sorry, sticking with Raid 0/1 for performance.


From a lessons learned perspective, and what I would do differently if I could; I would have included an Oracle 11g database upgrade in the scope of work. From what I've heard/read we can try to re-embrace NUMA performance optimizations for Oracle 11g, which is planned for 2011. We thought that this was a given that using it was a best practice for Oracle 10g (as advertised) but this is not so. Had we fully known about this before hand (that it wouldn't work in for Oracle 10g), we would have included an 11g upgrade in the scope of work to move to the new platform to try and get more out of the machine from the start. Of course, we don't know how much performance we're missing out by not taking advantage of that technology. By the time we realized that it was a missed opportunity, it would be too much wasted project time to back pedal acceptance testing to work in an 11G upgrade, especially given that the potential gains (if any) are unknown in size.

Naturally, depending on what you're doing, your decisions and crux points will be different, these are just the things we've come up with after running test scenarios, and reviewing our current systems behavior. Naturally, there are lots of others that I'm not discussing here, I'm choosing to bring out the ones that stood out in my mind in this posting over what I was doing before, or what I missed. I hope that it gives some folks some things to think about (in a helpful way) in their conversion efforts.

[TwoProc]

Hein, once again, thanks for the reply above my big summary posting. Your input is always valued/appreciated especially when it comes to tuning! :-)

Anyways, just to clarify my comment on Hawthorne effect. The numbers we were using for waits were ones that we come up with by observing the users while performing their jobs. The Hawthorne effect would submit that they are doing their job "harder" because they are being watched. Therefore, when we put in "realistic" timings from a bit of time motion type studies, and then used it across our "sim" users, the load was much higher than we actually see from the same number of users. Even adding in timing waits between recording events, still left us with a simulation that was much "larger" than what we expected. Much, much larger! Even though we are running the exact same code on exact same hardware on same size configured databases and disk systems!

And, I've got one more that I bet you've seen in trying to model these things. Instead of nice load builds / decreases in these tests, we see these huge swings up and down during the tests. What we've begun to learn is that our sims aren't random enough, and the sim users start to have harmonics, in which they start to hit lulls together, and start to hit high processing requests together, and the loads look more like rough seas than gentle waves of change over time. In my mind, it reminds me of a resonance test on a piece of material, wherein certain frequencies of force can move materials a great deal, even though the total forces applied are really the same. The only difference I've seen is that it seems that the resonant frequencies are easy to hit at multiple points, instead of only happening at very cleanly defined points in a materials test. Maybe more like when you look at graph of human voice harmonies, even though they are not super clean like a machine created one, they are still highly evident.

[Alzhy]

TwoProc/Herr Hein,

Any of you two gents used and "believe" in the credibility of SwingBench as a load/benchmark test suite?

These days -- that's how I usually do benchmarks and stress tests. I am not DBA but have managed to make use of it following recipes out there for DB tuning and also referring to configs found on TPC.

TPC suite is expensive and Mercury too...

[TwoProc]

I'd use it if it was all I had. But you can't beat a copy of production and production code.

[Duncan Edmonstone]

TP,

Lots of intersting results there.. seems you have Oracle data on filesystems rather than raw, out of interest did you try any tests with/without the new Concurrent IO mount option for VxFS?

Cheers,

D

[TwoProc]

You know Duncan, I'm glad you brought that up! We sure did!

Mount point options for all data areas, plus redo logs and archive logs:

delaylog,nodatainlog,cio,mincache=direct,convosync=direct,largefiles

Thank you!

[TwoProc]

Just wanted to add a point about cio, as well as setting the scheduler threshold.

When you do both of these, and you're running a test, you'll notice cpu a bit higher, but keep in mind that that cpu is going to run higher because it *can*! If your processes aren't waiting as much on I/O, and not waiting as much on latches for block buffers, yes, you can and will use more cpu, however, our number of transactions completed during the test went up as well! Meaning that our tests, when running them transaction count based, finished faster, or if we ran them for x amount of minutes, the amount of transactions completed in that same time increased. Meaning that the server is just way more responsive queries. Just don't freak out when you make those two move and you cpu load increase, it is only increasing because it *can* do more work in the same amount of time!

[Duncan Edmonstone]

TP,

Great that you are using CIO, however I think you should review Oracle Support Doc ID ID 1231869.1

Specifically:
-------------------------
Do not use "-o cio" and "-o mincache=direct,convosync=direct" together. Use either Direct I/O or Concurrent I/O.

Using Direct I/O and Concurrent I/O("-o mincache=direct,convosync=direct,cio") may cause performance regression.
-------------------------

And:
-------------------------
Placing Oracle binaries ($ORACLE_BASE directory) on a filesystem mounted with "cio" may cause data loss and other unexpected problems.
-------------------------

And Finally:
-------------------------
Concurrent IO is not expected to provide a performance benefit over direct IO when used with online and archived redo logs.
-------------------------

So:
- use none of cio,mincahce=direct,convosync=direct on Oracle binares
- use cio on datafiles
- use mincahce=direct,convosync=direct on online/archive redo logs

That said, I don't always trust Oracle support documents when it comes to HP-UX (e.g. I'm pretty sure that article 555601.1 is just plain wrong when it comes to the flags you see for direct IO on HP-UX), so you might want to test these assertions...

HTH

Duncan

[TwoProc]

Thanks Duncan,

I'm going to look into that. However, the notes I had were that we should be using cio and direct i/o together. I plan to research that note asap however, and ask for feedback from our performance specialists.

If I had to lose one or the other, I'd lose cio. We did test them separately (direct i/o and cio), and the big bang for our buck was direct i/o. However, while we did see increased cpu costs for cio, we also saw increased throughput on the testing model. We figured the increased cpu cost was for doing more processing per unit of time, because we could, after clearing the waits after writes in I/O. You know how it goes, you clear one bottleneck to move onto the next.

Fortunately/unfortunately (that is, happily)- I'm live with both this morning. But, I will definitely investigate and pass this information on with the HPUX/Oracle performance team at HP which I procured for this conversion.

And no, binaries, report output, etc. are not mounted either cio or direct i/o.

I appreciate the thoughtfulness for the posting.

[Hein van den Heuvel]

For anyone seriously interested in Hyper Threading under HP-UX there is a MUST READ (watch/listen) session in the Knowledge On Demand presentations.

All presentations:
http://h71028.www7.hp.com/enterprise/w1/en/os/hpux11i-kod-overview.html

HT session is: Hyper-Threading on HP-UX 11i v3

The flash version did not work for me.
TheZIP file worked:
http://downloads.hpmediasolutions.com.edgesuite.net/managed/19031-1-MontecitoHyperThreadingOnHpUx11iV3_WindowsMedia.zip
The slides alone (PDF) work fine also.


The part I thought was interesting were
- slide 27: "Time Accounting on Montecito"
One key word in the is "hint@pause" which is something OpenVMS also uses.
- slide 31: "OS Scheduler change"
- slide 34: 1) Just try it! 2) probably need 75% cpu load while non-HT, 3) Potential for more throughput, not for better response time.

hth,
Hein.

[TwoProc]

Hein, good reference, I watched that presentation yesterday. And, while I wish it was updated for Tukwila, which is what I own, it was excellent information. Although, the main message was that you're not going to benefit unless you've got enough utilization coupled with spare cycles waiting on I/O, ram, etc.

What we've found here is that hyperthreading is absolutely wonderful for our big, nasty largely untuned or not-tuned-enough code that runs at night, along with lot's of maintenance code which includes, lots of accounting stuff, gathering statistics, checking index depths, updating and checking data growth, generating price books, bom trees, etc.