Hi

My DB is currently MySQL, and has a mix of InnoDB and MyISAM tables. I am looking to convert to MariaDB.

Should my database be InnoDB or MyISAM? Does it matter for MariaDB? As a general point?

Cheers

John

 

 

In general, InnoDB is always better than MyISAM. 

You can use MyISAM  or InnoDB or both with MariaDB with no issues

But Innodb is better as RevengeFNF already post !

Keep in mind that InnoDB uses more hard disk space and more ram...

Why is innodb preferred over myisam?

And what is the process (and is it safe??) from changing a 6gb myisam database to innodb?

MyISAM:

The MyISAM storage engine in MySQL.

• Simpler to design and create, thus better for beginners. No worries about the foreign relationships between tables.
• Faster than InnoDB on the whole as a result of the simpler structure thus much less costs of server resources.
• Full-text indexing.
• Especially good for read-intensive (select) tables.

InnoDB:

The InnoDB storage engine in MySQL.

• Support for transactions (giving you support for the ACID property).
• Row-level locking. Having a more fine grained locking-mechanism gives you higher concurrency compared to, for instance, MyISAM.
• Foreign key constraints. Allowing you to let the database ensure the integrity of the state of the database, and the relationships between tables.
• InnoDB is more resistant to table corruption than MyISAM.
• Support for large buffer pool for both data and indexes. MyISAM key buffer is only for indexes.
• MyISAM is stagnant; all future enhancements will be in InnoDB

MyISAM Limitations:

• No foreign keys and cascading deletes/updates
• No transactional integrity (ACID compliance)
• No rollback abilities
• 4,284,867,296 row limit (2^32)
• Maximum of 64 indexes per row

InnoDB Limitations:

• No full text indexing (Below-5.6 mysql version)
• Cannot be compressed for fast, read-only

 

 

MyISAM vs InnoDB

Today we will focus on MyISAM vs InnoDB. The major difference between MyISAM and InnoDB is in referential integrity and transactions. There are also other difference such as locking, rollbacks, and full-text searches.

Referential Integrity

Referential integrity ensures that relationships between tables remains consistent. More specifically, this means when a table (e.g. Listings) has a foreign key (e.g. Product ID) pointing to a different table (e.g. Products), when updates or deletes occur to the pointed-to table, these changes are cascaded to the linking table. In our example, if a product is renamed, the linking table’s foreign keys will also update; if a product is deleted from the ‘Products’ table, any listings which point to the deleted entry will also be deleted. Furthermore, any new listing must have that foreign key pointing to a valid, existing entry.

InnoDB is a relational DBMS (RDBMS) and thus has referential integrity, while MyISAM does not.

Transactions & Atomicity

Data in a table is managed using Data Manipulation Language (DML) statements, such as SELECT, INSERT, UPDATE and DELETE. A transaction group two or more DML statements together into a single unit of work, so either the entire unit is applied, or none of it is.

MyISAM do not support transactions whereas InnoDB does.

If an operation is interrupted while using a MyISAM table, the operation is aborted immediately, and the rows (or even data within each row) that are affected remains affected, even if the operation did not go to completion.

If an operation is interrupted while using an InnoDB table, because it using transactions, which has atomicity, any transaction which did not go to completion will not take effect, since no commit is made.

Table-locking vs Row-locking

When a query runs against a MyISAM table, the entire table in which it is querying will be locked. This means subsequent queries will only be executed after the current one is finished. If you are reading a large table, and/or there are frequent read and write operations, this can mean a huge backlog of queries.

When a query runs against an InnoDB table, only the row(s) which are involved are locked, the rest of the table remains available for CRUD operations. This means queries can run simultaneously on the same table, provided they do not use the same row.

This feature in InnoDB is known as concurrency. As great as concurrency is, there is a major drawback that applies to a select range of tables, in that there is an overhead in switching between kernel threads, and you should set a limit on the kernel threads to prevent the server coming to a halt.

Transactions & Rollbacks

When you run an operation in MyISAM, the changes are set; in InnoDB, those changes can be rolled back. The most common commands used to control transactions are COMMIT, ROLLBACK and SAVEPOINT.
1. COMMIT - you can write multiple DML operations, but the changes will only be saved when a COMMIT is made
2. ROLLBACK - you can discard any operations that have not yet been committed yet
3. SAVEPOINT - sets a point in the list of operations to which a ROLLBACK operation can rollback to

Reliability

MyISAM offers no data integrity - Hardware failures, unclean shutdowns and canceled operations can cause the data to become corrupt. This would require full repair or rebuilds of the indexes and tables.

InnoDB, on the other hand, uses a transactional log, a double-write buffer and automatic checksumming and validation to prevent corruption. Before InnoDB makes any changes, it records the data before the transactions into a system tablespace file called ibdata1. If there is a crash, InnoDB would autorecover through the reply of those logs.

FULLTEXT Indexing

InnoDB does not support FULLTEXT indexing until MySQL version 5.6.4. As of the writing of this post, many shared hosting providers’ MySQL version is still below 5.6.4, which means FULLTEXT indexing is not supported for InnoDB tables.

However, this is not a valid reason to use MyISAM. It’s best to change to a hosting provider that supports up-to-date versions of MySQL. Not that a MyISAM table that uses FULLTEXT indexing cannot be converted to an InnoDB table.

