老问题变种，mysql 插入数据速度突然变慢了！

Question

我用c api操作MySql数据库，我的系统是XP，机器是AMD双核3600+ ,mysql的版本是mysql-essential-5.1.31-win32，我的数据库中有40个表，经常进行插入操作的有3个表，三个表一模一样！以前因为mysql插入数据速度很慢，查了很多资料找到原因，是因为innodb_flush_log_at_trx_commit参数为1的原因，后来调为0，插入速度就非常快了，插入1000条数据也就二三百毫秒，但是最近一段时间不知道为什么插入速度又特别慢了，插入1000条数据用了13秒，2000多条数据要用半分钟还多，重装了mysql好几遍，试了好多配置还是那样，我的程序调用c api执行插入操作的时候，mysqld.exe的占用马上升高到百分之二三十，而且一直是这样，不知道为什么简单的一句“insert into....” 语句会有这么大的占用！请高手帮忙找找原因

Answer 1

是innodb表吧插入巨量数据, 建议用一个事务包含多行插入, 
或者用多行insert into (..) values (..),(..),(..),....否则 插到后面 ,会越来越慢. 可能是和事务的COMMIT有关

Answer 2

对了，忘了说了，用的是 innodb引擎，连接的时候设置如下
if ( mysql_init(&mysql) == NULL )
{
AddLogA("DB::ConnectDB()",mysql_error(&mysql));
} // 最后一个参数 CLIENT_MULTI_RESULTS ，使得调用存储过程后得到返回结果集
if ( mysql_real_connect(&mysql,g_strHost.c_str(),g_strUser.c_str(),g_strPassword.c_str(),g_strDB.c_str(),0,NULL,CLIENT_MULTI_RESULTS) == NULL )
{
AddLogA("DB::ConnectDB()",mysql_error(&mysql));
} mysql_real_query(&mysql,"SET AUTOCOMMIT=1",17); // 自动提交 mysql_set_character_set(&mysql,"GBK"); // 中文字符 mysql_set_server_option(&mysql,MYSQL_OPTION_MULTI_STATEMENTS_ON); //允许多语句支持

Answer 3

打开事务试试
begin 开始一个事务
rollback 事务回滚
commit    事务确认

Answer 4

按照一楼的方法试试，把 mysql_real_query(&mysql,"SET AUTOCOMMIT=1",17); // 自动提交去掉，把多个小事务变成一个大事务。（不过很多情况下未必会比多个小事务快）

Answer 5

我的两个日志文件ib_logfile0和ib_logfile1都是28M多,数据库文件200多M，这没什么影响吧！

Answer 6

200M的data file不算大，应该不是问题。

Answer 7

这两个文件是日志文件
1. 用mysqldump等工具导出数据
2. 停止 mysqld
3. 删除ibdata*, ib_logfile* 文件
4. 重新启动 mysqld（这时mysqld就会自动创建 idbdata*, ib_logfile* 文件）
5. 将到出来的数据导回去，体积才会减小。 再执行INSERT 试试

Answer 8

正在测试楼上的方法，多谢  WWWWA 的关注，还忘了告诉大家一点，我经常使用的这三个表示用来备份即时数据的，备份一天的数据，三个表大约有 300万条数据，然后第二天用 TRUNCATE TABLE 清空这三个表，然后继续备份，用这个命令清空表没什么问题吧!

Answer 9

不行啊，WWWWWA的方法试了好几遍，先从services.msc里关掉mysql服务，然后删除两个日志文件和一个ibdata1文件这个文件280多M，然后启动mysql服务，系统自动生成3个文件，郁闷的是新生成的两个日志文件和删除前一样大小28.2M ！！！！！这是怎么回事，试了好几遍都是这样

Answer 10

根本就不是文件大小的问题。truncate 没有问题。 慢的主要原因有可能与事务有关。做个试验检查一下了。

Answer 11

1、ibdata1文件体积变化没有；
2、日志文件：应该有默认大小；
3、重新生成后，插入速度有无变化。

Answer 12

innodb的autocommit的事务是这样的在set autocommit=1的情况下(默认)
    每个语句都是一个事务, 除非手工用start transaction启动一个事务, 再用commit (rollback) 结束事务.
在set autocommit=0的情况下
    commit或rollback或set autocimmit=0 会结束上一个事务, 自动启动下一个事务, 其他任何语句(个别除外)都包含在这个事务之中, 每时每刻都包含在一个事务之中, 所以又叫隐性保持事务状态.你的事务问题还没测试, 不是单单去掉mysql_real_query(&mysql,"SET AUTOCOMMIT=1",17)这行
应该是
  --  在autocommit=1的情况下
    begin
       1000行插入
    commit或者
  --  在autocommit=0的情况下
       1000行插入
    commit
假如还是老问题, 可能是文件写盘时遇到阻碍, 比如杀毒软件的干扰等.

Answer 13

mysql-essential-5.1.31-win32,这个版本貌似是有问题,我最近也遇到了一次..升级到这个版本后,程序运行起来,cpu几乎100%,后来不得已又换回5.0去了....唉....

Answer 14

问题很复杂啊！ 昨天用 wwwwb 老兄的方法把日志文件搞了几遍，结果测试后还是老样子，然后一晚上没管，今天开机后用14楼说的，分别用  --  在autocommit=1  的情况下 
    begin 
      1000行插入 
    commit 
  --  在autocommit=1  的情况下 
      1000行插入   --  在autocommit=0  的情况下 
      1000行插入 
 
  --  在autocommit=0  的情况下 
      1000行插入 
    commit 测试，结果插入速度都恢复正常了，都在200-300ms以内，真不知道是怎么回事！感觉应该跟 清理日志有一定关系！ 如果真是这个原因以后不能总清理日志啊？16楼说的我这个mysql版本可能也有一定的问题！我想再继续测试一下，把问题解决的彻底一些和大家分享，希望大家也帮忙！
在这里也总结一下我的程序使用mysql的一些情况！我的程序是一个服务器程序，负责响应客户端的请求，然后去mysql里查询数据返回给客户端！之前用过mssql，效果很不好！
mssql非常稳定，但是查询效率很低！在50台客户端同时查询的时候就会很慢！在这方面我很佩服mysql的查询能力，140台客户机
的查询速度都快！但是感觉mysql不太好配置，还有感觉有点不稳定，可能还是我的使用方法的问题！请大家介绍下mysql高版本的稳定些的版本！

Answer 15

目前的正式release的版本最高的是 5.1.33-community

Answer 16

我们现在正式环境中跑得还是 MySQL 4.1
很稳定，跑一年也不需要停机。 所以一直也没打算升级。5.1.33 从官方文档上看，比 4应该更稳定。