Thread

I’m hoping someone can help me out.  I’ve got 7 sites in a MSM install under development and am unexpectedly bumping up against mysql’s Column-Count and Row-Size Limits.  I’d like some advice on how to stay well within those limits and have decent performance.  I’ve looked through some helpful forum posts like
Mysql Error: Too many columns
MySQL Error: 1054

My MSM situation:
* Seven sites in total
* custom field types include a fair number of file, playa, matrix, wygwam, p&t divebar, etc fields
* one site has 60 custom fields
* six sites have 130 custom fields each (they are science support sites with lots of info, not easy to redo the schema)
* total of 840 custom fields
* 1680 fields/columns in exp_channel_data

Currently, I’m under mysql’s limit, but probably not by much given that I got database errors for exp_channel_data before reducing the number of custom fields.  For exp_channel_data, I’m clearly beyond the 1000 column limit for innoDB.  So I switched exp_channel_data to myISAM.  Nonetheless, even with that table in myISAM, I still had problems before reducing the number of custom fields.  Presumably I’m still pushing the 64kb row size/length limit. Given that my average row length is 2.2kb, there must be a few rows that are much much longer than average (I haven’t dug into that yet).

Got any advice, especially related to the following questions?
1) How hard will it be to get decent performance with my current setup, given that it’s edging toward the mysql limit?  Is it no big deal or really tricky? Are there specific things to watch out for?
2) How important is it to reduce the number and types of custom fields further?  Are there particular field types that are especially long contributors to the length limit that I should look for?
3) Should I switch all tables to myISAM so that I don’t have a mix of engines?
4) Is there some magical workaround to all this?

Finally, a little rant in EllisLab’s direction:
<rant>How was I supposed to know that the EE schema would grow “sideways” so quickly with MSM?  The MSM docs are way too lean. And using a table structure like that of exp_channel_data seems kinda crazy for MSM.  It would seem to severely limit MSM’s use.  I’ve built a few EE sites previously and loved it.  Unfortunately, my experience with MSM has been quite different so far.</rant>

Also, I’d be most grateful if anyone can fill me in on the approximate row math here for exp_channel_data.  I’d like to have a sense of how close I might be to the 64Kb limit. 

in my case, the exp_channel_data table is composed just of TEXT fields for the contents and TINYTEXT for the field’s formatting type.  Shouldn’t both TEXT and TINYTEXT contribute 9-12 bytes to the row size each since mySQL stores TEXT fields elsewhere?  If so, wouldn’t a maximum estimate be roughly as follows?

10,080 bytes from 840 content columns x 12 bytes/col
10,080 bytes from 840 field formatting columns x 12 bytes/col
————————————————————
20,160 bytes total (max)
+ a few hundred more for NULL columns?

That seems too low given that I was apparently pushing the row-size limit.  Am I missing something about tinytext or utf8? Or maybe some other issue was causing a “too many columns” error with exp_channel_data? Mixing of myISAM and innoDB? php memory limit?

Ideas?

Hi fjldude,

I’m sorry you have bumped into this limitation. I completely sympathize with your frustration.

As you have noted, it is a technical limitation in MySQL, but that doesn’t change its adverse effect on you.

1) How hard will it be to get decent performance with my current setup, given that it’s edging toward the mysql limit?  Is it no big deal or really tricky? Are there specific things to watch out for?

Performance tuning is outside the scope of technical support. You would be better served asking an expert about this specifically.

2) How important is it to reduce the number and types of custom fields further?  Are there particular field types that are especially long contributors to the length limit that I should look for?

That depends on how much more you need to add? If you need more sites, fields and so on, it would be necessary.
If you are still in development and can be agile, take advantage of Matrix wherever possible. Matrix stores its columns and data in a separate table which gives you a bit more room to grow.

3) Should I switch all tables to myISAM so that I don’t have a mix of engines?

We recommend MyISAM for the entire database.

4) Is there some magical workaround to all this?

No, I’m sorry there is not :-(

If you would like to discuss this further, I invite you to email us at .(JavaScript must be enabled to view this email address) We may be able to offer additional solutions.

Cheers,

Hello Dan,

Thanks for the info and advice.  Although I have some flexibility with reducing, altering, and shifting fields, I still need to know how much room I have for future expansion.  To estimate that, I can run “duplicate another site” tests, but I’d also like to cross compare against an approximate row-size calculation.  I still kindly request that you comment on my clearly wrong bit of math earlier in this thread.

Regarding your invitation to email Sales, you’ve definitely piqued my interest.  I’ll send an email shortly and am curious what kind of solutions might be possible.

Given that most of my numerous fields are simply duplicates across sites, I’m pondering ideas for how to centralize them (ie a single site holding many fields and using a category group for “sites”).  I’m not yet sure whether I can do that sufficiently to avoid the mysql limit and keep up ee performance.  I’m considering abandoning MSM, but struggling with how to have a decent CP UI with sufficient permissions to separate out content on a per “site” basis.  I haven’t thought of any clever native solutions yet. Devot:ee has a few addons that might help a bit, but I didn’t see any great solutions there either.

Hi fjldude,

Thanks for your email to sales @. Much appreciated!

I’ll continue the conversation there, if it’s all the same to you 😊

Cheers,