mysql从数据库问题
目前我们有3个从数据库，
但几乎总是有一个比其他数据库慢得多（可能比主数据库慢一个小时）
有人遇到类似的问题吗？可能是什么原因？
Currently we have 3 slave databases,
but almost always there is one of them extremly slow than others(can be an hour after master database)
Has any one met similar problem?What may be the cause?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我猜想其他一些进程正在与慢速副本相同的主机上运行，并且它正在占用资源。
尝试运行“top”（或使用 Nagios 或 Cactus 等）来监视三个副本主机上的系统性能，并查看是否有任何可以观察到的趋势。 CPU 利用率受到 mysqld 之外的另一个进程的影响，或者 I/O 不断饱和，诸如此类的事情。
更新：阅读 MySQL 性能专家 Peter Zaitsev 的以下两篇文章：
作者指出复制是单线程的，副本按顺序执行查询，而不是像在主服务器上那样并行执行查询。 因此，如果您有一些运行时间非常长的复制查询，它们可能会“阻塞队列”。
他建议补救措施是简化长时间运行的 SQL 查询，以便它们运行得更快。 例如：
如果您有一个影响数百万行的 UPDATE，请将其分解为多个作用于行子集的 UPDATE。
如果您将复杂的 SELECT 语句合并到 UPDATE 或 INSERT 查询中，请将 SELECT 分离到其自己的语句中，在应用程序代码中生成一组文字值，然后对这些值运行 UPDATE 或 INSERT。 当然，SELECT 不会被复制，副本只会看到带有文字值的 UPDATE/INSERT。
如果您正在运行一个长时间运行的批处理作业，它可能会阻止在副本上执行其他更新。 您可以将一些睡眠放入批处理作业中，甚至可以编写批处理作业来定期检查复制延迟，并在需要时进行睡眠。
I'd guess some other process is running on the same host as the slow replica, and it's hogging resources.
Try running "top" (or use Nagios or Cactus or something) to monitor system performance on the three replica hosts and see if there are any trends you can observe. CPU utilization pegged by another process besides mysqld, or I/O constantly saturated, that sort of thing.
update: Read the following two articles by MySQL performance expert Peter Zaitsev:
The author points out that replication is single-threaded, and the replica executes queries sequentially, instead of in parallel as they were executed on the master. So if you have a few replicated queries that are very long-running, they can "hold up the queue."
He suggests the remedy is to simplify long-running SQL queries so they run more quickly. For example:
If you have an UPDATE that affects millions of rows, break it up into multiple UPDATEs that act on a subset of the rows.
If you have complex SELECT statements incorporated into your UPDATE or INSERT queries, separate the SELECT into its own statement, generate a set of literal values in application code, and then run your UPDATE or INSERT on these. Of course the SELECT won't be replicated, the replica will only see the UPDATE/INSERT with literal values.
If you have a long-running batch job running, it could be blocking other updates from executing on the replica. You can put some sleeps into the batch job, or even write the batch job to check the replication lag at intervals and sleep if needed.
所有从属服务器都位于同一位置吗？ 就我而言，其中一台从服务器位于另一个位置，这是一个网络问题。
Are all the slave servers located in the same location? In my case, one of the slave server was located in another location and it was a network issue.