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.