Data Replication with SQL Remote
PART 3. SQL Remote Administration
CHAPTER 10. SQL Remote Administration
Who needs to read this section? If performance is not a problem at your site, you do not need to read this section. |
There are several options you can use to tune the performance of the Message Agent. This section describes those options.
Sending messages and receiving messages are two separate processes. The major performance issues for these two processes are different.
Replication throughput The major bottleneck for total throughput of SQL Remote sites is generally receiving messages from many remote databases and applying them to the database at the consolidated site. You can control this step by tuning the receive process of the Message Agent at the consolidated site.
Replication turnaround The time lag from when data is entered at one site to when it appears at other sites is the turnaround time for replication. You can control this time lag.
Tuning throughput by controlling Message Agent threadingIt is assumed in this section that you are tuning the performance of a Message Agent that is running in continuous mode at a consolidated site.
Worker threads can be used by the Message Agent to apply incoming messages from remote users. This can improve throughput by allowing messages to be applied in parallel rather than serially.
The number of worker threads is set on the Message Agent command line, using the -w switch. The following command line starts the Message Agent for Adaptive Server Enterprise with twenty worker threads applying messages:
ssremote -c "eng=..." -w 20
The default is to use no worker threads, so that all messages are applied serially. The maximum number of worker threads is 50.
For the Message Agent for Adaptive Server Anywhere, the performance advantage will be most significant when the server is on a system with a striped drive array.
For Adaptive Server Enterprise, the Message Agent will benefit even more if the Server is used with multiple engines configured.
When worker threads are being used, messages from different remote users are applied in parallel. Messages from a single remote user are applied serially. For example, ten messages from a single remote user will be applied by a single worker thread in the correct order.
Deadlock is handled by re-applying the rolled back transaction at a later time.
Reading messages from the message system is single-threaded. Messages are read and the header information is examined (to determine the remote user and the correct order of application) before passing them off to worker threads to be applied.
Building messages and sending messages is single-threaded.
To use multiple worker threads with the Adaptive Server Enterprise Message Agent, you need to be using Open Client version 11.1 or above.
The Message Agent prints a message and then does not use worker threads when pre-11.1 versions are being used. The Open Client version is displayed in the first few lines of the Message Agent output.
Tuning throughput by caching messagesThe Message Agent caches incoming messages in a configurable area of memory as it reads them.
The size of the message cache is specified on the Message Agent command line, using the -m command-line switch.
The -m option specifies the maximum amount of memory to be used by the Message Agent for building messages. The allowed size can be specified as n (in bytes), nK, or nM. The default is 2048K (2M).
The following command line starts an Adaptive Server Anywhere Message Agent using twelve Megabytes of memory as a message cache:
dbremote -c "eng=..." -m 12M
When transactions are large, or messages arrive out of order, they are stored in memory by the Message Agent until the message is to be applied. This caching of messages prevents rereading of out-of-order messages from the message system , which may lower performance on large installations. It is especially important when messages are being read over a WAN (such as Remote Access Services or POP3 through a modem). It also avoids contention between worker threads reading messages (a single threaded task) because the message contents are cached.
When the memory usage specified using the -m switch is exceeded, messages are flushed in a least-recently-used fashion.
This switch is provided primarily for customers considering a single consolidated database for thousands of remote databases.
Tuning incoming message pollingWhen running a Message Agent in continuous mode, typically at a consolidated database site, you can control how often it polls for incoming messages, and how "patient" it is in waiting for messages that arrive out of order before requesting that the message be resent. Tuning these aspects of the behavior can have a significant effect on performance in some circumstances.
The issues to consider when tuning the message-receiving process are similar to those when tuning the message-sending process.
Regular messages Your choices dictate how often the Message Agent polls for incoming messages from remote databases.
Resend requests You can control how many polls to wait until an out-of-order message arrives, before requesting that it be resent.
Processing incoming messages If your polling period for incoming messages is too long, compared to the frequency with which messages are arriving, you could end up with messages sitting in the queue, waiting to be processed. If your polling period is too short, you will waster resources polling when no messages are in the queue.
For more information on the message sending process, see Tuning the message sending process.
Polling intervalBy default, a Message Agent running in continuous mode polls one minute after finishing the previous poll, to see whether new messages have arrived. You can configure the polling interval using the -rd command-line option.
The default polling interval from the end of one poll to the start of another is one minute. You can poll more frequently using a value in seconds, as in the following command line:
dbremote -rd 30s
Alternatively, you can poll less frequently, as in the following command line, which polls every five minutes:
dbremote -rd 5
Setting a very small interval may have some detrimental impact on overall system throughput, for the following reasons:
Each poll of the mail server (if you are using e-mail) places a load on your message system. Too-frequent polling may affect your message system and produce no benefits.
If you do not modify the Message Agent patience before it assumes that an out of sequence message is lost, and requests it be sent again, you can flood your system with resend requests.
In general, you should not use a very small polling interval unless you have a specific reason for requiring a very quick response time for messages.
Setting larger intervals may provide a better overall throughput of messages in your system, at the cost of waiting somewhat longer for each message to be applied. In many SQL Remote installations, optimizing turnaround time is not the primary concern.
Requesting resendsIf, when the Message Agent polls for incoming messages, one message is missing from a sequence, the Message Agent does not immediately request that the message be resent. Instead, it has a default patience of one poll.
If the next message expected is number 6 and message 7 is found, the Message Agent takes no action until the next poll. Then, if no new message for that user is found, it issues a resend request.
You can change the number of polls for which the Message Agent waits before sending a request using the -rp command-line option. This option is often used in conjunction with the -rd option that sets the polling interval.
For example, if you have a very small polling interval, and a message system that does not preserver the order in which messages arrive, it may be very common for out-of-sync messages to arrive only after two or three polls have been completed. In such a case, you should instruct the Message Agent to be more patient before sending a resend request, by increasing the -rp value. If you do not do this, a large number of unnecessary resend requests may be sent.
ExampleSuppose there are two remote users, named user1 and user2, and suppose the Message Agent command line is as follows:
dbremote -rd 30s -rp 3
In the following sequence of operations, messages are marked as userX.n so that user1.5 is the fifth message from user1. The Message Agent expects messages to start at number 1 for both users.
At time 0 seconds:
The Message Agent reads user1.1, user2.4
The Message Agent applies user1.1
The Message Agent patience is now user1: N/A, user2: 3, as an out of sequence message has arrived from user 2.
At time 30 seconds:
The Message Agent reads: no new messages
The Message Agent applies: none
The Message Agent patience is now user1: N/A, user2: 2
At time 60 seconds:
The Message Agent reads: user1.3
The Message Agent applies: no new messages
The Message Agent patience: user1: 3, user2: 1
At time 90 seconds:
The Message Agent reads: user1.4
The Message Agent applies: none
The Message Agent patience user1: 3, user2: 0
The Message Agent issues resend to user2.
When a user receives a new message, it resets the Message Agent patience even if that message is not the one expected.
Tuning the message sending processThe turnaround time for replication is governed by how often each sites sends messages and how often each site polls for incoming messages. To achieve a small time lag between data entry and data replication, you can set a small value for the -sd Message Agent command-line option, which controls the frequency for polling to see if more data needs to be sent.
The issues to consider when tuning the message-sending process are similar to those when tuning the incoming-message polling frequency:
Regular messages Your choices dictate how often updates are sent to remote databases.
Resend requests When a remote user requests that a message be resent, the Message Agent needs to take special action that can interrupt regular message sending. You can control the urgency with which these resend requests are processed.
Number and size of messages If you send messages very frequently, there is more chance of small messages being sent. Sending messages less frequently allows more instructions to be grouped in a single message. If a large number of small messages is a concern for your message system, then you may have to avoid using very small polling periods.
For more information on tuning polling for the incoming-messages, see Tuning incoming message polling.
Polling intervalYou control the interval to wait between polls for more data from the transaction log to send using the -sd command-line option, which has a default of one minute. The following example sets the polling interval to 30 seconds:
dbremote -sd 30s ...
Alternatively, you can poll less frequently, as in the following command line, which polls every five minutes:
dbremote -sd 5
Setting a very small interval may have some detrimental impact on overall system throughput, for the following reasons:
Too-frequent polling produces many short messages. If the message load places a strain on your message system, throughput could be affected.
Setting larger intervals may provide a better overall throughput of messages in your system, at the cost of waiting somewhat longer for each message to be applied. In many SQL Remote installations, optimizing turnaround time is not the primary concern.
Resending messagesWhen a user requests that a message be resent, the message has to be retrieved from early in the transaction log. Going back in the transaction log to retrieve this message and send it causes the Message Agent to interrupt the regular sending process. If you are tuning your SQL Remote installation for optimum performance, you must balance the urgency of sending requests for resent messages with the priority of processing regular messages.
The -ru command-line option controls the urgency of the resend requests. The value for the parameter is a time in minutes (or in other units if you add s or h to the end of the number), with a default of zero.
To help the Message Agent delay processing resend requests until more have arrived before interrupting the regular message sending activity, set this option to a longer time.
The following command line waits one hour until processing a resend request.
dbremote -ru 1h ...