0

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
  • < 1% for both UPDATE & DELETE
  • Are the inserts by appending an Auto_increment table (because there are table-lock free rather than other PK based insert)? Keep in mind innodb will need to be tuned its innodb_buffer_pool_size if you deside to transition. Any particular mariadb version? – danblack Mar 16 at 6:38
  • No, the primary key isn't from AUTO INCREAMENT. I haven't checked my MariaDB, but it should be latest version since the OS is rolling release. – ilos-vigil Mar 16 at 13:08
  • @ilos-vigil - Elaborate on what you mean by that comment on AUTO_INCREMENT. Show us a SHOW CREATE TABLE. – Rick James Mar 26 at 23:12
1

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 "MyISAM" to "InnoDB" 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

| improve this answer | |
  • btw XtraDB was removed in 10.3. The NTE is "Near Term Extinction" yes? – danblack Mar 16 at 6:55
0

InnoDB, period, fullstop. One caution: The disk space will increase by between 2x and 3x.

This discusses the various things you need to watch out for: http://mysql.rjweb.org/doc.php/myisam2innodb . And it provides the desired ALTERs.

Do change the settings. Most importantly is to decrease key_buffer_size and increase innodb_buffer_pool_size. (See my link.)

"Read speed" -- Oracle has been "proving" for the last few releases that InnoDB is faster. Because of the "clustering" of the PK in InnoDB, it may be better to make a few changes to the indexes (primary and secondary).

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.