Identifying resource bottlenecks – Memory

Continuing on from CPU, we turn our attention to Memory. One of the first steps when addressing a MySQL performance tuning problem is to perform a system audit of the physical hardware resources, then identify any obvious bottlenecks in these resources.

In auditing, I start with the ‘free’ command, the already used ‘vmstat’ command, the /proc/meminfo and /proc/sys/vm/swappiness files to get an indication of memory and swap resources. While we are looking at the Memory, the configuration of Swap is also very important. I will discuss this in more detail later.

$ free -m
             total       used       free     shared    buffers     cached
Mem:          3955       3838        117          0        402       2366
-/+ buffers/cache:       1069       2886
Swap:         1027          0       1027
$ vmstat 1
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 0  0    128 234088 409632 2474372    0    0     0     0 1081  198  0  0 100  0  0
 0  0    128 234088 409632 2474396    0    0     0     0 1003   59  0  0 100  0  0
 0  0    128 234088 409636 2474392    0    0     0   100 1085  209  0  0 100  0  0
 0  0    128 233836 409636 2474396    0    0     0     0 1014  184  3  0 97  0  0
 0  0    128 233284 409636 2474396    0    0     0     0 1182  435  2  0 98  0  0
 0  0    128 233176 409636 2474396    0    0     0     0 1024  104  1  0 99  0  0
 0  0    128 233176 409636 2474396    0    0     0     0 1079  195  0  0 100  0  0
 1  0    128 233168 409644 2474396    0    0     0   232 1021  188  3  0 97  0  0
 0  0    128 233176 409644 2474396    0    0     0     0 1111  213  2  0 98  0  0
 0  0    128 233176 409644 2474396    0    0     0     0 1005   60  0  0 100  0  0

Memory

  • swpd: the amount of virtual memory used.
  • free: the amount of idle memory.
  • buff: the amount of memory used as buffers.
  • cache: the amount of memory used as cache.
  • inact: the amount of inactive memory. (-a option)
  • active: the amount of active memory. (-a option)

Swap

  • si: Amount of memory swapped in from disk (/s).
  • so: Amount of memory swapped to disk (/s).
$ cat /proc/meminfo
MemTotal:      4050776 kB
MemFree:        120984 kB
Buffers:        411928 kB
Cached:        2423468 kB
SwapCached:          0 kB
Active:        1861536 kB
Inactive:      1492152 kB
HighTotal:           0 kB
HighFree:            0 kB
LowTotal:      4050776 kB
LowFree:        120984 kB
SwapTotal:     1052248 kB
SwapFree:      1052120 kB
Dirty:             172 kB
Writeback:           0 kB
AnonPages:      518112 kB
Mapped:          23140 kB
Slab:           544448 kB
PageTables:       9528 kB
NFS_Unstable:        0 kB
Bounce:              0 kB
CommitLimit:   3077636 kB
Committed_AS:   859208 kB
VmallocTotal: 34359738367 kB
VmallocUsed:    263132 kB
VmallocChunk: 34359474803 kB
HugePages_Total:     0
HugePages_Free:      0
HugePages_Rsvd:      0
Hugepagesize:     2048 kB
$ cat /proc/sys/vm/swappiness
60

What’s important is not to believe that ‘free’ or ‘MemFree’ values are the total free memory. Linux/Unix distributions use available memory as a File System Cache, dumping this data if additional memory is needed. It’s important to add Cached to get a better indication of the true memory available.

Your initial audit should also look at the processes that are using the memory. Options include ‘top’ and ‘ps’.

$ top
M

op - 15:33:56 up 36 days, 17:08,  2 users,  load average: 0.01, 0.11, 0.08
Tasks: 133 total,   1 running, 132 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.0%us,  0.1%sy,  0.0%ni, 99.9%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   4050776k total,  3792952k used,   257824k free,   368140k buffers
Swap:  1052248k total,      128k used,  1052120k free,  2329212k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 2159 mysql     15   0  411m 204m 5624 S    0  5.2  33:26.43 mysqld
 2965 buildbot  15   0  280m 105m 1932 S    0  2.7 393:15.54 buildbot
 8533 nobody    15   0  168m  25m 4092 S    0  0.7   0:03.49 httpd
 9121 nobody    15   0  166m  24m 3432 S    0  0.6   0:01.61 httpd
...
$ ps -eopid,fname,rss,vsz,user,command | more
  PID COMMAND    RSS    VSZ USER     COMMAND
    1 init       700  10324 root     init [3]
   346 httpd    25252 170484 nobody   /opt/httpd-2.2.9/bin/httpd -k start
  364 httpd    25184 170344 nobody   /opt/httpd-2.2.9/bin/httpd -k start
  425 kpsmouse     0      0 root     [kpsmoused]
  452 httpd    21000 165684 nobody   /opt/httpd-2.2.9/bin/httpd -k start
...
 2095 mysqld_s  1204  63800 root     /bin/sh bin/mysqld_safe
 2159 mysqld   209448 421248 mysql   /opt/mysql51/bin/mysqld --basedir=/opt/mysql51 --datadir=/opt/mysql51/data --user=mysql --log-error=/opt/mysql51/data/dc1.onegreendog.com.err --pid-file=/opt/mysql51/data/dc
1.onegreendog.com.pid
 ...
  • 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).

As I have written about previously, Are you monitoring RSS & VSZ?, these columns are important and should be monitored appropriately.

Regarding Swap. This is pre assigned disk space that is used to swap out (dump) memory processes when you have run out of memory for all the running processes. You never want to run out of memory on your database server. Swapping is both extremely slow, and if your database server swaps out the mysqld process, this will effectively kill your database. If you have insufficient swap space for the process, again in the case of database server this can cause your system to crash.

Historically in Unix world, swap was always defined as 2x Memory. This also doubled as a place to dump all memory in a kernel panic. I spent time in a past life doing core dump analysis.
Today, most Linux systems are ill-configured for swap. If you use a dedicated server for example, you may be limited to what is configured by a third party. The above example shows a configuration I would not recommend where swap is less then 1x the memory.
There is also a consideration to have no Swap. By setting swapiness to 0, you are effectively saying never swap. I would also not recommend this.

Monitoring memory usage closely is important. Taking appropriate action regarding the mysql process because you can’t control how much total memory it uses is critical. Correctly configuring mysql to use memory optimally is key to a well and long running database server.

Next, we will be looking at Disk and Network resource bottlenecks.