Details
Major
Description
We have have a multi-threaded program with multiple connections to the database. While this program is running, we often get a single child process (pid) that just hangs. This particular child process consumes 100% of any given CPU. We are trying to troubleshoot this and trying to determine which child pid maps to the SHOW PROCESSLIST Id.
With that in mind, we would like to know if there is a way to map a linux child pid that's associated with the linux parent pid (ppid) of the mysqld process to the Id shown in the output of SHOW PROCESSLIST.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
These UDFs seems interesting, especially the one to set the cgroup (if it could be used to limit resources per user/db, it would be really useful on a shared hosting environment for example).
It would be ideal to be able to add informations such as "CPU Time" or "Memory usage" of the process corresponding to the thread directly on I_S.PROCESSLIST which would be better than having to execute an external command to retrieve informations from "/proc" or use "ps".
I found no way to see ID returned by gettid() (actually syscall()) in output of ps command (its TID for MariaDB threads is equal to PID) but the number clearly belong to the same numeration no doubts (found in experimenting)
getpid() works and allow to find the victim in the ps output.
revision-id: 6811945eaf6a6e979d92739399b084bf4e00ae2d (mariadb-10.1.6-12-g6811945e)
parent(s): 86a3613d4e981c341e38291c9eeec5dc9f836fae
committer: Oleksandr Byelkin
timestamp: 2015-08-12 23:09:48 +0200
message:
MDEV-6756: map a linux pid (child pid) to a connection id shown in the output of SHOW PROCESSLIST
Added tid & pid for Linux.
—
revision-id: 7f7cbb132fa0a40687e4b25a9c9e163c93ec6d15 (mariadb-10.1.6-12-g7f7cbb1)
parent(s): 86a3613d4e981c341e38291c9eeec5dc9f836fae
committer: Oleksandr Byelkin
timestamp: 2015-09-14 12:47:57 +0200
message:
MDEV-6756: map a linux pid (child pid) to a connection id shown in the output of SHOW PROCESSLIST
Added tid (thread ID) for system where it is present.
ps -eL -o tid,pid,command
shows the thread on Linux
—
revision-id: a1f31cfa8d0f04cb166813786f7854db3dbcf715 (mariadb-10.1.6-12-ga1f31cf)
parent(s): 86a3613d4e981c341e38291c9eeec5dc9f836fae
committer: Oleksandr Byelkin
timestamp: 2015-09-15 21:50:28 +0200
message:
MDEV-6756: map a linux pid (child pid) to a connection id shown in the output of SHOW PROCESSLIST
Added tid (thread ID) for system where it is present.
ps -eL -o tid,pid,command
shows the thread on Linux
—
Following is a suggestion, provided by one of our senior developers, which could be used to accomplish this feature request.
There is a code suggestion, made by Chrisian Hammers, 2008, back for MySQL 5.0.x,
documented in MySQL feature request:
http://bugs.mysql.com/bug.php?id=33799.
However, I have a correction for the 'FIXME' line:
The PID &/or Thread IDs are easily available, but only to the parent process and child thread or process.
A) From the parent process perspective, the child PID or thread_id is only made available during thread creation:
The return code from fork(), is the PID of the child process. (Doc here http://pubs.opengroup.org/onlinepubs/007908799/xsh/fork.html)
The first parameter from the POSIX function pthread_create(). (Doc here http://pubs.opengroup.org/onlinepubs/007908799/xsh/pthread_create.html)
The sixth parameter from the Solaris function thr_create(). (Doc here http://docs.oracle.com/cd/E19253-01/816-5137/sthreads-69878/index.html)
B) From within the child Thread/Lwp (via pthread_create()), the thread_id is made available through:
The return code from POSIX function, gettid().
EX:
#include <sys/types.h> pid_t gettid(void);The return code from syscall(SYS_gettid), where SYS_gettid is an enum.
EX:
#include <sys/types.h> #include <sys/syscall.h> #define gettid() syscall(SYS_gettid);The return code from Solaris function, thr_self().
EX:
#include <thread.h> thread_t thr_self();C) From within the child process (via fork()), the PID is made available, at anytime through:
The return code from getpid().
EX:
#include <unistd.h> pid_t getpid();