远程连接mysql写入速度很慢

seesea2517

回复 10# netmarshal

但是web服务器向mysql数据库写入数据的时候。速度非常慢。2M的文件大约需要30分钟才能写入？

你测试的是同样的数据吗？如果同样的数据在web执行和mysql后台执行不一样的话，那就要考虑web相关服务的问题了。不会web服务每插入一个数据都是重新连接数据库的吧？

cyberstan

回复 9# netmarshal

show profile是由Jeremy Cole捐献给MySQL社区版本的。默认的是关闭的，但是会话级别可以开启这个功能。开启它可以让MySQL收集在执行语句的时候所使用的资源。为了统计报表,把profiling设为1

mysql> SET profiling = 1;

之后在运行一个查询
mysql> SELECT COUNT(DISTINCT actor.first_name) AS cnt_name, COUNT(*) AS cnt
-> FROM sakila.film_actor
-> INNER JOIN sakila.actor USING(actor_id)
-> GROUP BY sakila.film_actor.film_id
-> ORDER BY cnt_name DESC;
...
997 rows in set (0.03 sec)

这个执行语句的剖析信息存储在这个会话中。使用SHOW PROFILES进行查看。
mysql> SHOW PROFILES\G
*************************** 1. row ***************************
Query_ID: 1
Duration: 0.02596900
Query: SELECT COUNT(DISTINCT actor.first_name) AS cnt_name,...

你可以使用SHOW PROFILE语句来获取已经存储的剖析数据。如果不加参数，会显示状态以及它们持续的时间。
mysql> SHOW PROFILE;
+------------------------+-----------+
| Status | Duration |
+------------------------+-----------+
| (initialization) | 0.000005 |
| Opening tables | 0.000033 |
| System lock | 0.000037 |
| Table lock | 0.000024 |
| init | 0.000079 |
| optimizing | 0.000024 |
| statistics | 0.000079 |
| preparing | 0.00003 |
| Creating tmp table | 0.000124 |
| executing | 0.000008 |
| Copying to tmp table | 0.010048 |
| Creating sort index | 0.004769 |
| Copying to group table | 0.0084880 |
| Sorting result | 0.001136 |
| Sending data | 0.000925 |
| end | 0.00001 |
| removing tmp table | 0.00004 |
| end | 0.000005 |
| removing tmp table | 0.00001 |
| end | 0.000011 |
| query end | 0.00001 |
| freeing items | 0.000025 |
| removing tmp table | 0.00001 |
| freeing items | 0.000016 |
| closing tables | 0.000017 |
| logging slow query | 0.000006 |
+------------------------+-----------+

每行都是状态变化的过程以及它们持续的时间。Status那一列和SHOW FULL PROCESSLIST的State应该是一致的。
这个值是来自于thd->proc_info变量。因此你所查看的这个值是直接来自MySQL内部的。尽管这些数值是比较直接易懂的，你也可以查看MySQL手册。

你可以给SHOW PROFILES指定一个Query_ID来查看指定的语句，还可以给输出添加新的列。如，查看用户和CPU使用。可以用如下命令。

mysql> SHOW PROFILE CPU FOR QUERY 1;

SHOW PROFILE可以深入的查看服务器执行语句的工作情况。以及也能帮助你理解执行语句消耗时间的情况。一些限制是它没有实现的功能，不能查看和剖析其他连接的语句，以及剖析时所引起的消耗。


   

liguangyi

既然用innodb，那么 innodb_buffer_pool_size 这个关键的参数，设置的多少？

action08

没那么复杂，应该是上层应用的问题，这个需要优化。

他都说了本地130w数据，才两分钟，数据库配置本身很强大

action08

回复 10# netmarshal


    1秒写应该有1w条吧，怎么前文又说100条了？？
有不一样的地方

panyunlai

这种问题，我觉得本身mysql出问题的肯能行很小，我之前也遇到过，最后发现还是自己代码写的不够优化
楼主说了数据库本身命令导入很快，网络又没有问题，所以首先要排查的问题是自己的代码，
是不是对每一条数据都是按照“打开数据库”-》“拼接一条insert语句”-》“执行sql语句”-》关闭数据库连接这种
如有有这种问题，楼主可以考虑优化一下代码，思路是批量的提交数据，即拼接insert语句时可以做个计数，
如计数达到1000条了，那么往数据库插入一次，同时在插入数据之前可以考虑关闭table的索引，等所有数据插入
完成了在重新建立所以
希望能帮到楼主

action08

回复 16# panyunlai


    你反应的问题是属于程序的问题，得让开发那边处理

action08

也可以想象，现在很多公司的研发管理很混乱，没有人有效担当责任

这事情，新人能把事情做出来，就已经不错了。还是研发管理的问题
问题全部交给下层，太累人了

thy_shenzhen

上面有人说是SQL语句问题，连接+拼凑+执行等等，远程用连接工具导入不涉及这个问题吧。
我觉得可能是网络IO的问题，是不是缓存设置小了。

asdf2110

我觉得和web端网数据库写入数据的方式有关，是不是一条语句一提交导致的？
我做了个例子:

1. awk 'BEGIN{print "create table t(a int);\nbegin;";  for (i=0;i<10000;i++)print "insert into t values("i");"; print "commit;"}' | /opt/mastermysql/mysql/bin/mysql -u root -h 127.0.0.1 -P 3309 test

复制代码

如果没有 "begin;"和"commit;" ，那么就变成一条语句一提交，默认每次提交InnoDB都会将日志刷盘，导致很慢
但是如果变成成组提交，只在提交时刷一次盘，会快很多。


回复 1# netmarshal