DB or not DB?

We are currently switching over to a new Database System called Maria DB. Here are the reasons why!
On Friday 29 January 2016

Spearline are adding some new products to our portfolio and are developing some new and exciting features in our existing applications. I was given the responsibility to redesign our database to accommodate new requirements as adding them in our current database would have required lots of tweaks which would not have being ideal.

 

We at Spearline are ardent fan and advocate of open source technologies so there was in a possible open source solution available we will always evaluate it.. I was looking for a database platform that would offer us scalability, security, robustness and at the same time ease of use for developers.

The Options

I intentionally kept NoSQL databases out of our radar as they are relatively new concept and fair to say it requires a significant level of skill, effort and programming expertise to install, configure, develop on and maintain NoSQL databases. Further, most NoSQL databases lack the ability to perform true ACID transactions and that’s something we really do not want with our system. RDBMS platforms are around for quite some years now and you get a sort of instant support/help on Internet for any issues or doubts related to SQL.

With a number of potential candidates available in the market, the task became a bit of difficult while trying to choose one of them but then I zeroed in on MySQL, PostgreSQL and MariaDB to compare for the needs of our new database platform.

MySQL: We have been using MySQL for all of our applications and have found it good enough so far. However I believe on certain occasions we were unable to get kind of performance we expected even with optimized queries running on high end servers. In some situations we noticed MySQL Query optimizer was unable to choose right index and we had to force query to use a specific index. This led us to drop the idea of continuing with MySQL

PostgreSQL: PostgreSQL is one of the most advanced open source databases and would have supported everything that we wanted to do with database. However we do not have in house PostgreSQL expert and choosing PostgreSQL would have required a learning curve for me and eventually delaying our plans to roll out new features and products.

MariaDB: For MySQL users like us who are considering a move away from it, compatibility is the major concern. Eventually we decided to go with MariaDB as it was a binary drop in replacement for the MySQL.

What this means is:

->Data and table definition files (.frm) files are binary compatible.

->All client APIs, protocols and structs are identical.

->All filenames, binaries, paths, ports, sockets, and etc... should be the same.

->All MySQL connectors (PHP, Perl, Python, Java, .NET, MyODBC, Ruby, MySQL C connector etc) work unchanged with MariaDB.

->The mysql-client package also works with MariaDB server.

->The shared client library is binary compatible with MySQL's client library.

 

I had a chance to work with MariaDB in past and  was extremely happy seeing the performance for one of our applications for SMEs. Many of the world's largest companies and websites like IBM, HP, Wikipedia, Redhat use MariaDB database technology and that too was good reason to back our decision. We were really glad to see some speed improvements in MariaDB like much enhanced query optimizer; faster, safer and parallel replication; Pool of Threads and many others. It was also good to see some new features available in MariaDB like Microsecond precision for date, time data types; Microsecond Precision in Processlist; Virtual and Dynamic Columns; Faster joins and subqueries; Multi-source replication

Some of the storage engine enhancements in MariaDB over MySQL which we really like to implement in our applications are, XtraDB (drop-in replacement for InnoDB): Percona XtraDB is an enhanced version of the InnoDB storage engine, designed to better scale on modern hardware. In particular, it is designed to scale better on many cores, to use memory more efficiently, and to be more convenient and useful.

FederatedX (drop-in replacement for Federated) : Right now it can only talk to another MariaDB or MySQL RDBMS but the plan is to be able to use other RDBMS systems as a data source. This would allow us to integrate our applications with many 3rd party applications running on some other database platforms.

CONNECT : A DB engine that would allow us to manage information that exists outside RDBMS engines in the form of flat files and other such constructs.

We did some testing for some complex queries (join, sub query) in our lab environment to compare MariaDB 10.0 with MySQL 5.6 with default settings and MariaDB was a clear winner there. Some benchmarking results are available on MariaDB portal

https://mariadb.org/maria-10-1-mysql-5-7-commodity-hardware/

https://mariadb.org/performance-evaluation-of-mariadb-10-1-and-mysql-5-7-4-labs-tplc/

 

Conclusion:

We all know choosing a proper database platform is a long term decision and changing that decision later can be a difficult, expensive and painful experience. Well we took a futuristic approach while choosing a database and we know we can not afford to go wrong at first place so fingers crossed. I am confident that our decision to move over to MariaDB platform will pay off in future.