Mysql Tutorial

SQLSERVER复制优化之一《改变包大小》

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 03:20 PM

sqlserveroptimizationcopy

SQLSERVER 复制优化之一《改变包大小》自从搭了复制之后以为可以安枕无忧了，谁不知问题接踵而来这次遇到的问题是丢包，不知道情况的读者可以先看一下我之前写的一篇《SQLSERVER监控复制并使用数据库邮件功能发告警邮件》因为机房C和机房A不

SQLSERVER复制优化之一《改变包大小》

自从搭了复制之后以为可以安枕无忧了，谁不知问题接踵而来

这次遇到的问题是丢包，不知道情况的读者可以先看一下我之前写的一篇《SQLSERVER监控复制并使用数据库邮件功能发告警邮件》

因为机房C和机房A不在一个局域网，网络状况不是太好

分发积压的命令经常处于20W+条，复制并没有报错，每次传递的事务都是少于30个，正常来讲SQLSERVER默认每次会传输100个事务

后来测试了一下网络情况

从分发服务器ping一下订阅服务器，ping 4096Byte大小的包，ping100次，因为分发默认传输的包大小是4096Byte，中间偶尔会超时

SQLSERVER复制优化之一《改变包大小》

ping  订阅服务器ip  <span>-</span>l <span>4096</span> <span>-</span>n <span>100</span>

参数l是指包的大小，参数n是指ping的次数，不加 -l 参数的话默认ping的包大小为32Byte

SQLSERVER复制优化之一《改变包大小》

100个包有21个丢包

ping  订阅服务器ip  <span>-</span>l <span>1024</span> <span>-</span>n <span>100</span>

SQLSERVER复制优化之一《改变包大小》

100个包有5个丢包

后来又继续ping 512Byte 、256Byte、128Byte大小的包，发现越小的包，丢包率就越低

从当前的网络测试情况来看，需要调整一下分发代理的包大小，在分发代理配置文件里有一个参数 -PacketSize packet_size

这个参数是设置分发代理发送到订阅服务器的包大小的。

环境：发布和分发都在同一台机器

设置

我们将分发代理的包大小设置1024Byte，那么怎么设置呢？

有两种方法：

方法一：修改分发agent 的作业

（1）打开分发代理作业

SQLSERVER复制优化之一《改变包大小》

（2）转到步骤

SQLSERVER复制优化之一《改变包大小》

（3）双击“运行代理”，然后添加参数 -PacketSize 1024 ，点击确定，退出作业属性

SQLSERVER复制优化之一《改变包大小》

（4）停止分发代理作业

SQLSERVER复制优化之一《改变包大小》

（5）开始分发代理作业

SQLSERVER复制优化之一《改变包大小》

这样设置过后，分发代理就会以新的参数运行

但是代理配置文件是看不出来当前分发代理的运行参数的，还是显示4096Byte

SQLSERVER复制优化之一《改变包大小》

方法二：新建一个代理配置文件，然后直接修改参数

（1）默认的代理配置文件是修改不了的

SQLSERVER复制优化之一《改变包大小》

（2）新建一个代理配置文件

SQLSERVER复制优化之一《改变包大小》

（3）选择默认代理配置文件（新代理的默认值）

SQLSERVER复制优化之一《改变包大小》

（4）输入配置文件名：testprofile，把“仅显示此配置文件的参数”的勾去掉，修改-PacketSize参数为1024，然后点击确定

SQLSERVER复制优化之一《改变包大小》

（5）勾选testprofile，然后点击确定

SQLSERVER复制优化之一《改变包大小》

（6）跟分发作业一样，点击“停止分发代理”，然后点击“启动分发代理”，使设置生效

SQLSERVER复制优化之一《改变包大小》

验证

那么我怎么知道究竟当前分发代理是否使用1024Byte大小的包来传送呢？

这时候可以借助Microsoft Process Monitor 3.10这个工具

在发布端使用这个工具来监测一下

复制分发代理
复制分发代理是一个可执行文件，它能将快照（对于快照复制和事务复制）和保存在分发数据库表中的事务（对于事务复制）移动到订阅服务器上的目标表中。

若要启动分发代理，请从命令提示符下执行 distrib.exe

打开任务管理器，查看分发代理进程的进程ID（PID），然后打开Microsoft Process Monitor 3.10，设置筛选条件

使用process monitor来监控分发代理传输的包大小

SQLSERVER复制优化之一《改变包大小》

看一下length，最大的包也不会超过1024，说明设置生效了

SQLSERVER复制优化之一《改变包大小》

未分发命令降下来了

SQLSERVER复制优化之一《改变包大小》

注意：

当你的服务器中当前多个数据库是做了复制的，一个数据库只有一个logread进程，多个数据库就对应多个logread进程

