Slow queries on large website

Discussion regarding Joomla! 2.5 Performance issues.

Moderator: General Support Moderators

Forum rules
Forum Rules
Absolute Beginner's Guide to Joomla! <-- please read before posting, this means YOU.
Forum Post Assistant - If you are serious about wanting help, you will use this tool to help you post.
CyberCr33p
Joomla! Apprentice
Joomla! Apprentice
Posts: 14
Joined: Sun Feb 17, 2008 1:50 pm

Re: Slow queries on large website

Post by CyberCr33p » Fri Jan 20, 2012 2:49 pm

The problem is with the temporary mysql tables which on a 10.000 - 20.000 article site can use 120-150mb/s I/O.

What helps:

1) Reduce the article number, doesn't help much if you have a site with news articles as you can have 50-100 new articles per day
2) Use jotcache, 15-20 minutes cache is ok
3) Use tmpfs for mysql temporary tables, you can do this only if you have dedicated server and plenty of free RAM
4) Edit error.php page from template so pages that don't exist don't load joomla code at all

User avatar
garyjaybrooks
Joomla! Guru
Joomla! Guru
Posts: 556
Joined: Wed Feb 20, 2008 4:34 am
Location: Michigan
Contact:

Re: Slow queries on large website

Post by garyjaybrooks » Fri Jan 20, 2012 3:02 pm

Can you explain each point and give us more detail. Scaling is a art and you list some interesting points. I think the list we could create for scaling Joomla can include 100 more items. I am starting to take inventory of things it will take to scale Joomla and what people can do to improve a large site with a big DB.

I would like to hear bout point 2 a little more, and point 4.

What version of Joomla are you using. Thank you for coming on the forum and taking the time to help the community.

Gary Brooks
Gary Brooks - Joomla Hosting with Phone Support http://www.CloudAccess.net Official host of http://demo.joomla.org

CyberCr33p
Joomla! Apprentice
Joomla! Apprentice
Posts: 14
Joined: Sun Feb 17, 2008 1:50 pm

Re: Slow queries on large website

Post by CyberCr33p » Fri Jan 20, 2012 3:15 pm

Jotcache is a joomla extension compatible with versions 1.5, 1.6 and 1.7, you can find it here: http://kanich.net/radio/site/jotcache . With this extension only the first visitor for each page will use PHP/Mysql for a predefined amount of time. You can have cache expire in 15-20 minutes so save a lot of CPU and disk I/O with this.

Now about the error.php . Each time someone tries to visit a page (article) which removed , joomla load a page that says that the article doesn't exist. If you have many articles that you removed (for example removing 1.000+ articles) this uses PHP/Mysql again. But if you create an empty error.php file inside your template , or if you have just text then it doesn't load joomla code and it saves resources.

But the best option is using tmpfs for temporary tables because this is the real bottleneck. This way the temporary tables are created in RAM and not on the disk. But this only can be done in dedicated servers with plenty of free RAM and not in shared hosting if it's not already activated by the web host.

User avatar
garyjaybrooks
Joomla! Guru
Joomla! Guru
Posts: 556
Joined: Wed Feb 20, 2008 4:34 am
Location: Michigan
Contact:

Re: Slow queries on large website

Post by garyjaybrooks » Fri Jan 20, 2012 3:22 pm

Interesting... i'll take a look at this stuff and add it to my list and publish writing soon.

Again thank you for sharing.

** note to everyone i"ll work to report back on these examples.
Gary Brooks - Joomla Hosting with Phone Support http://www.CloudAccess.net Official host of http://demo.joomla.org

rojer_31
Joomla! Apprentice
Joomla! Apprentice
Posts: 7
Joined: Thu Nov 02, 2006 2:57 pm
Contact:

Re: Slow queries on large website

Post by rojer_31 » Tue Jan 24, 2012 2:56 pm

@Neuromante
Try full page caching. Jotcache is a great plugin for this (provides a lot of control)
http://extensions.joomla.org/extensions ... ache/13155

Cache cleaner will come in handy if you use full page caching:
http://extensions.joomla.org/extensions ... cache/9495

btomczak
Joomla! Apprentice
Joomla! Apprentice
Posts: 34
Joined: Wed Dec 14, 2005 5:38 pm
Location: Portland OR, USA

Re: Slow queries on large website

Post by btomczak » Fri Jan 27, 2012 12:05 am

