Description:
Hello
While debugging a query that hangs for 5000s without doing anything (reported in different bug report) I thought that it would be usefull if I could trace a single mysql thread with "strace" or a debugger like "gdb".
Maybe this could be interesting for your supporters, too?
Under Linux mysql usually shows up as just one process in "ps".
When using "ps ax -L" one gets at least one entry per mysql thread which can give hints about memory and cpu usage of a specific thread.
Currently I'm not aware of a way to correlate the linux NPTL thread pid to a MySQL query so it would be nice if the thread pid showed up in "SHOW PROCESSLIST".
The attached four liner does this. Sadly it is highly unportable as it directly
accesses a private memory structure :-) But I'm not a C programmer so maybe you
guys know how to do it more elegant.
bye,
-christian-
How to repeat:
With the patched version:
mysql> SHOW PROCESSLIST;
+----+------+-----------+------+---------+------+-------+------------------+-----------+
| Id | User | Host | db | Command | Time | State | Info | PThreadId |
+----+------+-----------+------+---------+------+-------+------------------+-----------+
| 5 | root | localhost | NULL | Query | 0 | NULL | SHOW PROCESSLIST | 17232 |
+----+------+-----------+------+---------+------+-------+------------------+-----------+
1 row in set (0.00 sec)
Now you can lookup this thread with "ps":
chammers@sys-251:/home/ch$ ps aux -L | grep 17232
mysql 17119 17232 0.0 10 0.6 99552 13632 pts/5 Sl+ 16:15 0:00 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/var --user=mysql --pid-file=/usr/local/mysql/var/sys-251.pid --skip-external-locking
You see memory usage and could attach with "strace -p" to see what it does!
Suggested fix:
sys-251:/home/ch/stuff/mysql# diff -u mysql-5.0.51/sql/sql_show.cc mysql-5.0.51-patched/sql/sql_show.cc
--- mysql-5.0.51/sql/sql_show.cc 2007-11-15 15:06:35.000000000 +0100
+++ mysql-5.0.51-patched/sql/sql_show.cc 2008-01-10 16:06:25.000000000 +0100
@@ -1290,6 +1290,7 @@
uint command;
const char *user,*host,*db,*proc_info,*state_info;
char *query;
+ unsigned long int posix_thread_id;
};
#ifdef HAVE_EXPLICIT_TEMPLATE_INSTANTIATION
@@ -1317,6 +1318,7 @@
field->maybe_null=1;
field_list.push_back(field=new Item_empty_string("Info",max_query_length));
field->maybe_null=1;
+ field_list.push_back(new Item_int("PThreadId", 0, MY_INT32_NUM_DECIMAL_DIGITS));
if (protocol->send_fields(&field_list,
Protocol::SEND_NUM_ROWS | Protocol::SEND_EOF))
DBUG_VOID_RETURN;
@@ -1336,6 +1338,7 @@
thread_info *thd_info= new thread_info;
thd_info->thread_id=tmp->thread_id;
+ thd_info->posix_thread_id = *((unsigned int *)(tmp->real_id + 72)); // FIXME this is from glibc-2.6.1/nptl/descr.h
thd_info->user= thd->strdup(tmp_sctx->user ? tmp_sctx->user :
(tmp->system_thread ?
"system user" : "unauthenticated user"));
@@ -1418,6 +1421,7 @@
protocol->store_null();
protocol->store(thd_info->state_info, system_charset_info);
protocol->store(thd_info->query, system_charset_info);
+ protocol->store((ulonglong) thd_info->posix_thread_id);
if (protocol->write())
break; /* purecov: inspected */
}
Description: Hello While debugging a query that hangs for 5000s without doing anything (reported in different bug report) I thought that it would be usefull if I could trace a single mysql thread with "strace" or a debugger like "gdb". Maybe this could be interesting for your supporters, too? Under Linux mysql usually shows up as just one process in "ps". When using "ps ax -L" one gets at least one entry per mysql thread which can give hints about memory and cpu usage of a specific thread. Currently I'm not aware of a way to correlate the linux NPTL thread pid to a MySQL query so it would be nice if the thread pid showed up in "SHOW PROCESSLIST". The attached four liner does this. Sadly it is highly unportable as it directly accesses a private memory structure :-) But I'm not a C programmer so maybe you guys know how to do it more elegant. bye, -christian- How to repeat: With the patched version: mysql> SHOW PROCESSLIST; +----+------+-----------+------+---------+------+-------+------------------+-----------+ | Id | User | Host | db | Command | Time | State | Info | PThreadId | +----+------+-----------+------+---------+------+-------+------------------+-----------+ | 5 | root | localhost | NULL | Query | 0 | NULL | SHOW PROCESSLIST | 17232 | +----+------+-----------+------+---------+------+-------+------------------+-----------+ 1 row in set (0.00 sec) Now you can lookup this thread with "ps": chammers@sys-251:/home/ch$ ps aux -L | grep 17232 mysql 17119 17232 0.0 10 0.6 99552 13632 pts/5 Sl+ 16:15 0:00 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/var --user=mysql --pid-file=/usr/local/mysql/var/sys-251.pid --skip-external-locking You see memory usage and could attach with "strace -p" to see what it does! Suggested fix: sys-251:/home/ch/stuff/mysql# diff -u mysql-5.0.51/sql/sql_show.cc mysql-5.0.51-patched/sql/sql_show.cc --- mysql-5.0.51/sql/sql_show.cc 2007-11-15 15:06:35.000000000 +0100 +++ mysql-5.0.51-patched/sql/sql_show.cc 2008-01-10 16:06:25.000000000 +0100 @@ -1290,6 +1290,7 @@ uint command; const char *user,*host,*db,*proc_info,*state_info; char *query; + unsigned long int posix_thread_id; }; #ifdef HAVE_EXPLICIT_TEMPLATE_INSTANTIATION @@ -1317,6 +1318,7 @@ field->maybe_null=1; field_list.push_back(field=new Item_empty_string("Info",max_query_length)); field->maybe_null=1; + field_list.push_back(new Item_int("PThreadId", 0, MY_INT32_NUM_DECIMAL_DIGITS)); if (protocol->send_fields(&field_list, Protocol::SEND_NUM_ROWS | Protocol::SEND_EOF)) DBUG_VOID_RETURN; @@ -1336,6 +1338,7 @@ thread_info *thd_info= new thread_info; thd_info->thread_id=tmp->thread_id; + thd_info->posix_thread_id = *((unsigned int *)(tmp->real_id + 72)); // FIXME this is from glibc-2.6.1/nptl/descr.h thd_info->user= thd->strdup(tmp_sctx->user ? tmp_sctx->user : (tmp->system_thread ? "system user" : "unauthenticated user")); @@ -1418,6 +1421,7 @@ protocol->store_null(); protocol->store(thd_info->state_info, system_charset_info); protocol->store(thd_info->query, system_charset_info); + protocol->store((ulonglong) thd_info->posix_thread_id); if (protocol->write()) break; /* purecov: inspected */ }