标签归档:lio

SLOB – Knowing the Maximum LIO Number

This is a quick blog post to learn the maximun LIO on and old server by Kevin Closson‘s SLOB.

Why care about LIO.

With the apoption of SSD for storage cache and database files, the tranditional database system bottleneck on IO subsystem is greatly mitigated. The cpu power on database server can easily become the new bottleneck. Because Oracle is license by the database cpu processing power, which is the number of processor, the dba need to keep an eye on the cpu resource of database server. It’s good to learn the processing power by oracle logical IO, and learn how many LIO the database server can handle. SLOB is an amazing tool to learn abount system capacity and limit, Not only for measuring the IOPS and latency, but also the LIO maximun number.

In this litle test, I’ll look at the LIO maximun number only. The test server has 8 cores, Linux 2.6.9-55, Oracle version is 10.2.0.5.6. It’s one node of RAC.

[oracle SLOB]$ cat /proc/cpuinfo | grep -e "processor\|model name"
processor       : 0
model name      : Intel(R) Xeon(R) CPU           E5440  @ 2.83GHz
...
processor       : 7
model name      : Intel(R) Xeon(R) CPU           E5440  @ 2.83GHz

SQL> show parameter cpu_count

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cpu_count                            integer     8

Running with below 8 concurrent read only session(./runit 8 with update_pct=0), here comes the awr.txt and mpstats.out. The 8 cores are almost 100 percent busy. From the awr report, the 8-core server is able to process 1,845,584 LIO per seconds, it’s 230,000 per core. New Intel processor is probably faster, but the 230,000 per core may give you a crude estimation.

~~~~~~~~~~~                       Begin        End
                             ---------- ----------
               Buffer Cache:     3,072M     3,072M  Std Block Size:         8K
           Shared Pool Size:     1,024M     1,024M      Log Buffer:    14,328K

Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:              4,439.12              3,884.76
              Logical reads:          1,845,683.68          1,615,193.45
              Block changes:                 12.25                 10.72
             Physical reads:                  0.81                  0.71
            Physical writes:                  1.45                  1.27
                 User calls:                  7.13                  6.24
                     Parses:                  4.84                  4.23
                Hard parses:                  0.03                  0.02
                      Sorts:                  2.96                  2.59
                     Logons:                  0.13                  0.12
                   Executes:              7,161.47              6,267.14
               Transactions:                  1.14


Event                                 Waits    Time (s)   (ms)   Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
CPU time                                          2,359          98.7
latch: cache buffers chains             629           4      7    0.2 Concurrenc
reliable message                         23           3    149    0.1      Other
PX Deq: reap credit                   1,425           3      2    0.1      Other
db file sequential read                 514           3      6    0.1   User I/O
          -------------------------------------------------------------

[oracle 8.0]$ cat db_stats.out
1|30000|29462|98.207|
3|30000|29675|98.917|
8|30000|29468|98.227|
7|30000|29562|98.54|
2|30000|29353|97.843|
6|30000|29304|97.68|
5|30000|28902|96.34|

[oracle 8.0]$ more mpstat.out
...
04:14:41 PM  CPU   %user   %nice %system %iowait    %irq   %soft   %idle    intr/s
04:14:44 PM  all   99.13    0.00    0.75    0.00    0.00    0.12    0.00   2614.72
04:14:44 PM    0   96.66    0.00    2.68    0.00    0.00    0.67    0.00   1574.25
04:14:44 PM    1   99.33    0.00    1.00    0.00    0.00    0.00    0.00      0.00
04:14:44 PM    2  100.00    0.00    0.33    0.00    0.00    0.00    0.00      0.00
04:14:44 PM    3   99.33    0.00    1.00    0.00    0.00    0.00    0.00     30.77
04:14:44 PM    4  100.00    0.00    0.00    0.00    0.00    0.00    0.00      0.00
04:14:44 PM    5  100.33    0.00    0.33    0.00    0.00    0.00    0.00      0.00
04:14:44 PM    6  100.33    0.00    0.33    0.00    0.00    0.00    0.00   1003.68
04:14:44 PM    7  100.00    0.00    0.33    0.00    0.00    0.00    0.00      6.02
...

reading this week

Dumps
http://www.juliandyke.com/Diagnostics/Dumps/HEAPDUMP.html

Advanced Oracle Troubleshooting Session – PGA/UGA memory fragmentation
http://oracle-randolf.blogspot.com/

不均衡分区和绑定变量窥视导致的查询计划错误
http://www.hellodba.com/Cases/Unbalance_part_hist.html

dbms_xplan(3)
http://jonathanlewis.wordpress.com/2008/03/06/dbms_xplan3/

Treedump – 2
http://jonathanlewis.wordpress.com/2010/03/07/treedump-2/

Index Efficiency 3
http://jonathanlewis.wordpress.com/2010/03/03/index-efficiency-3/

treedump
http://jonathanlewis.wordpress.com/2009/08/17/treedump/

Index Explosion
http://jonathanlewis.wordpress.com/2009/07/28/index-explosion/

PDF Doc: Oracle B-Tree Index Internals: Rebuilding The Truth

Log File Sync – What is Wrong with this Quote?
http://hoopercharles.wordpress.com/2010/06/16/log-file-sync-what-is-wrong-with-this-quote/

Gary Millsap: Why You Should Focus on LIOs Instead of PIOs

Tim Gorman: THE SEARCH FOR INTELLIGENT LIFE IN THE COST-BASED OPTIMIZER