Are you monitoring RSS & VSZ?

Monitoring MySQL Memory is a rather critical task because you can’t limit MySQL’s usage of physical memory resources. Improperly configured servers running MySQL can crash because you don’t understand memory usage.

MySQL uses memory in a number of different ways. Using the Oracle analogy, you can divide the mysqld memory usage into main areas of:

  • SGA – System Global Area
  • PGA – Process Global Area

The SGA is the footprint that MySQL uses for startup. This is attributed to the base footprint of the mysqld process and a number of buffers including:

NOTE: This is for a default MySQL 5.1 install. Other storage engines and/or other versions of MySQL may have additional buffers. Falcon for example in MySQL 6.x has additional buffers.

The PGA is more complex, and the cause of problems for the possible occurrence of your server running out of memory and needing to swap. The goal of monitoring memory usage is to of course avoid this.
This additional memory is a combination of a few areas including:

  • MEMORY tables
  • Connection management (such as thread_cache and table_cache)
  • Per Connection memory usage

The later is the cause of greatest concern, especially for environments that have persistent connections. Per Connection memory usage is a combination of many buffers including the thread_stack, 2 x net_buffer_length (to max_allowed_packet), read_buffer_size, read_rnd_buffer_size, join_buffer_size, sort_buffer_size, and up to min(max_heap_table_size,tmp_table_size). In fact, for example with temporary tables, a query may use multiple temporary tables.

Memory on a per connection basis is kept until the connection is closed. In some instances such as next_buffer_size, this is apparently reduced aftter each SQL Statement result. With a persistent connection model (e.g. Java), ensuring idle connections drop to a low watermark is a valuable task. The confusing part is MySQL instrumentation does not tell you exactly how much is used, and it’s impossible to calculate with available provided data.

As part of monitoring your server, you should monitor the size of the mysqld memory usage, because this will cause you to be proactive rather then reactive to scarce memory resources. You can easily get this using the ps command. For example:

$ps -eopid,fname,rss,vsz,user,command | grep -e "RSS" -e "mysql"
 5463 grep       764   5204 ronald   grep -e RSS -e mysql
13894 mysqld_s   596   3936 root     /bin/sh /usr/bin/mysqld_safe
13933 mysqld   4787812 5127208 mysql /usr/sbin/mysqld --basedir=/usr --datadir=/vol/mysql/mysqldata --user=mysql --pid-file=/var/run/mysqld/ --skip-external-locking --port=3306 --socket=/var/run/mysqld/mysqld.sock
13934 logger     608   3840 root     logger -p daemon.err -t mysqld_safe -i -t mysqld
$ ps -eopid,fname,rss,vsz,user,command | grep " mysqld " | grep -v grep | awk '{print $3,$4}'
4787820 5127208

From man ps
rss RSS resident set size, the non-swapped physical memory that a task has used (in kiloBytes). (alias rssize, rsz).
vsz VSZ virtual memory size of the process in KiB (1024-byte units). Device mappings are currently excluded; this is subject to change.
(alias vsize).

The motto of the story, don’t just monitor the free memory of your system, for a database server, you need to closely monitor the primary process on the server, that is mysqld.


How MySQL Uses Memory

More Information

Join me for my MySQL User Conference talk on “Monitoring 101 – Simple stuff to save your bacon”.

I also cover monitoring MySQL in my “MySQL Essentials” training course. For more information visit MySQL Education.


  1. Mark Callaghan says

    This has been a problem for me at times. I added code to export these in SHOW STATUS output. Unfortunately, getting this data is very non-standard. I use Linux and parse files in /proc/. But even limiting this to Linux, I am not sure how portable my code is.

  2. Ronald Bradford says

    ps -eopid,fname,rss,vsz,user,command | grep ” mysqld ” | grep -v grep | awk ‘{printf(“RSS=%.1fG, VSZ=%.1fGn”,$3/1024/1024,$4/1024/1024)}’
    RSS=7.2G, VSZ=7.8G

  3. says

    Yes, we are monitoring memory usage. We trend it and observe as it gradually increases over time; this increase appears to slow down. After several months we get too close to the address space limit, or the AS gets too fragmented and we have to restart the server.

    This will be mitigated by using a 64-bit OS; however, we still don’t really have a clue what causes the blips in the graph. MySQL doesn’t provide much to monitor things internally.

    We were always taught “applications never release memory to the OS”, however, looking at our address-space graph, this is definitely wrong – although the overall trend is up, it often goes down. Sometimes it steps up then falls back to its previous level after an hour or so. We don’t know why.

  4. says

    ps -eopid,fname,rss,vsz,user,command | grep “apache” | grep -v grep | awk ‘BEGIN{rss=0.0;vsz=0.0}{rss+=$3; vsz+=$4}END{printf(“RSS=%.1fG, VSZ=%.1fGn”,rss/1024/1024,vsz/1024/1024)}’