- 浏览: 850913 次
- 性别:
- 来自: 北京
文章分类
最新评论
-
zjhzwx1212:
为什么用threadLocal后,输出值是从20开始的,而定义 ...
j2ee的线程安全--threadlocal -
aeoluspu:
不错 mysql 测试部分感觉不详细
用sysbench(或者super-smack)测试mysql性能 -
nanPrivate:
有没有例子,只理论,实践起来还是不会啊
JMS可靠消息传送 -
lwclover:
一个网络工程师 装什么b
postfix 如何删除队列中的邮件 -
maimode:
我也欠缺不少啊
理想的计算机科学知识体系
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.
发表评论
-
找出mysql中最大的表
2011-08-04 12:41 1685SELECT concat(round(table_rows/ ... -
find 10 largest table in mysql
2010-07-20 11:09 1110SELECT concat(round(table_rows/ ... -
MySQL Back to Basics: Analyze, Check, Optimize, and Repair
2010-07-14 11:35 1188http://www.pythian.com/news/111 ... -
找出mysql中无用的索引
2010-07-13 14:49 1888select t.TABLE_SCHEMA , ... -
xtrabackup timeout bug
2010-06-13 10:21 1089I modified /usr/bin/innobac ... -
Compiling sysbench 0.4.12 for Debian
2010-06-09 10:16 985http://www.randombugs.com/linux ... -
mysql 实用工具集
2010-06-04 00:08 1108这些工具都是从网上搜集来的,对mysql的管理,调优和恢复有很 ... -
gearman for mysql
2010-05-10 18:26 1005http://www.slideshare.net/datac ... -
几个应该被修改的mysql默认值
2010-05-06 15:24 1493wait_timeout = 20 (不适合持久连接) in ... -
How to Perform a Healthcheck on the Database
2010-02-26 10:05 1401http://bbs.chinaunix.net/thread ... -
PostgreSQL与Innodb并发控制大比拼
2010-01-14 11:38 2087http://wangyuanzju.blog.163.com ... -
关于mysql的很好网站
2009-10-21 10:40 948http://www.mysqlperformanceblog ... -
最好的mysql备份工具
2009-08-19 16:45 1214Xtrabackup https://launchpad.n ... -
mysql 增量备份脚本
2009-08-18 09:44 3655根据网上脚本修改而成 mysqlFullBackup.sh ... -
mysql-zrm备份mysql数据库
2009-07-22 13:58 2776MySQL-zrm是用perl脚本写的 ... -
Base: An Acid Alternative
2009-06-18 15:55 1406http://queue.acm.org/detail.cfm ... -
关于innodb插入性能
2009-04-07 10:54 1599根据某网友的测试,innodb在以下条件下插入性能是稳定的: ... -
MySQL主从服务器的一些技巧
2009-03-25 15:18 958http://www.sunnyu.com/?p=150 -
mytop1.6补丁
2009-03-13 17:27 1006mytop是一个实时监控mysql状态的工具,很好用,但是有一 ... -
c3p0在hibernate+spring中的配置
2009-03-12 13:16 1619<bean id="dataSource& ...
相关推荐
MySQL数据库系统提供了多种存储引擎,其中最常用的两种是MyISAM和InnoDB。它们各自具有独特的特性和适用场景,理解二者的性能差异对于优化数据库设计至关重要。 MyISAM引擎是MySQL早期的默认存储引擎,以其高速度和...
### MyISAM与InnoDB的异同 在MySQL数据库系统中,存储引擎是数据库的核心组件之一,它负责处理数据的存储、检索等底层操作。MyISAM和InnoDB是MySQL中最常用的两种存储引擎,它们各自拥有不同的特点和适用场景。 ##...
自己总结的 关于mysql存储引擎myisam innodb 的比较 两者区别 对面试会很有帮助
MySQL中的存储引擎MyISAM和InnoDB在处理索引上有显著的差异,这些差异主要体现在索引的组织方式以及对数据存储的影响上。两者都基于B+树这种高效的索引结构,但具体实现有所不同。 首先,MyISAM的索引采用非聚集...
在InnoDB中,使用SELECT COUNT(*) FROM table可以快速地统计表中的行数,而MyISAM则需要扫描整个表来统计行数。 AUTO_INCREMENT InnoDB和MyISAM在AUTO_INCREMENT方面也有所不同。在InnoDB中,AUTO_INCREMENT可以...
### MySQL 数据库引擎 MyISAM 与 InnoDB 在 MySQL 数据库系统中,存在多种不同的存储引擎,其中最为人所熟知且广泛使用的两种是 MyISAM 和 InnoDB。这两种存储引擎各自具有独特的特点和适用场景。 #### InnoDB:...
◆5.LOAD TABLE FROM MASTER操作对InnoDB是不起作用的,解决方法是首先把InnoDB表改成MyISAM表,导入数据后再改成InnoDB表,但是对于使用的额外的InnoDB特性(例如外键)的表不适用。 另外,InnoDB表的行锁也不是...
### 数据库引擎 MyISAM 和 InnoDB 的对比分析 在MySQL数据库系统中,MyISAM与InnoDB是最常被提及的两种存储引擎。这两种引擎在功能、性能以及适用场景上存在显著差异,本文将深入探讨这两者之间的区别,并分析它们...
mysql数据库 选择合适的数据库引擎和myisam和innodb的主要区别
其中,MyISAM和InnoDB是最常用的两种存储引擎,各有其特点和适用场景。 MyISAM存储引擎是MySQL早期的默认引擎,以高速存储和检索以及全文搜索能力著称。在MyISAM中,每个表由三个物理文件组成:.frm存储表定义,....
### MySQL存储引擎详解:MyISAM与InnoDB的主要区别 #### 一、概述 MySQL作为一款广泛使用的开源关系型数据库管理系统,提供了多种存储引擎供用户根据不同的应用场景选择。其中最常用的两种存储引擎分别是MyISAM和...
本文主要讨论两种常见的存储引擎——MyISAM和InnoDB,它们在B+树索引实现上的差异。 首先,MyISAM是MySQL早期的默认存储引擎,它在索引方面采用B+树结构。对于主键索引,MyISAM的B+树叶节点存储的是数据记录的物理...
总结来说,`select count(*) from t`在InnoDB中比MyISAM慢主要是因为InnoDB的事务特性和MVCC机制导致的全表扫描。然而,InnoDB通过优化索引遍历来尽可能地提高效率。在实际应用中,为了提高查询速度,可以考虑创建...
本文实例讲述了mysql更改引擎(InnoDB,MyISAM)的方法,分享给大家供大家参考。具体实现方法如下: mysql默认的数据库引擎是MyISAM,不支持事务和外键,也可使用支持事务和外键的InnoDB。 查看当前数据库的所支持的...
在使用MySQL的过程中对MyISAM和InnoDB这两个概念存在了些疑问,到底两者引擎有何分别一直是存在我心中的疑问。为了解开这个谜题,搜寻了网络,找到了如下信息: MyISAM是MySQL的默认数据库引擎(5.5版之前),由...
对于是否保存行数,MyISAM会直接存储表的总行数,而InnoDB则不直接保存,需要在执行`SELECT COUNT(*) FROM table`时扫描整个表来计算。这在进行统计查询时可能影响到性能。 在选择存储引擎时,应根据具体应用场景来...
### MyISAM与InnoDB对比分析 #### 一、数据存储区别 - **堆表**:数据无序存储,适用于不频繁更新的情况。 - **索引组织表**:表的数据和索引紧密关联,通常主键就是数据的物理排列顺序。 **MyISAM**: - 属于堆...