Caching schemes and additional hardware only mask the fact that the query in question is extremely expensive and inefficient. There are ways this problem could be fixed in code, but we seem to be headed in the wrong direction. I just did a test upgrade of my client's 11,000+ article site and 2.5.0 is bringing an unloaded server with nothing else on it to a grinding halt. There is no way he can do this upgrade as a result. Suggestions regarding tmpfs have merit if you can throw enough memory at the problem, but this isn't an option for most people.

You can argue the point, but I don't consider a 12,000 record DB all that large.

User avatar
garyjaybrooks
Joomla! Guru
Joomla! Guru
Posts: 556
Joined: Wed Feb 20, 2008 4:34 am
Location: Michigan
Contact:

Re: Slow queries on large website

Post by garyjaybrooks » Fri Jan 27, 2012 12:15 am

What is the URL of the site? is it only the index or the inside landing pages?
Gary Brooks - Joomla Hosting with Phone Support http://www.CloudAccess.net Official host of http://demo.joomla.org

btomczak
Joomla! Apprentice
Joomla! Apprentice
Posts: 34
Joined: Wed Dec 14, 2005 5:38 pm
Location: Portland OR, USA

Re: Slow queries on large website

Post by btomczak » Fri Jan 27, 2012 12:24 am

The site remains at 1.7.3 so you won't see the problem. In 1.7.3 the problem on this site was limited to the latest articles module so we've already had to compromise by not using it.

http://indigenouspeoplesissues.com

Please also note that performance was no problem 1.5. Bottom line - the issue here is a very, very expensive query.

User avatar
garyjaybrooks
Joomla! Guru
Joomla! Guru
Posts: 556
Joined: Wed Feb 20, 2008 4:34 am
Location: Michigan
Contact:

Re: Slow queries on large website

Post by garyjaybrooks » Fri Jan 27, 2012 12:34 am

lets find the issue. Do you have the 2.5 site ready on your disk?
Gary Brooks - Joomla Hosting with Phone Support http://www.CloudAccess.net Official host of http://demo.joomla.org

btomczak
Joomla! Apprentice
Joomla! Apprentice
Posts: 34
Joined: Wed Dec 14, 2005 5:38 pm
Location: Portland OR, USA

Re: Slow queries on large website

Post by btomczak » Fri Jan 27, 2012 1:33 am

We KNOW what the issue is.

It's been pointed at by several people in this thread. I know what class::method generates the problem query. The query needs to be fixed.

User avatar
garyjaybrooks
Joomla! Guru
Joomla! Guru
Posts: 556
Joined: Wed Feb 20, 2008 4:34 am
Location: Michigan
Contact:

Re: Slow queries on large website

Post by garyjaybrooks » Fri Jan 27, 2012 2:12 am

Great, what is it? I have some coders and I have the ability to submit a bug report, I have the software to profile too. But we need a real good case study. Are you willing to help?
Gary Brooks - Joomla Hosting with Phone Support http://www.CloudAccess.net Official host of http://demo.joomla.org

btomczak
Joomla! Apprentice
Joomla! Apprentice
Posts: 34
Joined: Wed Dec 14, 2005 5:38 pm
Location: Portland OR, USA

Re: Slow queries on large website

Post by btomczak » Fri Jan 27, 2012 3:02 am

/components/com_content/models/articles.php

The offending query is generated by ContentModelArticles::getListQuery()

Standard Joomla stuff. here was at least one good suggestion for a potential solution, and I have some thoughts of my own, but I don't see any solutions that don't involve some fundamental reworking of the way models in Joomla operate. This is not a conversation I feel qualified to get into.

There are several examples of the problem query in this thread. I'm sure if your coders look at those examples and look at how that getListQuery() method generates the query in concert with the way Joomla models do what they do, they will know as much as I do.

User avatar
garyjaybrooks
Joomla! Guru
Joomla! Guru
Posts: 556
Joined: Wed Feb 20, 2008 4:34 am
Location: Michigan
Contact:

Re: Slow queries on large website

Post by garyjaybrooks » Fri Jan 27, 2012 3:10 am

Can we get access to your sample data set or your server where you its broken? If you would let me hookup our tools to your server we can see why you have the pain. If you give me your server and the sample data set I can compare our system compared to yours.

I would be willing to sign a NDA so we delete your data after we finish our testing.

I want to see how you have the data arranged and why it causes stress. We can not just generically say 11,000 articles makes Joomla slow. If you have a case study would be nice if you would provide your sample data set so I could run load test on it and watch the system. We own this software http://www.jetprofiler.com/ and can watch whats happening with MySQL and what causes the stress.