Conclusion

In conclusion, InnoDB should be your default storage engine of choice. Choose MyISAM or other data types when they serve a specific need.

I am also interested in how to change a db from MyISAM to InnoDB. Is there a quick way? table by table?

Actually my db includes both of them. I guess this is because my db is very old.

you can use:

ALTER TABLE tablenamehere ENGINE = InnoDB;

 

Sorry for asking again but any other faster method for all tables in the db? There are too many to do them one by one.

http://www.ryadel.com/en/mysql-convert-all-tables-of-one-or-more-databases-to-myisam-or-innodb/

Some great input there guys thanks!

As I am planning to convert to MariaDB I was looking at their site too and they raise concerns over indexes. Running the tests they suggest certainly highlight a few possible missing indexes. I was concerned that it might adversely impact ipb:

https://mariadb.com/kb/en/mariadb/converting-tables-from-myisam-to-innodb/

I exported the schema as suggested at the top of the article and searched for primary, fulltext and keys as suggested. I guess I am looking for tables that have no primary key & fulltext?

Has anyone else tried this?

Am I missing something?

Should all tables be converted to innodb or just a few like the posts table? would that see better performance?

A little concerned after reading that innodb can be slower. (maybe that is if converting all the tables over)

Yes it will be better to convert them all to Innodb and performance will be much better as you will avoid the locking of the tables.....

Innodb will not have better performance only on very small databases and very well optimized.....

When  i say small i mean a few megabytes.....

So Innodb is the best for many reasons that you can read on my above post like performance and your data will be more secure......

In updating to MariaDB 10.1 from MySQL 5.51 what upgrade path would you recommend?

Should I update all tables to InnoDB before the move or after the move?

Should I simply install MariaDB 10.1, or go in steps via MySQL 5.6?

The issue is further complicated by the system running on Amazon Linux. I intend moving to a MariaDB built Amazon image using Ubuntu or Cent.

It will be better if you use Centos

I recommend you to upgrade first and then convert tables ...

So first of all BACKUP !

It will be safe if you want to try to go first to MariaDB 5.5.54 and then to MariaDB 10.1.21 ....

 

It will be safe if you want to try to go first to MariaDB 5.5.54 and then to MariaDB 10.1.21 ....

Interesting! I am guessing I can just install MariaDB 5,5.54 directly over the top of MySQL.... sounds promising Thanks

My plan then would be to upgrade to MariaDB 5.5.54 on Amazon Linux, then upgrade to 10.1, then spin up 10.1 on CentOS and mirror the database over, before swapping databases and stopping the Amazon Linux server. I can then change tables to InnoDB.

I was trying to convert MyISAM tables to InnoDB on my test site but I got the same error for 5 tables.

 ALTER TABLE `core_widgets` ENGINE=InnoDB;
 ALTER TABLE `core_sitemap` ENGINE=InnoDB;
 ALTER TABLE `core_search_index` ENGINE=InnoDB;
 ALTER TABLE `core_dev` ENGINE=InnoDB;
 ALTER TABLE `cms_page_widget_areas` ENGINE=InnoDB;
 ALTER TABLE `cms_databases` ENGINE=InnoDB;
 
 #1071 - Specified key was too long; max key length is 767 bytes

Should they be kept on MyISAM or is there a way to convert them too ?

I have exported those tables, changed engine to InnoDB and imported them had the same error   Any idea @ASTRAPI & @RevengeFNF ?

 

I have exported those tables, changed engine to InnoDB and imported them had the same error   Any idea @ASTRAPI & @RevengeFNF ?

Yes, in your mysql configuration add this:

innodb_file_format=BARRACUDA
innodb_large_prefix=1

Now convert them using:

ALTER TABLE `core_widgets` ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

 

 

Yes, in your mysql configuration add this:

innodb_file_format=BARRACUDA
innodb_large_prefix=1

Now convert them using:

ALTER TABLE `core_widgets` ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

 

Thanks Worked perfectly

should row format be dynamic for all InnoDB tables, or just the ones that complain on conversion?

 

should row format be dynamic for all InnoDB tables, or just the ones that complain on conversion?

I use every in dynamic.

To flip-flop easily between the two, you can use the following:

To convert INTO InnoDB, run this SQL operation first:

SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' engine=InnoDB row_format=dynamic;') 
FROM information_schema.TABLES WHERE ENGINE = 'MyISAM';

This will output a list of tables. You will need to expand your options and swap to "full text", and edit the number of displayed rows. The output here is pre-built Quarries that you will then run again through SQL operations. To convert back to MyISAM, simply flip InnoDB and MyISAM.

When using MariaDB, should Aria be a consideration over InnoDB?

What would your choice be when using dedicated (it's VM, but I control the host) SSD storage?

I'm not really noticing any major performance issues when swapping between InnoDB and Aria. I have an issue where posting replies can take several seconds and I'm struggling getting to the bottom of it.

 

I have an issue where posting replies can take several seconds and I'm struggling getting to the bottom of it.

This might be related to the notification emails as I was having the same issues, turned out it was sparkpost fault. 

Does it happen on only large threads or new ones too? How many subscribers does the thread has? Does it happen when you edit a reply or just when you post a new one?
 

Thanks @jair101. I don't want to hijack this thread so I've posted all of the detail here.