Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Sep 2007
    Location
    Moscow
    Posts
    41

    Unanswered: DB2/AIX disk I/O performance

    Hello

    On this week i done some bencmarks with DB2 10.1 and get not so good result on AIX side.
    The testcase is simple select statement with table scan on adaptive compressed 21-column table with 80.6M rows. Select statement looks like :
    Code:
    select CALMONTH, "/BIC/ZCITY", "/BIC/ZPRODUCT", "/BIC/ZSU_SDC", sum("SALES")
    from "DB2INST1"."AZSU_TEST_ADAP"
    group by CALMONTH, "/BIC/ZCITY", "/BIC/ZPRODUCT", "/BIC/ZSU_SDC";
    I have two test environment :
    1. AIX 6.1 TL6 SP5 LPAR on POWER7 (3.7Ghz) machine with 8 virtual processors.
    + test LUN from dedicated RAID5 (6+1 10K HDD) array on SAN storage.

    2. SLES11 SP1 VM (8 vCPU) on VMWare vSphere 4.1 (Xeon x5690 3.4Ghz based server) + VMDK-disk from local datastore on RAID5 (7+1 10K HDD) array.

    In both testcases i create simple test database with one automatic storage path on JFS2 (AIX) and Ext3 (SLES) filesystems. On this databases i create tablespace with PAGESIZE=16K and EXTENTSIZE=32 (32x16=512k).

    Test tables is created on this tablespaces and loaded with 80M rows and after reorged with resetdictionary for maximum compression.

    I run test query through db2batch (with 5 iteration each) and monitor OS environment with nmon utility (both on AIX and SLES) and table scans with db2top.

    On AIX i get 97-100 sec. query runtime from db2batch reporting
    and on SLES VM i get 32-33 sec. I perform many time db2batch execution on both environments and get same results with 1-2 sec. difference.

    Acces plan for both query's is the same, but on SLES Total Cost is 30% more than on AIX.

    Also when test query executed i see what Disk Read on AIX is about 30MB/s and on SLES VM is about 90MB/s, but db2sysc process CPU utilization in both cases is ~ 105% (from nmon view). On db2top i see what table scan on SLES is about 3 times faster than on AIX (~ 2 500 000 vs. ~800 000 rows per second).

    According to "DB2 on AIX 6.1" redbook I try to increase AIX hdisk parameters queue_depth and max_transfer (with restart LPAR ), but result is the same. Also try to change DB2_PARALLEL_IO variable, but this not helped. In all cases on AIX i can't get Disk Read more than ~30MB/s with one thread from this compressed table.

    Also i try to test INTRA_PARALLEL execution of my test query and see what
    AIX is need 8 threads (CURRENT DEGREE=8) when SLES is need just 2 threads (CURRENT DEGREE=2) to complete query in 17-18 sec.

    Can somebody advise how i can tune AIX (may be LVM/ioo part) to get the same results as on virtualised SLES database.

    Thank's

    Best regards,
    Dmitry

  2. #2
    Join Date
    Apr 2011
    Posts
    19
    Somethings that helped our numbers

    DB2_USE_FAST_PREALLOCATION=ON
    DB2_SKIPINSERTED=ON
    DB2_USE_ALTERNATE_PAGE_CLEANING=ON
    DB2_EVALUNCOMMITTED=ON
    DB2_SKIPDELETED=ON
    DB2_PARALLEL_IO=*:17
    (17 being the number of disks per container. Default is 6)

    BTW: We got WAY better performance on real physical processors (P595 5Ghz -DB2 9.7.4 - Virtual Fibre Channel - Hitachi Array) Unless you are load balancing them to another system I would suggest you lock them down (take them out of VM). Our performance team messed with the que depth but I do not believe it made much of an impact, for us, past 16.

    Testing your data with you load and trying various things is the way to go.

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by mitrofun View Post
    Also when test query executed i see what Disk Read on AIX is about 30MB/s and on SLES VM is about 90MB/s, but db2sysc process CPU utilization in both cases is ~ 105% (from nmon view). On db2top i see what table scan on SLES is about 3 times faster than on AIX (~ 2 500 000 vs. ~800 000 rows per second).
    From what you're saying, my guess would be that the problem is outside DB2. Are you using VIOS on AIX? If you do, don't, for the DB2 LPAR anyway.

  4. #4
    Join Date
    Apr 2011
    Posts
    19
    Well I would certainly verify what kind of speeds you can attain outside of the DB.

    We have several dedicated VIO servers with many FC cards. Aix and SAN controller are not really in my job silo but... I do know we have driven each HDISK the DB has at around 600Mps per second using the tool NSTRESS.

    Sample:
    Code:
    #
    Command: /PERF61/NSTRESS/ndisk64 -f tempfile_800MB -R -M 5 -b 256k -t 300 -r 10 
            Synchronous Disk test (regular read/write)
            No. of processes = 5 
            I/O type         = Random
            Block size       = 262144
            Read-WriteRatio: 10:90 = write mostly
            Sync type: none  = just close the file
            Number of files  = 1
            File size        = 33554432 bytes = 32768 KB = 32 MB
            Run time         = 300 seconds
            Snooze %         = 0 percent
    ----> Running test with block Size=262144 (256KB) .....
    
    Proc - <-----Disk IO----> | <-----Throughput------> RunTime
     Num -     TOTAL   IO/sec |    MB/sec       KB/sec  Seconds
       1 -    732197   2440.6 |    610.16    624806.06 300.00
    All of out tablespaces are also set with "NO FILE SYSTEM CACHING" as CIO is important with JFS2

  5. #5
    Join Date
    Sep 2007
    Location
    Moscow
    Posts
    41
    Quote Originally Posted by n_i View Post
    From what you're saying, my guess would be that the problem is outside DB2. Are you using VIOS on AIX? If you do, don't, for the DB2 LPAR anyway.
    Yes. AIX LPAR get LUN from VIOS 2.2 through NPIV (virtual fibre channel), and unfortunatelly i can't get dedicated FC HBA to AIX LPAR for comparision.
    But i believe what NPIV does not get some I/O overhead like old VSCSI method.
    Also when test query running on AIX i monitor VIOS env from topas and not see any CPU or memory bottlenecks on VIOS side. As I say in first message may be there is some problem in I/O layer between DB2 and AIX, but i can't figure out what it is.

  6. #6
    Join Date
    Sep 2007
    Location
    Moscow
    Posts
    41
    Quote Originally Posted by DebianDog View Post
    Well I would certainly verify what kind of speeds you can attain outside of the DB.
    I get ndisk64 result like this (for one thread 100% sequential patern) :
    Code:
    bash-3.2# ndisk64 -f testfile -S -r100 -b 512k -M 1 -t 300
    Command: ndisk64 -f testfile -S -r100 -b 512k -M 1 -t 300
            Synchronous Disk test (regular read/write)
            No. of processes = 1
            I/O type         = Sequential
            Block size       = 524288
            Read-Write       = Read Only
            Sync type: none  = just close the file
            Number of files  = 1
            File size to be determined from the file(s) (raw devices will fail)
            Run time         = 300 seconds
            Snooze %         = 0 percent
    ----> Running test with block Size=524288 (512KB) .
    Determining the size of file:testfile
    File testfile size 10485760000 bytes
    Proc - <-----Disk IO----> | <-----Throughput------> RunTime
     Num -     TOTAL   IO/sec |    MB/sec       KB/sec  Seconds
       1 -    188664    628.9 |    314.44    321986.79 300.00
    bash-3.2#
    Raw disk bandwith is normal (about 300Mb/s) i think what can't to be botlleneck for query as DB2 "squeezes" only 30MB/s. In additional, dd test for 512k blocksize is get about 400MB/s (upper limit for our 4Gb/s connected SAN)

    And of course test tablespace on AIX have NO FILESYSTEM CACHING attribute.

  7. #7
    Join Date
    Nov 2011
    Posts
    334
    Did you try to set DB2_PARALLEL_IO=*:7?

    And plz uload the
    1、 output of the db2exfmt(access plan) and db2batch
    2、the application's snapshot which execute the query。
    3、the tablespaces prefech size
    and to make it simple plz use the statement :
    "select count(*) from DB2INST1"."AZSU_TEST_ADAP"
    instead of sum() to avoid the sort operation。

  8. #8
    Join Date
    Sep 2007
    Location
    Moscow
    Posts
    41
    Quote Originally Posted by fengsun2 View Post
    Did you try to set DB2_PARALLEL_IO=*:7?

    And plz uload the
    1、 output of the db2exfmt(access plan) and db2batch
    2、the application's snapshot which execute the query。
    3、the tablespaces prefech size
    and to make it simple plz use the statement :
    "select count(*) from DB2INST1"."AZSU_TEST_ADAP"
    instead of sum() to avoid the sort operation。
    Yes. I try DB2_PARALLEL_IO=*:7 (and other numbers) but scan rate is the same.
    Snapshot data from db2batch and db2exfmt output attached.
    By default prefetchsize is automatic. Also i try 128,256,512K for PREFETCHSIZE, but the result is the same.

    The query "select count(*) from DB2INST1"."AZSU_TEST_ADAP" with fullscan on tables (no indexes) is faster on AIX (9 sec. vs 14 sec on SLES), with Disk Read ~320Mb/s.

    Thank you.
    Attached Files Attached Files

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by mitrofun View Post

    The query "select count(*) from DB2INST1"."AZSU_TEST_ADAP" with fullscan on tables (no indexes) is faster on AIX (9 sec. vs 14 sec on SLES), with Disk Read ~320Mb/s.
    Interesting. I'd try rebuilding (or reorganizing) indexes on that table.

  10. #10
    Join Date
    Nov 2011
    Posts
    334
    from the output of the db2batch,
    The sort operation consuming the most of the time.
    Total sort time = 69857
    and sort overflow happend
    Sort overflows = 1。
    and io is not the bottleneck:
    Total Statement user CPU time = 93.317559
    Time waited for prefetch (ms) = 20。
    I dont know why the sort heap is so small:
    Sort Heap size: 52
    do you enable the stmm?
    any way, Maybe uploading the output of the db2batch and db2exfmt of SLES
    will give us more helpful information

  11. #11
    Join Date
    Sep 2007
    Location
    Moscow
    Posts
    41
    Quote Originally Posted by fengsun2 View Post
    from the output of the db2batch,
    The sort operation consuming the most of the time.
    Total sort time = 69857
    and sort overflow happend
    Sort overflows = 1。
    and io is not the bottleneck:
    Total Statement user CPU time = 93.317559
    Time waited for prefetch (ms) = 20。
    I dont know why the sort heap is so small:
    Sort Heap size: 52
    do you enable the stmm?
    any way, Maybe uploading the output of the db2batch and db2exfmt of SLES
    will give us more helpful information
    Sorts.....i'm forget about it
    Thank you

    Yes. STMM is enabled by both default created database and AIX have much less memory than SLES.

    I add memory to AIX LPAR and rerun db2batch
    Sort overflow is gone, but sort time is increased (why???) and runtime on AIX is also increased.
    Also i try to disable STMM and set SORTHEAP=10000 but with the same result.

    Archive with new db2batch and db2exfmt output from AIX and SLES attached

    Thank you for help.
    Attached Files Attached Files

  12. #12
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Must be something about the CPU scheduling on AIX: the same sort of the same number of rows takes 89 (elapsed) seconds on AIX vs. 0.6 sec. on Linux.

    I'm sure you have seen this: IBM Redbooks | Best Practices for DB2 on AIX 6.1 for POWER Systems but may be it's worth revisiting the CPU recommendations in that book.

  13. #13
    Join Date
    Nov 2011
    Posts
    334
    I notice there are two diffrent things between aix and sles。
    One thing is cpu speed , sles CPU Speed: 9.446885e-08, aix CPU Speed: 2.597893e-07.
    the cpu speed on sles is about 2.7 times faster than on aix.
    Maybe this is the main cause why query on sles is 3 times faster than on aix。
    another thing is access plan:
    There is a new step in access plan of sles :
    pGRPBY。
    I search for it on infocenter and found it is a new feature of db2 v10 to improve query performance
    Partial early aggregation (PEA)

    Similar to partial early distinct (PED), partial early aggregation (PEA) is an attempt to do a partial aggregation of data early in the processing of the query. While it is unlikely that all aggregation can take place at this point, it will at least reduce the amount of data that must be processed later in the query evaluation.
    To determine if partial early aggregation is being used for a particular query activate the Explain facility and run the query. A new value in the EXPLAIN_ARGUMENT table indicates when this new functionality has been applied to a query:

    ARGUMENT_TYPE column = AGGMODE
    ARGUMENT_VALUE column can now also have the value: HASHED PARTIAL which indicates that this new feature has been used

    The db2exfmt tool will also show HASHED PARTIAL in its output for GRPBY sections, along with a pGRPBY in the tree view, if this new functionality has been applied within that part of the query.
    I dont know why db2 on aix dit not choose this partial aggregation mode。
    Is there any difference about index , constraint and ddl of the table between aix and sels?
    Finally, i noticed that the statistics of the table on aix is a bit out of date
    Last Statistics Update: 2012-05-10-16.35.42.360244。
    Maybe this is the reason why sort time increased while sort overfolw is gone。
    you can collect the statistics ( with distributions on columns you want to group by)on the table on aix and try again to see whether it works 。

  14. #14
    Join Date
    Sep 2007
    Location
    Moscow
    Posts
    41
    Thank you very much for your help!

    Quote Originally Posted by fengsun2 View Post
    I notice there are two diffrent things between aix and sles。
    One thing is cpu speed , sles CPU Speed: 9.446885e-08, aix CPU Speed: 2.597893e-07.
    the cpu speed on sles is about 2.7 times faster than on aix.
    Maybe this is the main cause why query on sles is 3 times faster than on aix。
    It's very strange bacause AIX LPAR is run on new powerfull POWER7 3.7Ghz based server. Also i change LPAR processor mode to dedicated (from shared) with SMT=OFF and rerun db2batch, but wit the same increased to 114 sec. runtime and same sort time as in shared processors mode db2batch result.

    another thing is access plan:
    There is a new step in access plan of sles :
    pGRPBY。
    I search for it on infocenter and found it is a new feature of db2 v10 to improve query performance

    I dont know why db2 on aix dit not choose this partial aggregation mode。
    Is there any difference about index , constraint and ddl of the table between aix and sels?
    Finally, i noticed that the statistics of the table on aix is a bit out of date
    Last Statistics Update: 2012-05-10-16.35.42.360244。
    Maybe this is the reason why sort time increased while sort overfolw is gone。
    you can collect the statistics ( with distributions on columns you want to group by)on the table on aix and try again to see whether it works 。
    Runstat gathered after loading data and after this table not updated.
    By the way i recreate, reload, reorg and runstats with distribution test table on AIX again. There is no indexes, no any constraints, no foreign keys , no MDC or range partitioning, etc. Just plain compressed tables with identical DDL. db2batch on AIX get same result's

  15. #15
    Join Date
    Nov 2011
    Posts
    334
    I am very interesting on that why sort time increased while sort overflow is gone.
    that breaks the basic rules "sort overflow always need to be avoided?"
    Can you reproduce it again as the following steps:

    turn on the stmm
    db2stop
    db2start;
    activate the database
    db2batch -d <dbname> -i complete -o r 10 p 5 e yes > db2batch.out.stmm
    db2exfmt -d <dbname> -1 > db2exfmt.fmt.stmm (that will collect the
    access plan executed by db2batch )

    turn off the stmm and set the sort heap size to a specific value to avoid sort overflow
    db2stop;
    db2start;
    activate the database
    db2batch -d <dbname> -i complete -o r 10 p 5 e yes > db2batch.out.nostmm
    db2exfmt -d <dbname> -1 > db2exfmt.fmt.nostmm (that will collect the
    access plan executed by db2batch )

    and upload the output files here。
    Thx。

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •