Erm right then... 314 database queries on the dashboard!

First off this may sound slightly confrontational however it's not. I'm just shocked that's all.

Does anyone from Elgg care to discuss why the dashboard is using above 340 database queries for one page! Seriously. 340. How can this be feasable for any site to have this many database queries on 1 page? This isn't even including the ajax loaded widgets. Each of those has about 210 or more! I did a tally and the total number of queries just to get the dashboard to load is 1364!!!!!!!!!! And thats only with 5 widgets. Get a sudden unexpected surge in traffic and you are doomed. Get dugg and whoops there goes the server.

Hey I like the metadata based approach to the database design, it's seriously adaptable. But seriously how can you write a page that requires so many queries? Is it a matter of how the data is being pulled from the database? I was expecting a high number but come on, there must be some way of reducing this. For example, pull ALL the config info out of the database and keep it in php files.

This may sound harsh but there is no way I can launch a live site with Elgg if this is the state of affairs. And I was so looking forward to it too.

Actually I'm not going to through in the towel just yet but Elgg requires some serious hacking.

 

  • Why aren't you caching the database queries. This query appears 40 times on one page!

    select am.access_collection_id 
    from elggaccess_collection_membership am
    left join elggaccess_collections ag on ag.id = am.access_collection_id
    where am.user_guid = 2 and
    (ag.site_guid = 1 or ag.site_guid = 0)

    This also appears 40 times!

    select ag.id from elggaccess_collections ag  
    where ag.owner_guid = 2 and
    (ag.site_guid = 1 or ag.site_guid = 0)

  • This is called 19 times!

    SELECT * from elggentity_subtypes where type='object' and subtype='plugin'
  • This is called 18 times

    SELECT * from elggentities 
    where type='object' and
    subtype=2 and
    site_guid = 1 and
    (1 = 1) and
    enabled='yes'
    order by time_created desc
    limit 0, 10
  • I've made a hack to reduce the number of database queries by 175%. It can be found here. The dashboard goes from 341 to 124 queries.

  • Thanks for taking a serious look into speed and efficiency. This is something I think it important as well. I'll be testing the plugin shortly.

  • The Elgg start point is concerning.  It appears that either the core team has not clue about database performance or put it very low on their priority list to get the whole thing out of the door... I guess in the coming days/ weeks we will know how to proceed...

  • Yes it is somewhat concerning. I know sometimes writing software that is wrapped around objects and classes you can forget about the actual number of database queries that are being executed by the scripts, but even so.

    Speaking from experience, (I design bespoke content management systems for clients) there are many many pitfalls to watch out for, but unless you've actually had the problems yourself it's very hard to catch them.

    Hopefully if we all help out pointing out what needs to be optimised and writing patches/hacks/plugins to work around these points the core developers will take note and absorb them into the core.

    Personally I think they should look at this immediately, ignore whatever else and focus on optimisation. I know v1.5 has this focus but they really should release these changes before then. maybe 1.2?

    One thing that could solve this is an Elgg page compiling engine, that would compile a page into html and php. For example all the elgg_echo statements don't need to be rendered every time the page is created. This would mean the massive language library would never need to load and dramatically reduce the resources needed to make the page.

    Another thing  they could do is change the way the plugins work. They do after all have to be enabled in the backend, so the system doesn't really have to scan the entire plugin directory on every page, just look for the activated ones.

    Most of the problems I think stem from the extendability of Elgg. Whilst great to develop for the server requirements are crazy.

    Anyway we shall see if they respond

  • From my perspective, which is more business development and marketing than real technical, you are 100% right.  I believe that they should introduce a 1.2 release with focus on structural performance increase. In the meantime, I have no worry that the community will keep launching themes and plugins...

    Well, I messaged Dave, so indeed let's hear their feedback before we decide what to do...