Independent Consultant (Toronto, Canada) specializing in Lucene, Hadoop, HBase, Nutch, SOLR, LingPipe, GATE, Data Mining, Search Engines, WebLogic, Oracle, Liferay Portal, Java, J2EE, SOA, and more.
Master in MATH, Moscow State University n.a.Lomonosov
I got it.
I used Oracle 10g before that, and tried to evaluate MySQL.MySQL Server:
2x AMD Opteron 852 (2.6 GHz, double-core), 14Gb RAM, SuSE Linux Enterprise Server (SLES 10)InnoDB:
8Gb allocated, etc. MySQL 5.0.41, UTF-8SolidDB:
8Gb. MySQL 5.0.27, LATIN1Client:
2x AMD Opteron 246 (2.0GHz, single-core), 8Gb RAM, SuSE Linux Enterprise Server (SLES 10)Client Application:
Web-Crawler, Java Based. 300 Java Threads concurrently fetch HTML pages from Internet, parse it, and store in a database. Each [PARSE] operation generates in average 300 new records in a database within single transaction, including LONGTEXT column (about 128Kb in average). Most frequently used DML with initially empty database: SELECT, INSERT. Only two tables involved, parent-child.
Each Thread crawls specific Internet host and has 2.5 seconds delay between subsequent fetches; plus delay originated by transaction time. Data is organized by Internet hosts (index) so that I don't have any 'concurrency' and competition for data locks.
I had initially very strange performance problem with both InnoDB and SolidDB: very long running transactions, 1-3 minutes in average. Note that I had 300 concurrent transactions and each inserts 300 new records in a database. With Oracle 10g I had only 10-20 seconds per transaction!
Fortunately I found that I need to disable IP-to-Host name resolution via my.cnf.
Here are final results after 10 hours of execution (when system gets stabilized and has enough data):InnoDB
- 450 transactions per minute, 95% CPU on Client, 10% CPU on ServerSolidDB
- 250 transactions per minute (I need to retest it; it was 250 during first hour, then I changed to InnoDB), 60% CPU on Client, 60% CPU on Server
Oracle 10g - 150... but server was on the same machine as client.
It looks like InnoDB performs much-much better than 450/minute; Client application was simply overloaded.
SolidDB can't support even ucs2 (at least for JDBC-based client). Even with latin1 it is outperformed by InnoDB (UTF8).
It's really weird... most people still believe that 'pure' TPS means everything and forget about concurrency in a real world.
Labels: MySQL InnoDB SolidDB MyISAM Performance Java