MySQL Configuration

[Jacobite]

Greetings...

what DB configuration you prefer?
in special: InnoDB or MyISAM Storage Engine?

We use EspoCRM in production since 2 months and it works like a charm!
But now the database get bigger and bigger i wonder how it should be configured to be performant.

[yuri]

Prefer MyISAM because it's faster with select queries.

MySQL (both MyISAM and InnoDB) has a bad performance when run SELECT on huge table with COUNT function and JOIN another big table. For example, select meetings where a specific user is an attendee.

For these cases it will be needed to make some customization: not to calculate total count for list views.

Or migrate to PostgresSQL (we want to add support in future)

[Galaxy Express]

And what about Aria (MariaDB's)?
Internets say that Aria is very similar to MyISAM but crash-safe and with equal (or sometimes) faster performance...

[yuri]

http://forum.espocrm.com/forum/installation-and-upgrade-help/20845-mariadb-instead-mysql

[Galaxy Express]

it's not even an answer...
well, nvm then

[bandtank]

FYI for anyone else who found this thread while looking for a reason why InnoDB wasn't used - I converted my entire EspoCRM test database to InnoDB with foreign keys. The performance with 100,000 accounts, 500,000 contacts, and tons of cases, documents, etc. is just as fast as it was before the switch. Now AWS RDS will provide a reliable backup, which it (through no fault of its own) can't do with MyISAM. I'm working on changing the setup and install process to create InnoDB tables by default. Making selects faster at the expense of losing data is definitely not worth the risk and, if you index foreign keys correctly, the select speed is fast enough. Add a caching layer and you'll never notice it.

http://docs.aws.amazon.com/AmazonRDS...Practices.html

"For example, the MyISAM storage engine does not support reliable crash recovery and might prevent a Point-In-Time Restore or snapshot restore from working as intended. This might result in lost or corrupt data when MySQL is restarted after a crash."

[Kurt]

I stumbled upon this, and as of our version of Espo (5.0.1) it looks like there still isn't a way to configure Espo to use InnoDB (which should absolutely be the default for an application like this IMO).

The function _getCreateTableSQL in /application/Espo/Core/Utils/Database/DBAL/Platforms/MySqlPlatform.php is forcing it to use MyISAM. It has an options arg that can be used to change that but I can't find where those options would be passed or set in a custom/local configuration. From what I've found on stackoverflow it sounds like Doctrine only lets you set this as an option on each table. I'm not a PHP developer btw, so apologies if I'm missing something obvious.

Has anyone found a way to do this other than editing MySqlPlatform.php? bandtank did you end up making any code changes or just modify things in your database?

[yuri]

Since 5.1.0 EspoCRM uses InnoDB by default.

[Kurt]

Thanks, we'll update our installation.