Operating System Tools

No MySQL instance exists in isolation; there are always potential interactions with the operating system. Consequently, it behooves database administrators to understand the obtainable performance monitoring tools for their operating system specific environment. The following sections briefly look at the major choices available for the Windows and Linux/Unix operating systems.

Windows

Most modern versions of Microsoft Windows, including server- and client-based platforms, now sport very advanced graphical monitoring tools. These include the Windows Task Manager, which provides detailed data about the current activity level on a system, as well as the much more sophisticated performance monitoring capabilities of the Microsoft Management Console (MMC).

Figure 2.11 shows an example of the Performance tab of the Windows Task Manager.

Figure 2.11. Output from the Windows Task Manager.

Other tabs describe the current state of the network, all processes, and any running applications.

For much more robust reporting, including the ability to save information for further analysis, the MMC Performance Monitor is essential (shown in Figure 2.12).

Figure 2.12. Output from the MMC Performance Monitor.

This example has customized the report to provide details about the current CPU activity, along with memory and disk utilization. This represents a minute fraction of the hundreds of possible statistics that you can track.

Chapter 14, "Operating System, Web Server and Connectivity Tuning," which provides details on operating system, Web, and connectivity tuning, discusses specific examples of how these tools can help us research, isolate, and correct performance bottlenecks.

Linux/Unix

Traditionally, Linux and Unix platforms have been less graphically rich than their Windows-based cousins. This is changing: Increasing numbers of both open source and commercial utilities provide detailed yet easily understood performance and status reporting.

Red Hat Linux offers a basic performance monitor, as shown in Figure 2.13.

Figure 2.13. Performance information from the Red Hat Linux System Monitor.

For more sophisticated performance monitoring, IBM has done a nice job with its server-based rmfpms data gatherer and Windows or Linux-based rmf pm client (both available for download from IBM), as shown in Figure 2.14.

Figure 2.14. Output from the Performance Monitoring (rmfpms) utility from IBM.

This utility is highly configurable. For example, you can track some metrics for the Apache HTTP server, as shown in Figure 2.15.

Figure 2.15. Defining metrics for the Apache web server within the Performance Monitoring utility.

The following section briefly examines some of the character-based utilities that you can exploit to receive performance information about your system. The most commonly used utilities are covered briefly, and are covered in more detail later in Chapter 14.

Activity-Reporting Utilities

The ps and top utilities, provided by the Linux and Unix operating systems, return information, in static and dynamic format respectively, about current process activity on your system. You can pass numerous switches to these utilities to customize your monitoring. The following shows two examples:

 [clafong@WEBSERVER1 clafong]$ ps eaxf  PID TTY      STAT   TIME COMMAND    1 ?        S      0:04 init [5]    2 ?        SW     0:00 [migration/0] ..    9 ?        SWN    0:00 [ksoftirqd/3]   10 ?        SW<    0:00 [events/0]   11 ?        SW<    0:00 [events/1] 6407 ?        SW     0:01  \_ [pdflush] .. 2549 ?        S      0:00 /usr/sbin/sshd 15321 ?        S      0:00  \_ sshd: weisenhard [priv] 15323 ?        S      0:00  |   \_ sshd: weisenhard@pts/24 15324 pts/24   S      0:00  |       \_ -bash USER=rarons LOGNAME=rarons 15410 pts/24   S      0:00  |           \_ su 15411 pts/24   S      0:00  |               \_ bash 15431 pts/24   S      0:00  |                   \_ su daniellej 15432 pts/24   S      0:00  |                       \_ bash 15450 pts/24   S      0:03  |                           \_ php cust_lookup.php ... 6504 ?        S      0:00 /bin/sh /usr/bin/mysqld_safe  6527 ?        S      0:00  \_ /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql  data [clafong@WEBSERVER1 clafong]$ top top - 23:30:11 up 3 days,  8:04,  2 users,  load average: 4.40, 3.97, 2.33 Tasks:  96 total,   1 running,  95 sleeping,   0 stopped,   0 zombie Cpu(s): 42.9% us, 29.7% sy,  0.0% ni, 25.4% id,  2.0% wa,  0.0% hi,  0.0% si Mem:   2074924k total,  2064436k used,    10488k free,   209500k buffers Swap:  2040244k total,        0k used,  2040244k free,  1453200k cached   PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND 15456 danielle  15   0 21540 7876  16m S 59.1  0.4   6:29.60 php 15459 danielle  16   0 20196 7780  16m S 58.7  0.4   6:20.78 php 15462 danielle  16   0 27060  14m  16m S 53.2  0.7   6:17.57 php 15450 danielle  16   0 19384 6328  16m S  1.7  0.3   0:09.34 php 15458 danielle  16   0 19924 6252  16m S  0.1  0.3   0:01.09 php 15464 danielle  16   0 19000 6252  16m S  0.1  0.3   0:01.06 php  6527 mysql     16   0  384m 234m 7460 S  0.1 11.6   0:00.87 mysqld 15323 rschneid  15   0  8648 2296 6856 S  0.1  0.1   0:00.31 sshd 15457 danielle  16   0 18692 6204  16m S  0.1  0.3   0:00.63 php 15461 danielle  16   0 19724 6320  16m S  0.1  0.3   0:01.16 php  3433 root      15   0 93332 8196  84m S  0.0  0.4   3:42.95 X 15460 danielle  16   0 19380 6284  16m S  0.0  0.3   0:00.65 php 

