Oracle on NT vs. Solaris, first results

From: <nigel_tufnel_at_my-dejanews.com>
Date: Tue, 05 Jan 1999 17:06:13 GMT
Message-ID: <76tgq4$d7c$1@nnrp1.dejanews.com>

Gentlefolk:

As promised, here are the very first impressions of my testing on a Compaq Proliant 5500 Quad PPro 200 MHz box running first NT 4.0/Oracle 7 and then Solaris 2.6 with Oracle 8. Currently the box has 512 Megs of RAM, but this will go up as we continue to test.

I'll try to post future results to this thread.

Results:

Importing Oracle 7 on Solaris was about 3 times faster than importing the same DB on Oracle 7/Nt. This is perhaps the only apple-apple comparison I have.

Oracle 8/Solaris connection time is much much faster, about 10 x better than under Oracle 7/NT.

Most other transactions have a very wide performance curve, sometimes much faster, sometimes much slower.

It seems Solaris / Oracle 8 take advantage of RAM better than under NT/Oracle 7. I will post the actual differences in business transactions when we have more RAM. At lower usage levels, before we start serious usage of virtual memory performance seems much better than under NT/ Oracle 7.

Background:

I'm doing performance testing for a software company that sells client/server software. I won't get into any company specific results except to say that their scalability test results are much better since I've been here than before, so if you figure out who I work for, buy with confidence.<G>

We had been using Oracle 7.3.4 on NT 4.0 SP 3. The client applications use from 3 - 6 connections per user, which I find rather typical with thick clients. We ran into a hard limit of around 1200 connections using this environment, so we decided we had to look for alternatives as we await arrival of our huge UNIX based box. Oracle claimed the problem was running out of memmory. My response to this claim is baloney. Under NT each process can address 4 Gigs. We never used more than 600 Megs, so I suspect the real problem was running out of threads.

The Multi Threaded Server was suggested to us by Oracle because they claimed that the NT environment ran out of addressable memory. Additionally after talking to Oracle's Technical support, they feel the MTS is better suited to environments where the clients connect and disconnect per SQL query, such as when you have web browser based clients, so MTS was out. Additionally MTS was not available for NT and Oracle 7. Not sure about availability on other OS.

Our big problem is that we need to prove the C/S architecture works with 500 users against it, by Jan 5, and it's already Dec. 18.

It was my suggestion to use Solaris for Intel for the following reasons:

Solaris is process, not thread based. We wouldn't hit a 4 gig limit on any single process.
Configuring maximum thread counts on Solaris is fairly easy.
Oracle 7 was available for Solaris, so no DB conversion would be necessary.

So, we buy into it. We get Oracle 7.3.2.? and Solaris 2.6.

Early the week of Dec. 21 arrives Solaris, and shortly afterwards Oracle 7.3.2.? for Solaris/Intel. I have 6 working days in which to install a new OS, new database, and configure the OS and DB for 500 users, in the blinding snow, during a fire while naked and singing the Pirates of Penzance [sp?]. Ok, not that bad, but 6 days is cutting it awfully close.

Solaris installs well, with the regret now that I left the swap, /usr and root partitions too small.

The database comes up, imports our data, great I think, no problems. Then I try to connect and realise I have no TCP/ip listener, which has been the basis of all our testing. Hours later I finally conclude that the TNS listener never got linked. That's right, linked. Apparently Oracle for UNIX on Intel links at install time, no prebuilt executables..... ARRRGH!

So I call tech support. "Oh, sorry, 7.3.2.x was not certified for Solaris 2.6, we need to send you 7.3.4". Ok, I ask them to overnight it so maybe by Christmas eve I can get it, and install it. It arrives a day late, but in the mean time I start setting up for Oracle 8. I have nothing better to do, so I might as well start learning it, and who knows, maybe I'll have to use 8 anyway.

I get 7.3.4. on New Years day. It called for files off the CD rom which didn't even exist. I actually try copying the entire CD off to my hard disk so I can hack the installation script and rename files which end in .aSOL to .a. No dice, even after all that the make files are completely wrong, trying to use make options that don't exist. I fix that, still the linking of the listener fails. Let me make this clear, it's obvious to me that this script NEVER was properly tested, and should never have left the Oracle building. The installation script could only have been worse if it had a virus built in.

I'm desperate at this point. We need the results by Jan 5 or all our efforts in this OS/Oracle upgrade will have been for naught and I have given up on calling technical support for this issue, the only good fix would be a linked executable which they don't want to send me.

Knowing that I won't be able to keep my test results comparable, I try 8. I use the Motif installer. Everything bombs. <sigh> I'm resigned to doom. I walk back over to my PC where I only have telnet installed. I forgot the error messages, so while I'm on hold I run the character based installation. Same user, same options, and as I'm trying to tell the Customer service person my woes, the damn thing installs. Every bit of it. Installer, database, everything. Damn damn. He tells me that yes, in fact often the character based installs work better.

I go ahead and finish the installation of 8, and attempt to migrate the database. By 7PM on New Years Eve I have an installed database running on Oracle 8 and a TCP listener that works. Yipee, I think, I can go home. I'll deal with the system settings and database configuration (shared pool size, processes, dml locks, etc. ) later.

January 4 I come back in, and have the local DBA's ( I know almost nothing about this db's structure nor do I have any of the installation scripts) go in and create the views I need. Then it hits us. The migration process dropped the SYSADM user. Apparently he's not used by Oracle 8. What I should have done was create the SYSADM user first, then import the DB, then create my view. Arrrgh. No, it's not documented but thank you for asking.

I hack my test scripts to change ownership of the SYSADM views that are referenced. This way my SQL load tests stay intact, even if the actual GUI clients won't be able to connect.

I look at the installation guide, and the UNIX kernel recommendations are a little vague, I call Oracle. After a few minutes on the phone with them, I come to find out the book is wrong.

Set SEMMSL first. SEMMSL should be 2 x the number of processes used by the database.

Then set SEMMNS, which should be SEMMSL x SEMMNI, yes, a huge number, don't care, I do it, reboot.

As I ramp up I run out of data space, and DML_locks. DML was set to 200, I change it to the large value, 500 I think.

Restart my tests while monitoring swap space usage. I have a 600 Meg swap partition. I quickly run out around 300-400 users. Add a new 1 Gig swap file. Anyone know a reason I should use multiple smaller swap files?

Re-ren, and at the tests peak I use up around 1,100 megs of swap space, in addition to the 512 Megs of RAM. This actually makes me feel better, this way I know I have an Application and OS that can take advantage of more space. Unlike my Oracle 7/NT combination.

I hope this long winded story is of some use to you all, if perhaps only to let those of you who have gone through this know you are not alone.

See you soon, when I get more ram and can talk about actuall transaction improvements (hopefully).

Cheers,

Nigel

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Tue Jan 05 1999 - 11:06:13 CST