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:
`c2` char(100) DEFAULT NULL,
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.



Lasse said...

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

Karlsson said...

Yes, look at my previous blog here:

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

marko said...

In the wake of finding specialists, the following step is to approach them for quote and alongside that he will be obliged to fill an application structure asking some individual and area inclination point of interest. payday loans

marko said...

Attempt to figure out what might be your vehicle credit Vacaville installment every month by simply bolstering the Sacramento Car advances subtle elements into an online auto advance adding machine. It will consequently produce information demonstrating the amount you are required to spend on hobby and what number of such portions are to be made altogether. cash advance

JON said...

Envision trucking without end with the exchange for exchange your take in for spendable hitter what could be not absolutely an hour! Vivacious cash is clear when you utilize a vehicle title credit. Payday Loans