What MariaDB storage engine should i use? I have a database with size about 170GB with rows between 20.000 - 700.000.000 rows (depending on the table). Currently i'm using MyISAM storage engine.

In my case, querying (READ) speed is very important, but transactional is optional.

My CRUD Operation estimates is :


98% READ
2% INSERT
&lt; 1% for both UPDATE &amp; DELETE
Your only real choice here is between InnoDB and Percona's XtraDB engines - see this page.

Coincidentally, I wrote an answer to Change all table ENGINE at once from &quot;MyISAM&quot; to &quot;InnoDB&quot; in MySql with single query this morning on how to convert from MyISAM to InnoDB which might be of interest to you? There are very good reasons for not going with anything non-transactional!


  Any particular reason you recommend InnoDB/XtraDB since MariaDB blog recommend Aria instead?


Yes - for their transactional capabilities - you get DRI (Declarative Referential Integrity) thrown in. Enforcing DRI app-side is a disaster waiting to happen - whereas if you use the RDBMS, you have a system that's been tested by literally millions of people. If you "roll-your-own", you have to rewrite it every time you want to write a new app for that particular schema - and if your data is important, this will happen! I would urge you to use either InnoDB or XtraDB but if you do insist on an NTE, then go with Aria! -  Vérace