我们的应用程序通过VPN隧道连接到Web服务器上的MySQL数据库。 问题是，由于网络延迟，应用程序和远程数据库之间的所有交互都非常缓慢。 我使用sysbench测试了网络上的MySQL性能，结果显示每个查询花了将近1秒钟。 从应用程序更新DB中的300多行需要20多分钟。
如何在不将服务器移近客户端的情况下改善网络上的数据库性能？ 通过慢速网络进行数据库查询的最佳做法是什么？
We have the application that connects to a MySQL DB on the web-server through VPN tunnel. The problem is, all interactions between the application and the remote DB are extremely slow because of network latency. I tested the MySQL performance over the network using sysbench and it showed that each query takes almost 1 second. Updating 300+ lines in DB from application takes 20+ minutes.
How can the DB performance over network be improved without moving the server closer to the client? What is the best practice for DB queries over slow networks?
最满意答案
如果问题是网络延迟（而不是网络带宽），那么解决方案是将尽可能多的代码移动到数据库中。
也就是说，设置存储过程来完成工作，而不是逐行编写代码。 实质上，存储过程处理多行代码，只有一次往返数据库的开销。
这需要智能设置，特别是如果您必须将数据传递到存储过程中。 考虑以下两种做同样事情的方法：
insert into X(a) values ('a'); insert into X(a) values ('b'); insert into X(a) values ('c');和
insert into X(a) select 'a' union all select 'b' union all select 'c';第一次需要三次往返，因为有三个独立的声明，第二次只有一次往返。 在以延迟为主的环境中，第一个将比第二个环境长三倍。
If the problem is network latency (rather than network bandwidth), then the solution is to move as much code into the database as possible.
That is, set up stored procedures to do the work rather than writing code line-by-line. The stored procedure, in essence, handles multiple lines of code with the overhead of only one round-trip to the database.
This requires intelligent set up, particularly if you have to pass data into the stored procedures. Consider the following two ways of doing the same thing:
insert into X(a) values ('a'); insert into X(a) values ('b'); insert into X(a) values ('c');and
insert into X(a) select 'a' union all select 'b' union all select 'c';The first requires three round trips because there are three independent statements, the second only one round trip. In a latency dominated environment, the first will be about three times longer than the second.
Mysql和网络延迟(Mysql and network latency)我们的应用程序通过VPN隧道连接到Web服务器上的MySQL数据库。 问题是，由于网络延迟，应用程序和远程数据库之间的所有交互都非常缓慢。 我使用sysbench测试了网络上的MySQL性能，结果显示每个查询花了将近1秒钟。 从应用程序更新DB中的300多行需要20多分钟。
如何在不将服务器移近客户端的情况下改善网络上的数据库性能？ 通过慢速网络进行数据库查询的最佳做法是什么？
We have the application that connects to a MySQL DB on the web-server through VPN tunnel. The problem is, all interactions between the application and the remote DB are extremely slow because of network latency. I tested the MySQL performance over the network using sysbench and it showed that each query takes almost 1 second. Updating 300+ lines in DB from application takes 20+ minutes.
How can the DB performance over network be improved without moving the server closer to the client? What is the best practice for DB queries over slow networks?
最满意答案
如果问题是网络延迟（而不是网络带宽），那么解决方案是将尽可能多的代码移动到数据库中。
也就是说，设置存储过程来完成工作，而不是逐行编写代码。 实质上，存储过程处理多行代码，只有一次往返数据库的开销。
这需要智能设置，特别是如果您必须将数据传递到存储过程中。 考虑以下两种做同样事情的方法：
insert into X(a) values ('a'); insert into X(a) values ('b'); insert into X(a) values ('c');和
insert into X(a) select 'a' union all select 'b' union all select 'c';第一次需要三次往返，因为有三个独立的声明，第二次只有一次往返。 在以延迟为主的环境中，第一个将比第二个环境长三倍。
If the problem is network latency (rather than network bandwidth), then the solution is to move as much code into the database as possible.
That is, set up stored procedures to do the work rather than writing code line-by-line. The stored procedure, in essence, handles multiple lines of code with the overhead of only one round-trip to the database.
This requires intelligent set up, particularly if you have to pass data into the stored procedures. Consider the following two ways of doing the same thing:
insert into X(a) values ('a'); insert into X(a) values ('b'); insert into X(a) values ('c');and
insert into X(a) select 'a' union all select 'b' union all select 'c';The first requires three round trips because there are three independent statements, the second only one round trip. In a latency dominated environment, the first will be about three times longer than the second.
发布评论