Interleave factor performance impact

Interleave factor performance impact

Post by eaviles9 » Sat, 04 Feb 2006 07:13:00


My test Oracle database server is performing better than my production
database server.

I've spent much time looking at database stats and tweaking different
things at the database level but nothing has helped.

One thing I noticed between the two servers is that the test box has
all memory 16-way interleaved while the production box has a mix of
8-way and 16-way. I looked at memory configuration after I found memory
intensive queries ran faster on test.

How much impact does the interleave factor has on performance?

Here's the prtdiag output for both servers.

TEST SERVER:

/usr/platform/sun4u/sbin/prtdiag -v

System Configuration: Sun Microsystems sun4u Sun Fire 6800
System clock frequency: 150 MHz
Memory size: 32768 Megabytes

========================= CPUs
===============================================

CPU Run E$ CPU CPU
FRU Name ID MHz MB Impl. Mask
---------- ------- ---- ---- ------- ----
/N0/SB1/P0 4 1200 8.0 US-III+ 11.0
/N0/SB1/P1 5 1200 8.0 US-III+ 11.0
/N0/SB1/P2 6 1200 8.0 US-III+ 11.0
/N0/SB1/P3 7 1200 8.0 US-III+ 11.0
/N0/SB3/P0 12 1200 8.0 US-III+ 11.0
/N0/SB3/P1 13 1200 8.0 US-III+ 11.0
/N0/SB3/P2 14 1200 8.0 US-III+ 11.0
/N0/SB3/P3 15 1200 8.0 US-III+ 11.0
/N0/SB5/P0 20 1200 8.0 US-III+ 11.0
/N0/SB5/P1 21 1200 8.0 US-III+ 11.0
/N0/SB5/P2 22 1200 8.0 US-III+ 11.0
/N0/SB5/P3 23 1200 8.0 US-III+ 11.0

========================= Memory Configuration
===============================

Logical Logical Logical
Port Bank Bank Bank DIMM Interleave
Interleave
FRU Name ID Num Size Status Size Factor
Segment
------------- ---- ---- ------ ----------- ------ ----------
----------
/N0/SB1/P0/B0 4 0 512MB pass 256MB 16-way
0
/N0/SB1/P0/B1 4 1 512MB pass 256MB 16-way
0
/N0/SB1/P0/B0 4 2 512MB pass 256MB 16-way
0
/N0/SB1/P0/B1 4 3 512MB pass 256MB 16-way
0
/N0/SB1/P1/B0 5 0 512MB pass 256MB 16-way
0
/N0/SB1/P1/B1 5 1 512MB pass 256MB 16-way
0
/N0/SB1/P1/B0 5 2 512MB pass 256MB 16-way
0
/N0/SB1/P1/B1 5 3 512MB pass 256MB 16-way
0
/N0/SB1/P2/B0 6 0 512MB pass 256MB 16-way
0
/N0/SB1/P2/B1 6 1 512MB pass 256MB 16-way
0
/N0/SB1/P2/B0 6 2 512MB pass 256MB 16-way
0
/N0/SB1/P2/B1 6 3 512MB pass 256MB 16-way
0
/N0/SB1/P3/B0 7 0 512MB pass 256MB 16-way
0
/N0/SB1/P3/B1 7 1 512MB pass 256MB 16-way
0
/N0/SB1/P3/B0 7 2 512MB pass 256MB 16-way
0
/N0/SB1/P3/B1 7 3 512MB pass 256MB 16-way
0
/N0/SB3/P0/B0 12 0 512MB pass 256MB 16-way
1
/N0/SB3/P0/B1 12 1 512MB pass 256MB 16-way
1
/N0/SB3/P0/B0 12 2 512MB pass 256MB 16-way
1
/N0/SB3/P0/B1 12 3 512MB pass 256MB 16-way
1
/N0/SB3/P1/B0 13 0 51
 
 
 

Interleave factor performance impact

Post by ianal Vist » Sat, 04 Feb 2006 11:56:41


@g47g2000cwa.googlegroups.com:


Quantify "better".



What OS & version?
Which version of Oracle to four places?
CBO or RBO?
Are statisics current in production?
Has this performance difference always been the case?
If not, what changed just before the performance got worse.

Ready, Fire, Aim methodology of performance tuning at work here.

Since you don't know exactly what is wrong, you are randomly changing
things while hoping to get lucky & stumble upon the silver bullet.

I suggest that you take a single SQL query which runs noticably slower
in production than on the test system, enable SQL_TRACE at level 12, run
the resultant trace file thru TKPROF & then actually see where the extra
time is being spent

P.S.
More than likely it is NOT the memory.

 
 
 

Interleave factor performance impact

Post by Hans » Sat, 04 Feb 2006 22:09:38

nteresting !
I have also noted wildly different interleave configurations on different
machines for no obvious reason.
Do you have any numbers on the performance difference in your application ?

< XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...


 
 
 

Interleave factor performance impact

Post by eaviles9 » Sun, 05 Feb 2006 01:01:14

Hans,

The same batch process runs about 50% faster on the test server.
 
 
 

Interleave factor performance impact

