topic Re: cpu horsepower for database in Operating System - HP-UX

cpu horsepower for database

Martha Mueller — Wed, 18 Dec 2002 12:57:51 GMT

We are doing capacity planning. Our DBA has stated that too much cpu horsepower is bad for a database, so we are trying to "right size" the unix server. I'm just asking for opinions and comments....do you think that more numbers of smaller cpu's are better or worse than fewer high-powered cpu's for database work? The only application running on the server is the database, so there really doesn't seem to be any reason to keep a cpu just for the operating system to use.

Re: cpu horsepower for database

Pete Randall — Wed, 18 Dec 2002 13:04:40 GMT

Hi Martha,

On a 6 CPU N-class, we devote 5 440Mhz CPUs to our Informix database and leave one for the OS to do it's housekeeping. Runs like a champ. My opinion is - the more the merrier, the faster the better.

Pete

Re: cpu horsepower for database

harry d brown jr — Wed, 18 Dec 2002 13:05:51 GMT

That has to be the biggest pile of horse dung I have ever heard of: ""too much cpu horsepower is bad for a database""

Your dba needs to wake up to reality and stay focused on DateBase Administration and stay out of the SYSTEMS ADMINISTRATION. I'm not sure I'd let them login again to my server :-)

As for your system configuration, we would need a LOT more info:

(1) how many users?
(2) what kind of applications and how do they interact with other applications?
(3) what is the resourse consumption profile of each user/application?
(4) available disk, memory, network, ...
(5) Release of OS, patch level, DataBase release, 32 or 64 bit?
(6) amount of money in budget?

live free or die
harry

Re: cpu horsepower for database

Stefan Farrelly — Wed, 18 Dec 2002 13:08:33 GMT

I agree with Harry. Whoever said too much cpu is no good for a databases is talking crap.

In fact, less cpu's but faster ones is better than more cpu's that are slower. ie; 6x 550Mhz is not as good as 4x750Mhz. Most of the kernel work is done on the first cpu - so the faster cpu's you can get (instead of number of them) the better everything will run.

Re: cpu horsepower for database

Christopher McCray_1 — Wed, 18 Dec 2002 13:08:36 GMT

Hello,

That is odd... I wonder what the background for his statement is.... I guess idle cpus become frustrated and bored so they start picking on the database???

Anyway, you should start by looking at what you currently have. I believe that most of the 9000 servers need at least 2 cpus to run. You may want to look into icod (instant capacity on demand). This way you can "turn on" additional cpus as you need them, and hp will bill you at that point for the additional cpus.

Hope this helps

Chris

Re: cpu horsepower for database

harry d brown jr — Wed, 18 Dec 2002 13:12:39 GMT

Thanks Stefan! I thought I had heard it all, but that DBA learned me something new.

and Chris, I'm glad I wasn't drinking coffee when I read your comment, because I would have spit it out laughing: "" I guess idle cpus become frustrated and bored so they start picking on the database??? ""

live free or die
harry

Re: cpu horsepower for database

Martha Mueller — Wed, 18 Dec 2002 13:14:14 GMT

Thanks, Harry. I thought it was just me. However, I was just fishing for general type of information about number of cpu's vs. power of cpu's. Thank you Stephan. There is always the issue of cost of licenses that are based on a per cpu basis. Chris, that is pretty much what was explained...the database will spend more time looking for work, if none is found, goes to sleep. Then it takes extra time to get going again.

Re: cpu horsepower for database

Pete Randall — Wed, 18 Dec 2002 13:17:43 GMT

You do have to weigh the costs involved, but like I said, Martha, the more the merrier, the faster the better.

Good luck,

Pete

Re: cpu horsepower for database

harry d brown jr — Wed, 18 Dec 2002 13:22:51 GMT

Martha,

That explaination is crazy: ""the database will spend more time looking for work, if none is found, goes to sleep. Then it takes extra time to get going again""

If you had a SLOWER CPU it would take LONGER to service my request, making EVERYTHING SLOW.

Maybe your DBA is thinking about Micro$ucks Operating systems and software??

live free or die
harry

Re: cpu horsepower for database

Ravi_8 — Wed, 18 Dec 2002 13:23:46 GMT

Hi,

I Have a oracle database running N-class(rp7400) with 8 processors. we can say it is a super database server

Re: cpu horsepower for database

Christopher McCray_1 — Wed, 18 Dec 2002 14:01:40 GMT

Hello again,

Martha,

There is no upper limit to the amount of cpus you can have in a server, other than the capacity of the server itself.

Your dba is obviously the victim of a good joke that he apparently took seriously.

Harry,

I'm glad, too, that you weren't drinking your coffee too.

Chris

Re: cpu horsepower for database

A. Clay Stephenson — Wed, 18 Dec 2002 15:13:52 GMT

Hi Martha,

If I were you I would starting looking for an Apple II running a 1MHz 6502 8-bit processor; the database performance should then be absolutely phoenomenal. After you find the Apple II, I would then give it to your DBA on his way out the door. His was truly a "state of the art stupid" remark.

The only thing I can even remotely correlate this to was that on early versions of NT, there were cases were 4 processors were slower than 2; I actually measured this but this was a flaw in the OS design at the time rather than a database issue.

