Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2003
    Posts
    39

    Unanswered: Poor Query Performance - OS400 V5R2

    I am relatively new to AS400 but I have been enlisted as my company's AS400 admin and DBA (I am already a SQL DBA). We have an iSeries 270 with max'd out hardware, and running V5R2. I have been reading redbook after redbook discussing the vast hardware performance improvements, and of course, the new SQE. Everything I have read sounds pretty impressive. Nonetheless, my query performance is far from impressive and far behind our SQL boxes. I just don't get it.

    I have been investing time in learning about V5R2's new SQE, access plan cache, Statistics Manager, and redesigned MI layer, yet I cannot find anything wrong with my databases or my setup.

    Does anyone have experience with both DB2 and MS SQL? I have transfered large tables from DB2 to SQL, run identical queries, and SQL is killing DB2. Since our largest investment and most important systems are tied up in our AS400, I am pretty frustrated.

    I have noticed that the fast MS SQL queries are using 100% CPU on client side and 25% CPU on server side. The DB2 query is using 0% client side cpu and averaging 1.8% server CPU through the QADASOINIT thread. Does something need to be tuned? SQL is burning through a full table scan in 66 seconds compared to DB2 at 30 minutes!

    Any input would be appreciated.

    Thanks

    Ryan Hunt

    OS400 V5R2, DB2 level 8
    Last edited by AS400Admin; 08-21-03 at 13:36.

  2. #2
    Join Date
    Aug 2003
    Posts
    24
    Well, I dont have the type of gear you're running, but Im experiencing the same frustration as you are.
    Im currently running DB2 PDE 8.1.3 on w2k interfaced by PHP/ODBC.
    Hardware, is AMD 1700XP, 256M Ram, 7200rpm HD. Nothing spectacular, but for development needs should be more than enough.
    The same system is also running SQL Server 2k.
    Accessing the php pages, are like night and day between SQL2k and DB2.
    My biggest frustration is that DB2 isn't consistent with its performance.
    While browsing pages, performance would be quick, then it slows to a crawl, then pick back up, then crawl again.
    And Im talking about a database with barely any data in it.
    Im also searching for tips/tricks that would improve performance.
    But so far from my experiences is that SQL2k, is definately faster than DB2.
    Im hoping to get DB2 on even ground with SQL2k, so if anyone has any sure fire way to even the odds, Im open to suggestions.

    Later today, Ill post some results of some tests using the same php scripts, on SQL2k, and DB2 backends.

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    I don't know if this helps... but try looking at Performance thread.

    Hope this helps,
    Grofaty

  4. #4
    Join Date
    Aug 2003
    Posts
    39
    Yes, I think that may be helpful. I just finished quite a bit of testing with Visual Explain, as well as viewing the results of a SQL Monitor I ran through iSeries Navigator - great data! It appears I have some index work to do. However, I could use clarification on an issue.

    Are the "buffer pools" in the performance thread synonymous with memory pools on an AS400? I have been considering changing my base memory pool (the one all apps use) from *FIXED to *CALC. With 4GB of memory and 208 maximum concurrent threads, each query is limited to about 19mb of memory. *CALC will allow single queries to use greater amounts of memory if concurrent threads are low.

    Thanks

    Ryan

  5. #5
    Join Date
    Aug 2003
    Posts
    39
    I am also a bit confused about the following...

    If a run a SELECT * FROM two different DB2 tables, one with 11 million records, and one with 327,000 records, the 11 million record tables returns results in 6 seconds. The table with 327,000 records provides results in 30+ minutes.

    1) Since both are SELECT *'s, no indexes are used...both require a full table scan.
    2) Both SELECT's are being run from the same interface and thus alotted the same amount of memory in DB2
    3) Both queries are being run against our development data....therefore no records locks.
    4) I have run both queries with [...WITH UR FOR FETCH ONLY] and [FOR READ ONLY] and neither have made any difference. Performance remains the same.

    Is there something I am missing???

    Thanks

    Ryan

  6. #6
    Join Date
    Aug 2003
    Posts
    24
    Ok, here is what I did for DB2 so far.
    1. Increase BUFFPAGE to 2500 in db cfg
    2. Change NPAGES to -1 in the SYSBUFFERPOOLS table
    3. Stop and Restart the Database instance.

    Browsing the site, seemed a "little" (and I mean by not much) better. So I tried putting the server under Siege (http://www.joedog.org/siege/index.shtml) comparing DB2 to SQL2k.

    The test were done on the same machine, but when running the DB2 tests, I shutdown SQL2k, and MySQL services.
    Both tests uses the same pages, and for the DB2 test, it was conducted on a fresh boot, and I viewed the front page once to have the DB collect its initial variables etc.

    IBM DB2 8.1.3
    Lifting the server siege... done.
    Transactions: 5 hits
    Availability: 100.00 %
    Elapsed time: 29.99 secs
    Data transferred: 14496 bytes
    Response time: 5.99 secs
    Transaction rate: 0.17 trans/sec
    Throughput: 483.33 bytes/sec
    Concurrency: 1.00
    Successful transactions: 5
    Failed transactions: 0


    SQL2k
    Lifting the server siege... done.
    Transactions: 244 hits
    Availability: 100.00 %
    Elapsed time: 29.95 secs
    Data transferred: 1170781 bytes
    Response time: 2.83 secs
    Transaction rate: 8.15 trans/sec
    Throughput: 39097.30 bytes/sec
    Concurrency: 23.06
    Successful transactions: 244
    Failed transactions: 0

    Granted DB2 is interfaced by ODBC, and SQL2k by its own extention, but the difference is like night and day.
    This test is not meant to be a definiative comparision between the 2 pieces of software, but of my experiences so far with them.

    Ill keep reading that topic that was posted earlier by grofaty and see what else I can tweak...

  7. #7
    Join Date
    Jun 2003
    Location
    Canada
    Posts
    37
    A couple of thoughts:
    - I am assuming the two tables are on the same physical node, in the same database instance, bufferpool, and otherwise similar in their characteristics as well. If they are not, ... ;-)
    - Just from the fact you are doing a SELECT *, does not follow that DB2 does not use an index to better service the query. Even if you are not specifying a WHERE clause. DB2's general tendency is to pick up a well-clustered index even if it is not obvious that should need an index. I recommend performing an EXPLAIN to see what DB2 actually is doing (I am not an AS400 user but pretty sure it has an EXPLAIN facility).
    - Major performance differences may come from the various clauses and predicates used in your SELECT statement such as WHERE, ORDER BY, GROUP BY, etc.
    - Page size vs. record length also has an imnpact on you I/O efficiency (no in the order of magnitude difference typically, though).
    - I do not know much about your environment but development does not usually guarantee no record locks. Unless you can order all developers off your system, there is a chance of some process locking some records. This can be determined by using some performance monitor such as TMON for DB2 or similar.
    - [FOR READ ONLY] would only make difference from an originally ambiguous cursor with non-correlated subqueries, which appears not to be your case.
    - the impact of WITH UR on performance is not dramatic, it's obviously very relevant from data integrity point of view.

    Cheers,

    Julius

    Originally posted by AS400Admin
    I am also a bit confused about the following...

    If a run a SELECT * FROM two different DB2 tables, one with 11 million records, and one with 327,000 records, the 11 million record tables returns results in 6 seconds. The table with 327,000 records provides results in 30+ minutes.

    1) Since both are SELECT *'s, no indexes are used...both require a full table scan.
    2) Both SELECT's are being run from the same interface and thus alotted the same amount of memory in DB2
    3) Both queries are being run against our development data....therefore no records locks.
    4) I have run both queries with [...WITH UR FOR FETCH ONLY] and [FOR READ ONLY] and neither have made any difference. Performance remains the same.

    Is there something I am missing???

    Thanks

    Ryan

  8. #8
    Join Date
    Aug 2003
    Posts
    39
    I found the discrepancy. OS400/DB2 does not return drive space back to the system when records are deleted. Rather, deleted records are marked as inactive and retained in the file until a RGZPFM is performed. The table in question had ~380,000 live records and 32 MILLION deleted. Therefore, my select * was doing a table scan against a total of 32,380,000 records, not 380,000.

    Thanks

  9. #9
    Join Date
    Nov 2004
    Posts
    2

    How to permanently remove records tagged for deletion

    You can fix this by using the RGZPFM command.

  10. #10
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by trini0
    Ok, here is what I did for DB2 so far.
    1. Increase BUFFPAGE to 2500 in db cfg
    2. Change NPAGES to -1 in the SYSBUFFERPOOLS table
    3. Stop and Restart the Database instance.
    Dusting your computer and changing desktop picture would probably have better effect on DB2 performance. Please don't expect that setting some random parameters to some random values will improve system performance.

Posting Permissions

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