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.
When dealing with CPU, a quick audit should include identifying the number of CPU cores your server has, and the types of these cores. The key file on Linux systems is /proc/cpuinfo.
Number of cores can be found via the command cat /proc/cpuinfo | grep “^processor” | wc -l
You need to look more closely at the file to determine the type of CPU (e.g. below the model name shows Intel(R) Xeon(R) CPU X3220 @ 2.40GHz. The combination of knowing the number of processors (cores) listed and physical id and siblings helps identify how many CPUs and how many cores per CPU exist.
$ cat /proc/cpuinfo processor : 0 vendor_id : GenuineIntel cpu family : 6 model : 15 model name : Intel(R) Xeon(R) CPU X3220 @ 2.40GHz stepping : 11 cpu MHz : 2394.051 cache size : 4096 KB physical id : 0 siblings : 4 core id : 0 cpu cores : 4 fpu : yes fpu_exception : yes cpuid level : 10 wp : yes flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx lm constant_tsc pni monitor ds_cpl vmx est tm2 cx16 xtpr lahf_lm bogomips : 4789.96 clflush size : 64 cache_alignment : 64 address sizes : 36 bits physical, 48 bits virtual power management: ...
Other commands that help with identifying CPU/cores include mpstat and top.
$ mpstat -P ALL 5 11:43:43 AM CPU %user %nice %sys %iowait %irq %soft %steal %idle intr/s 11:43:48 AM all 0.00 0.00 0.00 0.00 0.05 0.00 0.00 99.95 1033.00 11:43:48 AM 0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00 1000.40 11:43:48 AM 1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00 0.00 11:43:48 AM 2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00 31.40 11:43:48 AM 3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00 1.00
$ top 0 top - 11:42:09 up 36 days, 13:17, 2 users, load average: 0.20, 0.24, 0.25 Tasks: 133 total, 1 running, 132 sleeping, 0 stopped, 0 zombie Cpu0 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu1 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu2 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu3 : 0.0%us, 0.3%sy, 0.0%ni, 99.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Mem: 4050776k total, 3825584k used, 225192k free, 397580k buffers Swap: 1052248k total, 128k used, 1052120k free, 2302408k cached
You can easily identify a CPU bottleneck using the vmstat command.
The following shows an idle system.
$ 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
The key columns (from the man page are)
CPU – These are percentages of total CPU time.
-
us: Time spent running non-kernel code. (user time, including nice time)
-
sy: Time spent running kernel code. (system time)
-
id: Time spent idle. Prior to Linux 2.5.41, this includes IO-wait time.
-
wa: Time spent waiting for IO. Prior to Linux 2.5.41, included in idle.
-
st: Time stolen from a virtual machine. Prior to Linux 2.6.11, unknown. Procs
r: The number of processes waiting for run time.
NOTE: The columns of vmstat may vary between different Linux Operating Systems.
If you system is CPU Bound then you will observe this. Look at id,us,sy,r
$ 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 3 0 128 275684 397176 2300672 0 0 0 0 1118 427 74 2 25 0 0 3 0 128 217404 397176 2300672 0 0 0 0 1017 138 74 1 25 0 0 6 0 128 239584 397176 2300672 0 0 0 0 1086 350 93 2 5 0 0 4 0 128 269468 397176 2300672 0 0 0 0 1005 229 98 2 0 0 0 4 0 128 217636 397180 2300668 0 0 0 168 1087 251 99 2 0 0 0 4 0 128 240576 397180 2300668 0 0 0 0 1006 182 99 2 0 0 0 4 0 128 270708 397180 2300668 0 0 0 0 1079 338 98 2 0 0 0 4 0 128 218752 397180 2300684 0 0 0 0 1005 106 99 1 0 0 0 4 0 128 226316 397180 2300684 0 0 0 0 1077 308 98 2 0 0 0 4 0 128 198664 397184 2300680 0 0 0 76 1010 250 99 1 0 0 0 4 0 128 179444 397184 2300680 0 0 0 0 1077 238 100 0 0 0 0 4 0 128 185396 397184 2300688 0 0 0 0 1006 210 99 1 0 0 0 4 0 128 199408 397184 2300688 0 0 0 0 1079 336 99 1 0 0 0
You should also be wary of a Single CPU Bound process. This is why knowing the number of cores is important. In this example, one CPU is bound.
$ 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 99592 412544 2477580 0 0 0 0 1017 89 0 0 100 0 0 0 0 128 99592 412544 2477580 0 0 0 0 1090 222 0 0 100 0 0 0 0 128 99592 412544 2477580 0 0 0 0 1019 98 0 0 100 0 0 1 0 128 99592 412544 2477580 0 0 0 0 1096 347 14 0 86 0 0 1 0 128 99592 412548 2477576 0 0 0 84 1030 194 25 0 75 0 0 1 0 128 99592 412548 2477576 0 0 0 0 1094 300 25 0 75 0 0 1 0 128 99592 412548 2477580 0 0 0 0 1012 76 25 0 75 0 0 1 0 128 99592 412548 2477580 0 0 0 0 1096 318 25 0 75 0 0 1 0 128 73192 412548 2477580 0 0 0 0 1039 273 29 0 70 0 0 1 0 128 77284 412556 2477572 0 0 0 268 1122 373 25 1 75 0 0 2 0 128 83592 412556 2477584 0 0 0 0 1036 374 27 1 72 0 0 0 0 128 56220 412564 2477576 0 0 0 172 1017 84 7 0 94 0 0 0 0 128 56220 412564 2477576 0 0 0 0 1078 192 0 0 100 0 0
$ mpstat -P ALL 1 12:15:55 PM CPU %user %nice %sys %iowait %irq %soft %steal %idle intr/s 12:15:56 PM all 25.00 0.00 0.00 0.00 0.00 0.00 0.00 75.00 1072.00 12:15:56 PM 0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00 1001.00 12:15:56 PM 1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00 0.00 12:15:56 PM 2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00 62.00 12:15:56 PM 3 100.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 9.00 12:15:56 PM CPU %user %nice %sys %iowait %irq %soft %steal %idle intr/s 12:15:57 PM all 25.00 0.00 0.00 0.00 0.00 0.00 0.00 75.00 1021.00 12:15:57 PM 0 0.00 0.00 0.00 0.00 0.00 1.00 0.00 99.00 1001.00 12:15:57 PM 1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00 0.00 12:15:57 PM 2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00 18.00 12:15:57 PM 3 100.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 2.00
I will be detailing identifying bottlenecks of Memory, Disk and Network in future posts. You can also find out more at the MySQL User Conference “Monitoring 101 – Simple stuff to save your bacon” session.
