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
-
For Falcon
-
./configure --prefix=/usr/local/mysqltest/mysql-<RELEASE> --with-falcon
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.
del.icio.us
digg
1025
到【灌水乐园】发言
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:01 am
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:01 am
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:01 am
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:01 am
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:01 am
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:01 am
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:01 am
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:01 am
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:01 am
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:01 am
[…] napisałem ostatnio o kolejnych testach jakie robili kolesie z tweakers.net. bluszcz wtedy skomentował, że chciałby zobaczyć testy postgresa z soliddb lub choćby falconem, a nie z kiepskawym innodb. prosił i ma blog mysql performance przeprowadził testy innodb, myisam i falcona. właśnie pod kątem skalowalności. efekt - ogólna, praktycznie całkowita porażka falcona. przykłady wykres wyników jednego z testów wygląda tak: przy czym uwaga: nie wybrałem takiego na którym falcon wyszedł najgorzej. są takie wykresy gdzie linia od wydajności falcona "leży" na osi. do tego dochodzi jeszcze takie jedno zdanie: "… Falcon cannot handle LIMIT properly …" (wyjęte z kontekstu, ale sens niezmieniony). czekam teraz aż ktoś pokaże testy z soliddb. wtedy też na pewno ja zaprezentuję. […]
Pingback :: January 8, 2007 @ 10:01 am
[…] There’s an interesting post over at the Mysql Performance blog testing performance differences between several storage engines. Their tests show that for some micro benchmarks that cover a lot of the basic usage patterns of databases in a web type environment, that InnoDB can actually be much faster than MyIsam. This goes against the prevailing belief that MyIsam was the fastest for read access especially in the very read heavy world of Web applications and that InnoDb was only used when transactions were required. […]
Pingback :: January 8, 2007 @ 11:01 am
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:01 am
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:01 pm
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:01 pm
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:01 am
Results are interesting, especialy MyISAM vs InnoDB.
Comment :: January 9, 2007 @ 2:01 am
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:01 am
Very useful. Thank you!
Comment :: January 9, 2007 @ 7:01 am
[…] InnoDB vs. MyISAM vs. Falcon in MySQL MySQL Performance Blog — InnoDB vs MyISAM vs Falcon benchmarks. Great test and lots of figures and graphs. It highlights two things. (1) Falcon is far from being mature, Jim still got lots to do, but I guess that’s still pretty impressive after 11 months of work (2) MyISAM toasts InnoDB in read performance is a myth. InnoDB rules for now with low concurrency. Eagerly waiting for part II. Tagged in Linky, MySQL | 2007-01-10 11:07 […]
Pingback :: January 9, 2007 @ 6:01 pm
[…] 叫做Falcon,猎鹰的意思,名字够酷. 昨天看到这个消息的时候还着实使劲的期待了一下,不过看到了Peter Zaitsev的一篇文章,一下子就浇灭了我那可怜的盲目热情.这篇文章是对InnoDB,MyISAM和Falcon三种引擎性能的一个对比.从对比结 果可以看出,Falcon和社会主义一样,还处于初级阶段.除了在READ_KEY_POINT这一项上有微弱的优势以外,几乎都严重的处于下风.不过另 我欣慰的是,InnoDB的表现基本都要好于MyISAM,看来InnoDB确实是个不错的选择,除了支持事务和外键等特性,查询性能也至少不输给 MyISAM,可以放心大胆的使用. […]
Pingback :: January 9, 2007 @ 8:01 pm
[…] 在 MySQL 把 Falcon Storage Engine 放進來後 (MySQL Falcon Storage Engine Open Sourced)，有人就在 Linux 上跑起來，同時對 MyISAM 與 InnoDB 做了一些簡單的 benchmark：InnoDB vs MyISAM vs Falcon benchmarks - part 1，跑出來的結果 Falcon 大敗… :p […]
Pingback :: January 12, 2007 @ 8:01 am
[…] Het populaire MySQL Performance Blog zit er boven op en heeft de eerste benchmarks tussen Falcon, MyISAM en InnoDB alvast online gezet en heeft ook z’n eigen gedachten over het ontwerp van Falcon geplaatst. Gerelateerde artikelen […]
Pingback :: January 13, 2007 @ 2:01 am
[…] Vadim Tkachenko wrote: […]
Pingback :: January 14, 2007 @ 6:01 pm
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:01 am
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:01 am
Alexey,
I know you have an experience in benchmarks, so your results against 100.000.000 records are welcome
Comment :: January 15, 2007 @ 4:01 am
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:01 am
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:01 am
InnoDB vs MyISAM vs Falcon 벤치마크…
InnoDB vs MyISAM vs Falcon benchmarks - part 1, http://www.mysqlperformanceblog.com/2007/01/08/innodb-vs-myisam-vs-falcon-benchmarks-part-1/Peter Zaitsev가 자신의 블로그에 흥미로운 벤치마크 결과를 올렸다.The second goal of benchma…
Trackback :: January 17, 2007 @ 3:01 am
First thoughts regarding the MySQL Falcon storage engine…
Unfortunately, just based on reading the Falcon documentation, I must draw the conclusion that without extensive further development, it won’t be usable for very large installations such as the ones we run for Habbo for a number of reasons. I’m usual…
Trackback :: January 19, 2007 @ 11:01 am
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:03 pm
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:03 pm
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:03 am
[…] This time I tested only READ queries, similar to ones in benchmark InnoDB vs MyISAM vs Falcon (http://www.mysqlperformanceblog.com/2007/01/08/innodb-vs-myisam-vs-falcon-benchmarks-part-1) The difference is I used new sysbench with Lua scripting language, so all queries were scripted […]
Pingback :: April 8, 2007 @ 12:04 pm
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:04 am
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:04 am
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:04 pm
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:04 am
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:04 am
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:04 am
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
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:05 pm
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:05 am
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:05 am
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:05 am
Nice test, nice results. This results have influence on my conclusion MyISAM vs InnoDB. InnoDB win :).
Comment :: May 24, 2007 @ 12:05 pm
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:05 pm
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:05 pm
[…] too easy. We ran a cluster on some dev boxes at first. We did some generic testing using the PHPTestSuite from the guys at MySQL Performance Blog. What we found was that while the cluster appeared slower […]
Pingback :: June 7, 2007 @ 10:06 am
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:06 pm
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:06 pm
Would you give us some Update test for large table, for example 30,000,000
Comment :: June 14, 2007 @ 4:06 am
Thanks to DDO Plateveryone for their kind words.
I’m always on the lookout for “how to WordPress” aEQ2 Gold rticles and sites. There are a lot of great articles out there, though few sites totally dedicated to WordPress, like mine. What I find mostly is a good “start” but no “finish”. Good intentions, but little follow through. After they’ve done the “how to post”, “how to podcastFFXI Gil ”, and “how to fix the sidebar”, they fade off either frustrated or bored.
The WordPress Codex is the online manual for WordPress Guild Wars Gold users written by WordPress users who have “been there, done that, borked WordPress, and lived to tell about it”. I will be leading a maLineage 2 Adena
jor campaign soon to update it and add new articles, so stay tuned for more WordPress joy for everyone at all levels
Comment :: June 19, 2007 @ 6:06 pm
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
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:07 am
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:07 am
なんとしてでも、地球を死の惑星にはしたくない。未来に向かって、
地球上のすべての生物との共存をめざし、むしろこれからが、
人類のほんとうの“あけぼの”なのかもしれないとも思うのです。
Comment :: August 1, 2007 @ 12:08 am
ching chang hung wi su mi kai? ne InnoDb wu MYISAM yao pen mu xi hu wa?
Comment :: August 3, 2007 @ 9:08 am
Guys 58 and 59
I would like to see all converations in this blog on English.
Comment :: August 3, 2007 @ 10:08 am
[转]InnoDB vs MyISAM vs Falcon benchmarks - part 1…
国外一篇关于Mysql存储引擎：InnoDB、MyISAM、Falcon(猎鹰) 的一些测试结果数据，可以明显发现InnoDB的性能已经略有超过MyISAM引擎，至于Falcon的性能就比较一般了，因为刚刚起步的原因。本文就是…
Trackback :: August 3, 2007 @ 6:08 pm
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:08 am
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:08 pm