MySQL Bugs: #24626: show processlist should display "pid" for each entry

Bug #24626	show processlist should display "pid" for each entry
Submitted:	27 Nov 2006 17:02	Modified:	21 Mar 2011 4:08
Reporter:	Roberto Spadim (Basic Quality Contributor)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	all	OS:	Any (all)
Assigned to:		CPU Architecture:	Any
Tags:	show processlist

Description:
could show processlist show PID ?

How to repeat:
show processlist
return
ID,user,host,db,command,time,state,info

could be

ID,user,host,db,command,time,state,info,PID

Suggested fix:
add pid info

Thank you for a reasonable feature request.

I'm sorry, I don't see how this can be done.
Connection != thread, i.e. starting from 6.0 with thread-pool option, each connection runs on different threads. Secondly, there a thread can not be identified by PID (process id) on all systems -- so the format of the variable will have to be system-specific. On most systems it's impossible to change priority of a single thread of an application.

if a O.S. can't display what PID or what Thread is running no problem, display NULL
a big problem is 
i have two or three process running a dead lock, or a big table update with delete and select queries, how could i stop it? 
stop database or stop program
if program don't run in same machine that database, stop database is the only solution (we shouldn't kill process, ok, we can kill queries), but another is renice process making it work faster
i know that windows and other O.S. don't display the thread that's running a process of mysql
but on *nux we could for example show the pid that started process, or the pid that's running process, on others o.s. we could display NULL
could we implement this? for example:

SHOW FULL PROCESSLIST
could display it and:

SHOW PROCESSLIST
don't

it's a way to optimize single queries on big database, or small computers
got the problem? we don't know the "real" PID, but the most CPU intensive PID
we could renice a PID and after renice back

SHOW PROCESSLIST displays connection id. You can pass it to KILL. Not being able to connect in case of a full deadlock is a separate problem -- there is a separate feature request for it.

Bug#33799 "SHOW PROCESSLIST" should show thread PID like in "ps ax -L" under Linux was marked a duplicate of this bug.

Separate feature request to be able to connect to a deadlocked server:
Bug#5309 "MySQL should reserve a connection for the admin (root) user"

it's not a problem on database
if a application have a dead lock, not database
pid of server could be good to renice it to make cpu more intensive on mysql process that's the main idea

I'm sorry, then I don't understand the request. 
Basically, you would like to have a way to select process id of the entire server, not thread ids of connection threads?

Note, that "re-nicing" the current connection thread won't bring the desired effect:
1) thread != connection, already in 6.0 with thread pool and even more so long term
2) There are auxiliary threads.
3) Storage engine has own threading system.

On the other hand it's a weak argument why we should not expose this information.
I just disagree that processlist is the right place to expose it.
Perhaps there should indeed be a way to list all threads in the system, with their OS ids (physical thread ids).

Putting back to "To be fixed later".

My argument for why mysqld Thread_id and the client process_id should be reported in the 'SHOW FULL PROCESSLIST':

The ultimate need is two part:
A) To identify which client program a DB operation is associated too.
B) To identify which OS-level thread/process a DB operation is associated too.

Here is the scenario:

We have a busy server with 1500+ active connections (plus a few 100 in-active).
Randomly every month or week, we get a 'slow-mode' state of execution where the mysqld will effectively reduces processing rates from 100k/sec to barely beyond 1-2 records a second. This affects all DML/DDL operations.

** The only symptom/clue (from the OS command prompt) is that a SINGLE mysqld lightweight thread starts running at 100% of a core. **

All other mysqld processing threads become nearly idle ( < 1% busy), several with extremely high lock counts. All of the client programs start sleeping, waiting for response from the database. The tool we are using to find that one clue is 'prstat -mL'. The 1500+ connections reported by 'SHOW PROCESS LIST' still appear be changing status, but just at an exceptionally slow rate. There is not a single entry that reports a status which cannot be accounted for, given the current work-load on the database. All the active applications had been executed as sometime prior for other data-sets on the same database within the same week, which eliminates a rare program execution.

We have resorted to adding 'DB thread_id' to our in-house processes logs and injecting 'OS process_id' in the comments of SQL queries. So that we can partially correlate our programs to active DB queries on the 'SHOW PROCESS LIST'. Unfortunately only SELECT operations will report comments in the show process list. The DML (data modify language) operations INSERT,UPDATE,DELETE,REPLACE,ALTER operations do not report comments in the show process list. Our suspect is one of the 500+ connections performing a DML operation, that is NOT report any text in the show process list.

Our server has 48-cores, 256G RAM, Solaris 10, running MySQL 5.1.49sp1 database.

hum, and could we have it?
for example
KILL query_id ~= kill process_id (*nix like)
RENICE query_id PRIORITY LEVEL ~= renice -n XXX process_id (*nix like)

don´t need a PID, but at SQL command to tune some queries/connections (maybe i/o rate in future)
for example a realtime applications must have a very high database priority, we could set this by user, by connection, by query

this could be done? diferent from the first post idea (only at o.s. level), the PID values is nice if we have kernel errors or another log we could know what query was running

i will close bug: http://bugs.mysql.com/bug.php?id=28194

this is a feature request that could help the bug 28194 using only SQL language, no o.s. command