Bambarbia Kirkudu

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

Monday, May 21, 2007

 

MySQL: InnoDB Outperforms SolidDB in Real World Application

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

SolidDB: 8Gb. MySQL 5.0.27, LATIN1


Client:
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 Server

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

So...

It's really weird... most people still believe that 'pure' TPS means everything and forget about concurrency in a real world.

Labels:


Comments:
Unfortunately I am busy to check all that staff... it depends on a kind of application... Sure, we need multithreaded load simulator. all published results are fake so far.
 
Hi!

Thanks for trying out solidDB for MySQL with your database application.

We'll be adding UCS2 and UTF8 support in our next release which is due out very soon.

Since you have so much available memory (14GB), I'd suggest that you increase the cache size. You can do this with the soliddb_cache_size variable. For more information, see the solidDB for MySQL User Guide.

Which version of solidDB for MySQL are you using? Make sure to mention the last four digits in the version number. For example, our current version is solidmysql-5.0.37-0070.

I'd also be interested to look at the my.cnf that you created. Could you post that file, your JDBC code, and the database schema that you are using? Which version of JDBC driver are you using?

Feel free to post any questions you might have on the forum at Solid's site.

Jonathan Cheyer
Open Source Community Development
Solid Information Technology
 
Hi Jonathan,

The version of SolidDB: solidmysql-5.0.37-0070

Unfortunately I can't continue with tests... looks like my RAID 0 with 4 Seagate Cheetah 15K.5 300Gb gets overheaten once a day... I'll try to add additional fans, I currently use Zalman aluminum HDD coolers.


That was my first feelings: I had 50-60% evenly distributed CPU loads with SolidDB and underloaded Client machine, and I currently have 10% evenly distributed load with InnoDB and overloaded Client machine.

In both cases I used the same Java-based multithreaded application.

It is simple enough: each thread has "own" data (accessed via specific index) so that I don't have any contention (blocking threads/locked records); a lot of single-row INSERT/UPDATE/SELECT statements united into single transaction.

Memory parameters were about 10-12 Gb in both cases.

UTF8: nice to have... As a workaround I can store BLOBs instead of VARCHAR, MySQL can index and compare short BLOBs. I already moved from TEXT to BLOBs, some problems with utf8 in MySQL.

I'll try to write specific test scenario in Java and to publish it (+config files +SQL schema); in any case it should help with tuning (and overheating!)

Thanks!
 

Post a Comment

Subscribe to Post Comments [Atom]



Links to this post:

Create a Link



<< Home

Archives

May 2007   June 2007   July 2007   August 2007   October 2007   April 2008   June 2008   July 2008   August 2008   January 2009  

This page is powered by Blogger. Isn't yours?

Subscribe to Posts [Atom]