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:
https://www.percona.com/blog/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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | 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:
1 2 3 4 | For MyISAM / InnoDB ./configure --prefix=/usr/local/mysqltest/mysql-<RELEASE> --with-innodb For Falcon ./configure --prefix=/usr/local/mysqltest/mysql-<RELEASE> --with-falcon |
mysqld startup params:
1 2 3 4 5 | 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:
https://www.percona.com/blog/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.
i can’t even check out the sources; it’s been bugging me the entire weekend 🙁
OK-root OK
ERROR-BAD CMD: export, Try help
As I remember Larry has just changed the protocol, so get new free client here:
http://www.bitkeeper.com/Hosted.Downloading.html
Very good post and very informative. The InnoDB vs. MyISAM performance numbers were just as interesting as the performance of Falcon.
-Dave
Johan,
You need new free client and
MySQL moved the falcon tree to new root
bk://mysql.bkbits.net/mysql-5.2-falcon
But I cannot clone it too:
./bkf clone bk://mysql.bkbits.net/mysql-5.2-falcon mysql-5.2-falcon
ERROR-unable to lock repo for export, try later.
Perhaps there is a maintenance, and the tree will be available later.
I wanted to compare disk usage too. For the same table structure and data, how large is each engine’s data and index file? Do you have this information?
Falcon is shy telling its disk usage in SHOW TABLE STATUS:
show table status\G
*************************** 1. row ***************************
Name: normal
Engine: Falcon
Version: 10
Row_format: Dynamic
Rows: 1000
Avg_row_length: 0
Data_length: 10000
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 1000001
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
The file size was 354MB which is data + indexes together.
MyISAM:
Data_length: 167519584
Index_length: 132239360
Innodb:
Data_length: 195772416
Index_length: 156794880
Interesting results. Obviously Falcon isnt as mature as the other 2 engines. Though I am surprised about how InnoDB is better than MyISAM in a lot of areas. I’m not really informed about this stuff but, do you think MyISAM would perform better on smaller (single proc) machines? I would be interested in seeing how each performs on different machines like single proc, dual proc, and quad proc.
Thanks for the hint with regards to the updated BK client. I have updated my blog entry on how to compile MySQL from source. However, the locking problem still persists, we are working with the BitKeeper people on resolving this issue.
Thanks for the great benchmarks!
Hope Jim will take this into account and work hard on Falcon, as this results are a bit of a “Falcon-killer”
Also hope that MySQL will set for one or two preferred engines focusing efforts on those, as I see all this storage engine plethora as essentially duplicated efforts and unneeded complexity added, I mean that MyISAM and one of the transactional engines (InnoDB, Falcon, whatever they this is better) should be enough for most cases, leaving SoliDB and other for special cases.
It would be interesting to run your benchmarks against Firebird/Vulcan to see if and how Jim’s new brainchild improves over the older designs.
Regards
Just to let you know: we are aware of the problems with creating a clone with the free BK client and have contacted BitKeeper support to resolve this ASAP. Sorry for the inconvenience.
These numbers are devastating. I fear that they’ll be used as FUD against Falcon when it’s mature and actually performs well.
That being said, it is interesting to see that InnoDB ends up having equal or better read performance compared to MyISAM. That really changes my perceptions of both of them…
2 bp:
I don’t know how MyISAM will perform on single- or dual- CPU boxes in 5.1.14 release,
at least I expect the scalability will be good enough.
Yes, results are not great for Falcon so far. The worst problem though with LIMIT is already identified and Jim will look into fixing it.
The good thing is we have now these results early and Jim will have time to look into these.
Someone can misuse results, which is very frequent with benchmarks results but it is not much you can do about it.
Interesting result of Falcon and between MyISAM and InnoDB. However I still think Falcon is helpful, since this test is carried out when all data resides in memory, so the advantage of Falcon’s record cache will not show.
BTW: Why Falcon needs an access to data beside key access in the READ_KEY_POINT_NO_DATA. I know Falcon use private in-memory caches to hold modified index entries for each update transactions, but I still don’t know why. Could someone be so kind to tell me the reason?
Results are interesting, especialy MyISAM vs InnoDB.
Breezes,
Actually this should show one of benefits of Falcon record cache – retrieving record from record cache generally should be faster as you do not need to lookup page by row_id and then look for the row on the page itself which could be implemented less efficiently.
Regarding why Falcon can’t only read data from Index as I understand there are two reasons.
1) Falcon stores collation values in the index not the data itself which means it is not always to perform reverse conversion and get real data from the index.
2) Falcon index structure is rather minimal (to keep indexes short) this means you can’t say from index records itself it it should be visible inside current transaction etc.
I personally thing this is serious matter as I frequently use covering indexes for optimization.
Very useful. Thank you!
Hi! Nowadays one million is pretty small number of records for database. Did you try similar benchmarks against, (say) 100 000 000 records? Or – (as we usualy did) against database 3 times bigger than RAM?
Alexey,
Thanks for feedback. Right this is small database and CPU bound benchmark. We did not test IO bound yet as Jim mentioned there are some issues with insert speed at this point plus it will take more time and require a lot of other decisions, such as distribution type etc. We’ll do it as Falcon is closer to production stage 🙂
Alexey,
I know you have an experience in benchmarks, so your results against 100.000.000 records are welcome 🙂
I’m curious whether you’ve run any benchmarks comparing InnoDB vs. MyISAM when queries are being done at the same time as new row creations. I had that situation a few years ago, and we tried InnoDB on the theory that table-level locking would benefit us. However we didn’t get the improvement we expected. We ended up taking advantage of MyISAM’s ability to do simultaneous select/creates if the table has no holes in it (we deferred all deletions until we could do a compact). That was a somewhat odd case though. The queries were large, but there weren’t a lot of simultaneous ones. Whereas the creates were going on pretty much continuously, and the rows contained several blobs.
Kee,
Innodb may have some issues with auto_increment columns when it comes to inserts. You may have had that problem or it could be something else like suboptimal Innodb configuration or simply much larger Innodb tables so worse memory usage.
The fact you had blobs is yet another question as Innodb may not be optimal with these.
As always any benchmarks show performance in particular case – in your case opposite may well be true.
create table tf (id varchar(32),val1 decimal(20,2)) engine=falcon;
create table tn (id varchar(32),val1 decimal(20,2)) engine=innodb;
create table ti (id varchar(32),val1 decimal(20,2)) engine=myisam;
delimiter //
CREATE PROCEDURE inserttf(p1 int)
BEGIN
DECLARE v1 INT DEFAULT 0;
WHILE v1 set autocommit=on;
Query OK, 0 rows affected (0.00 sec)
mysql> call inserttf(5000);
Query OK, 1 row affected, 5000 warnings (3.27 sec)
mysql> call inserttn(5000);
Query OK, 1 row affected, 5000 warnings (2.73 sec)
mysql> call insertti(5000);
Query OK, 1 row affected, 5000 warnings (0.30 sec)
mysql> set autocommit=off;
Query OK, 0 rows affected (0.00 sec)
mysql> call insertti(5000);
Query OK, 1 row affected, 5000 warnings (0.31 sec)
mysql> call inserttn(5000);
Query OK, 1 row affected, 5000 warnings (0.25 sec)
mysql> call inserttf(5000);
Query OK, 1 row affected, 5000 warnings (0.17 sec)
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
mysql> call insertti(25000);
Query OK, 1 row affected, 25000 warnings (1.52 sec)
mysql> call inserttn(25000);
Query OK, 1 row affected, 25000 warnings (1.25 sec)
mysql> call inserttf(25000);
Query OK, 1 row affected, 25000 warnings (0.88 sec)
mysql> commit;
Query OK, 0 rows affected (0.03 sec)
delimiter //
CREATE PROCEDURE inserttf(p1 int)
BEGIN
DECLARE v1 INT DEFAULT 0;
WHILE v1< p1 DO
insert into tf values(replace(uuid(),’-‘,”),round(rand()*100,2));
SET v1 = v1 + 1;
END WHILE;
END
//
delimiter ;
delimiter //
CREATE PROCEDURE inserttn(p1 int)
BEGIN
DECLARE v1 INT DEFAULT 0;
WHILE v1< p1 DO
insert into tn values(replace(uuid(),’-‘,”),round(rand()*100,2));
SET v1 = v1 + 1;
END WHILE;
END
//
delimiter ;
delimiter //
CREATE PROCEDURE insertti(p1 int)
BEGIN
DECLARE v1 INT DEFAULT 0;
WHILE v1< p1 DO
insert into ti values(replace(uuid(),’-‘,”),round(rand()*100,2));
SET v1 = v1 + 1;
END WHILE;
END
//
delimiter ;
May please define:
1. What are the major criteria before choosing an Database engine?
2. Analyse Based on All aspects for all Engines.
Regards,
smartgk
Hey there,
I like your site very much, just found it somewhere, really nice and useful!
Good article, but I decided to post a comment because I’m not really sure what to think.
Recently we were importing 12GB of data in CSV format via PHP script into database. We used only 1 thread and we had to parse data before inserting into database.
Each row in the CSV file used 11 queries max (5 selects, 6 insert on duplicate key updates). Using InnoDB on all tables, import took 8 hours, as we switched to MyISAM, import took only 2,5 hour.
2 largest tables were 5,5mil and 1,4mil entries after import (before empty).
All selects and on updates were using primary key.
I’d be interested what made such difference, because I can see that InnoDb should be faster with 1 thread. What would be the performance increase using 3 threads?
Machine is 4x Xeon, 2GB RAM, although load was only about 1.25 while importing.
Hi Michal,
Please tell me if you have done the followings
1. set autocommit=0 so you do not commit after each insert. This is the difference between Innodb vs MyIsam
2. disable index keys (not uniques ones which u might need)
Catalinux
Hi,
thank you for your answer!
1. we didn’t manipulated autocommit, it had default value. I understand what autocommit does, but MyIsam is writing data to disk after each query too, isn’t it?
I’ll try importing data with autocommit=0 this night…
2. we use as less indexes as we can. Basically each table has it’s primary id key and then for the smaller table (1.4mil entries) we use algorithm to create non unique integer hash out of string. Cardinality of this index is very low (16%), have to work on better algorithm, possibly unique.
Btw I’ve heard Jay Pipes saying indexes with cardinality lower than 30% are worthless, true?
Michal,
For Innodb you are much better of loading data in primary key order.
Also have Innodb buffer pool large and logs large this helps a lot.
Transaction commit overhead is rarely the problem because data load transactions are typically large.
Catalinux, Peter,
thank you for helping me.
With autocommit off we managed to outperform MyIsam by 10%, but without Peter I would be solving another problem, because our innodb & log buffer was set too low and commit didn’t go through the first time.
Again thank you guys for your help!
Are you kidding? “Method of benchmark: 1. Prepare table with 1,000,000 records (about 350Mb of data on disk) …”
– With 16GB of RAM!!!
SolidDB also has published some tests, unbelievable “short-term” performance of Bonsai Tree, (NOT!!!) linear increase of performance by number of CPU.
It is really difficult to write good test scripts which can simulate CPU overloading vs. HDD overloading vs. network overloading vs. software bottleneck (blocking threads) etc. Is it possible with PHPTestSuite? WOW!!! Is it multithreaded version of PHP, or pre-fork 😉
The most important test scenario for the enterprise: maximum number of concurrent users successfully accessing a database with 3 seconds (!!!) response time. Yes, 3 seconds is a standard “acceptable” response time for modern distributed enterprise network applications. 1 second is “excellent”, and 10 seconds is “maximum allowable”.
“Concurrent users” have a behavior: 2-3 events (mouse clicks) per minute. 20 seconds per decision made.
For instance, single Apache HTTPD v.2 Worker can support about 20000 concurrent users (2Mhz CPU, 2Gb RAM, 20kb static HTML, mem-cache, keep-alive, …). Can MySQL back-end support 20000 concurrent users making simple SELECTs each 20 seconds and waiting for response 3 seconds?
Hi,
I performed some tests with InnoDB and SolidDB. InnoDB outperforms (3-4 times faster) SolidDB. I noticed that my load-generator gets overloaded, but InnoDB (different machine) is still 15-20% CPU, and only 2 CPU from 4 are used. With SolidDB, I have equal load (Server + Client), and Server uses 50%-60% of all CPUs. I even disabled logging for SolidDB, still same results.
InnoDB + UTF8 outperforms SolidDB + UCS2 – that’s unbelievable.
I published some info in blog, sorry for not having the time to provide all details…
http://bambarbiakirkudu.blogspot.com/
Thanks!
P.S.
“Load Generator” is very specific; 300 Java Threads concurrently updating database, 100s ‘simple’ select/insert per ‘atomic’ transaction per thread.
Sorry for typo: I was unable to use SolidDB + UCS2 with JDBC-based client. latin1 must be faster theoretically.
>>InnoDB + utf8 outperforms SolidDB + latin1 – that’s unbelievable.
Bambarbia Kirkudu,
Thank you for info.
I would like to see more details about benchmarks.
Also 300 concurrent threads is not something typical for web-workload.
Hi Vadim,
I updated blog today with more details. 300 threads are not typical; more typical are 1000 alive connections from Connection Pool (I am primarily Java developer). I set all thread-related numbers on MySQL InnoDB to high numbers; I/O threads = 512… Client application is a standalone Java process running concurrently 300 threads (each one has own connection), it’s not a web application. I even have evenly distributed loading (4 CPUs) on a single-instance MySQL dedicated box.
I’ll try to perform 1-week crawl with InnoDB, 1-week crawl with SolidDB, and compare results (total number of successfully crawled pages).
I had a problem with Oracle 10g, daily data corruption (Seagate Barracuda, probably overheated). After moving to Cheetah 15K.5 SAS no any problem during 6 months!!! Hope MySQL performs well; performance is much better than Oracle.
Even better and faster: I can design specific load-stress generator and run it instead; and to tune databases without making noise on Internet 😉 (without crawler).
Nice test, nice results. This results have influence on my conclusion MyISAM vs InnoDB. InnoDB win :).
Is it possible to get either the test data set and / or some data relating the selectivity of the various columns to I can try to re-create this test with some accuracy?
Thanks.
Scott,
You can get sysbench and Lua script from
http://www.mysqlperformanceblog.com/2007/04/08/pbxt-benchmarks
to generate dataset and load.
Best,
Vadim
This test has a fundamental flaw to me. The test is read only. In real world, there are writing actions. Between MyISAM and InNoDB Let say R/W ratio is 10:1. I bet my pay check, you will see very different result (I will expect MyISAM are away better than others). If R/W ratio is 1:10. You may see something totally different from all others. So choose storage fits your needs. My question is how can I convince my boss to trash MS SQL. MS Tortures me :(.
William,
You are right that with Read/Write the result will be different, though I believe Read-only benchmark
gives initial impression of Storage Engine.
Read-Write benchmark is much more complex and requires more time to design, run and handle results.
Complexity does not scare me, nut time is the problem.
Unfortunatelly I’ve never dealt with MS SQL and moverover never had bosses who love MS SQL, so I am useless here.
Would you give us some Update test for large table, for example 30,000,000
The above charts are all nice but there are no write statements and results for write statements. My write test shows what InnoDB is slower than MyISAM when it comes to inserting new rows of data and updating those rows with new/updated information. I have not done any read tests but in my view the InnoDB will be slightly quicker than MyISAM, but only slighty. When it comes to writing data, MyISAM is about 30% quicker.
Da Man: When you test MyISAM do you test it with lots of parallel access going on at the same time?
I think the main advantage InnoDB has is that it’s MVCC locking mechanism allows lots of writes to happen without blocking reads, and vice versa. I.e. it should scale better than MyISAM under parallel load.
Hi,
exactly how could I possibly check to confirm the mysql engine on a given database, is there a command of some sort that could reveal whether or not the engine is innoDB or myISAM? Thanks.
I have tried to perform some benchmarking myself today, allthough only to see if InnoDB could prove to yield greater performance than MyIsam on my system.
I had 7 instances of a program set up on one server and basically had them hammer the database-table, first with 50.000 inserts each, and then wait until all inserts were done, and then all 7 programs performed an update on a random row (using primary key) of those inserted.
And the numbers are so close between InnoDB and MyIsam, the differences is not really noticable, and this puzzles me greatly:
I was under the impression that MyIsam performed a table-lock on each update, while InnoDB would only lock the row. So I expected InnoDB to win this one easily, not having to wait for the table to unlock all the time. Apparently this didn’t happen!
Is there any special trick to enable this row-locking, or is it always on and working?
And why isn’t the MyIsam results showing signs of having to wait for the locked table all the time?
Are 7 threads simply not enough for innoDB to get the upper hand?
You only had 7 connections which is why your performance between MySQL and Innodb was so close. Try increasing it to 100 threads and then 300 threads. I think you’ll see a difference.
Mike
Thanks for the useful post!
Need to know whether SolidDB is better/worse than InnoDB. I am evaluating SolidDB ?
Vadim,
In general: each tool has an area to be applied for, and all ‘universal’ is called ‘horilla’ in MySQL forums: Oracle, DB2. Want to compare test results for a simple query SELECT COUNT(*) FROM …?
Theoretically, InnoDB is faster than MyISAM (or MyISAM-based transactional SolidDB) for multi-user concurrent access because of physical data files format (in InnoDB, data is written into fixed-size blocks; in MyISAM, data is usually written at the end of file).
My database currently consists from mixed InnoDB and MyISAM datafiles. I need MyISAM for dictionary-like tables (like as list of countries, cities, postal codes), and I need InnoDB for frequently updated data.
One weakness: JDBC driver + Prepared Statements.
Thanks