前段时间把数据库的部分myisam表转变成了innodb了,感觉慢了好多。我知道autocommit对innodb性能有一定的影响,但不知道影响有这么大。如何关闭autocommit,请参考mysql禁用autocommit,以及遇到的问题
,为了解决这个问题,我做了一些测试,包括autocommit对myisam,innodb影响。
一,测试autocommit对myisam的影响
1,准备测试表和数据
mysql> CREATE TABLE `test_test` ( //测试表
-> `id` int(11) NOT NULL auto_increment,
-> `num` int(11) NOT NULL default '0',
-> PRIMARY KEY (`id`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ||
mysql> create procedure p_test(pa int(11))
-> begin
->
-> declare max_num int(11) default 100000;
-> declare i int default 0;
-> declare rand_num int;
->
-> select count(id) into max_num from test_test;
->
-> while i < pa do
-> if max_num < 100000 then
-> select cast(rand()*100 as unsigned) into rand_num;
-> insert into test_test(num)values(rand_num);
-> end if;
-> set i = i +1;
-> end while;
-> end||
Query OK, 0 rows affected (0.03 sec
2,测试autocommit开启的情况
mysql> call p_test(100000)|| //插入10000条数据
Query OK, 1 row affected (0.86 sec)
mysql> truncate table test_test; //清空表
Query OK, 0 rows affected (0.00 sec)
mysql> optimize table test_test; //优化一下表,收回资源,确保测试的公平性
这样我连续做了三次测试,平均一下插入10000的数据差不多要0.86秒
。关于optimize来优化表,请参考optimize table在优化mysql时很重要
3,autocommit关闭的情况下
mysql> call p_test(100000)|| //插入10000条数据
Query OK, 1 row affected (0.83 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> truncate table test_test; //清空表
Query OK, 0 rows affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> optimize table test_test; //优化一下表,收回资源,确保测试的公平性
这样我连续做了三次测试,平均一下插入10000的数据差不多要0.83秒
。为了使init_connect='SET autocommit=0'
启作用,我是换了个用户测试的。如果在执行储存过程的时候遇到这样的问题,
ERROR 1370 (42000): execute command denied to user 'mysql'@'localhost' for routine 'test.p_test'
解决办法是:grant execute on procedure p_test to 'mysql'@localhost;
由上面的测试数据我们可以看出,autocommit对myisam没有多大的影响。
二,测试autocommit对innodb的影响
1,测试autocommit开启的情况
mysql> alter table test_test type=innodb; //将表改为innodb
Query OK, 0 rows affected, 1 warning (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 1
mysql> call p_test(10000); //插入数据
Query OK, 1 row affected (16.32 sec)
mysql> truncate table test_test; //删除数据
Query OK, 0 rows affected (0.02 sec)
我也做了3次测试,都是在16点几秒。myisam插入10000条数据,都不到一秒,而innodb要十几秒,差了20多倍,太杯具了。
2,测试autocommit关闭的情况
mysql> call p_test(10000); //插入数据
Query OK, 1 row affected (0.61 sec)
mysql> commit; //提交
Query OK, 0 rows affected (0.02 sec)
mysql> truncate table test_test; //删除数据
Query OK, 0 rows affected (0.00 sec)
mysql> commit; //提交
Query OK, 0 rows affected (0.00 sec)
我也测试了3次,第一次测试的时候,我以为我只插入了1000条,不然怎么会差距这么大呢。我又测试了二次,测试用时差不多,都是在0.6秒左右。autocommit对innodb的影响太大了,差了快30倍
。我汗
所以我的建议是把mysql的autocommit自动提交功能关闭,这样可以提高mysql的性能,特别是innodb表比较多的情况下,不是提高一点点。如果关闭了autocommit,不要忘了commit。不然mysql服务器挂掉了,或者重起了,数据就丢失了。
分享到:
相关推荐
InnoDB支持事务安全、Mysq唯一支持外键的存储... 但同时InnoDB提供了两种自动提交和手动提交两种选择(SET AUTOCOMMIT=0设置),根据文档所述,自动提交的性能很差,所有的修改操作和非自动提交都有200多倍的性能差距!
在缺省模式下,MYSQL 是 autocommit 模式的,所有的数据库更新操作都会即时提交, 所以在缺省情况下,mysql 是不支持事务的。 MYSQL 表类型是使用 InnoDB Tables 或 BDB tables 的话,你的 MYSQL 就 可 以 使 用 事 ...
InnoDB的AUTOCOMMIT默认是打开的,即每条SQL语句会默认被封装成一个事务,自动提交,这样会影响速度,所以最好是把多条SQL语句显示放在begin和commit之间,组成一个事务去提交。 3、InnoDB支持数据行锁定,MyISAM不...
整理了一份最新基于MySQL 5.6和5.7的配置文件模板,基本上可以说覆盖90%的调优选项,用户只需根据自己的服务器配置稍作修改即可,如InnoDB缓冲池的大小、IO能力(innodb_buffer_pool_size,innodb_io_capacity)。...
在缺省模式下,MYSQL 是 autocommit 模式的,所有的数据库更新操作都会即时提交,所 以在缺省情况下,mysql 是不支持事务的。 但是如果你的 MYSQL 表类型是使用 InnoDB Tables 或 BDB tables 的话,你的 MYSQ
事务: 是数据库操作的最小工作...InnoDB的AUTOCOMMIT默认是打开的,即每条SQL语句会默认被封装成一个事务,自动提交,这样会影响速度,所以最好是把多条SQL语句显示放在begin和commit之间,组成一个事务去提交。 InnoDB
注意:mysql只有这个InnoDB驱动是支持事务处理的,默认MyIsAM驱动不支持,下面是实例代码: 复制代码 代码如下:<?php try{ $pdo=new pdo(“mysql:host=localhost;dbname=mydb”, “root”, “root”, array(PDO...
在缺省模式下,MYSQL是autocommit模式的,所有的数据库更新操作都会即时提交,所以在缺省情况下,mysql是不支持事务的。如何进行Linux分区优化?linux系统都有哪些运行级别?请分别解释一下
MYSQL中只有INNODB类型的数据表才能支持事务处理。 启动事务有两种方法 (1) 用begin,rollback,commit来实现 代码如下:begin 开始一个事务rollback 事务回滚commit 事务确认(2)直接用set来改变mysql的自动提交...
在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。 1、查看事务状态select @@autocommit ;查看事务是否开启自动,默认是自动开启的 2、set autocommit=0;关闭自动事务,每次sql语句后可以跟随...
在对一个事务表使用表锁定的时候需要注意以下几点: 事务隔离级别 在锁定表时会隐式地提交所有事务,在开始一个事务时,如START TRANSACTION,会隐式解开所有表锁定。 在事务表中,系统变量AUTOCOMMIT值必须设为0。...
这里以mysql为例,先明确以下几个问题: 一.一般项目如果不自己配置事务的话,一般默认的是autocommit,即执行完一个操作后自动commit,提交事务。 (注:事务是绑定在数据库操作上的,也就是当程序执行(statement....
MySQL中的事务,默认是自动提交的,即autocommit = 1; 但是这样的话,在某些情形中就会...在MySQL的InnoDB中,预设的Tansaction isolation level 为REPEATABLE READ(可重读) 在SELECT 的读取锁定主要分为两种方式:
看自己的数据库是否是自动commit,可以使用mysql> show variables like ‘%autocommit%’;来进行查看,如果是OFF即不自动commit,需要手动commit操作(命令行可以直接“commit;“命令),否则是自动commit。 提交...
修改事务提交方式(是否自动提交,mysql默认自动提交) SET AUTOCOMMIT = 1; //自动提交,为0手动提交 不同数据库引擎MVCC模式各不相同,典型有乐观和悲观并发控制。 innodb 说明: InnoDB的MVCC,是通过在每行...
1.MySQL参数autocommit生产环境设1还是0?为什么? 在生产环境中,为了事务一致性,需要把autocommit 设置为 0 这样可以进行事务全部成功后,在后一次性提交,如果某一步出错,可以rollback。 在会话进修改...
mySql的事务采取的是自动提交的模式,除非显示开始一个事务修改自动提交模式,值得注意的是,修改自动提交对非事务类型的表是无效的,因为它们本身不存在提交和回滚的概念,还有一些命令则是强制自动提交的,如DLL...