A very large transaction (gigabytes) being copied from the ER
grouper to the send queue, for then being sent to targets,
inevitably will undergo spooling. This will come at some cost
performance wise, but also serves a purpose, e.g. advancing the
replay position, and shouldn't be too bad if done efficiently.
Unfortunately, with some bad luck, this efficiency can be
underwhelming, and it would deteriorate more and more the bigger
the transaction (in terms of number of rows) and the more parts
of it, in terms of buffers, already got copied.
The problem is more or less independent from CDR_QUEUEMEM
setting, but typically would start when the large transaction
arrives at a send queue that already is full and might already
be spooling. After a while (hours, days even) the send queue
might have drained down to zero 'Txns in queue', but the one new
large transaction still is in the process of being copied, more
and more slowly.
In 'onstat -g rqm sendq', you'd see 'Pending Txn Buffers / Data'
being high and increasing only marginally, e.g.
RQM Statistics for Queue (0x70000002bfb1028) trg_send
Transaction Spool Name: trg_send_stxn
Insert Stamp: 319251
Flags: SEND_Q, SPOOLED, PROGRESS_TABLE, NEED_ACK
Txns in queue: 0 #!!!
no transaction in queue
Log Events in queue: 0
Txns in memory: 0
Txns in spool only: 0
Txns spooled: 0
Unspooled bytes: 0
Size of Data in queue: 0 Bytes
Real memory in use: 0 Bytes
Pending Txn Buffers: 26875389
Pending Txn Data: 5234034660 Bytes
... |
If in such situation, possibly already since a long time, and
since it's impossible at this point to tell the size of the
entire transaction, so how close to the end of the process you
are, the best you can do is restarting ER (or entire server), in
the hope that this transaction will now arrive at an empty or
much less crowded send queue, so the copy process can run much
more efficiently. |
