You are here
Reading other processes memory
As you probably have experienced yet MySQL does not always provide all internal information as you might want to have them and as you are used to have from other RDBMS.
MySQL plans to improve this implementing the/a performance schema and its probably already partly done in MySQL 5.4. But who knows when this will be finished and what it contains at all...
What is not provided to me I want to gather myself... But how? Other RDBMS provide interfaces to attach applications directly to their memory to retrieve information. But MySQL does not. So I was looking for a way to read an other process memory.
I have no clue about programming and thus changing MySQL code was out of focus. Further I am looking for a solution you can use immediately on a running systems at consulting gigs. Some tries to read /proc/<pid>/mem with a little php script failed.
An article by Domas M. helped me. I do not have to write something myself I can use a tool already existing to do the work. But gdb is not installed on every machine and usually not at all on production machines. Further gdb is probably an overkill to just read memory of other processes.
But an other application to do this job I did not find. I just found some comments that ptrace is the way to do it. Ptrace (man ptrace) is not a program (as for example strace) but an operating system function call.
What I did first was to trace gdb to see how it does the calls.
Then I assembled with the help of Google and the man pages a little program called read_process_memory to do the job. With this program I am capable to read any memory section of any process I have the rights to do so. For this I must be either root or the owner of the process (mostly mysql).
To compile my program the simple command was sufficient:
# gcc -g -o read_process_memory read_process_memory.c
It gives me a short (about 10 kbyte) program.
To verify if my program works as expected I always use the gdb equivalent to compare.
Examples
Let us concentrate on a simple MySQL variable and a simple MySQL status first: table_open_cache and Max_used_connections.
The usage of read_process_memory is as follows:
Usage: read_process_memory <pid> <addr> [<addr> ...]
What we need is the pid of mysqld:
# pidof mysqld
Caution: If several mysqld processes are running you have to figure out which one is the one you want.
And the addresses of the symbols we want to see. This we can get with the following command:
# nm `which mysqld` | grep -vi ' t ' | grep -i max_used_connection 08740388 b _ZL20max_used_connections
The table_open_cache is a bit more tricky to find:
# nm `which mysqld` | grep -vi ' t ' | grep -i open_cache 08743320 B open_cache gdb -p `pidof mysqld` -batch -ex "info address open_cache" -ex "print open_cache"
Open_cache seems to be something else than what we expect... So we have to have a look in the code:
sql/set_var.cc:static sys_var_long_ptr sys_table_cache_size(&vars, "table_open_cache",
Ah! It is the old name:
# nm `which mysqld` | grep -vi ' t ' | grep -i table_cache_size 08741fa0 b _ZL20sys_table_cache_size 0873e3c0 B table_cache_size
Gdb shows us that table_cache_size is the right one:
# gdb -p `pidof mysqld` -batch -ex "info address table_cache_size" \ -ex "print table_cache_size"
Now let us read the memory from mysql with our own tool:
# read_process_memory `pidof mysqld` 0x08740388 0x0873e3c0 3 64
This gives us the same results as the following MySQL commands:
SHOW GLOBAL STATUS LIKE 'max_used_connections'; SHOW GLOBAL VARIABLES LIKE 'table_open_cache'; +----------------------+-------+ | Variable_name | Value | +----------------------+-------+ | Max_used_connections | 3 | +----------------------+-------+ 1 row in set (0.00 sec) +------------------+-------+ | Variable_name | Value | +------------------+-------+ | table_open_cache | 64 | +------------------+-------+ 1 row in set (0.00 sec)
So far so good nothing spectacular yet.
There are some variables which are not displayed to world. For example srv_spin_wait_delay or skip-name-resolve.
# nm `which mysqld` | grep -vi ' t ' | grep -i srv_spin_wait_delay 08709068 D srv_spin_wait_delay # read_process_memory `pidof mysqld` 0x08709068 5 # nm `which mysqld` | grep -vi ' t ' | grep -i resolve 087419e0 b _ZL19sys_skip_networking 0874acc0 b _ZL21sys_slave_skip_errors 08741a20 b _ZL22sys_skip_show_database 0874ad80 b _ZL22sys_slave_skip_counter 087419a0 b _ZL25sys_skip_external_locking 082cc5c0 W _ZN14Stop_log_event13do_shall_skipEP14Relay_log_info 081401e0 W _ZN16Lex_input_stream11skip_binaryEi 082cc480 W _ZN18Start_log_event_v313do_shall_skipEP14Relay_log_info 08342d40 W _ZN26sys_var_slave_skip_counter10check_typeE13enum_var_type 08342d70 W _ZN26sys_var_slave_skip_counterD0Ev 08342d60 W _ZN26sys_var_slave_skip_counterD1Ev 085e4fa0 V _ZTV26sys_var_slave_skip_counter 0874d828 B my_getopt_skip_unknown 0873e32f B opt_skip_show_db 0873e338 B opt_skip_slave_start 0874a680 B slave_skip_error_names
Non of these variables matches for us. A look in the code could help?
sql/sql_acl.cc
static my_bool acl_load(THD *thd, TABLE_LIST *tables)
{
...
bool check_no_resolve= specialflag & SPECIAL_NO_RESOLVE;
The bit that I understand is that this is a local variable and I was told that those are not too simple to track. ThusI failed until now to see what this value is. If somebody has a suggestion, please let me know.
Up to here we learned:
- We can find and display VARIABLES provided to the official interface (SHOW VARIABLES).
- We can find and display STATUS information provided to the official interface (SHOW STATUS).
- We can find and display VARIABLES (hard coded) NOT provided by the official interfaces.
- We cannot find local variables.
So let us look for some NOT provided STATUS informations. For example these ones:
------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 183, seg size 185, 231571 inserts, 231571 merged recs, 5564 merges Hash table size 34679, node heap has 2 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s
When we look at the code they are named as follows:
srv/srv0srv.c
fputs("-------------------------------------\n"
"INSERT BUFFER AND ADAPTIVE HASH INDEX\n"
"-------------------------------------\n", file);
ibuf_print(file);
ha_print_info(file, btr_search_sys->hash_index);
fprintf(file, "%.2f hash searches/s, %.2f non-hash searches/s\n",
(btr_cur_n_sea - btr_cur_n_sea_old) / time_elapsed,
(btr_cur_n_non_sea - btr_cur_n_non_sea_old) / time_elapsed);
ha/ha0ha.c
fprintf(file, "Hash table size %lu", (ulong) hash_get_n_cells(table));
fprintf(file, ", used cells %lu", (ulong) cells);
if (table->heaps == NULL && table->heap != NULL) {
/* This calculation is intended for the adaptive hash
index: how many buffer frames we have reserved? */
n_bufs = UT_LIST_GET_LEN(table->heap->base) - 1;
if (table->heap->free_block) {
n_bufs++;
}
fprintf(file, ", node heap has %lu buffer(s)\n", (ulong) n_bufs);
}
So lets have a look if we can find some of them. Looking in the code gives us the following result:
hash_get_n_cells --> function cells --> local variable n_bufs --> local variable btr_cur_n_sea --> global variable btr_cur_n_sea_old --> global variable time_elapsed --> local variable btr_cur_n_non_sea --> global variable btr_cur_n_non_sea_old --> global variable # nm `which mysqld` | grep -i btr_cur_n 0874b8dc B btr_cur_n_non_sea 0874b8e4 B btr_cur_n_non_sea_old 0874b8e0 B btr_cur_n_sea 0874b8e8 B btr_cur_n_sea_old
OK so these values are still not sensational but you do not have to parse the output of SHOW ENGINE INNODB STATUS any more and we get the real base numbers an not some deltas calculated by InnoDB.
So let us see what Adaptive Hash Index variables are further around:
# nm `which mysqld` | grep -vi ' t ' | grep btr_sea 0874dc20 B btr_sea_pad1 --> fillers, out of interest 0874dc80 B btr_sea_pad2 --> fillers, out of interest 0874dc60 B btr_search_latch_temp --> pointers to somewhere 0874dc00 B btr_search_sys --> pointers to somewhere 0874b8ec B btr_search_this_is_zero --> a dummy variable to full the compiler read_process_memory `pidof mysqld` 0x0853b600 0x085496b0 0x086edfa0 0x086ee560 0x0874dc20 0x0874dc80 0x0874dc60 0x0874dc00 0x0874b8ec 0 0 -1228745880 -1228746008 0
OK nothing more of interest here...
In some other tests I found the following variables but it seems like they are not compiled in every release:
084fe050 D btr_search_n_hash_fail
#ifdef UNIV_SEARCH_PERF_STAT
btr_search_n_hash_fail++;
#endif /* UNIV_SEARCH_PERF_STAT */
Interesting keywords to investigate further: thread, srv_ my_ thr_ lock latch spin wait. If you find anything interesting it would be nice to let me know about.
To gather the data and prepare them for graphical output I have also written an other little script called read_process_memory.sh. It creates a *.csv file for you which you can easily load into your OpenOffice spread sheet.
This stuff could be also interesting for MySQL Cluster. But it looks like there the variables are named less meaningful. :(
IMPORTANT: Please keep in mind that these things described above are not really for production use. In worst case they can freeze your process (mysqld) in Trace (T) mode and you have to kill it manually and are therefore out of service for a while. In theory it should have the same impact than working with gdb but is only blocking for a very short time. I have not experienced any problems working with it. But I have not real big heavy systems to use.