来源:http://www.mysqlperformanceblog.com/2007/01/08/innodb-vs-myisam-vs-falcon-benchmarks-part-1/
Several days ago MySQL AB made new storage engine Falcon available for wide auditory. We cannot miss this event and executed several benchmarks to see how Falcon performs in comparison to InnoDB and MyISAM.
The second goal of benchmark was a popular myth that MyISAM is faster than InnoDB in reads, as InnoDB is transactional, supports Foreign Key and has an operational overhead. As you will see it is not always true.
For benchmarks I used our PHPTestSuite which allows to test wide range tables and queries.
The script and instruction are available here:
http://www.mysqlperformanceblog.com/files/benchmarks/phptestsuite.stable.tar.gz
We used table "normal" table structure which corresponds to typical structure you would see in OLTP or Web applications - medium size rows, auto increment primary key and couple of extra indexes.
- CREATE TABLE IF NOT EXISTS `$tableName` (
- `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
- `name` varchar(64) NOT NULL DEFAULT '',
- `email` varchar(64) NOT NULL DEFAULT '',
- `password` varchar(64) NOT NULL DEFAULT '',
- `dob` date DEFAULT NULL,
- `address` varchar(128) NOT NULL DEFAULT '',
- `city` varchar(64) NOT NULL DEFAULT '',
- `state_id` tinyint(3) UNSIGNED NOT NULL DEFAULT '0',
- `zip` varchar(8) NOT NULL DEFAULT '',
- `country_id` smallint(5) UNSIGNED NOT NULL DEFAULT '0',
- PRIMARY KEY (`id`),
- UNIQUE KEY `email` (`email`),
- KEY `country_id` (`country_id`,`state_id`,`city`)
- )
In this benchmark we used only read (SELECT) queries with different typical data access patterns:
primary key single row lookup, primary key range lookup, same access types for primary key and full table scans.
To highlight different properties of storage engines we tested ranges with and without LIMIT clause, and tested queries which
need to read the data or can only be satisfied by reading the index.
This benchmark is so called "micro" benchmark which concentrates on particular simple storage engine functions and we use it to see performance and scalability in this simple cases. We also use CPU bound workload in this case (no disk IO) to see how efficient storage engines are in terms of CPU usage. In real life workload results are likely to be very different.
The schema and queries are described here
Used hardware
CentOS release 4.4 (Final)
2 х Dual Core Intel XEON 5130model name : Intel(R) Xeon(R) CPU 5130 @ 2.00GHz
stepping : 6
cpu MHz : 1995.004
cache size : 4096 KB16GB of RAM
MySQL version
We used MySQL 5.1.14-beta sources for MyISAM / InnoDB
and MySQL 5.1.14-falcon bitkeeper tree
bk://mysql.bkbits.net/mysql-5.1-falcon for Falcon
(Please note this is a first release of Falcon and it is still in alpha stage and performance parameters may vary a lot in next releases)
Compilation parameters:
- For MyISAM / InnoDB
- ./configure --prefix=/usr/local/mysqltest/mysql-<release> --with-innodb </release>
- For Falcon
- ./configure --prefix=/usr/local/mysqltest/mysql-<release> --with-falcon </release>
mysqld startup params:
- Falcon:
- libexec/mysqld --no-defaults --user=root --falcon_min_record_memory=1G --falcon_max_record_memory=2GB --falcon_page_cache_size=1500M --max-connections=1500 --table-cache=512 --net_read_timeout=30 --net_write_timeout=30 --backlog=128
- MyISAM / InnoDB:
- libexec/mysqld --no-defaults --user=root --key-buffer-size=1500M --innodb-buffer-pool-size=1500M --innodb-log-file-size=100M --innodb-thread-concurrency=8 --max-connections=1500 --table-cache=512 --net_read_timeout=30 --net_write_timeout=30 --back_log=128
Method of benchmark:
1. Prepare table with 1,000,000 records (about 350Mb of data on disk)
2. Run each query for 1, 4, 16, 64, 128, 256 concurrent threads.
3. For each thread perform a warm-up run (duration 180 sec), and then
run three effective runs (duration of each is 60 sec).
As the final result we get a maximal result of three runs.
The raw numbers are available here:
http://www.mysqlperformanceblog.com/files/benchmarks/innodb-myisam-falcon.html
(Note: This benchmark is synthetic micro benchmarks focusing on particular simple data access patterns. Results for your workload are likely to be different.)
There are interesting results I want to show graphics with comments
READ_PK_POINT
Query: SELECT name FROM $tableName WHERE id = %d
The very common query with access by primary key.
InnoDB is faster than MyISAM by 6-9%.
Falcon shows very bad scalabilty.
READ_KEY_POINT
Query: SELECT name FROM $tableName WHERE country_id = %d
In this case Falcon is the best, because Falcon uses a tricky technic to retrieve rows (more
details with Jim Starkey's comments in Part 2).
There MyISAM shows bad scalability with increasing count of thread. I think the reason is pread system
call MyISAM uses to access data and retrieving from OS cache is not scaled.
READ_KEY_POINT_LIMIT
Query: SELECT name FROM $tableName WHERE country_id = %d LIMIT 5
The same query as previous but with LIMIT clause.
Due to Falcon's way of key access Falcon cannot handle LIMIT properly and that is why
we see bad performance. We hope the performance of LIMIT queries will be fixed before release.
MyISAM shows stable result.
InnoDB is better than MyISAM by 58% in case with 4 threads, but does not scale good enough.
Perhaps there is still a problem with InnoDB mutexes.
READ_KEY_POINT_NO_DATA
Query: SELECT state_id FROM $tableName WHERE country_id = %d
This query is similar to previous READ_KEY_POINT with only different the values of accessed column is stored in key. MyISAM and InnoDB handle this case and retrive the value only from key.
InnoDB is better by 25-30%.
Falcon needs an access to data beside key access, and most likely this will not be fixed, as this is
specific Falcon's way to handle multi-versioning. I think this is a big weakness of Falcon, as 'using index' is very common optimization we use in our practice.
READ_KEY_POINT_NO_DATA_LIMIT
Query: SELECT state_id FROM $tableName WHERE country_id = %d LIMIT 5
The previous query but with LIMIT.
Again the LIMIT is bad for Falcon.
InnoDB is better than MyISAM by 87% in case with 4 threads but drops down very fast.
READ_PK_POINT_INDEX
Query: SELECT id FROM $tableName WHERE id = %d
Simple but very quick query to retrieve value from PK.
The results for InnoDB and MyISAM are comparable and I think this shows both engines are maximally optimized and the result is maximal that can be reached for this query.
Falcon scales pretty bad and there is a big room for optimization.
READ_PK_RANGE
Query: SELECT min(dob) FROM $tableName WHERE id between %d and %d
Access by range of PK values.
MyISAM scales very bad, and reason is the same as for READ_KEY_POINT queries.
InnoDB is better than MyISAM by 2-26 times
and than Falcon by 1.64 - 3.85 times.
READ_PK_RANGE_INDEX
Query: SELECT count(id) FROM $tableName WHERE id between %d and %d
MyISAM scales good here, because of access only to key column and 'pread' syscall is not used.
READ_KEY_RANGE
Query: SELECT name FROM $tableName WHERE country_id = %d and state_id between %d and %d
As in case with READ_KEY_RANGE Falcon is the best here.
Falcon's resuts better than InnoDB by 10-30%
MyISAM drops down with 128-256 threads
READ_KEY_RANGE_LIMIT
Query: SELECT name FROM $tableName WHERE country_id = %d and state_id between %d and %d LIMIT 50
Again Falcon does not hanle LIMIT and the results are much worse.
READ_KEY_RANGE_NO_DATA
Query: SELECT city FROM $tableName WHERE country_id = %d and state_id between %d and %d
READ_KEY_RANGE_NO_DATA_LIMIT
Query: SELECT city FROM $tableName WHERE country_id = %d and state_id between %d and %d LIMIT 50
READ_FTS
Query: SELECT min(dob) FROM $tableName
The hardest query performs a scan of all million rows.
InnoDB is better than MyISAM by ~30% with 4-16 threads, but MyISAM scales a bit better in this case.
InnoDB is better than Falcon by 2-3 times.
相关推荐
MyISAM引擎与InnoDB引擎性能的对比
本文深入对比分析了MySQL的两大存储引擎InnoDB和MyISAM之间的区别,包括事务支持、数据恢复能力、并发控制、存储空间占用和读取性能等多个维度。通过详细的技术和场景分析, objectively评估两者的优缺点,并给出选择...
(1)xtrabackup只能备份InnoDB和XtraDB两种数据表,而不能备份MyISAM数据表 (2)innobackupex是参考了InnoDB Hotbackup的innoback脚本修改而来的,innobackupex是一个perl脚本封装,封装了xtrabackup,所以能...
本文实例讲述了mysql更改引擎(InnoDB,MyISAM)的方法,分享给大家供大家参考。具体实现方法如下: mysql默认的数据库引擎是MyISAM,不支持事务和外键,也可使用支持事务和外键的InnoDB。 查看当前数据库的所支持的...
Inside.MySQL_InnoDB.Storage.Engine_zh-CN[www.TopSage.com].part1
MyISAM和InnoDB的异同 MyISAM和InnoDB的异同
Inside.MySQL_InnoDB.Storage.Engine_zh-CN[www.TopSage.com].part2
Innodb与Myisam引擎的区别与应用场景 . 区别: (1)事务处理: MyISAM是非事务安全型的,而InnoDB是事务安全型的(支持事务处理等高级处理); (2)锁机制不同: MyISAM是表级锁,而InnoDB是行级锁;
MyISAM InnoDB 区别 InnoDB和MyISAM是许多人在使用MySQL时最常用的两个表类型,这两个表类型各有优劣,视具体应用而定。基本的差别为:MyISAM类型不支持事务处理等...而我的整体数据库服务器平均负载都在0.5-1左右。
自己总结的 关于mysql存储引擎myisam innodb 的比较 两者区别 对面试会很有帮助
MySQL存储引擎,Innodb与MyISAM谁更叼? 固定套路,问mysql数据库知识的时候,一定会带着问存储引擎,innodb,MyISAM,他们最主要的区别是索引的实现方式,然后是加锁粒度等等的区别,最后还会来到送命题你觉得选择...
MySQL InnoDB Java阅读器 innodb-java-reader是一个Java实现,用于直接访问MySQL InnoDB存储引擎文件。 使用库或命令行工具,它提供了只读功能,例如检查页面,通过主键,辅助键查找记录以及通过LSN或填充率生成页面...
MyISAM和InnoDB都使用B+树来实现索引: • MyISAM的索引与数据分开存储 • MyISAM的索引叶子存储指针,主键索引与普通索引无太大区别 • InnoDB的聚集索引和数据行统一存储 • InnoDB的聚集索引存储数据行本身,普通...
mysql数据据存储引擎InnoDB和MyISAM的优势及区别分享.pdf
InnoDB和MyISAM是在使用MySQL最常用的两个表类型,各有优缺点,视具体应用而定。基本的差别为:MyISAM类型不支持事务处理等高级处理,而InnoDB类型支持。MyISAM类型的表强调的是性能,其执行数度比InnoDB类型更快,...
percona-data-recovery-tool-for-innodb-0.5.tar.tar
MySQL内核 INNODB存储引擎-卷1-高清-完整目录-2014年5月
yum install make gcc flex bison cd /root/undrop-for-innodb-master make 会产生三个文件c_parser innochecksum_changer stream_parser
InnoDB索引与MyISAM索引实现的区别是什么?.mp4 InnoDB索引与MyISAM索引实现的区别是什么?.mp4 InnoDB索引与MyISAM索引实现的区别是什么?.mp4 InnoDB索引与MyISAM索引实现的区别是什么?.mp4 InnoDB索引与MyISAM...