$ Linux One Liners
Back to lessons

Hosting Operations

Find Long-Running MySQL Queries

MySQL is slow and you need to identify queries that have been running the longest.

Command

mysql -e "select id,user,host,db,command,time,state,left(info,80) as info from information_schema.processlist where command <> 'Sleep' order by time desc limit 10;"

What changed

Nothing changes. MySQL prints active non-sleeping sessions sorted by age.

Danger

safe

When to use it

Use during latency spikes, stuck reports, and migration incidents.

When not to use it

Do not assume every long-running query is unsafe; verify owner and purpose first.

Undo or recovery

No undo needed because this command is read-only.

Expected output

A sorted list of active MySQL sessions with runtime and query prefix.

demo script

Disposable terminal steps

  1. mysql -e "show processlist;"
  2. mysql -e "select id,user,host,db,command,time,state,left(info,80) as info from information_schema.processlist where command <> 'Sleep' order by time desc limit 10;"
  3. mysql -e "select command, count(*) from information_schema.processlist group by command;"
Terminal transcript Demo JSON

simulated output

What it looks like

disposable vessel 
::fixture-ready::
$ mysql -e "show processlist;"
Id	User	Host	db	Command	Time	State	Info
17	app	10.0.0.12	shop	Query	91	Sending data	select * from orders
18	app	10.0.0.13	shop	Sleep	20		NULL
22	report	10.0.0.30	analytics	Query	680	Copying to tmp table	select customer_id
::exit-code::0
$ mysql -e "select id,user,host,db,command,time,state,left(info,80) as info from information_schema.processlist where command <> 'Sleep' order by time desc limit 10;"
id	user	host	db	command	time	state	info
22	report	10.0.0.30	analytics	Query	680	Copying to tmp table	select customer_id, sum(total) from orders
17	app	10.0.0.12	shop	Query	91	Sending data	select * from orders join order_items
::exit-code::0
$ mysql -e "select command, count(*) from information_schema.processlist group by command;"
command	count(*)
Query	2
Sleep	1
::exit-code::0

YouTube Short

Find old MySQL queries.

When MySQL slows down, sort active sessions by runtime before you blame the entire server.

LinkedIn hook

One old query explained the whole slowdown.

Question: Do you sort MySQL sessions by runtime during slow incidents?

experiments

A/B tests to run

Metric: youtube_retention_30s

A: One old query explained the whole slowdown.

B: Sort MySQL processlist by runtime.