设置Distribution clean up 每次删除Command的数量-阿里云开发者社区

开发者社区> 长征6号> 正文

设置Distribution clean up 每次删除Command的数量

简介:
+关注继续查看

Replication Job “Distribution clean up: distribution” 默认设置是,每10minutes运行一次,每次删除2000个Command。这对于有1.9亿条Commands的distribution来说,显得力不从心。需要修改 sp [distribution].[dbo].[sp_MSdelete_publisherdb_trans],重新设置每次删除的Commands 数量,我的设置是每次删除20000 command。

设置的过程比较简单,在PROCEDURE [dbo].[sp_MSdelete_publisherdb_trans]中,查找2000,替换为 20000,需要修改三个地方

1, DELETE TOP(20000) MSrepl_commands WITH (PAGLOCK)

复制代码
    WHILE 1 = 1
    BEGIN
        DELETE TOP(20000) MSrepl_commands WITH (PAGLOCK) from MSrepl_commands with (INDEX(ucMSrepl_commands))
            WHERE publisher_database_id = @publisher_database_id 
                AND xact_seqno IN (SELECT DISTINCT snap_xact_seqno 
                                    FROM @snapshot_xact_seqno)
            OPTION (MAXDOP 1)

        SELECT @row_count = @@rowcount

        -- Update output parameter
        SELECT @num_commands = @num_commands + @row_count
    
        IF @row_count < 20000 -- passed the result set.  We're done
            BREAK
    END
复制代码

2,DELETE TOP(20000) MSrepl_commands WITH (PAGLOCK)

复制代码
WHILE 1 = 1
    BEGIN
        if @has_immediate_sync = 0
            DELETE TOP(20000) MSrepl_commands WITH (PAGLOCK) from MSrepl_commands with (INDEX(ucMSrepl_commands)) where
                publisher_database_id = @publisher_database_id and
                xact_seqno <= @max_xact_seqno and
                (type & ~@snapshot_bit) not in (@directory_type, @alt_directory_type) and
                (type & ~@replpost_bit) <> @scriptexec_type
                OPTION (MAXDOP 1)
        else
            -- Use nolock hint on subscription table to avoid deadlock
            -- with snapshot agent.
            DELETE TOP(20000) MSrepl_commands WITH (PAGLOCK) from MSrepl_commands with (INDEX(ucMSrepl_commands)) where
                publisher_database_id = @publisher_database_id and
                xact_seqno <= @max_xact_seqno and
                -- do not delete directory, alt directory or script exec commands. they are deleted 
                -- above. We have to do this because we use a (nolock) hint and we have to make sure we 
                -- don't delete dir commands when the file has not been cleaned up in the code above. It's
                -- ok to delete snap commands that are out of retention and perform lazy delete of dir
                (type & ~@snapshot_bit) not in (@directory_type, @alt_directory_type) and
                (type & ~@replpost_bit) <> @scriptexec_type and
                (
                    -- Select the row if it is older than max retention.
                    xact_seqno <= @max_immediate_sync_seqno or 
                    -- Select the snap cmd if it is not for immediate_sync article
                    -- We know the command is for immediate_sync publication if
                    -- the snapshot tran include articles that has virtual
                    -- subscritptions. (use subscritpion table to avoid join with
                    -- article and publication table). We skip sync tokens because 
                    -- they are never pointed to by subscriptions...
                    (
                        (type & @snapshot_bit) <> 0 and
                        (type & ~@snapshot_bit) not in (@syncinit, @syncdone) and
                        not exists (select * from MSsubscriptions s with (nolock) where
                            s.publisher_database_id = @publisher_database_id and
                            s.article_id = MSrepl_commands.article_id and
                            s.subscriber_id < 0)
                    )
                )
                OPTION (MAXDOP 1)

        select @row_count = @@rowcount
        -- Update output parameter
        select @num_commands = @num_commands + @row_count
    
        IF @row_count < 20000 -- passed the result set.  We're done
            BREAK
    END
复制代码

3,使用Script 查看Command的分布图

复制代码
USE distribution
GO

SELECT
    T.[publisher_database_id],
    DATEPART(mm, [entry_time]) 'month',
    DATEPART(dd, [entry_time]) 'day',
    DATEPART(hh, [entry_time]) 'hour',
    COUNT(C.[xact_seqno]) 'count of commands'
FROM [dbo].[MSrepl_transactions](nolock) T
INNER JOIN [dbo].[MSrepl_commands](nolock) C
    ON T.[xact_seqno] = C.[xact_seqno]
        and T.publisher_database_id=c.publisher_database_id
GROUP BY    T.[publisher_database_id],
            DATEPART(mm, [entry_time]),
            DATEPART(dd, [entry_time]),
            DATEPART(hh, [entry_time])
ORDER BY 1, 2, 3, 4
复制代码

附上本机的查询结果

 

引用文档《 How to resolve when Distribution Database is growing huge (+25gig)

