http://www.tocker.ca/categories/myisam
Converting MyISAM to InnoDB and a lesson on variance
I'm about to start working through converting a large MySQL installation to InnoDB. For the particular use case durability is desired, and with MyISAM a loss of power almost certainly guarantees data loss. Durability is not available as an option.
So the normal question people ask is.. okay, what do I pay for this feature?
I think if you are looking at the workload in question I actually believe InnoDB will perform better overall. Row-level locking, multiversion concurrency control, and the correct selection of hardware (fast-disks with a RAID controller + batter backed write cache) will really help it shine.
But my problem is that InnoDB could be better in 9 situations, and worse in 1. What matters more than anything else is performance regressions. That is...
"Our Customers Feel the Variance, Not the Mean" - a key concept in Six Sigma.
The way I like to explain this, is that if we were to go to a restaurant and order the same meal - or go to Toyota and order the same car, we should receive the same product. I can't get the equivalent to a race car and you get a Trabant. Nor can we allow some queries that were already fast enough get faster, and other queries become intolerably slower.
So what I am actively trying to do is identify situations where MyISAM will be faster than InnoDB. And so far I have on my watch list:
- Table Scans - MyISAM data is far more compact, and typically table scans need to examine far less data.
- Index Scans - In InnoDB, indexes will contain multiple versionsand typically be much larger.
- Inserts - MyISAM has a special optimization where it can just insert into the end of a table. I think even with the battery backed write cache this will be hard to beat.
- Single-threaded workloads - maybe there's an hourly cron that in InnoDB will take significantly longer. InnoDB really performs better in concurrent workloads.
Facebook and Percona have previously written about the importance of variance reduction as well.
I should also note that durability is not the only reason to use InnoDB. Operational tasks like backups become much easier. But that's the subject for a different post
situations where MyISAM will be faster than InnoDB的更多相关文章
-
MySQL数据库锁机制之MyISAM引擎表锁和InnoDB行锁详解
转 http://blog.csdn.net/hsd2012/article/details/51112009 转 http://blog.csdn.net/e421083458/article/de ...
-
MyISAM to InnoDB: Why and How(MYSQL官方译文)
原文地址:https://www.mysql.com/why-mysql/presentations/myisam-2-innodb-why-and-how/ MySQL使用一个插拔式的存储引擎架构, ...
-
the current differences between MyISAM and InnoDB storage engines
原文地址:https://*.com/questions/47680213/what-are-the-current-differences-between-myisam-an ...
-
Mysql 存储引擎中InnoDB与Myisam的主要区别
一直以为我spring事物没有配置好,结果发现是mysql的表本身设置成了Myisam 引擎.改成innodb就支持事物了. 1, 事务处理 innodb 支持事务功能,myisam 不支持. Myi ...
-
Mysql 存储引擎 InnoDB与Myisam的主要区别
MySQL默认采用的是MyISAM. 1,事务处理 innodb 支持事务功能,myisam 不支持. Myisam 的执行速度更快,性能更好. MyISAM不支持事务,而InnoDB支持.InnoD ...
-
Mysql更换MyISAM存储引擎为Innodb的操作记录
一般情况下,mysql会默认提供多种存储引擎,可以通过下面的查看: 1)查看mysql是否安装了innodb插件.通过下面的命令结果可知,已经安装了innodb插件. mysql> show p ...
-
MyISAM 和InnoDB 的区别.(存储,索引, 事务, 锁)
MyISAM类型的表强调的是性能,但是不支持事务.及外部键等高级功能. MySQL默认采用的是MyISAM. MyISAM不支持事务,而InnoDB支持.InnoDB的AUTOCOMMIT默认是打开的 ...
-
myisam、innodb存储引擎比较
MYSQL表类型(存储引擎) 1.概述 MySQL数据库其中一个特性是它的存储引擎是插件式的.用户可以根据应用需要选择存储引擎.Mysql默认支持多种存储引擎,以适用各种不同的应用需要.默认情况下,创 ...
-
MySQL中MyISAM和InnoDB的区别
MyISAM和InnoDB的区别 MySQL默认采用的是MyISAM. MyISAM不支持事务,而InnoDB支持.InnoDB的AUTOCOMMIT默认是打开的,即每条SQL语句会默认被封装成一个事 ...
随机推荐
-
SPOJ TSUM Triple Sums(FFT + 容斥)
题目 Source http://www.spoj.com/problems/TSUM/ Description You're given a sequence s of N distinct int ...
-
FastReport使用二——二维码
以下内容在FastReport Designer 中测试通过,如下图所示: 在使用FastReport Designer创建一维吗也就是一般普通的条码时,设置其Barcode属性为Code128 (建 ...
-
cocos2dx 3.1从零学习(一)——入门篇(一天学会打飞机)
没办法,浏览这么高,为啥没人投票呢?朋友们,我这篇文章參加了csdn博文大赛.喜欢的来点个赞吧!点击:http://vote.blog.csdn.net/Article/Details?article ...
-
CAAnimation动画--(旋转/缩放/移动/闪烁)
p.p1 { margin: 0.0px 0.0px 0.0px 0.0px; font: 18.0px Menlo; color: #1d9421 } p.p2 { margin: 0.0px 0. ...
-
ABP入门系列(20)——使用后台作业和工作者
ABP入门系列目录--学习Abp框架之实操演练 源码路径:Github-LearningMpaAbp 1.引言 说到后台作业,你可能条件反射的想到BackgroundWorker,但后台作业并非是后台 ...
-
java(7)数组
一.什么是数组及其作用? 定义:具有相同数据类型的一个集合 作用:存储连续的具有相同类型的数据 二.java中如何声明和定义数组 2.1 声明和定义的语法: 数据类型[ ] 数组名:( int[ ] ...
-
Navicat 远程连接Docker容器中的mysql 报错:1251 - Client does not support authentication protocol 解决办法。
出现这个问题 首先进入 1.docker exec -it mysql02 bash //mysql02是mysql容器的别名 2.mysql -uroot -p 3.输入密码 4.进入my ...
-
Matlab之视角旋转函数[转]
Matlab中有两个视角旋转函数:view和rotate,下面详细介绍: view: 一: view(az,el):az是方位角,el是仰角,单位均是度.具体: 以x轴从左到右(即从小到大)平行放置在 ...
-
Swift循环遍历集合方法
第一种方式:for-in循环 OC延续了C语言的for循环,在Swift中被彻底改造,我们无法再使用传统形式的for循环了 遍历数组和字典: //遍历数组 let iosArray = [&quo ...
-
HYSBZ(BZOJ) 4300 绝世好题(位运算,递推)
HYSBZ(BZOJ) 4300 绝世好题(位运算,递推) Description 给定一个长度为n的数列ai,求ai的子序列bi的最长长度,满足bi&bi-1!=0(2<=i<= ...