Results 1 to 15 of 15
  1. #1
    Join Date
    Apr 2012
    Posts
    156

    Unanswered: DB2 9.7.5 Performance on Windows

    I have been working with DB2 for several years but always on Unix. Several of the db's I am supporting run on Windows 2008 R2, and they all have really poor IO performance. I mean really poor. I set up a sandbox to do some testing and it have been able to replicate the poor performance, which is good since I can test any suggestions anyone comes up with. If I run SQLIO (found on the internet to test IO performance), I get the following:
    throughput metrics:
    IOs/sec: 9381.73
    MBs/sec: 293.17 <-- Not too bad
    When I watch this utility run through Resource Monitor I see similar mbs/sec. However, if I run a simple select count(1) on a table, that is only around 1.6 GB with 28 million rows it takes 5+ minutes. My sandbox is otherwise completely idle. Also, my MBs/sec visible thru Resource Monitor is only around 1 MB/sec, compared to the 293 MBs/Sec I see when running SQLIO. Any ideas on where to start? Is there anything special to look at when it comes to DB2 and Windows I/O? I turned off file system caching. I have plenty of memory, no paging but when I look at the disk I am running at 98%.

  2. #2
    Join Date
    Nov 2011
    Posts
    334
    why not publish the some snapshot and access plan of the query "select count()".
    prefer to use db2batch and db2exfmt to collect them.
    And what do you mean "but when I look at the disk I am running at 98%."

  3. #3
    Join Date
    Apr 2012
    Posts
    156
    The issue I am having is not the access path, even a full scan would not be an issue. The issue I am trying to investigate is why the IO is so slow. When I mentioned 98% busy, I am referring the disk activity as seen thru Resource Monitor on Windows.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by azready View Post
    The issue I am trying to investigate is why the IO is so slow.
    I'm afraid you're approaching the issue from a wrong direction. Imagine that your entire table fits in the bufferpool. How many physical I/O would you need to scan it? 0. How many I/Os/sec or MBs/sec would Windows report? 0. Would that indicate "slow I/O"? I'd say no.

    You would need to understand in detail what's happening in DB2 when you run your test workload: bufferpool efficiency, prefetch and page cleaning activity, locking, etc. Looking at the query plan is the first step.

    I don't know anything about SQLIO found on the Internet, but from my experience disk benchmarks are very poor indicators of the database I/O performance.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Apr 2012
    Posts
    156
    Normally I would totally agree with you. However, any action we perform on windows that requires high amounts of IO (backups, reorgs, runstats, table scans etc.) are ridiculously slow on these Windows databases. For example to backup a 1 TB database to disk takes 12 hours. I am 99% sure our disk sub system is configured incorrectly but I need to prove that is the case. Does anyone know of any good freeware tools for evaluating disk performance on windows?

  6. #6
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    good freeware tools
    you get what you pay for.
    Dick Brenholtz, Ami in Deutschland

  7. #7
    Join Date
    Nov 2011
    Posts
    334
    A simple way is :
    Copy/FTP a big file out to test the reading performance ,
    Copy/FTP a big file in to test the writing performance.
    And do it concurrently to test the i/o performance.

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by fengsun2 View Post
    A simple way is :
    Copy/FTP a big file out to test the reading performance ,
    Copy/FTP a big file in to test the writing performance.
    It is simple, for sure, but it's in no way similar to what DB2 does to disks...
    ---
    "It does not work" is not a valid problem statement.

  9. #9
    Join Date
    Apr 2012
    Posts
    156
    I did a bit more testing. When I run an operation on the database that should push the disk system (requires high I/O's), only opening 1 thread per Logical Disk. This gives me very similar results to SQLIO for a single thread, I gain the performance via SQLIO when I have multiple threads open per logical disk. So I know the performance is available from the disk. Is there a parameter either windows or db2 that controls the number of threads opened to the disk subsystem? DB2 is behaving like I have a single disk, when in reality I have a SAN. I have tried increasing my prefetchers, parallelism to no avail. Thanks again.

  10. #10
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    ---
    "It does not work" is not a valid problem statement.

  11. #11
    Join Date
    Apr 2012
    Posts
    156
    Tried setting db2_parallel_io=*:5 , did not make an impact. According to the article this should have done the trick. I have a single thread per container, unfortunately on the disk side I can see they are operating in a round robin fashion, processing 1 request at a time, instead of flooding the disk system and building my request queue. I opened a ticket to IBM hopefully they can come up with something.

  12. #12
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Did you also set the other parameters accordingly? Did you restart the instance after changing the registry variable?
    ---
    "It does not work" is not a valid problem statement.

  13. #13
    Join Date
    Apr 2012
    Posts
    156
    I think I set everything. I alter the tablespace to automatic prefetch, changed db2_parallel_io registry setting and started and stopped the instance.

  14. #14
    Join Date
    Nov 2011
    Posts
    334
    What "the thead" you metioned above is ? do you mean "ioserver" ?
    "I have a single thread per container, unfortunately on the disk side I can see they are operating in a round robin fashion, processing 1 request at a time"
    How to control " a single thread per container"?
    if it is running in a round robin fashion , how could the disk be 98% busy?

  15. #15
    Join Date
    Apr 2012
    Posts
    156
    Finally figured out what is going on with these systems. Apparently when they were originally set up the Extent Size for all of the tablespaces was set to 2 pages. This is way to low for this set up. I created a new tablespace with an extentsize of 32, spread across 4 containers. I set DB2_PARALLEL_IO to 5 and copied a table into the new tablespace. The new tablespace hammers the disk as it should. I do full tablespace scans on the new table in under 2 seconds, vs 3 minutes on the tables with the extentsize set to 2 pages. Now I have the task of fixing all of the tablespaces in 10 environments to have the correct extentsize. Always fun taking over someone elses system :-) Thanks for all the help

Posting Permissions

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