Sometimes my customers (especially the ones who are used to different database servers,) ask me about the CPU and memory consumption of a MySQL server process.
Initially I thought that this kind of requests were rather odd, mostly because the whole box was dedicated to the database, thus measuring the global usage of CPU and memory could give a rough estimate of how well the database server is performing.
With time, I came to appreciate the request, and I started digging into the operating system intricacies to find out as much as I could about the resources used by a single process. This information can be useful if there are different services running in the same machine, and it could be of paramount importance to find out if your DBMS process can exceed the operating system resources.

My measurements are referred to the GNU/Linux operating system. Other systems have different ways of gathering the same data.

Finding the resources

Most of the information we need comes from the ps command. Just run it, specifying in the command line which fields want, and grep the listing for commands containing “mysqld”.


$ ps -o pcpu,pmem,vsz,rss,dsiz,command
%CPU %MEM    VSZ   RSS DSIZ COMMAND
...
 0.0  0.1   4196  1720   3571 /bin/sh ./bin/mysqld_safe
 5.3  7.3 620476 76604 611912 /usr/local/mysql/libexec/mysqld
 0.5  7.3 620476 76604 611912 /usr/local/mysql/libexec/mysqld
 1.2  7.3 620476 76604 611912 /usr/local/mysql/libexec/mysqld
 4.1  7.3 620476 76604 611912 /usr/local/mysql/libexec/mysqld
...

The first column says how much CPU was used by every process. Threads are seen as processes in this list, so we need to make some additions to know how much was used globally by the DBMS.
The columns shown in the above listing must be interpreted as follows:

%CPU
The percentage of CPU used by the process since it was started. Thus, 7.4 means that this process was using CPU for 7.4 of its running time.
%MEM
It's the percentage of the system Memory used by this process. Since those processes are really threads, the memory is shared among them. (This is not true if you are running several servers in the same box, but we will come to it later).
%VSZ
The Virtual Size of your process, i.e. the amount of memory that your process may grow up to, not the actual used memory.
%RSS
The Resident Set Size is the memory that is currently allocated by the process.
%DSIZ
The Data Size is the amount of memory used for data allocation. It is included in the VSZ.

It's almost all we need, except for the detail already mentioned, that if we have several MySQL servers running in the same machine, we won't be able to find out exactly which is consuming what. To achieve a higher degree of precision, we can find out the process ID (PID) of each running server, and measure its consumption appropriately.
The method takes a few steps. First, we need to find out in which file is the PID stored, and the server will provide this information for us:


mysql> show variables like '%pid%';
+---------------+---------------------------------------+
| Variable_name | Value                                 |
+---------------+---------------------------------------+
| pid_file      | /usr/local/mysql/data/datacharmer.pid |
+---------------+---------------------------------------+

Here datacharmer is my box host-name, and the file is stored in the data directory. You can read it either as root or as the user who is running the mysqld process.


$ cat /usr/local/mysql/data/datacharmer.pid
5854

What can you do with this piece of info? It is the PID used by mysqld, but it tells you none about its brothers under the same mysqld_safe. But you can use it to find more about its family.
The Linux operating system tells information about every process in the /proc directory. We will find a /proc/5854 directory, containing several files and directory. You can find information about the exact command used to start the process, its environment, and the files that are currently open. For example, 


