Running transactions in a tight loop without monopolizing resources - nice Progress

[pinne65]

We are purging a large number of records in several tables. The purges all fit this pattern

DEF VAR min-id AS INT NO-UNDO.
DEF VAR max-id AS INT NO-UNDO.

DEF VAR num-records AS INT INITIAL 0 NO-UNDO.
DEF VAR trans-active AS INT INITIAL no NO-UNDO.

DISABLE TRIGGERS FOR LOAD OF big-table.
DEFINE BUFFER buf-4-big-table FOR big-table.

FOR EACH big-table
WHERE min-id <= big-table.id AND big-table.id <= max-id NO-LOCK:
DO TRANSACTION:
FIND buf-4-big-table WHERE
RECID(buf-4-big-table) = RECID(big-table) EXCLUSIVE-LOCK.
DELETE buf-4-big-table.
END.
END.
END.

If we run this purge during business hours it slows the system down to a crawl for everybody else.

I previously posted a question, http://www.progresstalk.com/showthread.php?121504-Limit-transaction-to-N-records, in regards to this operation, trying to improve the performance. The idea was to have several DELETEs per transaction instead of just one. But Tom Bascom had already found out that it made no difference for DELETEs. But I'm wondering if it will make a difference in this case...

Is there a way to make this code run "nice". I.e. run at a low priority and yield it's resources to other users if they request it, but at full speed if nothing else is going on?

I'm thinking about implementing the code suggested by rzr in the previous post and maybe add a PAUSE statement after each transaction. The problem is that the PAUSE wouldn't be necessary all the time and would slow things down.

Here are activity snapshots from our system

 NORMAL ACTIVITY:
Activity  - Sampled at 11/02/12 10:49 for 0:00:11.

Event                 Total   Per Sec   Event                 Total   Per Sec
       Commits          174      15.8            Undos            0       0.0
Record Updates          284      25.8     Record Reads        31990    2908.2
Record Creates           88       8.0   Record Deletes            3       0.3
     DB Writes          249      22.6         DB Reads         4980     452.7
     BI Writes           43       3.9         BI Reads            0       0.0
     AI Writes           24       2.2
  Record Locks         2349     213.5     Record Waits            0       0.0
   Checkpoints            0       0.0    Buffs Flushed            0       0.0

Rec Lock Waits    0 %    BI Buf Waits      0 %    AI Buf Waits      0 %
Writes by APW   100 %    Writes by BIW    28 %    Writes by AIW    83 %
Buffer Hits      96 %    Primary Hits     96 %    Alternate Hits    0 %
DB Size         198 GB       BI Size     488 MB       AI Size     125 MB
FR chain                232710 blocks   RM chain              4078070 blocks
Shared Memory   7941M        Segments      1

63 Servers, 581 Users (507 Local, 74 Remote, 15 Batch),3 Apws

RETURN - repeat, U - continue uninterrupted, Q - quit:


PURGING GOING ON:
Activity  - Sampled at 11/02/12 10:50 for 0:00:13.

Event                 Total   Per Sec   Event                 Total   Per Sec
       Commits         2852     219.4            Undos            1       0.1
Record Updates          383      29.5     Record Reads        39506    3038.9
Record Creates           87       6.7   Record Deletes         2755     211.9
     DB Writes         2883     221.8         DB Reads         8018     616.8
     BI Writes          397      30.5         BI Reads            0       0.0
     AI Writes          260      20.0
  Record Locks        24450    1880.8     Record Waits            2       0.2
   Checkpoints            0       0.0    Buffs Flushed            0       0.0

Rec Lock Waits    0 %    BI Buf Waits      0 %    AI Buf Waits      0 %
Writes by APW   100 %    Writes by BIW    48 %    Writes by AIW    98 %
Buffer Hits      96 %    Primary Hits     96 %    Alternate Hits    0 %
DB Size         198 GB       BI Size     488 MB       AI Size     131 MB
FR chain                232719 blocks   RM chain              4078365 blocks
Shared Memory   7941M        Segments      1

63 Servers, 582 Users (509 Local, 73 Remote, 15 Batch),3 Apws

RETURN - repeat, U - continue uninterrupted, Q - quit:

 

[Rob Fitzpatrick]

Re: Running transactions in a tight loop without monopolizing resources - nice Progre

For starters, please wrap character mode output (like promon screens) in CODE tags. This makes it a lot more legible.

 

[pinne65]

Re: Running transactions in a tight loop without monopolizing resources - nice Progre

That's true. Didn't think about that..

 

[LarryD]

Re: Running transactions in a tight loop without monopolizing resources - nice Progre

If this is Linux, you could create a script to execute the delete program(s) (same as setting up a Progress cron job) and run the script from progress code as a detatched job:

e.g. (untested)

os-command silent value( "nice -n 10 nohup mydeletescript > mydelete.log 2>&1 &" ).

