Thread

This post is not a question, it is a point of reference for those running high-traffic websites on an EE platform.

We have had ongoing struggles with EE’s schema and core queries for an extensive period, and have struggled to cope with high traffic levels due to the trade-off of the weblogs module. We understand, in pursuing the ideal of building a dynamic app, at times you will be forced to compromise efficiency. This is not a criticism, simply a fact.

Our findings indicate that the extensive number of SELECT DISTINCT() queries generated by the EE platform cause extensive table locking on an MySQL MyISAM database. This is after disabling statistical tracking and implementing memcache to EE’s core.

We have found an ongoing issue in that, when a write/update is made to the exp_weblog_titles table, all SELECT DISTINCT queries - which are forced to write to disk for sorting - will backup and take in excess of 30 seconds to complete.

Yesterday, having converted this table to InnoDB in the interests of doing away with table locking, and reverting to row locking, we have seen a 6-fold performance increase. The throughput of queries has risen to 1600 queries per second.

While Ellis do not officially support or recommend InnoDB - as EE defaults to MyISAM, we do recommend - from our experience - that you experiment with it should your site be struggling with table locks on your exp_weblog_titles, and exp_comments tables.

Yesterday, having converted this table to InnoDB in the interests of doing away with table locking, and reverting to row locking, we have seen a 6-fold performance increase. The throughput of queries has risen to 1600 queries per second.

Have run into that exact problem a few times when an EE site gets a heavy load on not so heavy hardware. After exhausting all the other caching and performance tunes and tweaks, we just rode it out. Not fun.

The “6-fold performance increase” applies to what? Query throughput?

While Ellis do not officially support or recommend InnoDB - as EE defaults to MyISAM, we do recommend - from our experience - that you experiment with it should your site be struggling with table locks on your exp_weblog_titles, and exp_comments tables.

I would find it interesting to see the pros and cons of InnoDB vs. MyISAM relative to EE. Found some good info comparing the two Here.

While InnoDB isn’t supported or recommended by EE, how much effort is required to convert tables, and what happens when EE updates the DB with a future version?

It’s also worth noting that mixing InnoDB with MyISAM tables in the same database presents a new set of problems.  I don’t know your specific situation, Andrew, nor the details of your server environment and load (you had a unique master/slave setup last I think we spoke) , and I’m glad that this switch made a dramatic improvement for you.  But I would like to just put a big red asterisk by your recommendation that people try it, as for most people it will not be the right decision, and definitely shouldn’t be the first thing one would want to try when experiencing performance or load issues.

Granted - yes we’re now running 4 apache boxes pointed at a master mysql box, backing up to a secondary mysql box for redundancy.

On a separate infrastructure, we have managed to get 4 web servers pointing at two separate mysql boxes with master <-> master replication working successfully.

Do you know of any specific issues (as opposed to risks) with mixing storage engines with EE?

A specific limitation would be the lack of Fulltext indexing, although I don’t believe EE’s search engine makes use of it?

Sure, here’s a good summary article that covers both storage engines as well as some complications that arise from mixing storage engines (backups being one) Should you move from MyISAM to InnoDB?

...a good summary article that covers both storage engines as well as some complications that arise from mixing storage engines (backups being one) Should you move from MyISAM to InnoDB?

I suspect that for most EE users this is a non-issue as MyISAM fills the bill and any gain from InnoDB tables is part of the diminishing returns scenario. For those few who need InnoDB’s differences, and can handle the resulting management complexity, it’s fine.

Thanks fo that Derek, I’ve passed this onto operations.

The information relating to backups is particularly useful.

Grumps - agreed. Our issue however is the table locking that goes on via the weblog module’s select queries. We simply don’t have another way around this unfortunately, but the benefit has been significant, and immediate. This post is simply a heads-up for people struggling with high-traffic sites.

Yep, thanks for sharing Andrew.  I don’t want my reply to be construed that no one should ever come to the conclusion you did, or that it’s always a bad idea.  Just that it needs to be a very measured and deliberate action, taken only by those that fully understand what they’re doing.  Again, glad it worked for you, and thanks for the success story!

Attached is an example.

At around 1500 queries/sec at peak times, we simply can’t cope with full table locks.

If you have any other suggestions however, I’m all ears.

We simply don’t have another way around this unfortunately, but the benefit has been significant, and immediate. This post is simply a heads-up for people struggling with high-traffic sites.

And much appreciated as we’ve run into a few sites that go so bogged that all we could do is sit and wait for the tide to go out. And kudos to EE for not locking everyone into MyISAM. I read some of your other posts regarding this issue and appreciate the efforts you took as well as sharing info. Well done.

Is this the sort of thing that one can test by simply converting over the database?  Or is recoding of EE required?

In simple terms yes you can convert to InnoDB with no major catastrophes AS LONG AS you haven’t gone crazy with custom fields. If you have more than 128 custom fields, your exp_weblog_data (now exp_channel_data) will be too wide, as the EE schema grows sideways for some reason. Too wide simply means, InnoDB cannot support greater than 128 columns in a table.

Aside from that, you only need to be aware of performance impacts.

The major benefit you will get from InnoDB is table locking is nullified, as InnoDB locks per-row that is being updated.

The trade-off however, is that InnoDB is less efficient at some tasks in EE’s core.

Specifically, InnoDB takes a hit on performance when performing a ‘select count(*)’ query as it will manually recount each individual row - where-as MyISAM maintains an indexed counter.

The count query is scattered all throughout the control panel. For example, when approving a comment, instead of EE incrementing the total_comments value in exp_weblog_titles (update exp_weblog_titles set total_comments = total_comments + 1 where entry_id = ##) , it will recount every comment in the exp_comments table:

-> select count(*) AS monkeypants from exp_comments where entry_id = ##
-> update exp_weblog_titles set total_comments = monkeypants where entry_id = ##

As I said earlier, this approach was taken (I assume for data integrity) across the majority of the control panel, for adding/editing articles, comments, and so on. We made the decision to convert to InnoDB and take the performance hit, then worked out the worst offenders, and rewrote them in the core.

We now have our own EE build with the inclusion of these changes, and memcache. So as we upgrade EE’s versions, we run our patching script over the top of it which brings EE back inline with our business req’s.

I’m pretty sure the InnoDB column limit is 1000 not 128…

Jamie

Just wanted to say thanks for posting this. This is a very interesting discussion.

I’m not very familiar with this stuff but I’d still like to ask a (stupid?) question(s)...

- Is this an EE issue or a weblog module issue?

- Does using the SQL Module make any difference?

- I understand that performance is a function of steel (read: handware and environment, etc.) but as a general rule of thumb how much traffic (users on the site at the same time?) causes EE to hit the fall? Or at least start to faulter a bit?

Thanks. Ya all actually taught this ol’ dog a couple new tricks 😊