Re: cpu horsepower for database

Jim Large — Thu, 19 Dec 2002 04:36:37 GMT

As a DBA and System Admin ( I have to wear both hats here! ) I can't even make up a good reason for your DBAs comments. . .When I started system planning for a recent implementation my only concern was buying the least amount of CPUs, but the fastest. This held licensing costs down for Oracle and HPUX add-ons. Even then, I bought a box that had room to grow. If money weren't a factor, I would have bought as many of the fastest CPUs I could cram into a cabinet!!

Wow. . .get him to sign off against your wishes and when the thing runs like crap, GLOAT!!

Best of Luck,
Don't forget to email him this link!!

jim

Re: cpu horsepower for database

Yogeeraj_1 — Thu, 19 Dec 2002 07:17:16 GMT

hi martha and everybody

Increadible that people just say anything without proving their points!

Let us see how a Database and CPU are related!
You have a shared resource -- a CPU. You run an intensive process that needs lots of CPU. You have lots of other stuff that needs CPU. You have a very very finite amount of CPU. All of a sudden, people have to WAIT to gain access to this very finite resource -- they run SLOWER. They don't STOP, they run slower. They don't have to wait until the process finishes, but they WILL wait for their turn on the CPU.

So additional CPU means additional processing power and additional processing power means (well-tuned) applications running faster.

I cannot imagine how can a large number of CPUs can be a bottleneck.

More CPUs can only prove to be costly for Database Systems like Oracle that are licensed per CPU. But this too has a solution today.

oh dear :)

Hope this helps!

Best Regards
Yogeeraj

Re: cpu horsepower for database

Yogeeraj_1 — Thu, 19 Dec 2002 07:33:29 GMT

hi again,

In general, let us see how we determine the requirements for a Database system.

It is difficult to get a right checklist get it right UNLESS you know the application inside out. If you are talking about installing Oracle applications -- then yes, they have sizing metrics and such that they can fairly accurately determine your needs. If you are just talking "the database", no --
there is no such checklist.

The problem is -- without knowing the entire application, we don't know how much CPU horsepower you need for example. If you do lots of tiny inserts/updates/deletes with few indexes and no triggers (so the operations themselves take a few hundreds of a CPU second) you need much less CPU's then a system where you run lots of queries that take 1 CPU second apiece -- or a
stored procedure that takes 30 CPU seconds to complete. You need to tell us how much CPU you will consume and how many people will be doing it concurrently. Then, the math is rather straightforward.

As for things like "number of disk controllers" and number of disks -- you only need 1. Additional ones may increase performance, again depending on the application itself. Is it IO intensive? Read or Write? Does it access LOTS of
data by LOTS of users (indicates you might benefit from more controllers) or is
there a small amount of data accessed by a small number of users (no benefit probably).

The rules of thumb are

o 80 users / CPU is not unheard of (RISC architecture).

o 3megs ram / user is a solid starting place (high but better safe than sorry).

o disks -- impossible to guess. You really need to see how the application behaves (benchmark) to settle on the right number.

Your mileage will vary radically from the above -- only given an application and
a benchmark of that application (and the needed response times and so no) can
one reliably determine the number of users/cpu, amount of physical ram needed and so on.

Hope this helps!

Best Regards
Yogeeraj

Re: cpu horsepower for database

Graham Cameron_1 — Thu, 19 Dec 2002 11:22:53 GMT

Your DBA is crazy.

We all know that what databases really need is slow disk, that way the database spends more time doing work in the cpu because it would take too long to write it to disk !

Also try reducing the memory, that way the database will work smarter because it doesn't have to waste time storing stuff in memory.

Graham ;-)

Re: cpu horsepower for database

Rory R Hammond — Thu, 19 Dec 2002 21:26:26 GMT

Martha,

Sound like your DBA might be a Penny Wise Pound Poor ex accountant...

If your System is NOT 100% busy most of the time and
your Oracle processes generally take 100% of a CPU, Faster CPU's would benifit you.

If your System is 100% busy most of the time and your Oracle Processes generally do not take 100% of an individual CPU, More CPU's would benifit.
AT face value, Beforue BUYING equipment, I would suggest looking FIRST at your ORACLE sql queries. It sound like you DBA might have exceed his highest level of compentance.

Ror

Re: cpu horsepower for database

John Poff — Thu, 19 Dec 2002 21:57:01 GMT

Hi,

The only thing I can figure is that maybe your DBA is thinking about configuring your database so that it runs with lots of threads, so they think that having fewer, more powerful CPUs will cut down on the number of threads they can run. That is the only thing I can figure, and it is a real stretch. Normally, our DBAs want the fastest CPUs they can get. Asking them how much CPU power they want is like asking them how much cash they want. Usually they'll take all they can get.

I'll mention your DBAs statement to our DBAs and I'll let you know how long it takes them to stop laughing. :)

JP

Re: cpu horsepower for database

Pete Randall — Thu, 19 Dec 2002 22:12:40 GMT

Geez, John, hold it down, will you? I can't think with all the racket from your laughing DBAs.

;^)

Pete