Post by hpuxra » Sun, 05 Feb 2006 01:07:15


Cary Millsap's book "Optimizing Oracle Performance" contains a proven
methodology for solving problems like yours. I recommend highly taking
the time to read this book as the primary way of determining how to
attack your problem.

You can get 10046 traces for a representative time of the batch run on
the production server and see what "is slowing it down". Compare and
contrast this with what you see from the batch run on the test server.
 
 
 

Interleave factor performance impact

Post by eaviles9 » Sun, 05 Feb 2006 13:46:47


I've seen that book referenced in so many plances that I placed an
order on Amazon.

Still, my original question remains unanswered. Given the interleave
factor difference, how much impact would that have on memory intensive
database queries? Is it negligible or is it a big deal?

Thanks.
 
 
 

Interleave factor performance impact

Post by Seongbae.P » Sun, 05 Feb 2006 18:00:52

It is a BIG deal. Having 8-way interleave, instead of 16, can reduce
the peak memory bandwidth by half. And you're running a database
workload where memory bandwidth is critical (well, nowadays, many
commercial workloads are either memory bandwidth bound or memory
latency bound or both - no wonder UltraSPARC T1 performs well given its
low latency and high peak bandwidth).

-Seongbae, usually as XXXX@XXXXX.COM
 
 
 

Interleave factor performance impact

Post by Noon » Sun, 05 Feb 2006 19:39:08


It can be a big deal if your queries are CPU limited.
It can be nothing if your bottleneck is instead IO.
Hence the request to go through Cary's approach with
10046: it will tell you exactly what's going on and where
to look for problems.

Interleave can be a big factor for cpu intensive work,
be it database or anything else. Try to get a memory
speed test program and do some comparative tests.
 
 
 

Interleave factor performance impact

Post by Scott Howa » Sun, 05 Feb 2006 22:30:56


One thing I noticed is that one is a 6800 and the other is a 15K. These
are _very_ different beasts that work in very different ways.
(6800 is snoopy coherency, F15K is ccNUMA)

There's far too many variables in the platform difference alone to start
with memory...

Scott.
 
 
 

Interleave factor performance impact

Post by Trinea » Mon, 06 Feb 2006 01:58:59

The way I understand it, if you want one BIG domain on your system, then
performance wise a single domain 6800 will be better than a 15K containing
the same number of system boards.
 
 
 

Interleave factor performance impact

Post by from the » Mon, 06 Feb 2006 02:25:12

XXXX@XXXXX.COM wrote:

Others have suggested the best approach for tuning Oracle. Since I have
never used Oracle, I can't comment on that.

This may be (probably is) too simplistic, but there is a program for
testing memory bandwidth:

http://icl.cs.utk.edu/projects/llcbench/

This is the part of output I get from my Ultra 80. As you can see,
figures in Mb/s drop quite dramatically at 4 MB, which I assume is
because the cache size of the X1195A 450 MHz CPUs (there are 4) is 4 MB.

sparrow /export/home/drkirkby/llcbench/cachebench % ./cachebench

Memory Set Library Cache Test

C Size Nanosec MB/sec % Chnge
------- ------- ------- -------
256 0.75 1279.72 1.00
256 0.72 1316.86 0.97
384 0.61 1567.58 0.84
384 0.60 1585.15 0.99
512 0.54 1764.63 0.90
512 0.54 1752.30 1.01
768 0.48 2001.51 0.88
768 0.48 1990.85 1.01
1024 0.45 2136.23 0.93
1024 0.45 2121.47 1.01
1536 0.41 2304.18 0.92
1536 0.41 2307.56 1.00
2048 0.40 2391.77 0.96
2048 0.40 2393.02 1.00
3072 0.38 2482.46 0.96
3072 0.38 2493.88 1.00
4096 2.53 377.16 6.61
4096 2.58 370.25 1.02
6144 2.57 371.07 1.00

Of course you have a very different ssort of machine. Here's data from a
similar is machine to the above. This time a Sun Ultra 60 with a single
360 MHz CPU. Again the cache size is 4 MB on this.

main-webserver # ./cachebench

Memory Set Library Cache Test

C Size Nanosec MB/sec % Chnge
------- ------- ------- -------
256 0.91 1052.24 1.00
256 0.91 1045.29 1.01
384 0.76 1248.05 0.84
384 0.76 1261.58 0.99
512 0.68 1404.07 0.90
512 0.68 1403.34 1.00
768 0.60 1590.35 0.88
768 0.61 1564.57 1.02
1024 0.56 1693.51 0.92
1024 0.56 1701.08 1.00
1536 0.52 1827.60 0.93
1536 0.53 1813.95 1.01
2048 0.50 1901.40 0.95
2048 0.50 1902.84 1.00
3072 0.48 1972.52 0.96
3072 0.48 1977.79 1.00
4096 1.43 666.12 2.97
4096 2.43 392.98 1.70
6144 2.41 396.41 0.99
6144 2.48 384.96 1.03

Anyway, it might confuse matters more, but the option is there of
running that if you want. It was pretty easy to build.

--
Dave K

http://www.southminster-branch-line.org.uk/

Please note my email address changes periodically to avoid spam.