High Availability MySQL Cookbook phần 10 pot

Chapter 8

221

By comparison, the following output shows the same system under high CPU usage. In

this case, there is no I/O waiting but all the CPU time is spent in sy (system mode) and

us (user mode), with effectively 0 in idle or I/O waiting states:

A more detailed view of what is going on can be seen with the vmstat command. vmstat is

best launched with the following argument, which will show the statistics every second (the

first line of results should be ignored as it is the average for each parameter since the system

was last rebooted):

Units in output are kilobytes unless specified otherwise; you can change it to

megabytes with the –s M flag.

In the output of the previous vmstat command, the following fields are particularly useful:

Swap: The two important swap values are:

si: KB/second of memory that is "swapped in" (read) from disk

so: KB/second of memory that is "swapped out" (written) to disk







Performance Tuning

222

In a database server, swapping is likely to be bad news—any

significant value here suggests that more physical RAM is required,

or the configuration of buffers and cache are set to use too much

virtual memory.

IO: The two important io values are:

bi: Blocks read from block devices (blocks/s)

bo: Blocks written to block devices (blocks/s)

CPU: The single most important cpu value is wa, which gives the percentage of CPU

time spent waiting for IO.

Looking at the example screenshot, it is clear that there was a significant output of bytes to

disk in the 9th second of the command, and that the disk was not able to absorb all the IO

immediately (causing 22 percent of the CPU to be in iowait state during this second). All

the other time, the CPU loads were low and stable.

Another useful tool is the sar command. When run with the -d flag, sar can provide, in

Kilobytes, data read from and written to a block device.

When installed as part of the sysstat package, sar creates a

file /etc/cron.d/sysstat, which takes a snapshot of system

health every 10 minutes and produces a daily summary.

sar also gives an indication of the number of major and minor page faults (see the There's

more… section for a detailed explanation of these terms). For now, remember that a large

number of major faults, as the name suggests, is bad and also suggests that a lot of IO

operations are only being satisfied from the disk and not from a RAM cache.

sar, unlike the other commands mentioned so far, requires installation and is part of the

sysstat package. Install this using yum:

[root@node1 etc]# yum -y install sysstat

Look at the manual page for sar to see some of the many modes that you can run it in. In

the following example, we will show statistics related to paging (the –B flag). The number next

to the mode is the refresh rate (in the example, it's 1 second) and the second number is the

number of values to print:

[root@node1 etc]# sar -B 1 2

Linux 2.6.18-164.el5 (node1) 11/22/2009









Chapter 8

223

09:00:06 PM pgpgin/s pgpgout/s fault/s majflt/s

09:00:07 PM 0.00 15.84 12.87 0.00

09:00:08 PM 0.00 0.00 24.24 0.00

Average: 0.00 8.00 18.50 0.00

This shows the number of kilobytes the system has paged in and out to the disk. A detailed

explanation of these Page Faults can be found in the There’s more... section. Now, we look

at the general disk IO figures with the lowercase -b flag:

[root@node1 etc]# sar -b 1 2

Linux 2.6.18-164.el5 (node1) 11/22/2009

08:59:53 PM tps rtps wtps bread/s bwrtn/s

08:59:54 PM 0.00 0.00 0.00 0.00 0.00

08:59:55 PM 23.00 0.00 23.00 0.00 456.00

Average: 11.50 0.00 11.50 0.00 228.00

This shows a number of useful IO statistics—the number of operations per second (total (tps)

in first column, reads (rtps) in the second and writes (rtps) in the third) as well as the fourth

and fifth columns, which give the number of blocks read and written per second (bread/s

and bwrtn/s respectively).

The final command that we will introduce in this section is iostat, which is also included

in the sysstat package and can be executed with the –x flag to display extended statistics

followed by the refresh rate and number of times to refresh:

This shows the details of an average CPU utilization (that is, those shown using top/vmstat),

but it also shows the details for each block device on the system. Before looking at the results,

notice that the final three lines relating to dm-x refer to the Device Mapper in the Linux kernel,

which is the technology that LVM is based on. It is often useful to know statistics by physical

block device but it can be useful to find statistics on a per LVM volume basis (in this case, sda).

To manually translate your LVM logical volumes to the dm-x number, follow these steps:

Performance Tuning

224

Firstly, look at the /proc/diskstats file, select out the lines for device mapper

objects and print the first three fields:

[root@node1 dev]# grep "dm-" /proc/diskstats | awk '{print $1, $2,

$3}'

253 0 dm-0

253 1 dm-1

253 2 dm-2

Take the two numbers, mentioned previously (known as a major and minor device

number, for example, in the example dm-0 has major number 253 and minor 0) and

check the output of ls -l for a match:

[root@node1 mapper]# ls -l /dev/mapper/

total 0

crw------- 1 root root 10, 63 Feb 11 00:42 control

brw------- 1 root root 253, 0 Feb 11 00:42 dataVol-root

brw------- 1 root root 253, 1 Feb 11 00:42 dataVol-tmp

brw------- 1 root root 253, 2 Feb 11 00:42 dataVol-var

In this example, dm-0 is dataVol-root (which is mounted on /, as shown in the

df command).

You can pass the -p option to sar and the -N option to iostat, which will

automatically print the statistics on a per logical volume basis

Looking at the results from iostat, the most interesting fields are:

r/s and w/s: The number of read and write requests sent to the device per second

rsec/s and wsec/s: The number of sectors read and written from the device

per second

avgrq-sz: The average size of the requests issued to the device (in sectors)

avgqu-sz: The average queue length of requests for this device

await: The average time in milliseconds for IO requests issued to the device to be

served—this includes both queuing time and time for the device to return the request

svctm: The average service time in milliseconds for IO requests issued to the device

Of these, far and away, the most useful is await, which gives you a good idea of the time the

average request takes—this is almost always a good proxy for relative IO performance.