Yes, I know, huge database is kind of relative, but generally if you see Distribution database growing more the 25gig it means the Cleanup processes is having a hard time deleting replicated transactions.  I’ll cover the how and why on Cleanup processes later, but for now I wanted to post a technique we’ve used to purge rows from the Distribution database.  This solution involves modifying the SQL Replication stored procedures to increase the number or rows being deleted per transaction.  If you’re uncomfortable making the code change, skip down to STEP 7).

This first posting coverage a “conservative” approach.  Later I’m post steps for a more “aggressive” solution.

1) script msrepl_commands cleanup proc and save original sp code

sp_helptext  sp_MSdelete_publisherdb_trans

2) change from CREATE to ALTER

ALTER PROCEDURE sp_MSdelete_publisherdb_trans

3) change all 3 locations from 2000 to 100000 rows

DELETE TOP(2000) MSrepl_commands . . .

4) script msrepl_transaction cleanup proc and save original sp code

sp_helptext sp_MSdelete_dodelete

5) change from CREATE to ALTER

ALTER PROCEDURE sp_MSdelete_dodelete

6) change both locations from 5000 to 100000 rows

delete TOP(5000) MSrepl_transactions . . .

7) Determine oldest day containing transactions

复制代码
USE distribution
GO

SELECT
    T.[publisher_database_id],
    DATEPART(mm, [entry_time]) 'month',
    DATEPART(dd, [entry_time]) 'day',
    DATEPART(hh, [entry_time]) 'hour',
    COUNT(C.[xact_seqno]) 'count of commands'
FROM [dbo].[MSrepl_transactions](nolock) T
INNER JOIN [dbo].[MSrepl_commands](nolock) C
    ON T.[xact_seqno] = C.[xact_seqno]
GROUP BY    T.[publisher_database_id],
            DATEPART(mm, [entry_time]),
            DATEPART(dd, [entry_time]),
            DATEPART(hh, [entry_time])
ORDER BY 1, 2, 3, 4
复制代码

8) Execute cleanup via SSMS or a TSQL job to delete JUST oldest day.  (24 hours @ 5 days = 120), then continue to reduce the @max_distretention valued by a few hours for each run.

   EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 0, @max_distretention = 120 

Example output: (4 hours to removed 340million rows)

Removed 3493 replicated transactions consisting of 343877158 statements in 15043 seconds (22859 rows/sec).

作者悦光阴
本文版权归作者和博客园所有,欢迎转载,但未经作者同意,必须保留此段声明,且在文章页面醒目位置显示原文连接,否则保留追究法律责任的权利。
分类: Replication






本文转自悦光阴博客园博客,原文链接:http://www.cnblogs.com/ljhdo/p/5161590.html,如需转载请自行联系原作者

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
图片设置level-list,根据不同状态显示不同图片
前言:工作需求,wifi图标根据不同的强度,显示不同的状态.于是想到用图层的方法 bg.xml note:android:maxLevel 必须从0递增,顺序错误后只会显示第一张图片 布局文件 代码中使用 Le...
637 0
Android TextView中文字通过SpannableString来设置超链接、颜色、字体等属性
在Android中,TextView是我们最常用的用来显示文本的控件。 <p>  一般情况下,TextView中的文本都是一个样式。那么如何对于TextView中各个部分的文本来设置字体,大小,颜色,样式,以及超级链接等属性呢?下面我们通过SpannableString的具体实例操作来演示一下。</p> <p><br></p> <p>   res-layout-main.xml</p
1244 0
PostgreSQL 10.1 手册_部分 III. 服务器管理_第 31 章 逻辑复制_31.8. 配置设置
31.8. 配置设置 逻辑复制需要设置几个配置选项。 在发布者端,必须将wal_level设置为logical, 并且max_replication_slots必须至少设置为预期连接的订阅数量, 加上一些预留用于表同步。
1067 0
PostgreSQL 10.1 手册_部分 III. 服务器管理_第 27 章 恢复配置_27.3. 后备服务器设置
27.3. 后备服务器设置 standby_mode (boolean) 指定是否将PostgreSQL服务器作为一个后备服务器启动。如果这个参数为on,当到达已归档 WAL 末尾时该服务器将不会停止恢复,但是将通过使用restore_command获得新的 WAL 段以及/或者通过使用primary_conninfo设置连接到主服务器来尝试继续恢复。
1383 0
Direct2D教程II——绘制基本图形和线型(StrokeStyle)的设置详解
目前,在博客园上,相对写得比较好的两个关于Direct2D的教程系列,分别是万一的Direct2D系列和zdd的Direct2D系列。有兴趣的网友可以去看看。本系列也是介绍Direct2D的教程,是基于Windows API Code Pack 1.1的Direct2D的教程,如果要调试文中的代码的话,还得参考前文 Direct2D教程I——简介及首个例子 下载导入Windows API Code Pack 1.1的动态库   在前文 Direct2D教程I——简介及首个例子 简单介绍了Direct2D,并给了一个简单的示例。
716 0
+关注
1224
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载