where mydeletescript is the script to execute the mass delete.

 

[TomBascom]

Re: Running transactions in a tight loop without monopolizing resources - nice Progre

"Nice" is not nice You really don't want to mess around with priorities when cooperative use of shared memory is involved -- the high priority processes will end up waiting for the low priority processes to finish.

Don't PAUSE on every iteration. Instead PAUSE every N iterations where N is 100 or 1,000 or something like that.

You could also make a call to a shared library and perform a sub-second pause. http://dbappraise.com/ppt/shlib.pptx

 

[LarryD]

Re: Running transactions in a tight loop without monopolizing resources - nice Progre

Tom, I was under the impression that the scheduler priority was used for pretty much all o/s requests hence my post. Thanks for the clarification!

 

[TomBascom]

Re: Running transactions in a tight loop without monopolizing resources - nice Progre

It is used to schedule processes -- but you want to leave it alone. If you artificially change the priority it has unintended consequences.

Suppose a low priority process obtains a latch (even low priority processes get lucky sometimes...) -- and is then pushed aside before it finishes with it. Meanwhile a high priority process is spinning & waiting for that same latch... Every higher priority process is going to get to run before our low priority guy gets a chance to finish what he is doing. And, the nature of latches being what it is, quite a few of those high priority processes are probably looking for access to that very same latch (especially if it is something popular like the LRU latch). The OS doesn't know any of this -- it is application specific stuff. And Progress isn't keeping track of who has the latch nor is there a queue (that sort of thing is expensive to maintain and latches are all about keeping the overhead at a minimum...) The assumption is that everyone gets a fair (and more or less random) shot at it. But when you start tweaking priorities that assumption is broken.

 

[pinne65]

Re: Running transactions in a tight loop without monopolizing resources - nice Progre

Yea, I wasn't really thinking about OS nice but rather Progress nice if there was such a thing. I already tested the pause at 100/200 iterations. But the result wasn't what I expected when viewing the promon stats. I will make some changes to the code and take another stab at it.
Thanks for the shared lib info Tom. I might give that a try as well.

 

[trx]

Re: Running transactions in a tight loop without monopolizing resources - nice Progre

I guess you could try to implement something like nice using VST. Just query VST to detect busy time and then pause. That could be more effective that just pause every 100/200 iterations.

As you mentioned earlier you are preparing data for migration, so maybe you should consider Tom's highly parallel dump&load .
You could also consider just dumping important records instead of purging unimportant ones (though that depends on ratio important / unimportant) or even something like that: create new table, copy there important records, then switch names of old and new table.

As of your code I think you could use something like that:

viLower = min-id.
DO WHILE viLower < max-id :
    viUpper = MIN(viLower + 100, max-id)

    <nice VST test here>

    DO TRANSACTION :
        /* I hope id field is indexed if not then maybe TEMP-TABLE should be used as temporary index */
        FOR EACH big-table EXCLUSIVE-LOCK WHERE
                big-table.id >= viLower
          AND big-table.id <= viUpper :
              DELETE big-table.
        END.
    END.
    viLower = viUpper.

END.

 

[Rob Fitzpatrick]

Re: Running transactions in a tight loop without monopolizing resources - nice Progre

I'm not sure I understand why this purge needs to happen during business hours. That is the kind of thing I would expressly schedule outside of business hours. I definitely wouldn't monkey with OS priorities on clients or servers though.

We have a purge routine we can run against selected tables. It is intended for culling the records in a table to a manageable quantity based on the business rules appropriate for the data in question. So for example you might want to always have the last thirty days of transaction data in one table, but only the last seven days of data in another.

In some cases, the tables in question have not been purged in months, or years, or ever. So if you simply ran a "delete everything older than a month" purge it would churn for days. To address this problem, the purge program takes a few parameters, including table name, threshold value, and cut-off time (time of day); example: table: foo; threshold: 30 days; cut-off: 07:00. This is then scheduled as a batch job to kick off each night, after the nightly reporting cycle is complete. So it starts at, say, 02:30 and does as much purging as it can until either it finishes or it hits 07:00. If the latter, it stops, and then is run again the next night. Eventually it catches up to the point where it's just deleting a day's worth of records each day (or a week's worth each week, etc.).

Once the purge "catches up" you might want to do an idxcompact on the table's indexes to improve utilization.

 

[pinne65]

Re: Running transactions in a tight loop without monopolizing resources - nice Progre

The purge doesn't really have to run during business hours. It starts at night and used to finish well before business started. Now it runs into it because of the way the real data is selcted, per business month (The ranges now being purged holds many more records than the previous ones did). And No, I never intended to "monkey around" with the OS. I was looking for the Progress counterpart of the unix nice functionality.

Checking the VSTs for activity could possibly be an option. But I agree, using a cutoff time probably makes more sense. Some times I just have an irresistible urge to monkey around with things though...