SQL/MX Queuing and Publish/Subscribe Services

HP NonStop SQL/MX Queuing and Publish/Subscribe Services—523734-002
4-1
4 Performance Considerations
This section describes performance issues for applications that use publish/subscribe
services. This section also provides tips to help you maximize performance.
Considerations for Partitions
•
Avoid using too many subscribers on a partition.
You can configure multiple subscribers on a partition, but if you configure too many,
especially subscribers using destructive SELECTs, they will tend to block each
other. In other words, a new row will cause all the subscribers’ DAM sessions to be
dispatched. Only one session gets and locks the row. The other sessions defer
processing the row and go back to sleep after using some processing resources
and doing no productive work.
•
Use range-partitioned tables to scale publish/subscribe applications.
For embedded UPDATEs or DELETEs that use stream access, HP recommends
that you use range-partitioned tables to scale publish/subscribe applications.
Subscribers should use the partition key boundaries in their predicates so that they
access only one partition.
Subscriptions across partitions are supported and might be convenient to use.
However, these subscriptions involve two extra file system messages whenever a
cursor scan of each partition begins to block while waiting for more rows.
Considerations for Cursors
•
Use a WITH HOLD cursor.
Using publish/subscribe services without a WITH HOLD cursor can lower
performance. If WITH HOLD is not specified, you must reopen the cursor after
each transaction, which is time consuming. You might also need to reposition the
cursor, which can be time consuming and require complicated application logic.
•
Commit the transaction after each row or after a small set of rows.
Cursors that fetch from a stream should be committed after each row or after a
small set of rows. This practice releases locks and reduces the likelihood of a
transaction being aborted by the TMF autoabort feature.
•
Use STREAM_TIMEOUT to limit the transaction duration and release the locks.
Because there is no guarantee that a fetch will return soon, you should also use
the STREAM_TIMEOUT session attribute. Publish/subscribe services returns error
-8006 after the stream timeout period elapses, but this error leaves the cursor
open. You can commit your transaction and then begin a new transaction and fetch
again. The recommended timeout period is about 20 seconds. Make sure that the
STREAM_TIMEOUT period is less than the value specified for the TMF autoabort