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.

Intermittent ORA-12545 When Trying To Connect To RAC Database

Posted by David Yahalom under RAC, Oracle

Well, got another RAC tidbit for you today. I’ve been doing some very interesting RAC installations in the past few weeks and came across several “bugs” (or “random features” in Oracle-tongue ) I felt like sharing.

So, you have a brand new 10g RAC cluster installed and when you try to connect using OCI (probably JDBC thin as well) only to recieve intermittent ORA-12545 errors. The client will connect fine every other attempt or so.

This error happens when you enable server side load balancing but the client does not have domain address / DNS search function setup (or valid /etc/hosts file) so it cannot translate a hostname to a proper IP address.

The solution is simple, make sure that the client from which you are trying to connect is able to resolve all the hostnames in your RAC installation. Either via registering the nodes in a DNS server or by updateding the /etc/hosts file (remember, Windows also has an /etc/hosts file under <WINDOWS>\system32\drivers\etc\hosts).

I’ve seen many DBAs forget that for server-side LOB you need to be able to resolve each cluster node hostname from the client EVEN when you only use IPs in your TNSNAMES.ORA entries. Even without server-side LOB, it’s good practice to be able to resolve RAC hostnames from the client.

This isn’t actually a bug, it’s by-design and if you understand they way server-side LOB works in RAC you’ll see why the client has to be able to resolve RAC hostnames to IP addresses.

Ora-12520 When listeners on VIP in 10g RAC Setup

Posted by David Yahalom under RAC, Oracle

A few days ago I’ve stumbled across an annoying problem with Oracle 10.2.0.1 (yep, need to patch it) RAC causing my OCI client fail to connect with an ORA-12520 error (ORA-12520: TNS:listener could not find available handler for requested type of server ).

Everything was configured correctly on both the server side and the client.

1. Remote_Listener parameter is set to an alias which is defined in the Tnsnames.ora file on both the nodes.
2. The hostname is setup correctly in the tnsnames.ora file.
3. The IP address, VIP and hostname are all properly configured in /etc/hosts on all nodes.

This is a documented Oracle bug with a complete note on this in Metalink (342419.1). 10g RAC instances have a problem where the instances are not registering themselves correctly with the virtual IP address. They get registered to the real ip address where the listener is not listening.

The workaround is very simple:

Define a LOCAL_LISTENER entry for each node in the node’s local TNSNAMES.ORA file:

LISTENER_NODE_XXX =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = <node1-vip>)(PORT =1521))
)

Then issue the following command in the sqlplus prompt:

sql>Alter system set LOCAL_LISTENER= 'LISTENER_XXXX' scope=both sid='SID1'.

Remember to do the same for all other nodes in the cluster (each node should have an entry in its TNSNAMES.ORA file with the LISTENER_NODE_XXX definition + setting the LOCAL_LISTENER spfile parameter to the same TNSNAMES.ORA entry).