InnoDB vs MyISAM vs Falcon benchmarks

原为链接地址：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.

PLAIN TEXT

SQL:

1. CREATE TABLE IF NOT EXISTS `$tableName` (
2.     `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
3.     `name` varchar(64) NOT NULL DEFAULT '',
4.     `email` varchar(64) NOT NULL DEFAULT '',
5.     `password` varchar(64) NOT NULL DEFAULT '',
6.     `dob` date DEFAULT NULL,
7.     `address` varchar(128) NOT NULL DEFAULT '',
8.     `city` varchar(64) NOT NULL DEFAULT '',
9.     `state_id` tinyint(3) UNSIGNED NOT NULL DEFAULT '0',
10.     `zip` varchar(8) NOT NULL DEFAULT '',
11.     `country_id` smallint(5) UNSIGNED NOT NULL DEFAULT '0',
12.     PRIMARY KEY  (`id`),
13.     UNIQUE KEY `email` (`email`),
14.     KEY `country_id` (`country_id`,`state_id`,`city`)
15.     )

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 5130

model name : Intel(R) Xeon(R) CPU 5130 @ 2.00GHz
stepping : 6
cpu MHz : 1995.004
cache size : 4096 KB

16GB 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:

PLAIN TEXT

CODE:

1. For MyISAM / InnoDB
2. ./configure --prefix=/usr/local/mysqltest/mysql-<RELEASE> --with-innodb
3. For Falcon
4. ./configure --prefix=/usr/local/mysqltest/mysql-<RELEASE> --with-falcon

mysqld startup params:

PLAIN TEXT

CODE:

1. Falcon:
2. 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
3.  
4. MyISAM / InnoDB:
5. 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.