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
Improve this question
3
  • 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? Commented Mar 16, 2020 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. Commented Mar 16, 2020 at 13:08
  • @ilos-vigil - Elaborate on what you mean by that comment on AUTO_INCREMENT. Show us a SHOW CREATE TABLE. Commented Mar 26, 2020 at 23:12

2 Answers 2

Reset to default
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
1
  • btw XtraDB was removed in 10.3. The NTE is "Near Term Extinction" yes? Commented Mar 16, 2020 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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.