UBB.Developers Forums UBB.threads 7 Development How Do I? Slow queries

Hi,

I wondered if anyone has any ideas on what might help with some slow mySQL queries that we're getting.

We're on version 6.4 right now (will be upgrading), and the slowdowns don't seem to occur on any one page or doing anything one thing.

Here are a few examples:

Generated in 27.738 seconds in which 27.725 seconds were spent on a total of 6 queries. Zlib compression enabled.

Generated in 45.657 seconds in which 44.546 seconds were spent on a total of 33 queries. Zlib compression enabled.

Generated in 56.191 seconds in which 56.172 seconds were spent on a total of 11 queries. Zlib compression enabled.

I just noticed the mySQL topic about tuning mySQL, which I will look into. Although I don't know if that'd help anything like this.

I'm not that much of a techy. It's a dedicated server, so we can alter things, but please go a bit easy on me with any advice. Also anything to monitor these queries, and if that would provide more information, would be welcome.

Many thanks

>Also anything to monitor these queries, and if that would provide more information, would be welcome.

If you have phpMyAdmin you can let it shouw you all active queries. Then you will see which queries kills the server.

Thank you for the reply Anno. I do have that, yes, but I wasn't aware that you could view active queries. How does one do that?

But also, will it act as a historical record? I can look at mySQL process list and look at queries, but that only works if something is going on at that particular point. Whereas these slow queries are sporadic and random.

Thanks

There's a link in phpmyadmin to show current queries.

[]AllenAyres said:
There's a link in phpmyadmin to show current queries. [/]

Thanks. But I can only see a 'processlist' link. Is there something else?

Processlist is the right spot. It shows the query in the last column.

Thanks guy. But that's just a fancy interface for looking at a process list in an ssh prompt. What it does it just show active queries at that particular moment in time; as I said above, the slow queries are random and sporadic. To look at the process list at the exact moment a slow query is running would be near impossible.

I've since read about a mySQL option that will create a log of slow queries. Has anyone had any experience in using that to know of it's useful?

You really should work through the recommendations in the Tuning MySQL threads (and search that forum for more advice). We had the same problem on occasion, I spent some time tuning MySQL, and our forums have been flying ever since.

The problem we were experiencing was massive table locks. If you look at processlist and see a lot of table locks, you need to tune your my.cnf settings.