Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

What rough estimate for DBMS transactional load?

SOLVED
Go to solution
Ralph Grothe
Honored Contributor

What rough estimate for DBMS transactional load?

Hi,

although I have no understanding of database processing power and performance (especially of the Oracle breed) I was given the task to evaluate the stuff.

The background is that the cutomer believes his application can equally well run on a Linux cluster.

Although we do already run our DNS and print servers on HA Linux clusters (self devised from various ha-linux components) I really feel very uneasy when thinking about an as reliable HA Linux solution for this SG clustered database monster.

I printed the manuals of OSCAR, HA-OSCAR and NPACI Rocks to have a little read the next days to see if these solutions could be adequate.

Yet, I have no idea how to size the Intel hardware for that.

I also had a look at http://www.tpc.org/ but found the benchmark rankings there rather confusing and meaningless.
Probably because I lack the DBA background.

How could I sort of run my own TPC benchmark to get a rough estimate of the current transactional "throughput"?

I do have GlancePlus and utility and extract as well as PerfView at my disposal.
So I can get a good picture from the system metrics.
But I'm lost with the DB specifics.

I have a MRTG running on another box that is (among other data) running an ssh command that simply counts the Oracle procs that appear as "LOCAL=NO".
I was told this comes pretty close to actual concurrent connections.
So I have these graphed with other metrics.
But this wouldn't give me a number like transactions per minute.

Are there any SQL queries that I could run from my MRTG to count these, or similar?

Has anyone experience with greater Oracle DBMS running on Linux?
How does it perform on Linux?
What about Oracle RAC on Linux?

Are there realistic alternatives to Oracle
(e.g. PostgreSQL)?

Any hints and opinions are highly welcome

Regards
Ralph

Madness, thy name is system administration
11 REPLIES
Jean-Luc Oudart
Honored Contributor

Re: What rough estimate for DBMS transactional load?

Lots of questions !

The processes marked as "LOCAL=NO" refer to "oracle processes" spawned by the oracle listener (this would probably mean that the client is/are remote)

At Oracle level the DBA should run a statspack report. This is probably a start. Saying that the transaction count is to be understood "Oracle transaction"

Regards
Jean-Luc
fiat lux
Steven E. Protter
Exalted Contributor

Re: What rough estimate for DBMS transactional load?

I can address a minor section of your query.

Alternatives to Oracle.

mysql

It does lack certain features on the high end, but is a pretty reliable database that works on the same principles as oracle. It costs a lot less.

I have run Oracle DBMS on Linux and PA-RISC HP-UX. The performance and throughput is much better on a properly tuned HP-UX system.

Linux is still a basically 32 bit platform running on Intel. You can't make an apple to apple comparision to Oracle running on a 64 bit platform. I know you can get 64 bit Linux and even run it on Itanium. Even there, I bet HP-UX outperforms Linux.

Many customers like the Linux option because the license is low or no cost. Oracle has been pushing it Linux solutions and works hand in hand with leading vendors to deal with peformance/tuning/reliability issues.

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Ralph Grothe
Honored Contributor

Re: What rough estimate for DBMS transactional load?

Hello Jean-Luc,

