The relation between an Oracle instance and memory in Windows

Posted by David Yahalom under Windows, Hardware, Oracle

The Oracle instance memory allocation works differently in Windows then it does in UNIX-Linux like operating systems.

In Windows environments, on starting up the Oracle instance all global memory pages are committed (like the buffer cache, redo log buffer and the library cache). However, and this is an important difference compared to POSIX operating systems, only a small number of these memory pages are actually cached in memory upon instance startup causing most of the SGA not to be part of  Oracle’s active working set.

This forces Oracle to compete on equal grounds with other processes. The O/S might swap certain pages from Oracle’s working set in memory to disk during periods of increased activity or when other processes are paging more and compete for RAM. Windows pages according to activity using an MRU-like mechanism, so when paging is going-on system-wide, Windows will try to reserve RAM pages to the most active process, the one that is paging the most.

If Windows decides to swap the database instance to disk, this will cause a severe performance degradation for the Oracle Database and might even grind the instance to a complete halt.

There are two registry parameters that exist and allow us to manipulate the working set of the Oracle process. These are:

• ORA_WORKINGSETMIN or ORA_%SID%_WORKINGSETMIN:
  Minimum working set for the ORACLE.EXE process (units = MB)
• ORA_WORKINGSETMAX or ORA_%SID%_WORKINGSETMAX:
  Maximum working set for the ORACLE.EXE process (units = MB)

You can set these parameters under:

• For single ORACLE_HOME installations: HKEY_LOCAL_MACHINE -> SOFTWARE -> ORACLE
• For multiple ORACLE_HOME installations: HKEY_LOCAL_MACHINE -> SOFTWARE -> ORACLE -> HOMEx (for multiple homes)

ORA_WORKINGSETMIN can be used to prevent the pages allocated to the Oracle process from dropping below the defined threshold (in MB) until the instance is shutdown.

The biggest benefit of setting these parameters will be in an environment where Oracle coexists with other applications. Although it can be beneficial in other scenraios as well, such as when production and test instances are running on the same physical machine.

Another very useful parameter that exist in the Win32/64 platform is: PRE_PAGE_SGA. This parameter causes Oracle will force Oracle to allocate all SGA pages upon instance startup thus bringing all of them to memory as the working set of the Oracle executable.

This will allow the instance to reach maximum performance more quickly rather than through an incremental build up as pages are loaded on a need-to basis.

Combining ORA_WORKINGSETMIN  with PRE_PAGE_SGA will force the Oracle instance to start above the minimum threshold and not drop below. Using ORA_WORKINGSETMIN in isolation will cause the Oracle working set rises above the threshold it will not drop below.

Migrating from SPARC to x86

Posted by David Yahalom under RAC, Hardware, Solaris, Unix, Oracle

Remember the SPARC Vs. x86 post I made some time ago? I’ve talked about sizing old SPARC hardware compared to new x86 based servers.

Well, I have just finished a rather big project migrating several single-instance Oracle 8i databases running on old(ish) SPARC machines to a state of the art Oracle 10g RAC on commodity x86 Linux servers.

I have migrated my client from this:

Two: 4 X UltraSparc III+ 1GHZ CPUs, 8GB RAM.
Two: 3X UltraSparc II 300Mhz CPUs, 4GB RAM.

To this:

Two HP DL580G5: 2X Intel QuadCore Xeon X7350@2.93GHz, 16GB RAM (list price: $15,000).
One HP DL380G5: 2X Intel QuadCore Xeon E5345@2.33GHz, 8GB RAM (list price: $8,000).

The workload remained the same. The old config had several different unique schemas on each physical server. Each applications running connected to a different schema on a different physical server. No shared data between servers. This was done to spread the workload across several machines. Five years ago they (client) considered it a poor man’s cluster.

When installing and configuration the RAC cluster, I have combines all of the different schemas under a single database.

I’ll try and published more detailed performance benchmarks later on, but now all I can say is this: in terms of raw CPU power (taking storage I/O and memory restrictions out of the function) the RAC cluster smokes, obliterates and completely destroys all of the older SPARC servers combined.

The RAC cluster consist a total of 6 CPUs, each with 4 cores (a total of 24 cores).  The average load on each node is around the 2.0-2.2 mark. Meaning a combined load of ~6/24 or roughly 25%.  Given the same workload,  the old SPARC machines reached well above 100%. For example, one of the “more powerful” Ultrasparc III+ 1Ghz machines (total of 4 CPUs) reached load ranges of above 20!

The workload is a mixed multi-million user, multi-session web application with short, cached queries and transactions plus several single-threaded very cpu-intensive financial applications that perform thousands of logical DML operations per second. One of these DML intensive processes caused a 4XUltraSprac III+ 1Ghz machine to reach average loads of 15-20. The same process running on the same data barely tickles the QuadCore Xeon.

Now, please keep in mind two things:
1) This isn’t scientific, far from it. At least not yet. I have also performed more concrete benchmarks, but until I’ll have time to arrange all the data into user-readable format you’ll just have to take my word on it.

2) You might say that comparing 4 year old SPARC hardware to brand new x86 machines might not be fair. And I’ll agree. It isn’t. But what’s important is to take the general sizing figures from this post and try to adapt them to occasions where you have a site “suck” with old SPARC hardware and you want sizing information to decide how exactly SPARC Mhz translates to x86 Mhz.

The bottom line is this: don’t be afraid to migrate old SPARC machines to modern Linux x86 hardware. It will usually be allot faster.
And…. you can achieve great performance when using cheap, x86 servers in a RAC cluster and very high ROI.