$ ls -l /proc/5854/fd
total 26
lr-x------ 1 mysql mysql 64 2006-07-05 14:58 0 -> /dev/null
l-wx------ 1 mysql mysql 64 2006-07-05 14:58 1 -> /usr/local/mysql/data/datacharmer.err
lrwx------ 1 mysql mysql 64 2006-07-05 14:58 10 -> /usr/local/mysql/data/ib_logfile1
lrwx------ 1 mysql mysql 64 2006-07-05 14:58 11 -> socket:[16303]
lrwx------ 1 mysql mysql 64 2006-07-05 14:58 12 -> /tmp/ib7menKQ (deleted)
lrwx------ 1 mysql mysql 64 2006-07-05 14:58 13 -> socket:[16304]
lrwx------ 1 mysql mysql 64 2006-07-05 14:58 14 -> /usr/local/mysql/data/mysql/host.MYI
lrwx------ 1 mysql mysql 64 2006-07-05 14:58 15 -> /usr/local/mysql/data/mysql/host.MYD
lrwx------ 1 mysql mysql 64 2006-07-05 14:58 16 -> /usr/local/mysql/data/mysql/user.MYI
lrwx------ 1 mysql mysql 64 2006-07-05 14:58 17 -> /usr/local/mysql/data/mysql/user.MYD
lrwx------ 1 mysql mysql 64 2006-07-05 14:58 18 -> /usr/local/mysql/data/mysql/db.MYI
lrwx------ 1 mysql mysql 64 2006-07-05 14:58 19 -> /usr/local/mysql/data/mysql/db.MYD
l-wx------ 1 mysql mysql 64 2006-07-05 14:58 2 -> /usr/local/mysql/data/datacharmer.err
lrwx------ 1 mysql mysql 64 2006-07-05 14:58 20 -> /usr/local/mysql/data/mysql/tables_priv.MYI
lrwx------ 1 mysql mysql 64 2006-07-05 14:58 21 -> /usr/local/mysql/data/mysql/tables_priv.MYD
lrwx------ 1 mysql mysql 64 2006-07-05 14:58 22 -> /usr/local/mysql/data/mysql/columns_priv.MYI
lrwx------ 1 mysql mysql 64 2006-07-05 14:58 23 -> /usr/local/mysql/data/mysql/columns_priv.MYD
lrwx------ 1 mysql mysql 64 2006-07-05 14:58 24 -> /usr/local/mysql/data/mysql/procs_priv.MYI
lrwx------ 1 mysql mysql 64 2006-07-05 14:58 25 -> /usr/local/mysql/data/mysql/procs_priv.MYD
l-wx------ 1 mysql mysql 64 2006-07-05 14:58 3 -> /usr/local/mysql/data/general.log
lrwx------ 1 mysql mysql 64 2006-07-05 14:58 4 -> /usr/local/mysql/data/ibdata1
lrwx------ 1 mysql mysql 64 2006-07-05 14:58 5 -> /tmp/ib7YLb7U (deleted)
lrwx------ 1 mysql mysql 64 2006-07-05 14:58 6 -> /tmp/ibu4new9 (deleted)
lrwx------ 1 mysql mysql 64 2006-07-05 14:58 7 -> /tmp/ibwMbhVn (deleted)
lrwx------ 1 mysql mysql 64 2006-07-05 14:58 8 -> /tmp/ibaG8tkC (deleted)
lrwx------ 1 mysql mysql 64 2006-07-05 14:58 9 -> /usr/local/mysql/data/ib_logfile0

You will recognize some files in the mysql database, which the server reads at startup to build the grant tables.
Mostly, though, what you want to do is a simple ls /proc/5854/fd | wc -l, to get the number of files open.

But the file we want to query now is /proc/5854/status.


$ cat /proc/5854/status
Name:   mysqld
State:  S (sleeping)
SleepAVG:       88%
Tgid:   5854
Pid:    5854
PPid:   5832
TracerPid:      0
Uid:    1001    1001    1001    1001
Gid:    1001    1001    1001    1001
FDSize: 256
Groups: 1001
VmPeak:   620536 kB
VmSize:   620476 kB
VmLck:         0 kB
VmHWM:     76604 kB
VmRSS:     76604 kB
VmData:   611912 kB
VmStk:        88 kB
VmExe:      5500 kB
VmLib:      1788 kB
VmPTE:       136 kB
Threads:        10
SigQ:   0/4294967295
SigPnd: 0000000000000000
ShdPnd: 0000000000000000
SigBlk: 0000000000087007
SigIgn: 0000000000001006
SigCgt: 00000001800066e9
CapInh: 0000000000000000
CapPrm: 0000000000000000
CapEff: 0000000000000000

There is a wealth of information about this process, some of which we have already seen in the output of ps. You will recognize the virtual memory size, the resident set size, and the data size. For the explanation of the other fields, you can run man proc, or read the excellent Optimizing Linux Performance by Phillip Ezolt1.
For now, it's enough to note that there are a Uid and Gid fields, telling you the owner of this process (user and group ID, which you can convert to a name by inspecting /etc/passwd and /etc/group), and there is also a PPid field, which is the field of the parent, i.e. the process that started this one.
Let's inspect this parent, then:


$ cat /proc/5832/status
Name:   mysqld_safe
State:  S (sleeping)
SleepAVG:       0%
Tgid:   5832
Pid:    5832
PPid:   1
 ...
(more rows clipped off)

Good! The parent ID brings us to mysqld_safe. Notice that it's PPid is 1, meaning that it was started by init, the father of all processes. Now, if we want to see all its children, the easiest method is using the pstree command:


$ pstree -p 5832
mysqld_safe(5832)---mysqld(5854)-+-{mysqld}(5855)
                                 +-{mysqld}(5856)
                                 +-{mysqld}(5857)
                                 +-{mysqld}(5858)
                                 +-{mysqld}(5860)
                                 +-{mysqld}(5861)
                                 +-{mysqld}(5862)
                                 +-{mysqld}(5863)
                                 `-{mysqld}(5864)

In a recent release of Linux, the pstree command will show the threads in braces. Older ones will not show the distinction. At this point, we should have all the information we need to measure exactly how much our DBMS is eating up. We just need to repeat the measurement for each PID shown by pstree, get the average of the CPU usage, and we are done.

A quick, albeit not very readable way, could be:


ps -o pcpu,pmem,vsz,rss,dsiz,command `pstree -p 5382 | perl -0ne 'print "@{[/(d+)/g]}n"'`

The lazy way

But searching recursively for the mysqld_safe PID sounds like a lot of work, doesn't it? Well, yes. That's why I created a little utility that does it all for you, producing a neat report of all the info we need.


$ mysqlresources
    The MySQL Resource Locator,  version 1.0
    (C) 2006 Giuseppe Maxia, Stardata s.r.l.

    hostname         : datacharmer
    mysql_pid        :       5854
    pid_file         : /usr/local/mysql/data/datacharmer.pid
    mysqld_safe      :       5832
    open_files       :         26

    user
    ----
        name         :       mysql
        UID          :       1001

    mysql_load
    ----------
        avg_perc_cpu :       2.75
        perc_memory  :       7.40
        vmem_size    :     620476
        res_set_size :      76604
        data_size    :     614979
        pids         :         11
        active_pids  :          4

    server_load
    -----------
        cpu5         :       0.19
        cpu10        :       0.08
        cpu15        :       0.08

mysqlresources is a Perl program that does everything was said so far, and more2. You can get a copy from datacharmer.org or just browse the source code.

You can get the result in the simple text format shown above, or you can ask for a Perl structure, which is easier to parse if you need to process the result and show it in your application or store it in a database.


$ mysqlresources -f perl
$INFO = {
          'open_files' => 26,
          'server_load' => {
                             'cpu10' => '0.19',
                             'cpu15' => '0.08',
                             'cpu5' => '0.08'
                           },
          'pid_file' => '/usr/local/mysql/data/datacharmer.pid',
          'mysqld_pid' => '5854',
          'mysqld_safe' => '5382',
          'user' => {
                      'UID' => 1001,
                      'name' => 'mysql'
                    },
          'hostname' => 'datacharmer',
          'mysql_load' => {
                            'data_size' => '614979',
                            'perc_memory' => '7.4',
                            'res_set_size' => '76524',
                            'avg_perc_cpu' => 2.75,
                            'vmem_size' => '620476',
                            'active_pids' => 4,
                            'pids' => 11
                          }
        };

It's easy in Perl to add new output formats, but you need to install modules for that. In most of the server that I administer for my customers, I am just lucky when I get Perl itself in the first place, so it's better to stick to a minimalistic approach. A Perl geek, OTOH, will be able to get a XML or YAML report in just a few strokes:


$ mysqlresources -f perl | perl -MXML::Simple -0ne 'eval $_; print XMLout $INFO, AttrIndent=>1'
<opt hostname="datacharmer"
     mysqld_pid="5854"
     mysqld_safe="5382"
     open_files="26"
     pid_file="/usr/local/mysql/data/datacharmer.pid">
  <mysql_load active_pids="4"
              avg_perc_cpu="2.75"
              data_size="614979"
              perc_memory="7.4"
              pids="11"
              res_set_size="76524"
              vmem_size="620476" />
  <server_load cpu10="0.08"
               cpu15="0.08"
               cpu5="0.19" />
  <user name="mysql"
        UID="1001" />
</opt>


$ ./mysqlresources -f perl | perl -MYAML -0ne 'eval $_; print Dump $INFO'
---
hostname: datacharmer
mysql_load:
  active_pids: 4
  avg_perc_cpu: 2.75
  data_size: 614979
  perc_memory: 7.4
  pids: 11
  res_set_size: 76524
  vmem_size: 620476
mysqld_pid: 5854
mysqld_safe: 5382
open_files: 26
pid_file: /usr/local/mysql/data/datacharmer.pid
server_load:
  cpu10: 0.19
  cpu15: 0.08
  cpu5: 0.08
user:
  UID: 1001
  name: mysql

For more options, run mysqlresources --help, and see what else you can do.

1 This book was recommended by Jay Pipes during a presentation on performance tuning at the latest MySQL Users Conference. After reading it, I must say that it's really worth all the praise it has got so far.

2 Run the program with DEBUG=2 mysqlresources and you'll see for yourself all the commands that are issued.