I asked our Oracle DBA, who btw. isn't in charge of the mentioned instances, if he knew the Oracle statspack.
Sure she did but when I heard from her that it required some action on behalf of the DBA responsible for the DB (e.g. setting up a test account and creating quite a few tables to hold the stats) I instantly hesitated.
You see I don't think that the responsible DBAs would give in on such a request, and besides I want to run my stats as non-intrusive as possible.
Thus any stats that require the creation, modification of tables (even if they only were in data dictionary) are off bounds for me.
But I was lucky to find a nice paper from the SUN Blueprints (I hope the forum's moderators won't blacklist this thread for my referring to a competitor's material ;-) which describes the Statspack and contains many more hints.

http://www.sun.com/blueprints/0702/816-7468-10.pdf

Though not yet having read the Blueprint I'm convinced that I could carve up something similar with own scripts that dump the stats in an external DB like Berkeley DB or MySQL rather than claiming space from the Oracle instance.
Madness, thy name is system administration
Ralph Grothe
Honored Contributor

Re: What rough estimate for DBMS transactional load?

Steven,

that's pretty interesting to hear from a Unix/DB veteran to put MySQL as a replacement for Oracle into consideration.
Whenever I mentioned MySQL and the likes in presence of any Oracle DBA I could instantly see them begining to roll their eyes and shudder in disgust.
Sure we Unix admins always have been using MySQL instances for our monitoring and documentation tasks as well as for webserver backends to the greatest content and always loved it for the ease of administration.

Having attended the last Berlin Linux Days last fall there were amazing presentations from the PostgreSQL folks who reported to successfully have replaced Oracle for a Greek telephone company with databases with billions of records.
It all boiled down to just having a single SQL statement slightly modified to transfer the data.

I am also convinced that you cannot compare the computational power of PARISC with commodity PC Intel HW.
I know our customers enjoy doing this,
always telling us that their home PC has a much higher clock rate than our servers.
Of course they neglect the scaling truth and SMP.
But anyway that doesn't seem to be the point.
Taking that the PC HW is so much cheaper the objective seems to be to just throw in yet another few more boxes in a cluster if performance isn't sufficient.
I mean that's more or less the success behind most HPC Linux clusters (e.g. Beowulf) where Unis and R&D institutes that do number crunching (e.g. CFD, FEM) outperformed the venerable irons.
Madness, thy name is system administration
Jean-Luc Oudart
Honored Contributor

Re: What rough estimate for DBMS transactional load?

Ralph

I don't think statspack is that intrusive.
We use a dedicated tablespace for it and it's less than 100Mb.

The blueprint is a bit outdated.

on statspack you can check the following link
(p70 onward)http://www.osborne.com/products/0072230657/0072230657_ch02bb.pdf

or Metalink Note:228913.1
"Systemwide Tuning using STATSPACK REPORT"

Regards
Jean-Luc

PS : we set up statspack environment in all our production databases so we can build baseline reports and check the database performance when required.
fiat lux
Brian Crabtree
Honored Contributor

Re: What rough estimate for DBMS transactional load?

Ralph,

Statspack is marginally intrusive, but only when you gather statistics (generally takes about 10-15 seconds). The table creation happens with the $OH/rdbms/admin/spcreate.sql script, and does everything needed for you (including user creation).

The easiest way to get current sessions is from the v$license view. This will show you current and high water mark.

Personally, you will probably get better performance out of a HP-UX system over a Linux system. Clustering does allow for additional systems to be used, but you still only get 1.8x a single system, and that is assumed that everything is perfectly tuned. If it is poorly tuned, it can be worse.

As for mysql, it depends on what you are doing with it. Oracle has a number of features that are worth having, if you use them. Replication, clustering, tablespace options, partitioning, different index options, etc. If you are talking about a simple database, then mysql would be fine for you. If you are talking about a complex database with a need for these things, Oracle is your best option.

As an Oracle DBA, we are required to roll our eyes if you mention any other databases. Unless it is MS Access. Then we are required to hit you. :)

Brian
Ralph Grothe
Honored Contributor

Re: What rough estimate for DBMS transactional load?

Jean-Luc,

thanks for the link to the Oracle PDF.
For me, lacking the database background,
this is almost too much.
But I'm sure that I will find valuable hints in the doc.
For the time being however, I think that I will stick to the SUN Blueprint even if it is a bit dated by now.
Albeit they still refer to the Oralce9i,
the beast I'm confronted with, and the approach is more hands on for someone with only Unix background.
The PL/SQL is readable for me and only requires few lookups for Oracle esoterics like decode() and other intrinsic functions.
Madness, thy name is system administration
Ralph Grothe
Honored Contributor

Re: What rough estimate for DBMS transactional load?

Brian,

thanks for pointing me to the v$license view.
I think I will be able to see what it records with a "describe", although unfortunately it lacks explanatory comments.
But I can look up in web reference or ask our DBAs, provided they are willing to answer.

I knew that Linux cluster nodes would hardly be able to compete with the power of an HP enterprise server.

I've heard MS/SG by now is also available for Linux, probably on Itanium (or even on 32Bit ix86)?
Has anyone been using this to tell about?


Madness, thy name is system administration
TwoProc
Honored Contributor

Re: What rough estimate for DBMS transactional load?

