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
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
Code:
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: