is myisam the best table type for site_content?

i’m having a few performance issues at the moment using the manager when our site is under heavy load. i wonder if it’s because the site_content table uses the myisam engine (a little background for those who don’t know: myisam requires global locks for inserts, but is capable of fulltext searching, innodb doesn’t require global locks for inserts, but doesn’t support fulltext indexing).

perhaps it would make 0.9.7 more robust to use two tables for the site_content:

1) site_content - the existing table, but converted altered to use the innodb engine,
2) site_content_fulltext - duplicates of the text fields for each document. this table would use the myisam engine to enable fulltext searching on the content.

using myisam for the site_content table as currently happens means that any inserts into that table require the user to obtain an exclusive (global) lock. if users were creating documents themselves (by submitting form data, etc.) this could make them have to wait an unacceptable length of time.

using the above system, the site_content table would be updated much quicker because innodb doesn’t require global locks for inserts, and it’s likely that the site_content_fulltext table would be under much lighter load, so the overall insert process would probably be much faster.

has anyone got any thoughts on this? i’m guessing that with the new xpdo core, even if this wasn’t formally adopted, it’d be easy enough to override the ’save document’ method to implement this anyway...

redman:

I tried the InnoDB approach and unless new pages are being added 1/4 or 1/2 as often as they are being viewed, this will do nothing but degrade read performance, which I would expect to be the more important of the two. MyISAM is much faster than InnoDB for reads and the benefit of making this change would be lost in most scenarios.

As for the full-text index and insert speed, that can be corrected by externalizing the search to a proper, isolated indexing table as you suggest. I would love for this to become an official MODx Extension project for the 0.9.7+ releases.

BTW, the 1.0 data model, which 0.9.7 is a stepping stone towards, removes all localizable meta content from the site_content table and moves them to being any kind of Element which can be directly attached to a Resource, including the Resource content field. This effectively makes all Content become part of an Element instance, all of which will be available for i18n and versioning.

And yes, customizing this in the new xPDO-based model is a matter of simply changing the engine value in the schema before installing (or manually alter the table and change it in the map directly), though that functionality might need to be tested.

ok, thanks for the insight opengeek. i didn’t know myisam had faster reads than innodb.