January 8, 2007

InnoDB vs MyISAM vs Falcon benchmarks – part 1

Posted by Vadim |

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
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
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
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
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
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
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
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
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
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
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
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
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
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.

Posted by Vadim @ 1:58 am :: Innodb, benchmarks, myisam  
Print This Post Print This Post book mark InnoDB vs MyISAM vs Falcon benchmarks – part 1 in del.icio.usdel.icio.us :: submit InnoDB vs MyISAM vs Falcon benchmarks – part 1 to digg.comdigg  
Comment RSS :: Trackback URI  
Related posts: :Will Falcon fly?::MyISAM Scalability and Innodb, Falcon Benchmarks::MySQL Northern European Customer Conference:
 

80 Comments »

  1. 1. Johan Bergström

    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

    Comment :: January 8, 2007 @ 2:26 am

  2. 2. peter

    As I remember Larry has just changed the protocol, so get new free client here:
    http://www.bitkeeper.com/Hosted.Downloading.html

    Comment :: January 8, 2007 @ 2:50 am

  3. 3. David Shrewsbury

    Very good post and very informative. The InnoDB vs. MyISAM performance numbers were just as interesting as the performance of Falcon.

    -Dave

    Comment :: January 8, 2007 @ 6:04 am

  4. 4. Vadim

    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.

    Comment :: January 8, 2007 @ 7:27 am

  5. 5. Xaprb

    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?

    Comment :: January 8, 2007 @ 8:03 am

  6. 6. peter

    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

    Comment :: January 8, 2007 @ 9:14 am

  7. 7. bp

    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.

    Comment :: January 8, 2007 @ 9:50 am

  8. 8. LenZ

    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.

    Comment :: January 8, 2007 @ 9:51 am

  9. 9. pabloj

    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

    Comment :: January 8, 2007 @ 9:52 am

  10. 10. LenZ

    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.

    Comment :: January 8, 2007 @ 10:13 am

  11. 11. Charles

    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…

    Comment :: January 8, 2007 @ 11:28 am

  12. 12. Vadim

    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.

    Comment :: January 8, 2007 @ 1:36 pm

  13. 13. peter

    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.

    Comment :: January 8, 2007 @ 2:20 pm

  14. 14. Breezes

    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?

    Comment :: January 9, 2007 @ 2:18 am

  15. 15. MySQL

    Results are interesting, especialy MyISAM vs InnoDB.

    Comment :: January 9, 2007 @ 2:36 am

  16. 16. peter

    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.

    Comment :: January 9, 2007 @ 3:08 am

  17. 17. BOLK

    Very useful. Thank you!

    Comment :: January 9, 2007 @ 7:57 am

  18. 18. Aleksey Kishkin

    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?

    Comment :: January 15, 2007 @ 3:31 am

  19. 19. peter

    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 :)

    Comment :: January 15, 2007 @ 4:21 am

  20. 20. Vadim

    Alexey,

    I know you have an experience in benchmarks, so your results against 100.000.000 records are welcome :)

    Comment :: January 15, 2007 @ 4:58 am

  21. 21. Kee Hinckley

    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.

    Comment :: January 16, 2007 @ 8:51 am

  22. 22. peter

    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.

    Comment :: January 16, 2007 @ 8:58 am

  23. 23. l1t

    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)

    Comment :: March 11, 2007 @ 6:24 pm

  24. 24. l1t

    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 ;

    Comment :: March 11, 2007 @ 6:39 pm

  25. 25. smartgk

    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

    Comment :: March 20, 2007 @ 1:26 am

  26. 26. Michal

    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.

    Comment :: April 9, 2007 @ 3:13 am

  27. 27. Catalinux

    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

    Comment :: April 10, 2007 @ 10:56 am

  28. 28. Michal

    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?

    Comment :: April 10, 2007 @ 2:31 pm

  29. 29. peter

    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.

    Comment :: April 11, 2007 @ 2:23 am

  30. 30. Michal

    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!

    Comment :: April 12, 2007 @ 1:29 am

  31. 31. Bambarbia Kirkudu

    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?

    Comment :: April 24, 2007 @ 11:44 am

  32. 32. Bambarbia Kirkudu

    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.

    Comment :: May 21, 2007 @ 9:05 pm

  33. 33. Bambarbia Kirkudu

    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.

    Comment :: May 21, 2007 @ 9:07 pm

  34. 34. Vadim

    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.

    Comment :: May 22, 2007 @ 1:41 am

  35. 35. Bambarbia Kirkudu

    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.

    Comment :: May 22, 2007 @ 9:40 am

  36. 36. Bambarbia Kirkudu

    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).

    Comment :: May 22, 2007 @ 9:47 am

  37. 37. Vladimir

    Nice test, nice results. This results have influence on my conclusion MyISAM vs InnoDB. InnoDB win :) .

    Comment :: May 24, 2007 @ 12:50 pm

  38. 38. Scott Marlowe

    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.

    Comment :: May 31, 2007 @ 1:30 pm

  39. 39. Vadim

    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

    Comment :: May 31, 2007 @ 1:55 pm

  40. 40. William Wang

    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 :( .

    Comment :: June 7, 2007 @ 5:05 pm

  41. 41. Vadim

    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.

    Comment :: June 7, 2007 @ 11:28 pm

  42. 42. chunning

    Would you give us some Update test for large table, for example 30,000,000

    Comment :: June 14, 2007 @ 4:25 am

  43. 43. Da Man

    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.

    Comment :: July 10, 2007 @ 2:07 am

  44. 44. Scott Marlowe

    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.

    Comment :: July 10, 2007 @ 9:40 am

  45. 45. ire

    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.

    Comment :: July 11, 2007 @ 2:12 am

  46. 46. Christian

    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?

    Comment :: August 14, 2007 @ 4:52 am

  47. 47. Mike

    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

    Comment :: August 26, 2007 @ 12:33 pm

  48. 48. free ps3

    Thanks for the useful post!

    Comment :: September 2, 2007 @ 3:55 am

  49. 49. Rama

    Need to know whether SolidDB is better/worse than InnoDB. I am evaluating SolidDB ?

    Comment :: September 4, 2007 @ 9:40 am

  50. 50. Bambarbia Kirkudu

    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

    Comment :: September 20, 2007 @ 7:12 am

  51. 51. azazel

    Try this test SELECT COUNT(*) FROM $tableName;

    Comment :: October 13, 2007 @ 11:05 am

  52. 52. RDG

    Any chance for “Part 2″ based on more recent releases?

    Comment :: October 19, 2007 @ 12:17 pm

  53. 53. Vadim

    RDG

    Yes, We are going to test next release of Falcon

    Comment :: October 19, 2007 @ 12:31 pm

  54. 54. Shaun

    Great information.

    I was having trouble deciding between MyIsam and InnoDB. I wanted to create a log for everything that happened on my website, so I created a log table. But…what if a user inserts/updates something and then the PHP script fails before updating the log table?

    I realized that I should use transactions, but unfortunately I had to give up the “speed” of MyISAM for InnoDB. Your benchmarks make me feel much better about my decision. =)

    Comment :: November 1, 2007 @ 5:05 am

  55. 55. Jacob

    Hi,

    I measured the performance of InnoDB vs MyISAM for insertion queries for up to 100 threads (see presentation “Measuring MySQL Server Performance for the Sensor Data Stream Processing” at MySQL User Conference 2006. Unless you have very short records (less than 200 bytes) MyISAM outpreforms InnoDB for insertion speed. For the recird sizes between 1KB and 10MB MyISAM is about twice faster.

    Comment :: November 7, 2007 @ 3:46 pm

  56. 56. Scott Marlowe

    Inserts by themselves aren’t all that interesting. What is interesting is what happens to 20 reading threads when one inserting thread shows up.

    Or how do reading threads behave when updating threads show up.

    Or when you’ve got say 20 inserting threads, and then you start one long reading thread that hits a lot of data.

    Each of those use cases simulates some kind of real world behaviour.

    100 inserting threads, with no readers simulates a real world behaviour too, so I’m not slamming it. It’s a pretty common behavior in things like petroleum exploration.

    But, the situation where you have mixed selects, updates, and inserts if far more common, and usually far more interesting.

    Comment :: November 7, 2007 @ 4:57 pm

  57. 57. Jacob

    I don’t think that statement “inserts by themselves aren’t all that interesting” is correct. What about logging? What if you have sensors writing into a database? Is the database able to keep up? Those are VERY interesting and important cases.

    For me pure reading is not not interesting at all – you have to insert before you read.

    Inserts are much slower than reads, so if you two times slower with inserts your reading speed might be not important – you have to wait for inserts to get your data.

    Comment :: November 8, 2007 @ 9:07 am

  58. 58. Scott Marlowe

    QUOTE:
    What about logging? What if you have sensors writing into a database?
    UNQUOTE:

    Um, you did read my whole post, right? I made the point that write only is still used, and an example of petroleum exploration, which is exactly that, sensors writing into the database.

    Either table handler can likely keep up just fine with the insert load. But what happens if insert performance falls 50% on one table handler when a large query runs against your logs, while another table handler drops by only 2%? That’s far more interesting. Or what happens when you have a few small selects against that heavily written table running all the time?

    You ARE going to query the log table at some point, and if you’re like me, you’re likely going to need to query it at the worst possible times, in the middle of the day when the system is acting up. You need to read the logs, and if your table handler has a tendency to explode in sheets of flame when you do that, you really need to know THAT, so you can change table handlers (or databases etc…) before you find out in production. After all, what good would logs be that you can’t read?

    The point I’m making is that the INTERACTION of reads and writes is what makes for an interesting thing to study. The individual bits, just reads, or just writes are a nice baseline. It’s like chemistry class. Water, kinda intersting. Sodium, kinda interesting. Water AND sodium, much much more interesting.

    Comment :: November 8, 2007 @ 9:23 am

  59. 59. Christian

    “I realized that I should use transactions, but unfortunately I had to give up the “speed” of MyISAM for InnoDB. Your benchmarks make me feel much better about my decision. =)”

    Allthough using transactions is desirable in many cases, you should also consider calling stored procedures if you are worried about some kind of client-failure, like a php-script crashing etc.
    If all the PHP-script did was to call a Stored Procedure with the required parameters, the crash would not hurt the database-operations.

    A real-world example:
    A typical user-signup process may call several checks, updates and inserts.
    In a php-script, this could look like (pseudo code)
    - Check validity
    - Insert user-data (which can be one or more rows in different tables)
    - Possibly fetch the new “auto-increment” id for the user, and use this to further insert rows in other tables
    - Other logic
    - Check and verify that everything went ok
    - Send feedback to the client

    Here you can easily make a stored procedure that handles everything, from checking, inserting, updating etc.
    The PHP-script would then simply look like
    - Check validity
    - Call the stored procedure with the required parameters
    - Send feedback to the client

    So, you could stick to MyIsam here, and still make sure crashing scripts do not cause problems.

    Just my 2 cents :)

    Comment :: November 8, 2007 @ 9:35 am

  60. 60. Christian

    “The point I’m making is that the INTERACTION of reads and writes is what makes for an interesting thing to study. The individual bits, just reads, or just writes are a nice baseline. It’s like chemistry class. Water, kinda intersting. Sodium, kinda interesting. Water AND sodium, much much more interesting.”

    Indeed. More often than not, real-word usage consists of reading and writing to the database simultanously, and how one affects the other is of utmost importance. If Storeage-Engine A is faster when inserting data, this means nothing if the speed drops a lot when 30 threads are inserting at the same time.

    This is what makes Benchmarking difficult, and forces us to interpret results carefully.

    Comment :: November 8, 2007 @ 9:38 am

  61. 61. runescape money

    The point I’m making is that the INTERACTION of reads and writes is what makes for an interesting thing to study. The individual bits, just reads, or just writes are a nice baseline. It’s like chemistry class. Water, kinda intersting. Sodium, kinda interesting. Water AND sodium, much much more interesting.”

    Indeed. More often than not, real-word usage consists of reading and writing to the database simultanously

    Comment :: November 24, 2007 @ 7:07 pm

  62. 62. Nadeem

    Interesting but for selects.
    Can anyone suggest if which engine out of InnoDB and MyISAM should be used for a forum based site?

    Comment :: December 24, 2007 @ 9:41 am

  63. 63. karpakavalli

    I can understood how the best is InnoDB from ur practical example.

    Comment :: January 7, 2008 @ 5:55 am

  64. 64. Antivirus

    This is what makes Benchmarking difficult, and forces us to interpret results carefully.

    Comment :: January 7, 2008 @ 2:02 pm

  65. 65. Cevarief

    Very interesting benchmark. Anyway, when will the InnoDB vs MyISAM vs Falcon benchmarks – part 2 availabe ? I’m very curious to see the progress after 1 year from part 1.

    Comment :: January 8, 2008 @ 9:54 pm

  66. 66. Tomas

    I found nice application stress test at spamassassin
    http://wiki.apache.org/spamassassin/BayesBenchmarkResults

    In this test MyISAM won, anyway quite interesting numbers.

    Comment :: January 20, 2008 @ 5:15 am

  67. 67. 環保袋

    The InnoDB vs. MyISAM performance numbers were just as interesting as the performance of Falcon.

    Comment :: January 25, 2008 @ 10:06 pm

  68. 68. Edy Ursus

    Thanks for sharing.

    Comment :: March 17, 2008 @ 12:13 am

  69. 69. 創盈分類資訊網

    Results are interesting, especialy MyISAM vs InnoDB.

    Comment :: April 8, 2008 @ 8:54 am

  70. 70. ffxi gil

    Try increasing it to 100 threads and then 300 threads. I think you’ll see a difference.

    Comment :: April 18, 2008 @ 10:25 pm

  71. 71. Renato

    Vadim,
    I only have one question. Which type of MYISAM rows did you use ? Fixed or Dynamic ?
    I am pretty sure that the performance will be completely different when you use one or other !

    Thanks,

    Renato

    Comment :: May 27, 2008 @ 7:52 am

  72. 72. forum

    Thank you for information,

    I useing MyISAM!

    Comment :: May 27, 2008 @ 1:28 pm

  73. 73. Design

    Once we query the log table we read over the logs. Our table handler did very well with it. For each thread we performed the warm-up run (duration 180 sec), and then ran three effective runs (duration of each is 60 sec). Only problem our final result was much different.

    Comment :: July 2, 2008 @ 12:17 am

  74. 74. itix

    Once we query the log table we read over the logs. Our table handler did very well with it. For each thread we performed the warm-up run (duration 180 sec), and then ran three effective runs (duration of each is 60 sec). Only problem our final result was much different.

    Comment :: August 23, 2008 @ 10:27 pm

  75. 75. runescape money

    InnoDB tables are mostly faster than MyISAM. The only thing MyISAM supports is full-text search which is crappy and slow on large datasets anyway

    Comment :: September 8, 2008 @ 2:24 am

  76. 76. Dexter

    Very intresting, i think i should migrate to InnoDB with my site’s.

    Comment :: December 27, 2008 @ 12:57 pm

  77. 77. iGuide

    How about a speed test with a MyISAM-packed table vs InnoDB? InnoDB’s great, but if you’re uploading massive tables back and forth to your server, MyISAM will be less of a hassle due to much smaller files.

    Comment :: January 9, 2009 @ 10:18 pm

  78. 78. Andy

    This test is not neutral. MyISAM is disadvantaged from the beginning, since MyISAM is optimized to be used in combination with CHAR fields in contrast to VARCHAR fields. This given, MyISAM is optimized for read access when fixed row size is given.
    To make a statement comparing the InnoDB and MyISAM engine the table has to be set up differently for MyISAM. Then it would be very interesting to see the results especially concerning reads for MyISAM compared to InnoDB and Falcon. Likely MyISAM will beat InnoDB is at least some of the stated queries above.

    Comment :: April 23, 2009 @ 1:02 am

  79. 79. Eric

    In my case, I need to insert/update 40,000+ rows quite often
    InnoDB will take me around 1 hr
    compare with MyISAM only will take no more than 70 “SECONDS”

    Comment :: February 9, 2010 @ 6:49 pm

  80. 80. Alexandre

    Eric, you should open a transaction (BEGIN TRAN) before inserting a large number of rows, them COMMIT.

    Comment :: February 27, 2010 @ 10:38 am

 

Subscribe without commenting

Trackbacks/Pingbacks