Start your servers: Intel’s next-gen CPU smokes!

Posted by David Yahalom under Hardware, Oracle

UPDATE: Whoops! Had bad URL in link to Nehalem Benchmarks. Fixed!

AnandTech, one of the best sources of hardware news & reviews,  have just published preliminary benchmarks of Intel’s next-gen CPU - the Nehalem.

Even when the CPU is not using final silicon and being coupled with a very early and unstable motherboard, it smoked the benchmarks suppressing a Penryn-based Core2 quad by as much as %50! In fact, a 2.6Ghz Nehalem is faster than a 3.2Ghz Penryn! Amazing.

This got me thinking about the x86 server market, especially when compared to non-x86 servers such as SPARC or PowerPC machines.
Even now we see that dollar-to-dollar, x86 servers can sometime outperform non-x86 hardware.

Nowadays when Oracle RAC clusters based on cheap $7000 x86 servers outperform single-instance super expensive high-end Sun/IBM servers, Intel’s new Nehalem could very well be the final proof many DBAs need in order to ditch their old SPARC/PowerPC servers and transition to Linux x86 RAC installations.

Anyway, AMD is going to launch a new chip design in early 2009 and I still see great promise in IBMs Cell CPU deign.

Interesting times ahead.

Read more about Nehalem’s architecture and on-die memory controller here.

UltraSparc Vs. x86 servers. Which one runs Oracle faster?

Posted by David Yahalom under Hardware, Solaris, Linux, Oracle

I was doing research for a client that is trying to replace old Sun UltraSparc Solaris servers with new, cheaper (and hopefully better performing), Linux x86 machines. I want to see if I can find any benchmarks that will help me compare the performance of SPARC servers to x86 machines when running Oracle Database.

First I started by looking in TPC’s OLTP benchmark results, which are considered somewhat of an industry standard. I found several interesting things.

Let’s look at two low end HP machines, the ProLiant ML350G5 and the ML370G5. These are both low-end Dual-CPU socket x86 servers from HP starting at a price lower then $2,500 for both servers.

low-end HP ProLiant ML350G5 (1 X Intel X5355 Quad-Core) - 100,926 TPC-C/Sec
http://www.tpc.org/tpcc/results/tpcc_result_detail.asp?id=107061101

low-end ProLiant ML370 G5 (2 X Intel X5460 Quad-Core) - 273,666 TPC-C/Sec
http://www.tpc.org/tpcc/results/tpcc_result_detail.asp?id=107111201

While I was unable to fund recent benchmarks of Sparc machines in TPC, I was able to find benchmark that while conducted several years ago might still be useful.

OLDish (2003) Fujitsu PRIMEPOWER 2500 (64 X SPARC64 - 1.3 GHz ) - 595,702 TPC/Sec
http://www.tpc.org/tpcc/results/tpcc_result_detail.asp?id=103103101

This is a high-end server with 64 SPARC processors. In its time it was quite the beast and can still be found in many data centers worldwide crunching very important numbers.

As you can see it scored twice the number of points compared to the ProLiant ML370G5. Impressive. Sure. Just keep in mind that we are talking about a really expensive high-end 64CPU machine against a very low-end 2 X QuadCore (8 cores total) Intel-based commodity server. So eight times the CPUs for roughly twice the performance? 83.2 total SPARC Ghz vs. 25.28 total x86 Ghz ? Still confused? According to TPC the Fujitsu machine is almost X20 more expensive compared to the x86 HP server. Twice the performance for 20 times the price?

So yes, the Fujitsu PRIMEPOWER is an older server with older CPU models but it is also a much more expensive server and we can still use this data when considering replacing old SUN servers with newer X86 alternatives. These results can help us better understand what type of x86 hardware we need in order to replace our old SPARC servers. This isn’t %100 scientific comparison but it can still give us a ballpark estimate.

We’ll need to do some more digging to find more current results using newer SPARC processors (such as the Sun T1). What I was able to find were some MySQL benchmarks done by tweakers.net for their own web application. While these are very tailored benchmarks done for a specific web site application it does help contribute to the general picture here.

http://tweakers.net/reviews/646/14/server-duel-xeon-woodcrest-vs-punt-opteron-socket-f-pagina-14.html

http://tweakers.net/reviews/649/8/database-test-sun-ultrasparc-t1-vs-punt-amd-opteron-pagina-8.html

You can see that the Xeons fares much better compared to the UltraSparcs.

Another very interesting benchmark I found was a blog entry quoting official SAP benchmarks between x86 and SPARC hardware. The SAP benchmark shows the x86 servers to be faster compared to the Sun T2000 SPARC server with some limitations.

2 X Quad-Core Intel Xeon X5355 2.66 GHz - 9330 SAPS
www.sap.com/solutions/benchmark/pdf/cert4207.pdf

2 X Dual-Core Intel Xeon 5160 3 GHz - 5120 SAPS
www.sap.com/solutions/benchmark/pdf/cert3107.pdf

Sun Fire Model T2000 - 1 processor / 8 cores / 32 threads - 4780 SAPS
www.sap.com/solutions/benchmark/pdf/cert4705.pdf

You should go ahead and read the reservations made by the author of the above post. They are all valid. Just keep in mind that the Sparc system is a $33,000 server compared to a much cheaper HP.

Of course I’m only talking about raw performance here. The x86/Linux hardware has several other advantages such as being more open, more future-proof and arguably easier and cheaper to service and maintain.

This is all very interesting stuff. I’ll continue to do more digging and update this post as necessary.