Produce a table with the IDs needed to kill processes

[busboy]

I found this query online as a way to show all processes that need to be killed. I tweaked it to reflect my table names and ran it, but it produced nothing. This despite over 70 connections right now are preventing any more queries from being executed.

SELECT CONCAT('KILL ',id,';') AS Kill_List FROM information_schema.processlist WHERE user='recordau_admin' AND info = 'SELECT * FROM processlist';

What am I doing wrong with the above query? I guess a better question is why am I having this problem while executing queries from my iMac.

Thanks!

[m_hutley]

@busboy

AND info = ‘SELECT * FROM processlist’;

in every one of the records you show in that screenshot, info is NULL. So none of them match the criteria in your query.

[busboy]

Doesn’t SELECT * normally return all relevant rows despite if certain columns are NULL? Please advise, as I’m not following you very well.

Thank you.

[Mittineague]

I usually write my queries more verbose than they need to be, but even rewritten that query looks wonky to me.

SELECT CONCAT('KILL ', information_schema.processlist.id, ';') AS Kill_List 
FROM information_schema.processlist 
WHERE information_schema.processlist.user='recordau_admin' 
AND information_schema.processlist.info = 'SELECT * FROM information_schema.processlist'; 

I’m not seeing how the field “info” could have a value equal to a result set.

What is your criteria for determining which processes should be “killed” and which should not?

[m_hutley]

@busboy

info = ‘SELECT * FROM processlist’;

As far as this goes, this is equivilant to saying

info = “Im a smelly cheese head”

or

info = “run C:\warkwark\thing.exe”

A string is a string, regardless of if it could be evaluated as something else, and as a WHERE condition, it acts like any other string.

What he’s trying to do with that query as written is inspect the currently connected clients and see if any of them is running that particular query against the database (which is what i assume info gets filled in with while a client is executing a query). Whether that’s what he WANTS to do, is a different question.

[busboy]

Here is the webpage that I found the query on:

How to Kill All MySQL Processes For a Specific User | DBA Diaries

What I’m trying to do is nuke any processes that are building up. Because when there is about 50 of these, I am prevented from running any more queries in my database client app.

Why do these processes usually only become a problem when I’m running queries myself? It doesn’t seem to be an issue from day to day on my website front end, with hundreds of people logging in and conducting keyword searches all the time.

Thank you.

[Mittineague]

@busboy

Why do these processes usually only become a problem when I’m running queries myself?

Might these queries be extremely inefficient and resource intensive? For example, a complex JOIN query that does full table scans of multiple large tables could be taking a long time to run.

Can you post an example of a query you are “running yourself”?

[busboy]

These are the two I run back to back, over and over, but with different words instead of just plaque.

select * from topics where topic like ‘%plaque%’;

update searches set topicID = 2077 where topicID is null and keyword = ‘plaque’;

The update query normally updates several hundred rows.

[Mittineague]

Leading wildcard matches can be expensive, but I’m assuming there is no easy way to get around that. Do you really need “*” everything? If you only need the id from the table SELECTing that only could help, especially if it’s indexed.

You may be able to save some with some type of subquery so that rows that have already had the keyword found aren’t included?

[busboy]

Here is a typical result for a keyword search from the first query. I didn’t think it was that resource intense, considering that it doesn’t return very many columns. But maybe I’m wrong.

[Mittineague]

I wasn’t thinking in terms of how many rows were returned, but how much work was involved to get those rows.

Please run an EXPLAIN query and post what it returns

EXPLAIN SELECT * 
FROM topics 
WHERE topic LIKE '%plaque%' 

[busboy]

Here you go:

[busboy]

@Mittineague? Are you still around?

[Mittineague]

Sorry. I had this topic bookmarked so I could get back to it but got sidetracked with New Years stuff.

I don’t see any time (as in how long it took to run) in the screenshot but the null “keys” can’t be helping any. Are all the “topic” fields (eg. the one with “arterial-plaque” unique? Maybe after being sure to save a backup you could try

CREATE INDEX {some name} ON topics (topic) 

Then to use it query like

SELECT * FROM topics 
USE INDEX ({some name}) 
WHERE topic LIKE '%plaque%' 

* where “{some name}” is some name that makes sense to you eg. topic_index
Check the times to see if it helps.