Tuesday, June 29, 2010

Time for an UPDATE using the Maria Storage Engine then...

Again, another simple test. Same basic tuning as yesterday, and the table schema is the same:
CREATE TABLE `t1` (
`c1` int(11) NOT NULL AUTO_INCREMENT,
`c2` char(100) DEFAULT NULL,
PRIMARY KEY (`c1`)
);
The table is again filled with 1.3 million rows, but this time I'm doing an UPDATE. The update is again a simple primary key update:
UPDATE t1 SET c2 = CONCAT('xxx', RAND(), 'yyy') WHERE c1 = <random value 1 - 1000000>;
I run this on the Maria, InnoDB and MyISAM engines. The issues with the MyISAM and Maria engines here is that they lack row level locking. In MariaDB 5.1.47, the InnoDB version is 1.0.6, so it is more scalable than what it used to be. The testbench is not an incredibly hot machiine, just a 4 core AMD box.

I run the test in some different configurations, using a single thread, using 10 threads and using 100 threads. Here we can see that InnoDB Row-level locking really hlps. With InnoDB, performance was on par with Maria with 1 thread, MyISAM lagging behind (yes, here Maria was faster than MyISAM, it might be some MyISAM tuning that I missed, beyond basic key_buffer_size and all that).

Moving to 10 threads, InnoDB could now perform about twice as many operations per second, whereas Maris throughput increased by aout 15%, MyISAM stayed the same old slow. Going to 100 threads, again InnoDB nearly doubled the number of ops, Maria abot 15% up, and MyISAM still about the same, actually slightly slower both at 10 and 100 threads.

Is this a scientific test? Nope, hardly. Does it reflect something that might be happening in a system in the real world? Yes, I'd say so, given that there would be other things going on also.

All in all, I think the Maria Engine has improved a bit, and may be an alternative to MyiSAM, if it wasn't for the fact that InnoDB really kicks them both in their rearward facing bodily regions.

/Karlsson

3 comments:

Lasse said...

hej Anders,
Have you compared read i.e. select between the engines?

Karlsson said...

Yes, look at my previous blog here:
http://karlssonondatabases.blogspot.com/2010/06/mariadb-performancxe-once-again.html

muhammad adil said...

A really good article you've written here. I really love your idea! I want to get inside your brain and pump out all these brilliant ideas that you get. I could earn a fortune with these ideas. Even you could earn a fortune with your brilliant ideas. prostate massage therapy