OE11.5.1 Win32: May I ask for comments about protop performa

i see nothing interesting or surprising

The "c" configuration screen would tell us interesting things too.

My initial impression is that you must have -B set quite small.  You have quite a few OS reads with very low demand.  So maybe you are scanning those tables.  If that is intentional then I suppose it is ok.

OTOH you seem to be updating as many records as you are reading.

3,800 TRX/sec is not horrible.   But you could probably improve things.  You might want to start by looking at the code and grouping multiple updates into a TRX.  50 or 100 at a time for instance rather than 1 update per TRX.

You have a lot of empty bi buffer waits.  I would increase -bibufs.  I don't know what your bi cluster size is.

You should run a dbanalys and put the output in the $PROTOP/dbanalys directory -- that way you can see how this activity compares to the table sizes and index characteristics.  (ProTop should do this for you in the background if you use the "A" command.)

I doubt that you need 3 APWs.

After-imaging is disabled.  Presumably this is a throw-away db?

IO response is not very good.  Is  this a SAN?  Or slow rotating rust?

BogoMIPs is decent.  That's a reasonably modern CPU.

(This post also shows how awful the communities website is at supporting technically focused content.  This ridiculous space wasting screen format drives me nuts.  Less than 20% of the my browser window is showing me useful content -- most if it is just empty.  Meanwhile the content of the post is almost unreadable because of the line wrapping that the website is forcing.  Communities should not be "Facebook for Progress".)

One record update per transaction, 6 recovery notes per transaction (including RL_TBGN/RL_TEND notes), the average size of recovery notes is 127 bytes. Transactions are really small => inefficient.

No index creates/deletes?

   DB Version:    11.5.1 32bit       Sessions (-n):         61        PF Delay:         Not        Area Config: Extended, Mixed

   DB Up Time:        04:02:33      Max Srvs (-Mn):          6       PF Factor:           0%       # Ext Areas:              16

   Backup Age:        3d 10:08       Cnx/Srv (-Ma):         10        PF #Recs:          16       # of Extents:              19

 DB Bufs (-B):         115,002       Min Cnx (-Mi):         10     PF Priority:           0       Lrgst Extent:        63.59 GB

        -spin:           2,000       Max Msg (-Mm):      1,024       -Nmsgwait:           2        Schema Area:           Clean

Lock Tbl Size:       2,000,000       After-Imaging:   Disabled       -lruskips:           0       RPB Settings:           Mixed

DB Block Size:            8 KB         AI Archiver:  Available      -lru2skips:           0         Areas Full:               0

AI Block Size:            8 KB      OE Replication:  Available     -recspdepth:           0         Worst Area:               0

BI Block Size:            8 KB       Crash Protect:    Enabled             -B2:           0           AI Areas:               0

BI Clstr Size:           32 MB          BI IO (-r):   Reliable          -Bpmax:          64       AI Area Type:        Disabled

   AI Buffers:              20      BI Delay (-Mf):          3           -hash:      35,863       AI Sequence#:               0

   BI Buffers:              20      BI Cluster Age:          0        -semsets:           3      Tbl Stat Base:               1

      DB Size:          88,155           Direct IO:         No         -omsize:       1,024     Tbl Stat Range:             500

 Alloc Blocks:             128                 2PC:         No           -pica:          64        # of Tables:             380

   Var Blocks:          88,155        Tainted (-F):      Clean            -Mxs:          35      Idx Stat Base:               1

      BI Size:             128       Index Rebuild:         Ok      # SHM Segs:           1     Idx Stat Range:           1,000

     -bithold:               0         Large Files:    Enabled      SHM Seg Sz:    2,048 MB       # of Indexes:           1,345

     -bistall:        Disabled            Failover:  Available      SHM Pinned:         Yes          # of LOBs:               0

     -aistall:        Disabled         DB Auditing:  Available     -cpinternal:   ISO8859-1

This database is not production, I am testing the performance during the inital load of new fields in every table.

hQueryHandle:query-prepare("for each " + cTableName + " exclusive-lock where RecordGUID = ~"~"").

repeat transaction:

       hQueryHandle:get-next().

       if hQueryHandle:query-off-end = true then

           leave.

       assign

              hBufferHandle:buffer-field("RecordGUID"):buffer-value()    = guid(generate-uuid).                

end.

IO response is not very good: Yes, it's something somewhere attached to my virtual Windows Server for OpenEdge.

Do I need to complain at IT-OP?

"This post also shows how awful the communities website is at supporting technically focused content - See more at: community.progress.com/.../86864

And It was perfect during the creation in full screen mode!

Meanwhile the second job finished and with one job I have now 2-3 seconds checkpoints.

  Chkpt# v  Start           Finish     Length         Dirty      ChkPtQ        Scan       APW Q     Flushed Duration Sync Time

───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────

     4,242  May 20 18:36:20            ?              2,109         249           0           0           0     0.19      0.18

     4,241  May 20 18:36:18 18:36:20   00:00:02       2,106       2,069           1           0           0     0.10      0.10

     4,240  May 20 18:36:15 18:36:18   00:00:03       2,110       2,073           1           0           0     0.20      0.19

     4,239  May 20 18:36:12 18:36:15   00:00:03       2,153       2,068           0           0           0     0.18      0.17

     4,238  May 20 18:36:10 18:36:12   00:00:02       2,101       2,016           0           0          48     0.13      0.12

     4,237  May 20 18:36:07 18:36:10   00:00:03       2,207       2,170           0           0           0     0.24      0.23

     4,236  May 20 18:36:04 18:36:07   00:00:03       2,117       2,080           1           0           0     0.18      0.16

     4,235  May 20 18:36:01 18:36:04   00:00:03       2,109       2,072           0           0           0     0.23      0.22

I reduced the APWs too:

        Hit%     97.90          Commits:    12,424         New RM:   10,778       Oldest TRX:  00:00:00   Connections:      12

  Log Reads:    35,990            Undos:         0        From RM:   10,777     Curr BIClstr:     4,280       Brokers:       1

   OS Reads:       755     Lock Tbl HWM:       388      From Free:        1   Oldest BIClstr:     4,280   4gl Servers:       1

  Rec Reads:    12,428     Curr # Locks:         3       Examined:   11,114      Checkpoints:     4,279   SQL Servers:       0

LogRd/RecRd:      2.90    Modified Bufs:     2,097       Front2Bk:        0   Curr AI Extent:  Disabled   4gl Clients:       5

 Log Writes:    45,440      IO Response:      4.77      Remove Lk:        0     Curr AI Seq#:         0   SQL Clients:       0

  OS Writes:     1,110         BogoMIPS:      4.17                             Empty AI Exts:        -1    App Server:       1

Rec Creates:         0         BogoMIP%:     91.67                              Full AI Exts:        -1           BIW:       1

Rec Updates:    12,425                                                                                            AIW:       0

Rec Deletes:         0            Notes:    70,289               0                APW Writes:       751          APWs:       2

  Rec Locks:    24,851    BIW/AIW Write%        88               0                 APW Write%        68          WDOG:       1

  Rec Waits:         0    Writes to Log:     1,487               0              Bufs Scanned:        96         Local:       1

Idx Blk Spl:         0   BIW/AIW Writes:     1,307               0             APW Scan Wrts:         1        Remote:       1

Resrc Waits:        39   Partial Buf Wr:         1               0                APW Q Wrts:         0         Batch:       4

Latch Waits:         3   Busy Buf Waits:        38               0              Chkpt Q Wrts:       750           TRX:       1

                          Empty Buf Wts:       202               0              Flushed Bufs:         4       Blocked:       0

> On May 20, 2016, at 9:16 AM, ChUIMonster wrote:

>

> This ridiculous space wasting screen format drives me nuts.

Low, low information desnity is the current fashion, Tom. It was a deliberate design goal to limit the amount of information per page, along with increasing the number clicks required to retrieve information.

Low information desnity became fashionable because it allows for more advertising and other useless stuff. That then became the norm for pages that don't have advertising as well.

> Meanwhile the second job finished and with one job I have now 2-3 seconds checkpoints.

Two simultaneous processes:

Commits: 3,813 per sec

Chkpt Length: 10 sec

One process is running 3 times faster than two processes:

Commits: 12,424 per sec

Chkpt Length: 3 sec

Two processes seemed to spend too much time to compete for some shared resources. MTX latch contention?

> 6 recovery notes per transaction (including RL_TBGN/RL_TEND notes)

Aren't the records get fragmented during updates? I guess the RecordGUID field is not a part of any index. So each your transaction should create only three notes: RL_TBGN, RL_RMCHG, RL_TEND. Exception is a record fragmentation.

> hBufferHandle:buffer-field("RecordGUID"):buffer-value()    = guid(generate-uuid). 

Length of guid(generate-uuid) is 32 bytes. The size of your records is increasing by more than 32 bytes.

What is RPB in the table's area? What is the mean record size?

IMHO, in the year 2016 If your IO response is higher than 1ms you should complain.

It is 2016 -- nobody should be running 32 bit database servers.

It's your code.  "Chunk" the transactions:

define variable i as integer no-undo.

outer: do while true transaction:

 repeat:

   /* your stuff ... */

   i = i + 1.

   if i modulo 100 = 0 then next outer.

 end.

 leave.

end.

I would also increase the bi cluster size, the bi blocksize and number of bi bufs.

While you are complaining about IO run the standard write performance test:

#!/bin/sh
#
# Similar to:
#   dd if=sports.b1 of=foo oflag=dsync
#
# minimum acceptable result is 10MB/sec -- or 9 seconds
#   4 or 5 seconds is "good"
#   3 or less is excellent (probably SSD)

prodb sports sports

proutil sports -C truncate bi -bi 16384

ls -ltr sports.b1

time proutil sports -C bigrow 2 # -zextendSyncIO

ls -ltr sports.b1

# time dd if=sports.b1 of=sports.b1.dd oflag=dsync

Tom,

no Linux, it's windows.

What do you use? cywin and time or ResourceKit and timeit

~5-6 seconds parallel to the running job (used my clock)

> it's windows

You can run bigrow on Windows as well. ;-)

Timestamps are in db log.

               Fri May 20 19:24:32 2016

[2016/05/20@19:24:32.123+0200] P-16636      T-45752 I DBUTIL   : (451)   Bigrow session begin for .... on CON:.

[2016/05/20@19:24:32.124+0200] P-16636      T-45752 I DBUTIL   : (15321) Before Image Log Initialization at block 0  offset 36679560.

[2016/05/20@19:24:36.292+0200] P-16636      T-45752 I DBUTIL   : (7129)  Usr 0 set name to ....

[2016/05/20@19:24:36.293+0200] P-16636      T-45752 I DBUTIL   : (6600)  Adding 2 clusters to the Before Image file.

[2016/05/20@19:24:38.312+0200] P-16636      T-45752 I DBUTIL   : (15109) At Database close the number of live transactions is 0.

[2016/05/20@19:24:38.313+0200] P-16636      T-45752 I DBUTIL   : (15743) Before Image Log Completion at Block 0 Offset 235.

[2016/05/20@19:24:38.320+0200] P-16636      T-45752 I DBUTIL   : (334)   Bigrow session end.

4 seconds for the init and 2 for adding the clusters?

> 4 seconds for the init and 2 for adding the clusters?

4 seconds to allocate 4 initial bi clusters.

2 seconds to add 2 bi clusters specified by bigrow's parameter

If it were up to me, I would apply the Linux patch before I went much further ;)

So 6 seconds -- that's on the high side of "good" but a  long ways from *great*.

At least it isn't "horrible".

Hi,

Thank you for your feedback. I am working with the developer team to determine steps we can take to make this a more enjoyable user experience. I will update you once I've met with the team. Thanks again for your feedback. Carly Connor

Just run the -bigrow test on a customer's DB server. 21 seconds. I wonder why they have performance issues when the application is busy?!

Got new virtual disks and copied .d files from C to E: ~ Remaining window shows 100MB/sec, is this a good value?

Hi Tim,

how to chunk:

FOR EACH table EXCL-LOCK WHERE FIELD BEGINS "B":

DELETE table.

END.

I have a rate of 3000 records a second and the query matches 99% of each record.

Isn't this too slow?