Database really slooooow - Forum - OpenEdge RDBMS - Progress Community

Database really slooooow

 Forum

Database really slooooow

This question is not answered

AIX 5.3

OE 10.0.b 32-bit - I know - we got this baby when we merged with another company -

800 users concurrent

378 GB size

LOT of BKSH

I adjusted the spins and TXE updates and commits

what else should I do  - like finding a needle in a hay

03/28/17        Status: Buffer Locks
09:58:24

      User    DBKEY   Hash T S Usect

    1   87   276159  36427 I S     1
    2 1491 54421504  25379 D R     1
    3 1492 202742016  24381 D R     1
    4 1494   274502  34770 D R     1
    5 1494   215949  16206 I S     1
    6 1550 219229696  33301 D R     1
    7 1591 414414592  38602 D R     1
    8 1607 34347008  28509 D R     1
    9 1635 123456512   4875 D R     1
   10 1652 127484672  36345 D R     1
   11 1670 166728704  23027 D R     1
   12 1679 93406976  10047 D R     1
   13 1684 219058560  31887 D R     1
   14 1688 61306496  39185 D R     1
   15 1726 210240256   3060 D R     1
   16 1881  1672832  13178 D R     1

 17 2046 32037888  10469 D       0
 18 2047 110665984  24910 D R     1
 19 2133 47301120   9746 D R     1
 20 2139 54821632  28511 D R     1
 21 2177 102917760   4372 D R     1
 22 2219  3729920  29241 D R     1
 23 2231 188270464  31366 D R     1
 24 2250 147669504  34083 D R     1

All Replies
  • what does the activity summary screen in promon R&D show?

    note that lots of buffer share locks is not necessarily bad, especially with 800 users.

    what does the blocked clients screen in promon show?

  • > LOT of BKSH

    Can you post "Status: Buffer Lock Queue"?

    promon/R&D/debghb/6/15

    and "Performance Indicators":

    promon/R&D/3/1

  •  User   DBKEY       Status        Type         Usect

       0 189384576         LOCKED            TIO         1

       0 190068096         LOCKED            TIO         1

       0 57073536         LOCKED            TIO         1

       0 11475712         LOCKED            TIO         1

       0 109552896         LOCKED            TIO         1

       0   376225         LOCKED          SHARE         1

       0 140684544         LOCKED            TIO         1

       0 120578304         LOCKED            TIO         1

       0 213102464         LOCKED            TIO         1

       0 515214848         LOCKED            TIO         1

       0 213237120         LOCKED            TIO         1

       0 152068992         LOCKED            TIO         1

       0  3776128         LOCKED            TIO         1

       0 193431168         LOCKED            TIO         1

       0 24801024         LOCKED            TIO         1

  • Usr Name     Type Wait            Trans id  Login time

    1629 0001sem1 REMC REC   92530769 1044608720  03/28/17 08:08

    1724 0001kcv  REMC REC   92530769 1044522601  03/28/17 07:46

    1901 0001dst  REMC REC   92530769 1044382314  03/28/17 11:09

    1968 0001kmb  REMC REC   92530769 1044420192  03/28/17 11:11

    2004 0001jlk2 REMC REC   92530769 1044599421  03/28/17 11:20

    2005 0001wpc  REMC REC   92530769 1044477174  03/28/17 11:14

    2024 0001mms2 REMC REC   92530769 1044526840  03/28/17 07:04

    2262 0001jtn  REMC REC   92530769 1044534270  03/28/17 06:46

    2374 0001smk  REMC REC   92530769 1044600183  03/28/17 11:25

  • Usect = 1 and no waits for locked buffers => the buffers are not a bottleneck.

  • 11:28:23        03/28/17 06:40 to 03/28/17 11:26 (4 hrs 45 min)

                                 Total   Per Min    Per Sec    Per Tx

    Commits                     2008095      7036     117.27      1.00

    Undos                          1721         6       0.10      0.00

    Index operations             167096K   599530    9992.17     85.21

    Record operations            171786K   616358   10272.64     87.60

    Total o/s i/o              43586513    152721    2545.35     21.71

    Total o/s reads            42612114    149307    2488.44     21.22

    Total o/s writes             974399      3414      56.90      0.49

    Background o/s writes        913893      3202      53.37      0.46

    Partial log writes           159286       558       9.30      0.08

    Database extends                  0         0       0.00      0.00

    Total waits                 2471155      8659     144.31      1.23

    Lock waits                      716         3       0.04      0.00

    Resource waits              2470439      8656     144.27      1.23

    Latch timeouts               464119      1626      27.10      0.23

  • 03/28/17        Activity: Summary

    11:31:41        03/28/17 11:31 to 03/28/17 11:31 (10 sec)

    Event                  Total  Per Sec |Event                  Total  Per Sec

    Commits                1189     118.9 |DB Reads              47682    4768.2

    Undos                     1       0.1 |DB Writes               417      41.7

    Record Reads         101153   10115.3 |BI Reads                  0       0.0

    Record Updates          403      40.3 |BI Writes                31       3.1

    Record Creates          115      11.5 |AI Writes                18       1.8

    Record Deletes           48       4.8 |Checkpoints               0       0.0

    Record Locks           1961     196.1 |Flushed at chkpt          0       0.0

    Record Waits              0       0.0

    Rec Lock Waits     0 %    BI Buf Waits      0 %    AI Buf Waits      0 %

    Writes by APW    101 %    Writes by BIW    45 %    Writes by AIW    83 %

    DB Size:         378 GB   BI Size:       2499 MB   AI Size:         47 MB

    Empty blocks:7501567      Free blocks:   2344      RM chain:   1178481

    Buffer Hits       85 %    Active trans:   174

    76 Servers, 596 Users (0 Local, 596 Remote, 0 Batch), 6 Apws

    Iteration 4 of 9999, pause for 10 seconds ...

  • Your record reads vs db reads are very low.  Are you running Type I storage areas ?  

    Would be interesting to know if your disks are not able to keep up with the read requests.

    Would be interesting to know what a table analysis of your heavily read tables show.

  • > 4 hrs 45 min

    Can you use a shorter sample interval?

    Or you can run dbmon script:

    ftp.progress-tech.ru/.../dbmon.sh

    It will collect all information that needs to find out the answer.

  • Inherited this DB from merger - I know type 1 and 32-bit and OE 10 all not good MGMT is aware - they did not want to improve database - we are migrating all user to our ERP which is HP-UX 11.4 64-bit (TREND).

  • > DB Reads  4768.2 (per sec)

    If db blocksize is 8K then you read 37 MB/sec from disk. Probably it's close to the max disk IO.

  • I shutdown the DB and one of the db area was running into variable - so I added some more extents - so far ok.

  • Because of Type I storage areas and the few records read / db read, the database tables probably have high scatter factor and fragmentation.  Since the db is 32 bit, you can't allocate a large number of buffers to help that issue.

    A full D&L if you have time.

    Another option would be to create a new Storage Area for the smaller tables with high read rates.  Then move those tables to the new Storage Area.

  • I see that:

    0) all the blocked clients are waiting for a record lock and all for the same record: rowid 92530769

    1) db reads per seocnd is quite high

    2) ratio of record reads to db reads is very low

    3) buffer pool hit ratio of 85% is quite low.

  • Could you attach this scripts how to query the locked buffer?

    and join the buffer name.

    Thanks