Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Scaling Out the Distribution Database Expand / Collapse
Author
Message
Posted Tuesday, March 30, 2010 10:22 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 12:44 PM
Points: 361, Visits: 866
RML51, the transaction retention period is used by the distribution cleanup agent to mark subscriptions as inactive if they are further behind than the retention period and to remove transactions that are older than the specified period. How long commands and transactions are retained after they've been delivered to all subscribers depends on the immediate_sync setting for the publication. If TRUE, transactions will be retained until they are older than the retention period; if FALSE they will be removed by the distribution cleanup agent the next time it runs after the transaction has been delivered to all subscribers.

Toby, I agree with you - David did a great job explaining the how but not the why. The way I see it, you're doing the same amount of IO whether or not you've got one distribution DB or ten. By setting up multiple distribution DBs you may even be adding IO overhead by making your disk heads jump around from one DB to another to read\write transactions and commands. Maybe I can see the logic in creating multiple distribution DBs if you are putting them on separate physical drives...but if you've got those drives available I'd just keep on distribution DB and spread it across as many spindles as you can to begin with.

When it comes to replication the idea is to keep the distribution database lean and mean. If you've got distribution DB growth issues I'd look at identifying the root cause first. Make sure immedite_sync is set to FALSE and that your distribution cleanup agent is running.

Chris Skorlinski from the Microsoft SQL Server Replication Support Team has two great articles about distribution DB growth that I recommend reading before considering the more invasive approach of creating multiple distribution DBs:
How to resolve when Distribution Database is growing huge (+25gig)
How Replication setting Immediate_sync may cause Transactional Replication Distribution database growth

FWIW I asked Chris about the author's suggestion and he indicated that he's never had a case where he found it necessary to create multiple distribution DBs to alleviate performance issues.


Kendal Van Dyke
http://kendalvandyke.blogspot.com/
Post #893018
Posted Tuesday, March 30, 2010 10:27 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 12:44 PM
Points: 361, Visits: 866
David.Poole (3/30/2010)
On one particular publication we changed the retention period to 3 hours because if something goes wrong with the subscriber then the transactions start building up in the distributor and if that comes under stress the publisher log files start to bloat until disk space on the production box is threatened.


David,
I'm curious - for those publications is immediate_sync set to TRUE? Or is it set to FALSE and you're just writing off any subscriber that falls further than 3 hours behind?


Kendal Van Dyke
http://kendalvandyke.blogspot.com/
Post #893025
Posted Tuesday, March 30, 2010 10:54 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, August 23, 2014 6:03 AM
Points: 351, Visits: 1,556

FWIW I asked Chris about the author's suggestion and he indicated that he's never had a case where he found it necessary to create multiple distribution DBs to alleviate performance issues.


Great discussion!

For what it's worth, I have seen scenarios where multiple Distribution databases provide a performance improvement.

For example, let’s say we have two Publications that form part of an application platform. One Publication is sourced from a very intensive OLTP database and another Publication has relatively moderate activity. Given that both Publications share the same Distributor database, the OLTP Publication could be responsible for 80% of the overall activity to the Distributor, thereby adversely affecting/constraining the performance of any other Publications that share the same Distributor.

By providing a resource intensive Publication its own dedicated Distributor database, you can isolate the performance from other Publications/Applications.

Who knew Replication could be such fun




John Sansom (@sqlBrit) | www.johnsansom.com
Post #893059
Posted Tuesday, March 30, 2010 11:19 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 12:44 PM
Points: 361, Visits: 866
For example, let’s say we have two Publications that form part of an application platform. One Publication is sourced from a very intensive OLTP database and another Publication has relatively moderate activity. Given that both Publications share the same Distributor database, the OLTP Publication could be responsible for 80% of the overall activity to the Distributor, thereby adversely affecting/constraining the performance of any other Publications that share the same Distributor.


