Thread

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 😊

ChiefAlchemist,

I’m sure there going to say there’s no way to know.  Because EE doesn’t constrain the designer/developer from building a site any way they wish every site has a very different performance profile and very different impact on a server.  Something as simple as how you handle your conditionals can have a major affect on how many users your site can handle and how quickly it handles them. 

Jamie

Thanks Jamie. Agreed, I was just looking for some back of the envelope type estimates.

That said, I might be cool to have a forum / wiki topic area on the finer points of performance. Even a simple Top 10 things you can do to increase your EE performance.

Ah, well, having wrestled with performance stuff an awful lot myself (always related to stressing out the MySQL server too much) I could give some tips probably.

1. Don’t over use embeds.

They are pretty light on the SQL server (generally speaking) but I’ve seen people go way overboard on them and then they bog things down.

2. Pay special attention to parsing order and conditionals.

Especially important with conditionals (simple versus not simple).  Simple conditionals will parse before whatever they enclose.  But I’ve seen many of them in close proximity cause EE to get confused and still parse the stuff inside first.  Just ran into this this week actually and it caused serious problems as suddenly I had roughly twice the number of queries occurring.

3. Use as few entries tags as possible and keep the sorting and order parameters to the indexed columns (entry_date, entry_id, weblog, etc…)

The custom fields aren’t indexed which means sorting on them requires MySQL to work harder in order to figure out which entries to show.

When your table of entries gets big enough even things like selecting entries off of categories (which are fully indexed but in a separate table) can start making the SQL server work pretty hard.

4. Try to be very careful and smart about how you use caching.

You can make things worse using tag caching and page caching too much.

Tag and page caching are both URL based.  Meaning if you Page cache an embed and it exists on 100 different URLs a cache gets saved for each URL not just for the one instance of an embed.  Same goes for Tag caching.  End result (if your site is busy enough) is that you end up with a new performance problem due to the GIGANTIC cache clearing and being rebuilt too often.  Likewise writing to/and deleting from the disk can also create a new bottleneck.

Finding ways to use Page and Tag caching that don’t create multiple caches each time the page gets called in a different URL are pretty helpful.

5. Take a look at Solspace’s Template Morsels module.  It solves the multiple cache issue and the disk write/delete issues.  But introduces it’s own as it completely ignores the URL so things like pagination can’t be used unless you get kind of hackish with your usage.  I’m using it in ways it wasn’t intended and it sometimes backfires one me as a result.

6. Keep in mind that solving one bottleneck often causes you to discover a new one.

It’s frustrating but fixing one issue sometimes just causes another one as your new found performance allows the traffic to push another aspect of your server harder than it could before. 

Look at large sites struggles even with Memcached (which is so efficient compared to other types of caching that it’s ridiculous) but sites like Facebook and Twitter have managed to find ways in which it will bog down too.

I’m sure all of this has been written about before though and I actually seem to remember a WIKI article already existing on this. Maybe I’m wrong though.

Jamie

Thanks. I’ll read and log in my mind later.

One other, disable anything you do need. For example, comments. I’ve noticed that has a pretty big impact on page loads times even on small sites.

I think each of these would make for an interesting blog article, eh?

@ Jamie—

From my POV as someone responsible for a large EE site, your points are exactly right—a good summary of the challenges of tuning EE.  We also use InnoDB tables for a variety of reasons having to do with table locks, which were occurring to an unacceptable degree before.  The key to this is the size of your weblog_titles and comments tables.  If they are large, then it’s likely that you will be afflicted by table locks in some way.

One other tidbit:  pay attention to exp_search_log.  It can become very very large very quickly on a high traffic site.  We’ve disabled search query logging for this reason.

My 2 cents:

Cache, Cache, Cache, Cache and Cache.  Switching from MyISAM to InnoDB can be a major pain; however I WOULD WHOLEHEARTEDLY SUPPORT switching given certain preconditions.

I worked for a major sports media corporation (1-2M visitors / month) and we used a heavily modified expression engine.  (The site was replaced last year with a different commercial CMS). We cached everything we could.  We normally had 5 large primary cloud web servers (6 core, 16 gig ram).  We had one master mysql server on iron and 2 - 4 cloud slaves and used 3 memcache servers.  We would cache db queries, templates and tags.  We separated administrative tasks via a separate server for admin access only and static assets were served via another server. Further, we had Akamai in front of everything.  Still, there were times (especially when serving rest api endpoints to customize individual UX) that we wound up locking up the master AND slaves and getting nearly 40 minutes behind on replication.  The only solution during these rush hours was to restart all the web heads and sql servers and babysit until rush hour was over.

Needless to say, this was a major PITA for the on-call guys (read: me).  We finally made the decision to switch the tables to InnoDB and from that point on we never had to babysit the server.  We did come close (several times) to overflowing the number of available columns in exp_channel_data (mainly due to varchar columns + utf8 storage); and we had some corruptions when some younger developers decided to add a column during peak hours while replication was on-going. 

I know you may be tempted to make the switch, and if you do, and do it right you shouldn’t run into any major snags; but before you do anything make sure that you utilize as much caching as you can.  If you (or your client) can’t afford a good quality CDN, you can always put a squid caching proxy in front of your main servers.