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.