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
[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
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
We use InnoDB for 6 years == all VM table convert to InnoDB without this for type TEXT or for Elastic Search
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
Fulltext not function on InnoDB, then all tables for fultext must be in MyISAM
QuoteFulltext 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
I don't know, but I have only info from our server's support
So you kept the language tables as MyIsam? Interesting.
Quote from: EvanGR on December 22, 2020, 15:14:32 PM
@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.
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/
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?
http://virtuemart.net/news/473-security-release-virtuemart-3-0-12
added hidden config updEngine to prevent changing of the table engine
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.
Quote from: GJC Web Design on February 21, 2021, 09:46:12 AM
http://virtuemart.net/news/473-security-release-virtuemart-3-0-12
added hidden config updEngine to prevent changing of the table engine
Jörgen
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