Thread

I will be creating a content site ( with a lot of articles, 1-3 mln ) so i was wondering will EE (weblog module) handle this amount of data?
I looked a bit at the internals and this query makes me think about the speed:

FROM exp_weblog_titles AS t LEFT JOIN exp_weblogs AS w ON t.weblog_id = w.weblog_id LEFT JOIN exp_weblog_data AS wd ON t.entry_id = wd.entry_id LEFT JOIN exp_members AS m ON m.member_id = t.author_id LEFT JOIN exp_member_data AS md ON md.member_id = m.member_id

It does not look fast.
If you combine 1-3 mln articles with huge traffic then this is a recipie for headakes.

I am thinking of a few ways to avoid problems, and i am wondering if you can comment on them or provide alternative solutions:

1. Avoid weblog module by using the query module and custom tables.
  I can use fast queries but there won’t be an admin interface to manage the articles/categories.
2. Avoid calls to weblog module member functions and use custom queries.
  Provided i know the weblog fields, is it possible to simplify the queries, can you give an example?
3. Considering the fact that the installation will run on site with little changes made to if after it is live, is there a way to get rid of the queries like

SELECT es.* FROM exp_sites AS es WHERE es.site_id = '1'

, i would like them to be also cached, to have 0 queries on a cached page?

4. Is there a way to add memcached as a cache engine using a a custom plugin/extension or do i have to modify core files?
5. How would you go about loadbalancing for one site?

Thank you

I’m interested in this as well…

haha I thought you said 1-3 min. I do believe the correct term is just a small m for million.

Unless you’re displaying 1-3 million per page, the total content of your site is largely immaterial.  MySQL’s a very good database system and on a professionally managed server, with properly indexed tables (EE’s tables are properly indexed), pulling results from among massive amounts of data is, well, it’s job.  Depending on the actual content size it’s possible you might reach MySQL’s limits, but if memory serves, that’s like 4GB per table.  Your server is liable to blow up before you hit MySQL’s physical limitations on data size. 😉

Database caching is included in EE, but there are some queries that cannot be cached.  For example:

SELECT es.* FROM exp_sites AS es WHERE es.site_id = '1'

Loads your site’s preferences, one of which is…you guessed it…database cache settings.  And overall, it’s not MySQL read access that would likely kill you during a heavy traffic spikes.

You can certainly use the Query module for queries for simple information that do not need the weblog entries tag (a list of titles and links, for instance), but there’s no reason to utterly avoid it.  Again, we have file-based caching mechanisms at the template level, tag level, and the Weblog entries tag has a special file cache for its own dynamic queries.  MySQL has its own caching mechanisms as well.

Load balancing EE has no different requirements than load balancing any other PHP/MySQL application.  What type of traffic are you expecting?  There are many high volume prominent sites that are powered by ExpressionEngine, and they do not employ performance “tricks” or modifications.

I am looking at using Webfaction for an upcoming project, a host that provides very easy access to Memcached. Every app is allotted dedicated memory.

What general steps might I need to take to utilize Memcached with Expression Engine?

Thanks!

I would imagine that you would need to modify the database driver to set and fetch data from memcached as necessary.  All of EE’s queries run through the database driver, so the extent of modifications necessary would likely be limited to this driver.  Though I would seriously question the necessity of that.  This extra layer of complexity isn’t automatically going to be better than a well-tuned MySQL server using EE as-is out of the box.  Before considering these types of modifications, the more important question to be answered first is whether or not the site actually needs this technology.