Author Topic: [DB Performance] Converting product & custom field tables to InnoDB?  (Read 843 times)

EvanGR

  • Jr. Member
  • **
  • Posts: 381
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

  • Jr. Member
  • **
  • Posts: 381
Re: [DB Performance] Converting product & custom field tables to InnoDB?
« Reply #1 on: December 22, 2020, 09:10:03 am »
[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

  • Global Moderator
  • Sr. Member
  • *
  • Posts: 3212
    • Kreativ Fotografi
  • VirtueMart Version: 3.4.x
Re: [DB Performance] Converting product & custom field tables to InnoDB?
« Reply #2 on: December 22, 2020, 09:27:21 am »
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

  • 3rd party VirtueMart Developer
  • Full Member
  • *
  • Posts: 892
    • MiniJoomla
  • Skype Name: support-easysoftware
  • VirtueMart Version: 3
Re: [DB Performance] Converting product & custom field tables to InnoDB?
« Reply #3 on: December 22, 2020, 10:48:54 am »
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 from products, orders and database table
Virtuemart Email Manager - customs email templates

EvanGR

  • Jr. Member
  • **
  • Posts: 381
Re: [DB Performance] Converting product & custom field tables to InnoDB?
« Reply #4 on: December 22, 2020, 15:14:32 pm »
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

  • 3rd party VirtueMart Developer
  • Full Member
  • *
  • Posts: 892
    • MiniJoomla
  • Skype Name: support-easysoftware
  • VirtueMart Version: 3
Re: [DB Performance] Converting product & custom field tables to InnoDB?
« Reply #5 on: December 22, 2020, 17:12:17 pm »
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 from products, orders and database table
Virtuemart Email Manager - customs email templates

balai

  • 3rd party VirtueMart Developer
  • Full Member
  • *
  • Posts: 1435
Re: [DB Performance] Converting product & custom field tables to InnoDB?
« Reply #6 on: December 22, 2020, 18:50:58 pm »

Quote
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

  • 3rd party VirtueMart Developer
  • Full Member
  • *
  • Posts: 892
    • MiniJoomla
  • Skype Name: support-easysoftware
  • VirtueMart Version: 3
Re: [DB Performance] Converting product & custom field tables to InnoDB?
« Reply #7 on: December 22, 2020, 19:05:50 pm »
I don't know, but I have only info from our server's support
www.minijoomla.org  - new portal for Joomla!, Virtuemart and other extensions
XML Easy Feeder - feeds from products, orders and database table
Virtuemart Email Manager - customs email templates

Milbo

  • Virtuemart Projectleader
  • Administrator
  • Super Hero
  • *
  • Posts: 10087
  • VM3.2 Cached and Optimized
    • VM3 Extensions
  • VirtueMart Version: VirtueMart 3 on joomla 3
Re: [DB Performance] Converting product & custom field tables to InnoDB?
« Reply #8 on: January 13, 2021, 21:26:13 pm »
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.
I should fix your bug, please support the VirtueMart project and become a member
______________________________________
Extensions approved by the core team: http://extensions.virtuemart.net/