The Elgg Community: Erm right then... 314 database queries on the dashboard!

Home Plugins Groups Discussion

Server optimisation

Subscribe to feed

Group discussion

Group discussion > Erm right then... 314 database queries on the dashboard!

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

Oliver Lillie

Oliver Lillie
670 days ago

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.

Oliver Lillie

Oliver Lillie
670 days ago

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)

Oliver Lillie

Oliver Lillie
670 days ago

This is called 19 times!

SELECT * from elggentity_subtypes where type='object' and subtype='plugin'

Oliver Lillie

Oliver Lillie
670 days ago

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

Oliver Lillie

Oliver Lillie
670 days ago

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.

Jade

Jade
669 days ago

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.

aduiski

aduiski
669 days ago

OMG!

Oliver Lillie

Oliver Lillie
669 days ago

An update to the compactor plugin has reduced this by another 20 or so queries.

Tom

Tom
668 days ago

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...

Oliver Lillie

Oliver Lillie
668 days ago

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

Tom

Tom
668 days ago

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...

Ben Werdmuller

Ben Werdmuller
668 days ago

Hi folks,

This is our #1 development priority, and to answer Tom's points, we have extensive experience in maintaining scalable web applications (four years on Elgg, plus many years running popular applications prior to that). Elgg 0.9x runs extremely swiftly, and those lessons have been taken on board for Elgg 1.x. That said, we are aware that performance isn't where it needs to be.

We've made some serious database enhancements for the 1.1 release, and have further major enhancements in progress. (If you're watching svn, you'll see some schema enhancements etc start to creep in.) The techniques that Oliver has provided are, I believe, a good step down the right road. We're experimenting with variations on these approaches, which will mean a drastic reduction in database queries.

We're delighted with the positive approach the community has taken, providing examples of code and solutions. This is where open source really works. I'll keep you up to date with what we're doing, and in the meantime will engage in discussion about how to make Elgg the most efficient it can be.

Oliver Lillie

Oliver Lillie
668 days ago

Thanks for taking the time to reply Ben. It's good to know that this is your number 1 priority. If I come up with any more optimisations do you want me to post hacks as I have done before or put them somewhere else.

Also, have you considered making the compactor plugin part of the core plugins? With it's caching functionality it does speed up Elgg sites quite considerably.

Ben Werdmuller

Ben Werdmuller
667 days ago

It'd be good to talk a little about what compactor is doing - it's possible it would be more efficient to look at how these techniques could be integrated right into core rather than as a plugin. There may also be other techniques that are more efficient when looked at in this way.

Feel free to keep posting hacks; I think, though, it'd be good to talk about the underlying technical goings-on in the forum and open it up to discussion.

Oliver Lillie

Oliver Lillie
666 days ago

@Ben The compactor does several things, mostly it just applies YSlow's recommendations to any outputted content. There is only one aspect of the compactor that could benefit from absorbtion into the core and that is the file caching. Because the cache files are checked on the system init event, there is still a lot of processes that go on before it that don't really need to happen. It caches css, javascript and dynamic images (gif, jpeg and pngs). Each cached item means it doesn't have to be regenerated freeing up a lot of resources. At the moment because each file is cached based on "md5($_SERVER['PHP_SELF'].$_SERVER['QUERY_STRING'])" the caching is permanent until the cache directory is cleared (automatically in hourly, weekly, monthly intervals etc) and I still have to hook into the avatar update event to regenerate the avatars when they are updated.

Apart from the caching functionality as mentioned nothing else would really benefit from absorption into the core code, but due to the speed increases seen from it I think it should be one of the default plugins. It doesn't just free up server resources with the caching but also speeds up delivery of the page to the browser with it's white space stripping and defalting (gzip) methods. It also searches for any external js sources and then collects them all into one resource so instead of loading 3-4 js files it loads 1. If the js file can't be combined with others (ie if it is server dynamically through php) it leaves it alone, but the output of the dynamic js is still compacted, deflated and cached all the same. The same method is applied with css.

It can also perform other functions such as moving all the discovered css to just below the opening <head> tag and the js to just above the closing </body> tag for optimisation purposes, but they are rather superfluous and I coded them just for shits and giggles.

The basic results of the whole plugin shrink the required output of one Elgg page load from 300kb to 44kb, then with the etag and last-modified headers a page can load in as little as 2kb. Vastly speeding up the delivery, saving 298kb in bandwidth (saving an estimated 1.5GB per day based on 5 (max) page loads per second over 24 hours).

