Multi-processor performance

[nsew45]

We have a Sunfire 4800 with 3blades of 4 900Mhz processors and 8Gb RAM running Solaris 8 and Oracle. We have a discussion going regarding increasing performance by adding processors. If you run a task with 2 processors will the task run 2x faster with 4 processors, 4x faster with 8 processors? Will the performance increase linearly with the number of processes you add (12 procs 12x faster than 1)? Or does the performance plateau depending on memory? Oracle 8.1.7 32-bit can only use a 3.75Gb SGA.

Let's hear it from the sysadmins running Oracle.

 

[jimbopalmer]

I try to never make my SGA over 1/4 of my RAM, (as the unix buffer cache will mirror the database block buffers) so 3.75 GB may not be a limit just yet. I have never used the parallel option in Oracle (as it seemed to require rewriting each SQL statement with hints) so take that into account.

With the previous Sun servers, memory could only be banked 1 way, 2 ways, 4 ways, or 8 ways, so 3 blades may well be a limit even if CPUs are not. A CPUless blade may double the speed of your Sun by allowing 2 way interleave to go to 4 way interleave,if you balance the RAM on all 4 blades. (it happened on my ue4000)

Ok now, one process uses one CPU, to use more than one CPU, you need to break your problem into more than one process. (Again, both Oracle and Unix can perform certain background operations on other CPUs DBwriter, Logwriter, fsflush, etc so multiCPUs help some, but to use more, you need to break your problem into multiple processes)

Some problems are easier to break into multiple processes without record locking, payroll is easy, no employee's paycheck is in anyway dependant on a value for any other employee, while G/L end of month is going to find that some account values are needed by as many as 80% of the transactions, so record locking will prevent much multiprocessing, no matter which attack you try to improve the speed.

so 1) improving interleave may improve speed more than CPUs in your configuration
2) too large a SGA may swap it to disk, destroying any advantage of having data in a SGA
3)if you have multiple users/processes, multiple CPUs can be used easily.
4) not every problem can be easily divided into many smaller problems.
I tried to remain child-like, all I acheived was childish.