Ralph, I dropped in on this thread to see what others have found to date. I see that my thoughts to date agree with most of what I've seen posted. How big is the current server? If it's just a few processors (like 8 or 12) and the SGA isn't that large (like 3 or 4 Gig total) then I could see that you could possibly make this go. However, if you're envisioning a room full of cheap little boxes all working happily together, I think that as you start add ing more and more boxes you'll be doing interprocess control I/O and cpu work than you will be doing actual database work. What I mean is that a 4way box + another 4 way box probably gives you (I'm just guessing here) about 6 procs of workable cpu horsepower, and three boxes of them gives you a little more than two boxes of them, and five will give you 3 boxes worth.. etc. In otherwords, the law of diminishing returns starts to kick in.
But, more importantly, I've got Linux servers here, and they simply don't stay up like what you're used to on your HP boxes. Not even close. Better than what I've seen the Windows OS do on an Intel box. But, you just can't compare the reliability and uptime of that $20K OS to the $300 one. Just this morning I had to reboot a seemingly "up" Oracle Application Server... that just didn't want to "work" for almost any services that it had running. I see this once a month on those few boxes. On the same amount of HP boxes, I've not seen that situation in a year or more. Luckily, I've got buy-in from all the parties who "get" to experience this from time to time that this would probably happen. The exchange was for cost. Now, bouncing a Linux box holding an Oracle Application server is a 4 minute deal and cut-up correctly involves a small amout of people, but I'm pretty sure I wouldn't want to start experiencing the fun of bouncing my database servers with hung databases in the middle of the day for my mission critical applications... just because I have a different idea of how I'd like to spend my Maalox ridden days...
We are the people our parents warned us about --Jimmy Buffett
Alexander M. Ermes
Honored Contributor

Re: What rough estimate for DBMS transactional load?

Hi Ralph.
Linux cluster is good for small applications.
But if you need power, go either to Xeon or Itanium cpu's. Check the technical data of the small RX servers and get a compare to Risc driven machines. Perhaps that can help you. We have Risc systems and will replace these with Itanium machines. We have Oracle databases on any kind of OS. I like the HP-UX systems, because they don't give us too much trouble.
Just my 0.03 â ¬
Alexander M. Er
.. and all these memories are going to vanish like tears in the rain! final words from Rutger Hauer in "Blade Runner"
Mic V.
Esteemed Contributor
Solution

Re: What rough estimate for DBMS transactional load?

I have a few bits to add, none of which is intended as derogatory of Linux. I like it just fine.

One thing -- you don't mention (I don't think! :-) which apps you're running, or if you're just doing straight Oracle, or what version is planned for use. These might help respondants to suggest things.

I personally would tend to agree, that PA-RISC would be better than regular Intel. But one must have facts, as you know.

One reason I've always given for avoiding Linux in this sort of enterprise role is lack of support. It's been a few years since I looked into this; maybe things have changed. Having enterprise mission-critical apps, they need to stay up and get fixed ASAP. I wasn't aware of any commercial Linux support at the level of HP's reactive services, let alone the proactive mission critical stuff. I have too much on my plate -- although I did this stuff when I was younger, I now don't have the bandwidth to sit around and debug open source software when it dies. I need reliable people on a payroll to do that for me when needed.

(If anyone's aware of good (comparable to second-tier and beyond inside HP) support for Linux, commercially, I'd love to know.)

Another thing that might make me itchy is licensing. I don't know how Oracle deals with Intel, but last I looked into this, Oracle was charging per-CPU for HP-UX -- clustered or not, ICOD or not (meaning you pay for unactivated CPUs on the application nodes...). This could get very expensive.

It does look like you can get ServiceGuard for Linux:
http://www.hp.com/hpinfo/newsroom/press_kits/2003/linuxworld/fs_serviceguard.pdf

Here are some sometimes-more, sometimes-less interesting links I found:
- "world record" benchmark 4-CPU Intel Itanium -- http://www.oracle.com/corporate/press/2005_jan/01.12.05%20oracle%20on%20linux%20benchmark%20final%20site.html
- Oracle on Linux including eval kit -- http://www.oracle.com/webapps/dialogue/dlgpage.jsp?p_dlg_id=3458696&src=2671650&Act=1614
- performance/analysis tools, halfway down page -- http://www.oracle.com/technology/deploy/performance/index.html
- 9i/Linux/Intel -- http://www.puschitz.com/TuningLinuxForOracle.shtml (more at http://www.puschitz.com/OracleOnLinux.shtml )
- database site -- http://www.databasejournal.com
- IBM e-server xSeries benchmarks -- http://www.pc.ibm.com/ww/eserver/xseries/benchmarks/series.html

HTH. Looking forward to your conclusions.

Mic
What kind of a name is 'Wolverine'?