分发代理也是，一个数据库可以有多个分发代理，每个分发代理对应他们各自的进程（distrib进程）

所以一定要看清楚，你当前查看的distrib进程是不是你刚才设置的那个分发代理

SQLSERVER复制优化之一《改变包大小》

总结

这篇文章只是阐述了复制的过程当中出现问题的其中一个原因，当然还有很多原因，例如发布库的日志文件VLF太多等等。。。

在分析的时候一定要多个角度分析，不能死磕一个方面，因为复制涉及到的方面比较多

有可能是发布端的问题，有可能是分发端的问题，也有可能是订阅端的问题

感谢菠萝兄的热心帮助o(∩_∩)o

如有不对的地方，欢迎大家拍砖o(∩_∩)o

Statement

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

How does MySQL index cardinality affect query performance?Apr 14, 2025 am 12:18 AM

MySQL index cardinality has a significant impact on query performance: 1. High cardinality index can more effectively narrow the data range and improve query efficiency; 2. Low cardinality index may lead to full table scanning and reduce query performance; 3. In joint index, high cardinality sequences should be placed in front to optimize query.

MySQL: Resources and Tutorials for New UsersApr 14, 2025 am 12:16 AM

The MySQL learning path includes basic knowledge, core concepts, usage examples, and optimization techniques. 1) Understand basic concepts such as tables, rows, columns, and SQL queries. 2) Learn the definition, working principles and advantages of MySQL. 3) Master basic CRUD operations and advanced usage, such as indexes and stored procedures. 4) Familiar with common error debugging and performance optimization suggestions, such as rational use of indexes and optimization queries. Through these steps, you will have a full grasp of the use and optimization of MySQL.

Real-World MySQL: Examples and Use CasesApr 14, 2025 am 12:15 AM

MySQL's real-world applications include basic database design and complex query optimization. 1) Basic usage: used to store and manage user data, such as inserting, querying, updating and deleting user information. 2) Advanced usage: Handle complex business logic, such as order and inventory management of e-commerce platforms. 3) Performance optimization: Improve performance by rationally using indexes, partition tables and query caches.

SQL Commands in MySQL: Practical ExamplesApr 14, 2025 am 12:09 AM

SQL commands in MySQL can be divided into categories such as DDL, DML, DQL, DCL, etc., and are used to create, modify, delete databases and tables, insert, update, delete data, and perform complex query operations. 1. Basic usage includes CREATETABLE creation table, INSERTINTO insert data, and SELECT query data. 2. Advanced usage involves JOIN for table joins, subqueries and GROUPBY for data aggregation. 3. Common errors such as syntax errors, data type mismatch and permission problems can be debugged through syntax checking, data type conversion and permission management. 4. Performance optimization suggestions include using indexes, avoiding full table scanning, optimizing JOIN operations and using transactions to ensure data consistency.

How does InnoDB handle ACID compliance?Apr 14, 2025 am 12:03 AM

InnoDB achieves atomicity through undolog, consistency and isolation through locking mechanism and MVCC, and persistence through redolog. 1) Atomicity: Use undolog to record the original data to ensure that the transaction can be rolled back. 2) Consistency: Ensure the data consistency through row-level locking and MVCC. 3) Isolation: Supports multiple isolation levels, and REPEATABLEREAD is used by default. 4) Persistence: Use redolog to record modifications to ensure that data is saved for a long time.

MySQL's Place: Databases and ProgrammingApr 13, 2025 am 12:18 AM

MySQL's position in databases and programming is very important. It is an open source relational database management system that is widely used in various application scenarios. 1) MySQL provides efficient data storage, organization and retrieval functions, supporting Web, mobile and enterprise-level systems. 2) It uses a client-server architecture, supports multiple storage engines and index optimization. 3) Basic usages include creating tables and inserting data, and advanced usages involve multi-table JOINs and complex queries. 4) Frequently asked questions such as SQL syntax errors and performance issues can be debugged through the EXPLAIN command and slow query log. 5) Performance optimization methods include rational use of indexes, optimized query and use of caches. Best practices include using transactions and PreparedStatemen

MySQL: From Small Businesses to Large EnterprisesApr 13, 2025 am 12:17 AM

MySQL is suitable for small and large enterprises. 1) Small businesses can use MySQL for basic data management, such as storing customer information. 2) Large enterprises can use MySQL to process massive data and complex business logic to optimize query performance and transaction processing.

What are phantom reads and how does InnoDB prevent them (Next-Key Locking)?Apr 13, 2025 am 12:16 AM

InnoDB effectively prevents phantom reading through Next-KeyLocking mechanism. 1) Next-KeyLocking combines row lock and gap lock to lock records and their gaps to prevent new records from being inserted. 2) In practical applications, by optimizing query and adjusting isolation levels, lock competition can be reduced and concurrency performance can be improved.

See all articles