If you wont provide the data set, maybe someone else?
Gary Brooks - Joomla Hosting with Phone Support http://www.CloudAccess.net Official host of http://demo.joomla.org

btomczak
Joomla! Apprentice
Joomla! Apprentice
Posts: 34
Joined: Wed Dec 14, 2005 5:38 pm
Location: Portland OR, USA

Re: Slow queries on large website

Post by btomczak » Fri Jan 27, 2012 4:25 am

As I've already said - I do not have the time or resources to pursue this any further.

User avatar
garyjaybrooks
Joomla! Guru
Joomla! Guru
Posts: 556
Joined: Wed Feb 20, 2008 4:34 am
Location: Michigan
Contact:

Re: Slow queries on large website

Post by garyjaybrooks » Fri Jan 27, 2012 4:32 am

It would take 15 min and you came here advertising that the core is messed up in Joomla.

Seems you might have been fooling with us.
Gary Brooks - Joomla Hosting with Phone Support http://www.CloudAccess.net Official host of http://demo.joomla.org

CyberCr33p
Joomla! Apprentice
Joomla! Apprentice
Posts: 14
Joined: Sun Feb 17, 2008 1:50 pm

Re: Slow queries on large website

Post by CyberCr33p » Fri Jan 27, 2012 8:23 am

So things go worse in 2.5?

User avatar
garyjaybrooks
Joomla! Guru
Joomla! Guru
Posts: 556
Joined: Wed Feb 20, 2008 4:34 am
Location: Michigan
Contact:

Re: Slow queries on large website

Post by garyjaybrooks » Fri Jan 27, 2012 3:34 pm

As of this point we do not know. If you upgrade and you have a large site and it goes slow. Please contact me and i'll help to find out why the issue is happening. At this point we are looking for case studies.

Gary Brooks
Gary Brooks - Joomla Hosting with Phone Support http://www.CloudAccess.net Official host of http://demo.joomla.org

cuteschweinchen
Joomla! Apprentice
Joomla! Apprentice
Posts: 12
Joined: Fri Sep 09, 2011 1:10 pm

Re: Slow queries on large website

Post by cuteschweinchen » Tue Jan 31, 2012 3:43 pm