Were the distribution databases on separate physical drives? I could see why that would help, but if they're on the same set of spindles I'm still not convinced that if your indexes and statistics are in good shape that having multiple databases offers a significant performance improvement. (I'll buy that it may offer a marginal improvement in some very specific circumstances)


Kendal Van Dyke
http://kendalvandyke.blogspot.com/
Post #893088
Posted Tuesday, March 30, 2010 12:31 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 1:51 PM
Points: 21,644, Visits: 15,317
Great article David.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #893174
Posted Tuesday, March 30, 2010 12:57 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 21, 2014 12:08 PM
Points: 1,414, Visits: 4,540
Kendal Van Dyke (3/30/2010)
David,
Can you elaborate on the following statement you made:
the number of records in the MSRepl_transactions table is likely to be very high and once it has gone beyond a certain size

and provide guidance on what that "certain size" is and how someone knows if they've exceeded it?

Also, do you have any suggestions for other steps that someone should take to optimize their existing distribution database before deciding that it's time to create a new one?

Kendal Van Dyke
@SQLDBA


we're having this problem. first thing is we're looking to upgrade to 32GB of RAM soon on the distributor. we're at 14GB now and seeing memory pressure sometimes.

otherwise best thing is to buy a lot of disk and split everything up on it's own RAID5 volumes. with HP servers, the Proliant G6 supports up to 16 drives in a server


https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #893193
Posted Tuesday, March 30, 2010 2:15 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 1:07 AM
Points: 2,901, Visits: 1,805
If you have everything hammering through one database you are talking about one transaction table having to deal with all transactions. Ditto the commands table.

Try reindexing a very large table. TEMPDB will be thrashed as well, if nothing else. I did find an article (which I can't find now I need it) that showed that indexes are less effective when they get more than 'n' levels deep.

Yes you can have the different distribution databases on different drives with obvious benefits but separate databases have at least got separate files. It's a myth that separate files get separate threads but you are dealing with far smaller files and each distribution database has fewer records to deal with.

We implemented the policy of distribution database per publisher policy and most of our replication woes just went away.

If you reindex a table then the table is locked briefly. What do you want to do, reindex a huge table locking it up for a relatively long length of time or a small table locking it briefly?


LinkedIn Profile
Newbie on www.simple-talk.com
Post #893292
Posted Tuesday, March 30, 2010 6:19 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 5:52 PM
Points: 2,270, Visits: 3,789
David,

Thanks for this wonderful article. Please share your comments.
1) Could we do this on SQL server 2000
2) Is there a way by which we can track at which point the distribution database is stuck and is unable to send the transactions to subscriber?


Mohammed Moinudheen
Post #893427
Posted Tuesday, March 30, 2010 11:09 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 12:44 PM
Points: 361, Visits: 866
David.Poole (3/30/2010)
If you have everything hammering through one database you are talking about one transaction table having to deal with all transactions. Ditto the commands table.


But if you have two, three, four, etc. distribution databases that doesn't change the number of commands and transactions that have to be written. The IO is still the same.

Yes you can have the different distribution databases on different drives with obvious benefits but separate databases have at least got separate files. It's a myth that separate files get separate threads but you are dealing with far smaller files and each distribution database has fewer records to deal with.


Yes, that is a myth. The CSS team has a great blog post about it here.

I think the discussion has more merit if we put some metrics out for comparison. My busiest distributor is a PowerEdge 2950 running SQL 2008 standard edition (x86) on Windows 2003 (x86) with 6 local drives (2 in RAID 1 for OS and transaction logs and the other 4 in RAID 10 (with 64KB partition offset, 64 KB block size, and 64 BK stripe size) for data) and 4 GB of RAM. It's responsible for 82 publications, 240 subscriptions, and based on distribution agent history for the last 48 hours it's pushing through just shy of 80 million commands per day. All distribution agents run continuously and there are rarely any latency issues that need to be dealt with.

How do those numbers and hardware compare to your distributor? (Please don't take this the wrong way - it's not a "prove it" tone I'm taking here...I'm just trying to understand how our environments compare to each other so I can better understand your reasoning for using multiple distribution DBs)


Kendal Van Dyke
http://kendalvandyke.blogspot.com/
Post #893483
Posted Wednesday, March 31, 2010 1:33 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 1:07 AM
Points: 2,901, Visits: 1,805
No offence taken.

We have four production distributors two of which are virtual servers.

We put roughly 24 billion transactions through them each day. We have spiked up to 370 billion transactions but this was exceptional.

On the subject of whether this works with SQL2000 distributors the short answer is "I don't know". Some of of publishers are SQL2000 but as the distributors have to be the highest version of SQL that are acting as publishers none of our distributors are SQL2000.


LinkedIn Profile
Newbie on www.simple-talk.com
Post #893522
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse