query hangs

[Hexweb]

Hi all,

One of my users has a few queries in his php script that are freezing.

And keeps running so if i dont kill them at night they are running for a few hours.

Is there a way to auto kill queries that are freezing?

I cant suspend the user at this time so that is a problem..

Thanks!

 

[zEitEr]

Hello,

Do you mean queries to MySQL DB from a PHP script? If yes, then check running queries in MySQL to see details. And/or enable logging of slow quieries in MySQL.

 

[Hexweb]

Heey Alex,

Yes the queries that are hanging are coming from a PHP script.

when i use mytop / mysql show processlist;
i see the user/query and running time of the querie.

At the moment when there is a query that's hanging

i'm using

show processlist;

too show the id's and then kill them because they won't finish. (The database it's using is around 280mb's)

With enable slow queries it's only logging them to trackback where the issue is right?

 

[zEitEr]

What is the status of those SQL queries: sleeping, running, waiting? What they do: select? update? delete?

 

[Hexweb]

The query is running.

| 163648 | USERNAME | localhost | DATABASE | Query | 2689 | Copying to tmp table | select b.maten as maten , b.picture_big as picture, b.picture_small as picture2 from brand_color a RIGHT JOIN content b on (a.name = b.maten) where a.name <> '' group by a.name order by a.name asc |

that is the query that is hanging al the time.

 

[zEitEr]

I guess you need to tune options related to tmp tables, size of join buffers in /etc/my.cnf, and I'd suggest that you mount /tmp as tmpfs