Comparing MySQL and MariaDB

MariaDB appears to be a direct replacement for MySQL and with additional enhancements and fixes.

For almost a week, I’ve made some performance tests using both an XP 32-bit and Windows 8 64-bit utilizing the MyISAM ENGINE.  The versions I considered were MySQL 5.1, 5.5, 5.6 and MariaDB 5.5,10.0.


NOTE:  My "Test" DATA consisted of 20 Million ROWs averaging 157 characters per ROW in one DATABASE of 100 properly indexed MyISAM-TABLEs.


I discovered significant performance differences between MySQL 5.1, 5.5 and 5.6.  MySQL 5.1 seems to be the fastest MySQL for both IMPORT and SELECT.  I have no idea why 5.1 was the winner BUT it is the reason I choose it to compare with MariaDB.

I did not find any noticeable difference between MariaDB 5.5 and MariaDB 10.0 THEREFORE I choose MariaDB 10.0 to compare with MySQL 5.1.

In my opinion, MariaDB 10.0 outperforms MySQL 5.1 by 10% to 15%.  The performance of MariaDB 10.0 between a 64-bit AMD A8-5500 3.20 MHz outperformed the 32-bit Intel P4 2.66 MHz very close to 3 times.  The hard drives in both systems for the data were formatted with 64K blocks.

After these tests and knowing that the original developers of MySQL are behind the new MariaDB development, I’m recommending the move to MariaDB 10.0.


As for the MyISAM ENGINE:

I think there’s too much negativity on the internet about using it.  I think everyone agrees that MyISAM is the fastest of all of the available ENGINES but the consensus is that it’s the most risky for potential data loss.

The MyISAM risk can be significantly reduced by using FIXED LENGTH ROWS, NO DELAY in KEY/INDEX WRITES and CHECKSUM.  If an interruption occurs at the server, the worst case is for the possibility of only one corrupt row of data in a TABLE.  If an INDEX gets corrupted, the INDEX can be rebuilt (or recreated) based solely on the DATA.  The FORMAT File (.frm) must be available and not have changed.

Also, if you must use VARIABLE ROWS, put these in a separate TABLE and create a One-to-One Relationship.  A TRIGGER can be created on the FIXED ROW TABLE to create an empty ROW with the proper Auto-Increment (Primary Key) VALUE of the FIXED ROW TABLE into the VARIABLE ROW TABLE.  This ROW would be the place holder ROW for an UPDATE QUERY to fill in its variable-width content.

Since MariaDB offers other ENGINES for the Maximum Safety Factor, you do have the options to MIX ENGINES in a single DATABASE to achieve that Safety and keeping as much speed as possible utilizing MyISAM everywhere possible.  Using InnoDB for the Maximum-Safety Factor can use as many system resources as the big-name databases do.  Unlike MyISAM, InnoDB caches TABLE-DATA.  MyISAM can do the same job using as little as 20% of the resources needed for an InnoDB since MyISAM only caches INDEXes.

For Maximum Performance, setup one (or more) REPLICATION Server(s).  REPLICATION isn’t difficult and the type of hardware chosen can be of the very inexpensive type.  Who should care if (or when) a REPLICATION Server fails; just replace that hardware and use the data from the MASTER Server to rebuild it with.  Afterwards, POINT all of the hard hitting Report QUERY(s) to the REPLICATION system(s).

Yep, I'm a fan of MariaDB.


BUT

There does seem to be some Fork'n-Around with MySQL due to some questionable actions by Oracle with the licenses associated with MySQL ... as evidenced by the existance of MariaDB, Percona (and possibly others) .


10 Years Ago ... I spent some time looking into PostgreSQL ... but I chose to stick with MySQL.

TODAY, I think they have made some HUGE strides with their lastest release of PostgreSQL 9.3 and pgAdmin III GUI.

Also, the PostgreSQL License appears to be ... Clean, Simple and Straight-Forward.

Yep, I believe the time has come to begin spending some Quality-Time in ... HERE.