[DB Performance] Converting product & custom field tables to InnoDB?

[EvanGR]

We are trying to squeeze out more performance, and wonder why the products table (and others) is in MyISAM format?

In a shop with almost 100 thousand products, do you think it would make a difference? (i.e. converting to InnoDB)

Thanks

[EvanGR]

[Bump for 2020-2021 attention.]

I recently received this advice by a prominent VM extension developer (to convert all the VM tables to InnoDB) to increase reliability and performance.
The rationale was that:
1) InnoDB is much safer for concurrent transactions, and in events of server failures/restarts.
2) MyISAM development has essentially stopped for years. So any performance advantage it may have had in the past, does not necessarily hold for today's needs and purposes.

What do you think? Should I expect any trouble by converting all VM tables to InnoDB for a large site?

Thanks

[Jörgen]

In my install the tables seems to be a mix of MyIsam (VM-tables) and InnoDB. Thought InnoDB was the better alternative. Sorry that this was not much of a help to You.

Jörgen @ Kreativ Fotografi

[pinochico]

We use InnoDB for 6 years == all VM table convert to InnoDB without this for type TEXT or for Elastic Search

[EvanGR]

Thanks.

@Jörgen
Yes, default installation is a mix of both. The rationale is (was) that MyISAM is faster for reading data, and VM should be mostly optimized for reading large amounts of data often (rather than writing).
Not sure if this still stands in 2020-2021.
btw... MyISAM data reliability can be disastrous in certain situations, InnoDB is a lot more resilient in error recovery. That alone should be a good reason to drop MyISAM completely.

@pinochico
Do you mean you have converted all tables to InnoDB?
"without this for type TEXT or for Elastic Search" <--- I don't understand this part

[pinochico]

Fulltext not function on InnoDB, then all tables for fultext must be in MyISAM

[balai]

Fulltext not function on InnoDB, then all tables for fultext must be in MyISAM

How did you end up in that conclusion?

https://dev.mysql.com/doc/refman/5.6/en/innodb-fulltext-index.html

[pinochico]

I don't know, but I have only info from our server's support

[Milbo]

So you kept the language tables as MyIsam? Interesting.

@Jörgen
Yes, default installation is a mix of both. The rationale is (was) that MyISAM is faster for reading data, and VM should be mostly optimized for reading large amounts of data often (rather than writing).
Not sure if this still stands in 2020-2021.

Correct explained. It could be interesting to find new data about the reading speed of innoDb compared to MyIsam and the search functionality.

[EvanGR]

I would ask the VM devs to reconsider, and move on to InnoDB (or other modern alternatives).

MyISAM may be faster in very specific scenarios, but it is slow in writing data, and it is in high risk of data loss.
Plus, it's been deprecated.
https://www.percona.com/blog/2016/10/11/mysql-8-0-end-myisam/

InnoDB has caught up in most features by now.


And here's a guide to convert ISAM --> InnoDB
https://mariadb.com/kb/en/converting-tables-from-myisam-to-innodb/

[EvanGR]

We converted our most of our big tables to InnoDB in a shop with:
70000+ products
over 2 million custom field entries...

The result was actually increased speed!

(It may have been because the tables were defragmented/optimized during the conversion? )

Also our occasional server crashes with blank/incomplete orders coming in under certain stress conditions.... are gone.

Overall, very happy with the result.


One problem though... it seems that after a VM update, or some other procedure, the tables get converted back to MyISAM?

[GJC Web Design]

http://virtuemart.net/news/473-security-release-virtuemart-3-0-12

added hidden config updEngine to prevent changing of the table engine

[EvanGR]

Thank you GJC!

This is a hindrance for our purposes. How can we bypass this check?
We don't want any change to our storage engine config.

[Jörgen]

http://virtuemart.net/news/473-security-release-virtuemart-3-0-12

added hidden config updEngine to prevent changing of the table engine

Jörgen

[GJC Web Design]

To clarify .. if updEngine=1 I think means it won't update the engines on a VM update but someone will be able to confirm this