I just upgraded a large radio & new site to Joomla 2.5 and I have lots of problem with loading time in the front & administrator. It takes me about 1 minute to open an article :( Also the cache options are not very helpful for a site which get's updated every hour

User avatar
garyjaybrooks
Joomla! Guru
Joomla! Guru
Posts: 556
Joined: Wed Feb 20, 2008 4:34 am
Location: Michigan
Contact:

Re: Slow queries on large website

Post by garyjaybrooks » Tue Jan 31, 2012 3:54 pm

What is the URL of the site?
Gary Brooks - Joomla Hosting with Phone Support http://www.CloudAccess.net Official host of http://demo.joomla.org

cuteschweinchen
Joomla! Apprentice
Joomla! Apprentice
Posts: 12
Joined: Fri Sep 09, 2011 1:10 pm

Re: Slow queries on large website

Post by cuteschweinchen » Tue Jan 31, 2012 4:08 pm

I've sent you a PM - Thank you so much for looking into this

flashgordonweb
Joomla! Intern
Joomla! Intern
Posts: 74
Joined: Sat Mar 29, 2008 3:52 pm

Re: Slow queries on large website

Post by flashgordonweb » Tue Jan 31, 2012 10:30 pm

I too have a site (~13,000 articles) that I am waiting to update because of how slow it becomes in 2.5. Can we please keep this thread going so that we can find a solution.

User avatar
garyjaybrooks
Joomla! Guru
Joomla! Guru
Posts: 556
Joined: Wed Feb 20, 2008 4:34 am
Location: Michigan
Contact:

Re: Slow queries on large website

Post by garyjaybrooks » Tue Jan 31, 2012 11:13 pm

I found out that we do have a issue. I have a database on file with the issue and I'm going to look deep into the issue. If anyone is a coder and interested in helping me, please send me a private message. We can work on this issue together and submit a bug fix.

@flashgordonweb, if you can please send me a private message so we can connect on skype.
Gary Brooks - Joomla Hosting with Phone Support http://www.CloudAccess.net Official host of http://demo.joomla.org

User avatar
BBC2009
Joomla! Intern
Joomla! Intern
Posts: 50
Joined: Sun Feb 08, 2009 4:41 pm

Re: Slow queries on large website

Post by BBC2009 » Wed Feb 01, 2012 3:19 am

I have noticed one very strange thing.
Same with Joomla 1.7-2.5.
Same at localhost (Xampp) and two live servers (PHP 5.1-5.3)

I first beleived it is some strange settings at my Xampp server, but soon i noticed same behaviour even at two live servers.

If i have setting for Error Reporting in Global Configuration as "System default" or "None" Joomla is very, very slow.

If i put setting as "Maximum" or "Development" Joomla is much, much faster. Every time i change settings i see difference, so it is not some imagination.

How to explain that ?

I never noticed any difference in pageload and speed with Joomla 1.5, when i changed those settings.

puneetjain87
Joomla! Explorer
Joomla! Explorer
Posts: 337
Joined: Mon Feb 16, 2009 4:38 am

Re: Slow queries on large website

Post by puneetjain87 » Thu Feb 02, 2012 4:01 pm

It's all happening because of mysql. My webhost says your mysql processes puts too much load on the server. I hope somebody will soon come up with a solution.

There was no such issue on 1.5, It all started when I moved to 1.7 and then to 2.5. It is strange that none of the joomla developers come up with any solution till now.

User avatar
BBC2009
Joomla! Intern
Joomla! Intern
Posts: 50
Joined: Sun Feb 08, 2009 4:41 pm

Re: Slow queries on large website

Post by BBC2009 » Thu Feb 02, 2012 5:02 pm

Is it only me thinking that Joomla 1.7-2.5 is much, much more slower than Joomla 1.5 ?

Yes, it is fast when it is just installed, but as soon you install some modules, plugins and extensions (even from editors pick, or best voted) it gets painfully slow.

( I know what you might say, many modules, extensions and plugins, slower page. But it is not a point. Joomla 1.5 manage a lot more plugins and extensions without getting so slow.)
Last edited by BBC2009 on Thu Feb 02, 2012 5:08 pm, edited 1 time in total.

User avatar
garyjaybrooks
Joomla! Guru
Joomla! Guru
Posts: 556
Joined: Wed Feb 20, 2008 4:34 am
Location: Michigan
Contact:

Re: Slow queries on large website

Post by garyjaybrooks » Thu Feb 02, 2012 5:06 pm

Its not about versions it's about getting the code right. We are still looking into the issue. Everyone hold tight we will fix this. - if you want to help with the fix please contact me.
Gary Brooks - Joomla Hosting with Phone Support http://www.CloudAccess.net Official host of http://demo.joomla.org

cuteschweinchen
Joomla! Apprentice
Joomla! Apprentice
Posts: 12
Joined: Fri Sep 09, 2011 1:10 pm

Re: Slow queries on large website

Post by cuteschweinchen » Thu Feb 02, 2012 5:12 pm

Thank you Gary!!!

puneetjain87
Joomla! Explorer
Joomla! Explorer
Posts: 337
Joined: Mon Feb 16, 2009 4:38 am

Re: Slow queries on large website

Post by puneetjain87 » Thu Feb 02, 2012 5:14 pm

Hey thanks a lot, I have been waiting for that since quite long time.
I hope you will come up with it soon.

This issue might be related to caching also. You may read one of my earlier threads, http://forum.joomla.org/viewtopic.php?f=616&t=675073

PS: Right now I am on 2.5.1

User avatar
BBC2009
Joomla! Intern
Joomla! Intern
Posts: 50
Joined: Sun Feb 08, 2009 4:41 pm

Re: Slow queries on large website

Post by BBC2009 » Thu Feb 02, 2012 5:32 pm

Most disappointing is that it is slow even with expire headers. First i believed all those extern files make problems. Then i disabled everything that pulls JS and CSS files from other servers. Not much gain in pageload speed.

As before, have to say....Joomla 1.5 with expire headers are lightning fast, cache, GZIP, etc don´t matter at all.

User avatar
BBC2009
Joomla! Intern
Joomla! Intern
Posts: 50
Joined: Sun Feb 08, 2009 4:41 pm

Re: Slow queries on large website

Post by BBC2009 » Thu Feb 02, 2012 6:21 pm

Does this patch require new install ?

http://joomlacode.org/gf/project/joomla ... m_id=27774

I just opened last 2.5.1 Joomla ZIP (from Joomla website) and i see that original joomla.sql in "installation" folder has 1676 lines. This one from patch has 6431 lines.


Locked

Return to “Performance - Joomla! 2.5”