Results 1 to 14 of 14
  1. #1
    Join Date
    Mar 2005
    Posts
    108

    Unanswered: Lock Manager Heap ?

    Hi All,
    I am using DB2 V 8.1 on linux. The "Memory Visualizer" alarmed 99.65% of "Lock Manager Heap" is in use. And snapshot shows:

    Code:
     
    get snapshot for database on ....
     
    ... ...
    Lock list memory in use (Bytes)            = 54880
    ... ...
     
        Memory Pool Type                           = Lock Manager Heap
           Current size (bytes)                    = 42188800
           High water mark (bytes)                 = 42188800
           Maximum size allowed (bytes)            = 42336256
     
    ... ...
    After I doubled locklist cfg parm, Memory Visualizer still showed almost 100% and snapshot showed locklist-in-use the same.
    My question is: Do I need to care about this alarm? How can I control it, reset the alarm? Thank you in advance.
    Last edited by DBA-Jr; 05-22-06 at 00:49.

  2. #2
    Join Date
    Dec 2004
    Location
    Italy
    Posts
    32
    Yes, You should absolutely increase the size of your locklist.

    According to the normal practice, locklist should be increased when hwm is > 50% of the maximum allowed.

  3. #3
    Join Date
    Sep 2003
    Posts
    237
    Implementing FREQUENT COMMITs in your batch programs and long running transactions, should bring down the usage of this heap.
    mota

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    What's the value of your MAXLOCKS?

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Mar 2005
    Posts
    108
    Thanks to ereryone.
    What I don't understandard is after I doubled and doubled again locklist, snapshot showed no increase in locklist-in-use, but the use of Lock Manager Heap was quadrupled - still 100% in use. What's the major part of Lock Manger Heap if it's not locklist? Why the use of Lock Manager Heap quadrupled while locklist in use remained the same?

  6. #6
    Join Date
    Mar 2005
    Posts
    108
    Maxlocks =40%

  7. #7
    Join Date
    Mar 2005
    Posts
    108
    Although no one answered my question, I still want to share my story here. It seems the velues in "locklist" and "Lock Manager Heap" are inconsintent. If I double locklist cfg parm, the snaphot shows me "Lock Manager Heap" gets doubled, but locklist in use remains the same. Actually "Lock Manager Heap" is always about 100% in use no matter what values I set for locklist.

    Code:
     
    db2 get snapshot for locks on afdb1:
      Database name                              = AFDB1
      Database path                               = ...
      Input database alias                       = AFDB1
      Locks held                                    = 1
      Applications currently connected      = 13
      Agents currently waiting on locks      = 0
      Snapshot timestamp                       = 05-26-2006 12:17:00.729396
     
    db2 get snapshot for db on afdb1:
      Locks held currently                       = 1
      Lock waits                                    = 16
      Time database waited on locks (ms)  = Not Collected
      Lock list memory in use (Bytes)         = 10920
      ... ...
       Memory Pool Type                           = Lock Manager Heap
       Current size (bytes)                         = 337313792
       High water mark (bytes)                   = 337313792
       Maximum size allowed (bytes)            = 337444864
    The above snapshot was taken after I restart my DBM. There was only on lock held in the entire DB, but "Lock Manager Heap" was still 100%.
    Am I misunderstanding something, or a bug in DB2? What is does your snapshot show?
    Last edited by DBA-Jr; 05-26-06 at 13:50.

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    What is the value of your locklist in the db config?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Sep 2003
    Posts
    237
    My understanding is lockmgrheap=locklist*pagesize + some overhead; it is typically a static value and that much memory is allocated when database is activated;as locks are needed, locks are created; each lock uses some 40 or so bytes; One application cannot use more than MAXLOCKS percentage of locks availble. If you issue "select somerecord for update where somecondition" and then check 'Lock list memory in use' you should see different values depending on how many records you selected; If MAXLOCKS percentage is exceeded, db2 will escalate the lock; instead of using many update locks in our example, db2 will replace many update locks with one table lock.
    mota

  10. #10
    Join Date
    Mar 2005
    Posts
    108
    The db cfg locklist = 80,000. It's apparently used up by the Lock Manager. This value was 20,000 before I increased it two days ago. Lock Manager always use up all my configured locklist value. But, there was only one or few locks.

  11. #11
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by DBA-Jr
    The db cfg locklist = 80,000. It's apparently used up by the Lock Manager. This value was 20,000 before I increased it two days ago. Lock Manager always use up all my configured locklist value. But, there was only one or few locks.
    80.000 is an outrageously large number for locklist. That is the number of 4K pages allocated to hold locks.

    On 32-bit platforms in V8.2, each lock requires 40 or 80 bytes of the lock list, depending on whether other locks are held on the object:

    - 80 bytes are required to hold a lock on an object that has no other locks held on it
    - 40 bytes are required to record a lock on an object that has an existing lock held on it.

    If you have an OLTP system, it is hard to imagine that you need that much storage for locks at any given time since transactions are short and commit frequently.

    If you have Data Warehouse application with lots of table scans, you would usually be much better off with a small locklist and letting row locks escalate to table locks, since multiple share (select) locks can co-exist without a problem. It is fairly expensive for DB2 to lock and unlock each row when doing a scan of a large table.

    I checked one of my Linux systems that has 4096 4K pages for the locklist, and since it is Friday night, there is no one using the system but me. Here is what my snapshot shows:


    Locks held currently = 0

    Memory Pool Type = Lock Manager Heap
    Current size (bytes) = 17383424
    High water mark (bytes) = 17383424
    Configured size (bytes) = 17383424


    7383424 bytes is slightly more (4244 4K Pages) than the 4096 4K pages allocated for the locklist (as mentioned in a post above, the heap is the locklist plus some memory needed for overhead). So it appears that the memory heap is fully allocated even if the entire locklist is empty.

    So I would conclude that any kind of monitoring software that warns you about the Lock Manager Heap being at or near 100% is causing a false alarm, since it should always be at 100% unless you don't have enough real memory.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  12. #12
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by tibullo
    Yes, You should absolutely increase the size of your locklist.

    According to the normal practice, locklist should be increased when hwm is > 50% of the maximum allowed.
    That might be good advice for an OLTP application, but poor advice for a Data Warehouse application.

    Usually "normal practice" is for people who don't understand how a product really works, and are looking for a cookbook approach. Sometimes the cookbook approach (normal practice) works OK, but since there a often exceptions to the "rule" then it can also cause problems.

    Of course, as we have determined, the lock memory heap usuage is not the same thing as the percent of locklist used to hold locks, but that is another subject.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  13. #13
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    I fully agree with Marcus on this. Even if there is very few applications are active and there may be only a single lock existing, then too you can see the "LOCK Manager Heap" has attained the High Water Mark, may be 50 MB or whatever size configured for the same.
    So the best practice to avoid memory issues in locking is to:
    1> Using COMMITCOUNT is your import statement. Ideally 1500 rows or less.
    2> In case of bulk delete of any table, use multimple UOW using WHERE clause with frequent COMMIT statement.
    3> If you are sure that the number of locks attained will be more and the memory required will be significant then increase the LOCKLIST parameter to a reasonable value, take care of the MAXLOCKS parameter too. Ideally it should be between 20% to 40%. Depending on application it can vary too.

    Thanks,
    Jay

  14. #14
    Join Date
    Mar 2005
    Posts
    108
    Quote Originally Posted by Marcus_A
    80.000 is an outrageously large number for locklist. That is the number of 4K pages allocated to hold locks.
    ... ...
    If you have an OLTP system, it is hard to imagine that you need that much storage for locks at any given time since transactions are short and commit frequently.

    If you have Data Warehouse application with lots of table scans, you would usually be much better off with a small locklist and letting row locks escalate to table locks, since multiple share (select) locks can co-exist without a problem. It is fairly expensive for DB2 to lock and unlock each row when doing a scan of a large table.
    ... ...
    So I would conclude that any kind of monitoring software that warns you about the Lock Manager Heap being at or near 100% is causing a false alarm, since it should always be at 100% unless you don't have enough real memory.
    This is a very good point I complete agree with! Your comments also assure me to ignore this kind of alarm.
    The reason that my locklist is that huge is I keep increasing it when I see the alarm above.

    A lot Thanks to everyone for helping me out.

    Gary

Posting Permissions

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