September 8, 2010 at 3:15 am
Hi,
We have a data warehouse database on which we have set up transactional replication, which is running continuously. In the last few days we are experiencing a lot of performance problems, where distributor database lies. We have analyzed the situation and noticed that replication agent is constantly running procedure sp_MSget_repl_commands. Result is that our disks, where distribution database resides are constantly under heavy load, since this procedure continuously scans distribution database table MSrepl_commands. We currently have approximately 8M rows in that table (max retention is set to 72 hours). So it takes about 20 seconds to scan the table, then it pauses for a few seconds and starts scanning again.
I don't understand why this is happening. All transactions have been distributed to subscriber, so there is no need to do this scanning all the time. Does anyone one have any idea what could be the cause?
thanks in advance
September 8, 2010 at 7:46 am
is this a pull subscription?
The probability of survival is inversely proportional to the angle of arrival.
September 8, 2010 at 8:07 am
No, its a push subscription. Basically I don't think I understand how this kind of replicaton is functioning. My understandig was that replication agent should scan distribution database only if log-reader agent discovers that there are new transactions to be distributed. But in our case there are no new transactions to be distributed.
The solution would be to put max retention to 2-3 hours, because then distribution database would be very small and scanning would be noticable but I don't think this is wise.
September 8, 2010 at 8:15 am
you may have to. You haven't stated the transactional rate of your system and the average size of the transactions.
The probability of survival is inversely proportional to the angle of arrival.
September 8, 2010 at 8:30 am
Its roughly 2.5M transactions per day and average size is 400 bytes. Are you saying that this kind of behavior (scanning distribution database continuously) is normal?
September 8, 2010 at 8:40 am
is your snapshot agent running everytime? Check in the history of job for snapshot agent for that perticualr publisher.
Are you getting any error detail in replication monitor or msrepl_errors in distribution database?
----------
Ashish
September 8, 2010 at 8:58 am
No, snapshot agent is not scheduled to run, we start it only if we add article to subscription. Last time we have run it was two days ago, when we were already trying to fix this issue with distribution database scanning. If I look into msrepl_errors table I see that there was an error at that time: "Unable to complete login process due to delay in opening server connection"
But I don't think that this is the reason, since we noticed the problems more than a week ago.
regards,peter
September 8, 2010 at 11:05 am
2.5 million per day, that's 30 per second... spread evenly. More likely you have bursts of perhaps many more times that.
There is an upper limit in terms of TPS and Mbps Vs. the I/O capability of a given server where transactional replication will not be effective at keeping up and it would be more efficient to use other methodologies. I would have to look at your replication setup in more detail before I could make any more comment.
The probability of survival is inversely proportional to the angle of arrival.
September 10, 2010 at 6:44 am
The way this replication works can't be normal. No offence, but I find it hard to believe that replication can't manage more than 100 transactions per second, if that is so, its useless. Most larger systems have more than 100 transactions at a certain point of a day.
A day before yesterday we cleared distribution database (before that we checked if everything has been delivered to subscribers) and yesterday everything worked perfect. There were no scanning of distribution database after replicated transactions have been delivered. Today overnight, after new ETL job has started working, we discovered continuous scanning again. How come it worked ok the first day after the cleanup of distribution database and now its back to the old track?
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply