Joomla Templates Club 406,261 posts

[Andrew Winkler Friend Andrew WinklerJoin date: September 2014Posts: 725Downloads:206Uploads:31 Thanks: 291 Thanked: 34 times in 2 posts]

#205089

Ever since implementing my first JA template, I got problems with my site producing heaps of slow SQL queries, and coming to a grinding hold way too often. My hosting firm of 9.5 years, Siteground, who are specialists in Joomla hosting kept telling me that my site had bad SQL coding and suggested to get an SQL specialist in. I didn’t believe them and kept changing templates and upgrading my server, till I ended up with the top of the range dedicated enterprise server upgraded to 64GB of RAM and a 4x500GB RAID 10.

It still didn’t suffice, it only got marginally better after enabling memcache. As soon as I was doing a massmail or had more than 10,000 daily visitors, SQL server collapsed.

In the end I migrated my server to a different hosting company, who were offering me an even better machine for the same money, this time with 128GB of RAM and a 2x520GB SDD RAID 0.

I’m also running a wordpress site on the same server, which is now blazingly fast. Every article, no matter how big, opens in a second, even though the server is on the other side of the world.

My Joomla 3.4 site though with the JA University T3 template is not as happy. It’s only (very) fast for about 5 minutes after restarting the server. Shortly after it gets to a grinding hold and produces nothing but 504 errors. Even upgrading from apache/memcache to nginx/FastCGI cache and from MySQL to MariaSQL didn’t help. We have run MySQLTuner and followed all the recommendation, but it made no difference. I have to restart the server or at least the SQL engine every 5-10 minutes, that’s how ridiculous it is.

The hosting company put their best man on the case. He came with the following result. The site is badly written because there are too many SQL queries without joins. He doesn’t think it’s Joomla, because he hasnt’ seen this problem before with other Joomla sites. In fact, he has never before seen an SQL server collapsing like mine.

He thinks it’s most likely the template or maybe one of the extensions which isnt’ properly coded. I hope you can help me with this.

[Manos Moderator ManosJoin date: February 2014Posts: 2806Downloads:46Uploads:56 Thanks: 200 Thanked: 633 times in 576 posts]

#564918

Do you have any 3rd party extension installed ?
I wasn’t able to visit your site it throws a 504 Error ( Gateway Time-out)

[Andrew Winkler Friend Andrew WinklerJoin date: September 2014Posts: 725Downloads:206Uploads:31 Thanks: 291 Thanked: 34 times in 2 posts]

#564924

504 errors? That’s exactly the problem I was writing about. Yes, I have 3rd party extensions installed on my Joomla site. Who doesn’t? There are quite a few: K2, EasyDiscuss, EasyBlog, Komento, AcyMailing, JFBConnect, Jomsocial, Contus HD Videoshare, Akeeba and a few others, but the problem with the slow SQL already existed before I added those extensions.

[Manos Moderator ManosJoin date: February 2014Posts: 2806Downloads:46Uploads:56 Thanks: 200 Thanked: 633 times in 576 posts]

#564925

Hello,

I run quite a few tests on a small VPS with no problem at all but if I start adding all those extensions I am sure my VPS will run out of memory and MySQL will start having big issues to cope.

What’s the server load on your box ?
How much RAM etc ?
Can you provide some info ?

Regards

Manos

[Phill Moderator PhillJoin date: February 2014Posts: 7013Downloads:40Uploads:77 Thanks: 917 Thanked: 2206 times in 1818 posts]

#564927

Here Is what I would do.

1: Take a backup using Akeeba.
2: Using WAMP or similar install your site on your local machine.
3: Once you have it running insall one of the many SQL reporting tools available on the JED.
4: Using that you should be able to work out what query is causing the delay. Remember, the template does not run any queries of its own but may interact with other extensions that do. Having said that I have seen and even helped many with this template and have yet to see a problem like yours.
5: If I cannot work out which extension is causing the problem from the report I would disable all but the core extensions using the extension manager and see how things run. It should spring to life unless there is a corrupt table or something else such as a hack causing the problem. If all runs well turn on extensions a few at a time then extensively try and replicate the issue before turning on more. You should be able to work out where the issue lies quite easily this way.

Over the years I’ve seen all sorts of problems like this. The last one was over at Coppermine Gallery. We are using SMF for the forum and it turns out that the banning system SMF uses is poorly coded so when ban records get very large it repeatedly crashes MySQL. In that case our host, Bytemark, used their own reporting tools and very quickly found the problem for us. We could have re-written the code but instead decided to drop the banning and just use a spam protection tool instead. It does show that just one badly written query can cause this kind of problem.

2 users say Thank You to Phill for this useful post

1. Manos
2. Andrew Winkler

[Andrew Winkler Friend Andrew WinklerJoin date: September 2014Posts: 725Downloads:206Uploads:31 Thanks: 291 Thanked: 34 times in 2 posts]

#564931

Which SQL reporting tool do you recommend. I searched on JED for SQL reporting but none of the listed products seemed to be what you were talking about.

[Andrew Winkler Friend Andrew WinklerJoin date: September 2014Posts: 725Downloads:206Uploads:31 Thanks: 291 Thanked: 34 times in 2 posts]

#564932

Dedicated server with 2x Quad Core Processors, 128GB RAM, 2 x 480 GB SSD, RAID 0. Not cheap.
The server load hovers around 3 to 6. It only runs two sites. A wordpress site that opens every page instantly. It only takes 1 second to open even the biggest page from the other side of the world. The joomla 3.4 site is reasonably fast too immediately after restart, but runs out of puff after a short while because of heaps of slow queries with no joins.

[Phill Moderator PhillJoin date: February 2014Posts: 7013Downloads:40Uploads:77 Thanks: 917 Thanked: 2206 times in 1818 posts]

#564934

<em>@andrewwinkler 465918 wrote:</em><blockquote>Which SQL reporting tool do you recommend. I searched on JED for SQL reporting but none of the listed products seemed to be what you were talking about.</blockquote>

Please create an Akeeba backup of your site the PM me a direct link where I can download it.

1 user says Thank You to Phill for this useful post

1. Andrew Winkler

[Phill Moderator PhillJoin date: February 2014Posts: 7013Downloads:40Uploads:77 Thanks: 917 Thanked: 2206 times in 1818 posts]

#564935

<em>@andrewwinkler 465920 wrote:</em><blockquote>heaps of slow queries with no joins.</blockquote>

Joins can actually slow down queries. For example.

Each JOINed table causes an additional lookup per row of the result set. So, if the WHERE clause selects 5,000 rows from table x, and lets say for instance you have 8 joins to table x, you will have 5,000 * 8 = 40,000 lookups. That’s a lot to ask from your database server. So Joins are not always the way to go.

[Andrew Winkler Friend Andrew WinklerJoin date: September 2014Posts: 725Downloads:206Uploads:31 Thanks: 291 Thanked: 34 times in 2 posts]

#564938

Are you sure, Phill Luckhurst? It’s 60GB. The database alone is 2GB. I’ve got an Akeeba backup, it’s actually quite a few files. If you like I can PM you the links.

[Phill Moderator PhillJoin date: February 2014Posts: 7013Downloads:40Uploads:77 Thanks: 917 Thanked: 2206 times in 1818 posts]

#564955

Sure. I cannot promise anything as it depends on how much time I have but hopefully I can diagnose it on my local machine.

[Andrew Winkler Friend Andrew WinklerJoin date: September 2014Posts: 725Downloads:206Uploads:31 Thanks: 291 Thanked: 34 times in 2 posts]

#565090

Great. I’m actually upgrading tonight to an even more powerful server, a 2 x Hexa rocket of a machine. Right now I’m still running on the old server, with all non-vital features like Contus HD VideoShare, EasyBlog and EasyDiscuss unpublished in the extensions manager. That way the site opens pages almost instantly, even though the server is on the other side of the world. After the upgrade to the gruntier box, I’ll reenable all the unpublished features, perform a new akeeba backup and PM you the backend superadministrator credentials. That way you can download the backup files yourself and play around with the configuration.

[Author]

Posts