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

Started by EvanGR, November 07, 2019, 13:06:04 PM

Previous topic - Next topic

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
Joomla 3.9.18
Virtuemart 3.4.x
Olympiantheme Hera (customized)
This reflects current status when viewing old post.

pinochico

We use InnoDB for 6 years == all VM table convert to InnoDB without this for type TEXT or for Elastic Search
www.minijoomla.org  - new portal for Joomla!, Virtuemart and other extensions
XML Easy Feeder - feeds for FB, GMC,.. from products, categories, orders, users, articles, acymailing subscribers and database table
Virtuemart Email Manager - customs email templates
Import products for Virtuemart - from CSV and XML
Rich Snippets - Google Structured Data
VirtueMart Products Extended - Slider with products, show Others bought, Products by CF ID and others filtering products

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
www.minijoomla.org  - new portal for Joomla!, Virtuemart and other extensions
XML Easy Feeder - feeds for FB, GMC,.. from products, categories, orders, users, articles, acymailing subscribers and database table
Virtuemart Email Manager - customs email templates
Import products for Virtuemart - from CSV and XML
Rich Snippets - Google Structured Data
VirtueMart Products Extended - Slider with products, show Others bought, Products by CF ID and others filtering products

balai


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

pinochico

www.minijoomla.org  - new portal for Joomla!, Virtuemart and other extensions
XML Easy Feeder - feeds for FB, GMC,.. from products, categories, orders, users, articles, acymailing subscribers and database table
Virtuemart Email Manager - customs email templates
Import products for Virtuemart - from CSV and XML
Rich Snippets - Google Structured Data
VirtueMart Products Extended - Slider with products, show Others bought, Products by CF ID and others filtering products

Milbo

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.
Should I fix your bug, please support the VirtueMart project and become a member
______________________________________
Extensions approved by the core team: http://extensions.virtuemart.net/

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

GJC Web Design
VirtueMart and Joomla Developers - php developers https://www.gjcwebdesign.com
VM4 AusPost Shipping Plugin - e-go Shipping Plugin - VM4 Postcode Shipping Plugin - Radius Shipping Plugin - VM4 NZ Post Shipping Plugin - AusPost Estimator
Samport Payment Plugin - EcomMerchant Payment Plugin - ccBill payment Plugin
VM2 Product Lock Extension - VM2 Preconfig Adresses Extension - TaxCloud USA Taxes Plugin - Virtuemart  Product Review Component
https://extensions.joomla.org/profile/profile/details/67210
Contact for any VirtueMart or Joomla development & customisation

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

Joomla 3.9.18
Virtuemart 3.4.x
Olympiantheme Hera (customized)
This reflects current status when viewing old post.

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
GJC Web Design
VirtueMart and Joomla Developers - php developers https://www.gjcwebdesign.com
VM4 AusPost Shipping Plugin - e-go Shipping Plugin - VM4 Postcode Shipping Plugin - Radius Shipping Plugin - VM4 NZ Post Shipping Plugin - AusPost Estimator
Samport Payment Plugin - EcomMerchant Payment Plugin - ccBill payment Plugin
VM2 Product Lock Extension - VM2 Preconfig Adresses Extension - TaxCloud USA Taxes Plugin - Virtuemart  Product Review Component
https://extensions.joomla.org/profile/profile/details/67210
Contact for any VirtueMart or Joomla development & customisation