Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2003
    Posts
    127

    Unanswered: DB2 9.5 fixpak 4 memory issue

    We have an LPAR with 12 GB of memory. DB2 9.5 fixpak 4 on AIX 6.1. There are two instances. An application using a database under one instance is having performance issues. The websphere application slows down and pretty much comes to a stand still. We have around 200+ connections most of the time. During non-peak hours it is at 100-150 connections. When monitoring the instance, the memory usage that db2pd showed was a few MB. The shared memory of the database through db2top shows around 8-9 GB when the app hangs. nmon shows high physical memory usage and paging around 20% or so. Self tuning has been enabled. When the application was shutdown and database reactivated, the usage goes to a few MB but slowly climbs up.
    I have seen quite a few APARs like JR30285 - Pervasive memory leak when compiling SQL statements that use SQL/XML functions. It has been tough to do an upgrade as there are application changes coming up.

    Did anyone experience this kind of a problem and did an upgrade help? Someone posted an article on the net saying going to a higher level of fixpack did not help. Any suggestions ???

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Memory use on the database server by itself does not cause web application slow-down. I would start with looking at the snapshot to determine the cause of the slow-down.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jun 2003
    Posts
    127
    From what you are saying, memory is not the only thing slowing down the application. I already checked the snapshots and found one connection not releasing locks. After that was forced, the application has been much better but the database is still using 5GB. The DB2 logs show the stmm trying to release and increase memory and failing. Do you think upgrade would help? As a temp. fix, is it better to manually tune and turn off the auto feature?

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Kota View Post
    the database is still using 5GB
    And why is this a problem on a 12 G LPAR?

    Quote Originally Posted by Kota View Post
    The DB2 logs show the stmm trying to release and increase memory and failing. Do you think upgrade would help? As a temp. fix, is it better to manually tune and turn off the auto feature?
    I don't know what you see in the logs, but what you are saying does not sound like a problem, therefore requiring no fix.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    IBM completely re-wrote the memory management for DB2 with release 9.5 and there are lots of memory bugs. I would highly recommend an upgrade to 9.7 with the latest fixpack. 10.1 would be OK, but still a little bit new. If for some reason you cannot upgrade to 9.7 (such as not being able to get a license file for 9.7), you should definitely install the latest fixpack for DB2 9.5.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Jun 2003
    Posts
    127
    Thanks Marcus. Wanted to get a second opinion to see if there is any alternative like turning off STMM. Will consider upgrading to 9.7 or 10.1. I also have a PMR open with IBM. Will give an update if anything different.

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by Kota View Post
    Thanks Marcus. Wanted to get a second opinion to see if there is any alternative like turning off STMM. Will consider upgrading to 9.7 or 10.1. I also have a PMR open with IBM. Will give an update if anything different.
    Keep in mind that V9.5 end of support is scheduled for April 30, 2014, so you will need to upgrade sooner or later anyway.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by Kota View Post
    We have an LPAR with 12 GB of memory. DB2 9.5 fixpak 4 on AIX 6.1. There are two instances... When monitoring the instance, the memory usage that db2pd showed was a few MB. The shared memory of the database through db2top shows around 8-9 GB when the app hangs. nmon shows high physical memory usage and paging around 20% or so
    Does db2top show private memory usage? If it does, do you remember what it was for each instance? I've seen some private memory leaks related to authentication. What do you use for authentication?

    What was db shared memory usage for the db in the second instance?
    Is STMM enabled for all databases? Is instance_memory / database_memory set to automatic for all instances/databases?


    To start with, capture the following during high memory / swap usage:

    db2pd -dbptnmem (for both instances)
    ps aux
    vmstat -v
    vmstat -Itw 2 10
    lsps -a


    Check that you're on the recommended AIX TL, look for APARs related to memory usage / leak and swap:
    IBM Known issues for DB2 for Linux, UNIX and Windows on AIX 5.2, 5.3, 6.1, and 7.1 - United States


    Check recommended VMM settings, should be default on AIX 6.1:
    IBM Recommended AIX Virtual Memory Manager settings for DB2 database product - United States


    Quote Originally Posted by Kota View Post
    turning off STMM
    Yes. Yes. Yes. But that's just me...

    Seriously, if I had any reason to suspect STMM is at fault, I would turn it off. But make sure database shared memory allocation for all active databases doesn't exceed physical RAM.

  9. #9
    Join Date
    Jun 2003
    Posts
    127
    I checked the APARs.
    DB2 APAR says they have an issue with stmm.
    Other DBs are not so heavily used. I already checked the instance memory for both. It doesn't exceed 200 MB

    I'll check the URLs you sent. We use server_encrypt.
    After we had a meeting with AIX team, the IBM Active Memory Expansion (AME) has been enabled. This doubled the memory. It is done on the fly and basically AIX compresses the data in the memory. Now it looks much better. Still we are working on the upgrade as the next step.

    Will check your URLs and give share with you guys if something comes up.

  10. #10
    Join Date
    Jun 2003
    Posts
    127
    IBM came back and asked us to upgrade to 9.7 or 10.1. They pointed out the STMM issues I noticed. Marcus made a point earlier in this chain as to how IBM rewrote meomory management in 9.5.

Posting Permissions

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