`

Should you move from MyISAM to Innodb ?

阅读更多

There is significant portion of customers which are still using MyISAM when they come to us, so one of the big questions is when it is feasible to move to Innodb and when staying on MyISAM is preferred ?

I generally prefer to see Innodb as the main storage engine because it makes life much simpler in the end for most users – you do not get to deal with recovering tables on the crash or partially executed statements. Table locks is no more problem, hot backups are easy, though there are some important things which we have to consider on case by case basics before recommending the move.

Is MyISAM used as default or as a choice ? This is the most important question to ask upfront. Sometimes MyISAM is there just because it is default, in other cases this is deliberate choice with system being optimized to deal with MyISAM limits, for example there is a dedicated slave available for all long reporting queries. In case MyISAM was chosen not just happened to be it is important to build the good argument to suggest Innodb.

Application Readiness Application should be ready to work with Innodb, for example be ready to deal with deadlocks which can happen with Innodb even if you do not use transactions, but which are not existent with MyISAM. QA has to be performed as part of the move.

Performance Innodb has a lot to offer in terms of performance – Performance benefits and drawbacks. On the benefits side we usually see clustering by primary key, caching data, higher concurrency, background flushes while on the drawbacks side we see significantly large table size (especially if data size is close to memory size), generally slower writes, slower blob handling, concurrency issues, problems dealing with very large number of tables, slow data load and ALTER TABLE and others. Another big one is COUNT(*) without where clause which is often the show stopper for them move until it is worked around.

Operations What is good for MyISAM kills Innodb, such as copying binary tables between the servers. It is important the team understands Innodb and knows how to handle it, or be able to learn it. It is also important to adjust processes as required to work with Innodb. For example binary copy of one of the databases from the Slave to the dev envinronment works great for MyISAM but does not work with Innodb. Backup tools like “mysqlhotcopy” does not work etc. Note Performance also affects Operations aspects a lot – for example using mysqldump as a backup may well work for MyISAM but will start taking way too much time to do restore for Innodb. On large scale installations mysqldump does not work anyway but it may still work for you when you’re running MyISAM but instantly break upon upgrading to Innodb.

Features The MyISAM features which forbid moving to Innodb are typically Full Text Search and RTREE indexes/GIS with Full Text being much more common. There are workarounds for both of them, including dedicated MyISAM slave or shadow table but it is important to consider them.

How about Mixing Storage Engines ? Sure you can mix storage engines but I suggest you doing is wisely. It complicates operations tasks (backups, balancing, performance analyzes) as well as it exercises not so common paths in the MySQL server – in particular Optimizer may have harder time because costs between storage engines may not be well balanced or replication of mixed table types which is quite complicated.

I prefer to pick one storage engine (typically Innodb) and when use other tables when it really gives substantial gains. I would not switch table to MyISAM because it gives 5% performance improvement but I can perfectly use MyISAM (or Archive) for logging.

Innodb Needs Tuning As a final note about MyISAM to Innodb migration I should mention about Innodb tuning. Innodb needs tuning. Really. MyISAM for many applications can work well with defaults. I’ve seen hundreds of GB databases ran with MyISAM with default settings and it worked reasonably. Innodb needs resources and it will not work well with defaults a lot. Tuning MyISAM from defaults rarely gives more than 2-3 times gain while it can be as much as 10-50 times for Innodb tables in particular for write intensive workloads. Check here for details.

Note: As Few people questioned me, I indeed forgot to clarify the scope here – I’m mainly speaking about OLTP/ Traditional web applications. for Analytics things are a lot different.

分享到:
评论

相关推荐

    MyISAM引擎与InnoDB引擎性能的对比

    MySQL数据库系统提供了多种存储引擎,其中最常用的两种是MyISAM和InnoDB。它们各自具有独特的特性和适用场景,理解二者的性能差异对于优化数据库设计至关重要。 MyISAM引擎是MySQL早期的默认存储引擎,以其高速度和...

    MyISAM和InnoDB的异同

    ### MyISAM与InnoDB的异同 在MySQL数据库系统中,存储引擎是数据库的核心组件之一,它负责处理数据的存储、检索等底层操作。MyISAM和InnoDB是MySQL中最常用的两种存储引擎,它们各自拥有不同的特点和适用场景。 ##...

    myisam与innodb 区别 值得下载

    自己总结的 关于mysql存储引擎myisam innodb 的比较 两者区别 对面试会很有帮助

    MyISAM与InnoDB的索引差异

    MySQL中的存储引擎MyISAM和InnoDB在处理索引上有显著的差异,这些差异主要体现在索引的组织方式以及对数据存储的影响上。两者都基于B+树这种高效的索引结构,但具体实现有所不同。 首先,MyISAM的索引采用非聚集...

    8.MySQL存储引擎--MyISAM与InnoDB区别1

    在InnoDB中,使用SELECT COUNT(*) FROM table可以快速地统计表中的行数,而MyISAM则需要扫描整个表来统计行数。 AUTO_INCREMENT InnoDB和MyISAM在AUTO_INCREMENT方面也有所不同。在InnoDB中,AUTO_INCREMENT可以...

    mysql DB引擎myisam与innodB

    ### MySQL 数据库引擎 MyISAM 与 InnoDB 在 MySQL 数据库系统中,存在多种不同的存储引擎,其中最为人所熟知且广泛使用的两种是 MyISAM 和 InnoDB。这两种存储引擎各自具有独特的特点和适用场景。 #### InnoDB:...

    MyISAM InnoDB 区别

     ◆5.LOAD TABLE FROM MASTER操作对InnoDB是不起作用的,解决方法是首先把InnoDB表改成MyISAM表,导入数据后再改成InnoDB表,但是对于使用的额外的InnoDB特性(例如外键)的表不适用。  另外,InnoDB表的行锁也不是...

    数据库引擎 MyISAM 和 InnoDB 对比

    ### 数据库引擎 MyISAM 和 InnoDB 的对比分析 在MySQL数据库系统中,MyISAM与InnoDB是最常被提及的两种存储引擎。这两种引擎在功能、性能以及适用场景上存在显著差异,本文将深入探讨这两者之间的区别,并分析它们...

    68.选择合适的数据库引擎和myisam和innodb的主要区别?.avi

    mysql数据库 选择合适的数据库引擎和myisam和innodb的主要区别

    MyISAM,InnoDB存储引擎1

    其中,MyISAM和InnoDB是最常用的两种存储引擎,各有其特点和适用场景。 MyISAM存储引擎是MySQL早期的默认引擎,以高速存储和检索以及全文搜索能力著称。在MyISAM中,每个表由三个物理文件组成:.frm存储表定义,....

    myisam和innodb的区别

    ### MySQL存储引擎详解:MyISAM与InnoDB的主要区别 #### 一、概述 MySQL作为一款广泛使用的开源关系型数据库管理系统,提供了多种存储引擎供用户根据不同的应用场景选择。其中最常用的两种存储引擎分别是MyISAM和...

    MyISAM和InnoDB索引引擎的B+树索引实现1

    本文主要讨论两种常见的存储引擎——MyISAM和InnoDB,它们在B+树索引实现上的差异。 首先,MyISAM是MySQL早期的默认存储引擎,它在索引方面采用B+树结构。对于主键索引,MyISAM的B+树叶节点存储的是数据记录的物理...

    为什么 select count(*) from t,在 InnoDB 引擎中比 MyISAM 慢?

    总结来说,`select count(*) from t`在InnoDB中比MyISAM慢主要是因为InnoDB的事务特性和MVCC机制导致的全表扫描。然而,InnoDB通过优化索引遍历来尽可能地提高效率。在实际应用中,为了提高查询速度,可以考虑创建...

    mysql更改引擎(InnoDB,MyISAM)的方法

    本文实例讲述了mysql更改引擎(InnoDB,MyISAM)的方法,分享给大家供大家参考。具体实现方法如下: mysql默认的数据库引擎是MyISAM,不支持事务和外键,也可使用支持事务和外键的InnoDB。 查看当前数据库的所支持的...

    MySQL存储引擎中的MyISAM和InnoDB区别详解

    在使用MySQL的过程中对MyISAM和InnoDB这两个概念存在了些疑问,到底两者引擎有何分别一直是存在我心中的疑问。为了解开这个谜题,搜寻了网络,找到了如下信息: MyISAM是MySQL的默认数据库引擎(5.5版之前),由...

    MySQL存储引擎MyISAM与InnoDB区别总结整理

    对于是否保存行数,MyISAM会直接存储表的总行数,而InnoDB则不直接保存,需要在执行`SELECT COUNT(*) FROM table`时扫描整个表来计算。这在进行统计查询时可能影响到性能。 在选择存储引擎时,应根据具体应用场景来...

    myisam innodb对比1

    ### MyISAM与InnoDB对比分析 #### 一、数据存储区别 - **堆表**:数据无序存储,适用于不频繁更新的情况。 - **索引组织表**:表的数据和索引紧密关联,通常主键就是数据的物理排列顺序。 **MyISAM**: - 属于堆...

Global site tag (gtag.js) - Google Analytics