Thread

Hello,

in the process of optimizing the number of queries on a slow (gs) media temple EE site (v1.6.7), I ran the SQL query analyzer, a Media Temple tool:

### 1 Query
### Total time: 27, Average time: 27
### Taking 27 seconds to complete
### Rows analyzed 402
SELECT /*!XXX SQL_NO_CACHE */ * FROM `exp_relationships`;

SELECT /*!40001 SQL_NO_CACHE */ * FROM `exp_relationships`;

Could I get some information on this, what does SQL_NO_CACHE mean?

In the course of one query analyzer run, I got 3 of these taking different times to execute.

Thanks!

Is your site actually slow as well? What does the built-in template debugger return? Any reason you’re still using 1.6.7?

The site has suffered a lot of Unable to connect to your database errors in the past week or two. We do have a little bit more traffic but it’s been slow for a while now.

Do you mean the Display Template Debugging option? If so, I don’t see problems per se, lots of SELECTS as you can imagine. I’ve been cutting the number of queries from 40-50 to 20-30 depending on the template involved.

No particular reason other than not having the time to assess all the ramifications of updating to 1.6.9 on various plugins, modules, extensions I use. For all I know so far, a bug in 1.6.7 is actually a “feature” in our current website 😉

I hope all my cleaning of templates will have positive impact but my original post about the slow query was a bit of a mystery to me, hence this conversation.

Thanks.

Are you using query caching? Try toggling that setting to see if there’s any change.

I’m not sure what SQL_NO_CACHE means either, but queries taking that long usually indicate a server issue, so this may be a question for MT. First let’s rule out EE - keep us posted.

Waiting on a reply from MT.

I tried turning Query Caching ON for a few days and that did not seem to change anything. Not faster, still got unable to connect to database errors.

deckard97, there are enough times that the unable to connect to a database is a MT only issue. If you were to change hosting to a different host such as EngineHosting, you wouldn’t run into these issues.

Have you heard any more from them?

Yes, they told me to check the MySQL Runtime information and notice the errors that are rampant there, in fact, I’m experiencing almost exactly the same issues as this poster here in the forums:
http://ellislab.com/forums/viewthread/163646/

- Handler_read_rnd
- Handler_read_rnd_next
- Created_tmp_disk_tables
Are all in the red.

From what I hear, it’s very difficult to pinpoint what can cause these errors which could be slowing our database down, so I’ll try to turn plugins/extensions OFF to see what code is “probably doing table scans, checking every single line to find what it wants instead of using indexing to help things run more efficiently”.

If anyone can point in the right direction I’d be grateful.

I see you asked in this thread about more info, and received a reply from Craig. Did you want to continue in that thread instead of this one?

Well his reply does not apply to me as our site is not close to 1000 uniques per hour. I’ll be looking into his tips though and could continue the thread on SQL errors over there.

I just wish that my original post could be answered somehow, but I understand that there are too many variables involved.

deckard97,

I tried turning Query Caching ON for a few days and that did not seem to change anything. Not faster, still got unable to connect to database errors.

Can you confirm your cache folder is writeable (777)

Clear your cache via the control panel which will flush the exp_relationships table for you.
Have you tried disabling your extensions temporarily via the large green button in the top right of the extensions page? Doing it this way keeps your extension settings when you enable them again. Do not disable individually.

I just wish that my original post could be answered somehow, but I understand that there are too many variables involved.

What exactly were the queries you were optimizing?

Take a look a this Performance Guidelines document from Solspace which has great tips in it

Yes it’s writeable, and very full of cache files. I read in the EE docs that having Query Caching ON wasn’t necessarily a good thing when the queries were cleaned up a bit, which is what I just did.

I only have 1 extension, “Simple Language” switcher from Mark Huot, I did disable it but I’d need to do it again and experiment some more which is hard because I need to put the site offline and load pages myself as an ADMIN but some break because they need the extension. And since there’s only me, the site isn’t getting hammered everywhere at once.

I’ll clear the cache tonight.

I cleaned up my templates to reduce the amount of queries, there were many exp:weblog:entries that I got rid of, finding other ways to achieve similar goals.
I also got rid of almost ALL advanced conditionals and went the way of simpler conditionals instead.

Since I’m using the Simple Language switcher extension I can’t cache templates or tags, otherwise the pages get stuck in whatever language it was last cached in for X minutes, but I managed to cache a few of them.

The site is significantly faster, most pages got from 48-56 queries down to 24-36 queries and with the few cached embeds it takes a lot of load off the MySQL.

But I’m still getting Unable to connect to database errors so I’m not out of the woods yet.

I’ll have a look at that PDF, thanks.

Sounds good. Unfortunately this does all sound like an issue with your db server or setup, something that unfortunately we can’t offer support for (and I for one know practically nothing about…)

Keep us in the loop, please.

For what it’s worth, I’ve had many of the same issues with sites hosted on MediaTemple. They’ll insist that it’s something in your setup but when I duplicate the site on a different host the problems go away. Go figure.

Thanks for letting us know, Edward. Closing this thread, but please don’t hesitate to post again in case there’s anything else.