The mpstat utility provides helpful information about processor activity, with special value for multi-CPU machines:

 [clafong@WEBSERVER1 clafong]$ mpstat Linux 2.6.5-1.358smp (WEBSERVER1)    2/18/2006 11:36:56 PM  CPU   %user   %nice %system %iowait    %irq   %soft   %idle    intr/s 11:36:56 PM  all    2.38    8.03    0.60    0.86    0.01    0.01   88.11   1019.03 

The highly configurable sar utility helps track, over time, a number of critical performance details about your system:

 [clafong@WEBSERVER1 clafong]$ sar 12:00:00 AM       CPU     %user     %nice   %system   %iowait     %idle ... ... 11:10:00 PM       all      0.05      0.00      0.01      0.00     99.94 11:20:01 PM       all      2.68     16.01      2.01      0.60     78.69 11:30:00 PM       all     44.30      0.00     31.64      2.42     21.64 11:40:00 PM       all     43.98      0.00     31.34      1.14     23.54 

Finally, you should note the contents of the /proc directory, which are an enormous, virtual collection of information about the current state of your system, including kernel configuration, processor activity, memory utilization, network operations, and so on. The following is just one small sample:

 [jbishop@WEBSERVER1 jbishop]$ cat /proc/stat cpu  2988575 9278095 871824 101906758 994740 8505 15503 cpu0 336920 2325449 185697 25921556 246029 76 296 cpu1 181478 2308893 137074 26154657 221931 4299 7658 cpu2 1895367 2333828 376550 24146295 252279 4129 7541 cpu3 574808 2309924 172502 25684249 274499 0 7 intr 295688116 290125508 12 0 3 3 0 4 0 1 0 0 0 117 0 492 5197266 ... ctxt 38515831 btime 1103153119 processes 16169 procs_running 4 procs_blocked 0 

Memory-Reporting Utilities

The free utility delivers a status update on the current state of physical memory and caching:

 [clafong@WEBSERVER1 clafong]$ free              total       used       free     shared    buffers     cached Mem:       2074924    2060688      14236          0     153436    1502764 -/+ buffers/cache:     404488    1670436 Swap:      2040244          0    2040244 

The vmstat utility describes the current state of virtual memory:

 [clafong@WEBSERVER1 clafong]$ vmstat procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----  r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy id wa  3  0      0  23756 163668 1492272    0    0    10    23   32    31 10  1 88  1 

You'll spend significant time later in the book analyzing how MySQL itself uses memory to boost performance, along with the interplay between MySQL's memory usage and that of the operating system.

Network-Reporting Utilities

System and network administrators make great use of netstat, which provides a tremendous variety of insight into all aspects of your system's networking profile:

 Active Internet connections (w/o servers) Proto Recv-Q Send-Q Local Address           Foreign Address           State tcp        0      0 ::ffff:20.69.21.174:ssh  ::ffff:20.69.21.115:4823 ESTABLISHED tcp        0      0 ::ffff:20.69.21.174:ssh  ::ffff:20.69.21.115:4824 ESTABLISHED Active UNIX domain sockets (w/o servers) Proto RefCnt Flags       Type       State         I-Node Path unix  13     [ ]         DGRAM                    3594   /dev/log unix  2      [ ]         DGRAM                    1180   @udevd unix  4      [ ]         STREAM     CONNECTING    0      /var/lib/mysql/mysql.sock unix  3      [ ]         STREAM     CONNECTED     150634 ... ... unix  2      [ ]         DGRAM                    3813 unix  2      [ ]         DGRAM                    3656 unix  2      [ ]         DGRAM                    3604 unix  2      [ ]         DGRAM                    1163 

Disk-Reporting Utilities

The iostat utility helps administrators gain a truer picture of what is happening with their file systems and other storage devices:

 [sbooth@SNA-R379 sbooth]$ iostat Linux 2.6.5-1.358smp (SNA-R379)    11/12/2005 avg-cpu:  %user   %nice    %sys %iowait   %idle            2.45    8.02    0.68    0.86   87.99 Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn sda               0.00         0.02         0.00       4662        768 sdb              17.92        79.32       477.19   22947706  138060952