InnoDB vs MyISAM vs Falcon benchmarks

核心提示： Falcon scales pretty bad and there is a big room for optimization.READ_PK_RANGEQuery: SELECT min(dob) FROM $tableName WHERE id between %d and %dA

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.