So as you can see, it may well be worth it.

Oliver Lillie

Oliver Lillie
666 days ago

Even if not you really should include the htaccess additions supplied with compactor

### CONFIGURE EXPIRIES ############################################################
# Configures the caching expiry date for browser caching of static files.

<IfModule mod_expires.c>
  <FilesMatch "\.(jpg|jpeg|gif|png|mp3|flv|mov|avi|3pg|html|htm|swf)$">
       ExpiresActive on
       ExpiresDefault "access plus 1 year"
   </FilesMatch>
</IfModule>

### CONFIGURE ETAGS	###############################################################
# Configures E-tag headers to be sent with static content. Basically the browser
# updates it cache if this header changes.

<FilesMatch "\.(jpg|jpeg|gif|png|mp3|flv|mov|avi|3pg|html|htm|swf)$">
	FileETag MTime Size
</FilesMatch>

Oliver Lillie

Oliver Lillie
666 days ago

fyi, performance via Firebug's Net panel

Oliver Lillie

Oliver Lillie
666 days ago

obviously, ignore the 2mb swf header, it's just a placeholder design.

kurtqian

kurtqian
659 days ago

Hi Oliver, there is bug i just found at the Compactor plugin.

when i check the

Compact Javascript Resources: Attempt to join any external Javascript files into one file. option to enabled, the tinymce plugin will not work properly,for instance at the blog edit post part,anyway you do an excellent work,thanks for sharing:)

Dan Knauss

Dan Knauss
577 days ago

What is the status with Compactor? Still useful/workable/worthwhile with Elgg 1.2?

Phoenix

Phoenix
508 days ago

Has this been fixed in 1.5? I hope!

DhrupDeScoop

DhrupDeScoop
507 days ago

@phoenix

if you are expecting to setup a large site, but do not have the resources yourself to investigate into the finer details of what Elgg offers or perhaps may have a sight shortcoming for what you envision, you will not necesarily get satisfyng answers by posting on numerous topics, hoping that someone may have some magical answers to all your queries. Open Source -- such as Elgg particularly grows in features and power simply there are some people out here and there who - when they have spare time from their real day-work put in a lot of of effort in developing varius areas of the pkg.

I suggested gently to you earlier.. You sound like you need a *professional consultant to investigate Elgg for what seems to a large project that you are planning. And large projects generally deserve a much more serious fact-finding approach that the "fishing" you seem to be doing.-

Phoenix

Phoenix
507 days ago

Dhrup, thanks for the sagacious response, if somewhat condescending :)

I'm fishing because that's what one has to do when the documentation is not consistent or the search function doesn't work well. Elgg is getting superb reviews from universities and so on--it certainly looks like it deserves them!--but those are not high traffic public-web environments. I am a fairly savvy techie, but the approach of Elgg seems quite different to me. May it's this whole MVC thing, manifest files and whatnot.

I'll study the plugin system a little more to be able to extend it, but one comes to a forum to hasten that process by, you know, asking questions. Thanks for your advice.

DhrupDeScoop

DhrupDeScoop
507 days ago

@Ph

I did give you some small suggestion regarding "searching on google..."

you must have realized by vaious reactions to your ostensibility as a "trolling.." may have been unintended on yr part. I told you were too many questions on too many posts. a minute statement like "Has this been fixed in 1.5? I hope!" does not go across well, unless backed up with evidence of resasnable prior attempt at research. The whole "MVC thing" becomes a moot point" -- You do .."sound like you need a *professional consultant to investigate.." if your project is that large. :High traffic" ? "public" ? ANY high traffic, public site can be sustained performancd-wise if one has a sufficiently large budget to fund it.

Phoenix

Phoenix
294 days ago

Dhrup. I came back to see how Elgg has progressed and chanced upon your remark. Sorry mate, a tool that is defensive about the basics is not quite there yet, is it. Elgg looks really pretty and I love some of its functionality, but even on its own site the forums are sometimes terribly slow. There isn't such huge amounts of traffic on the site. And no, I shouldn't need a professional to investigate a product if the product is well-coded. Never needed any professionals in implementing PHPBB or Wordpress. Elgg's interface is what I want, but as a collaboration tool with simple document management functionality and such, it still has a long way to go. Its popularity in the academic world should not, imho, guide its development as a much more potent platform. The opportunity is for Elgg to lose.

| About | Terms | Privacy |

Powered by Elgg

Powered by Elgg, the leading open source social networking platform