OS commands to diagnose Database Performance issues.

How to use OS commands to diagnose Database Performance issues?

Below you will find some helpful commands to diagnose database performance issues like memory, swap space, CPU % usage and idle, IO usage in the server in general and also to find a few most OS resouce consuming processes to get better understanding about how the UNIX server is performing and whether any of these resources are causing a bottleck in the database performance or not.
Since the popular UNIX systems do not support the same commands or even same options for a command.
Please refer to the corresponding OS man pages for detailed information about each UNIX command mentioned in this post.

Solaris:
$ /usr/sbin/prtconf |grep -i "Memory size"
$ swap -s
$ df -k
$ /usr/local/bin/top
$ vmstat 5 100
$ sar -u 2 100
$ iostat -D 2 100
$ mpstat 5 100

Out of these commands, top command may not be installed in your server by default.
In that case,you can get it for free from www.sunfreeware.com and install it preferably under /usr/local/bin directory and then use it. Please take a few snpshots of the top command output and store it in a file.It refreshes the output screen every 5 sec.

-Prtconf command will show how much Physical Memory (RAM) the Solaris server has.
-Swap command will provide us with the usage of swap space including the RAM.
-Df command will indicate how much space is free in each mount point and also provides information about swap space(s).
-Top command wil provide the above information plus information about top CPU consuming processes, CPU usage in the system, etc.
-Vmstat will provide information about process run queue, memory usage, paging and swapping and CPU % usage in the server. Different options in vmstat can provide more specific information, if required.
-Iostat provides IO usage by Disk, CPU % usage, etc. depending on the options used. Sar command with "-u" option also provides CPU usage and idle time information.
-Mpstat will provide CPU usage stats for a Solaris server with 1 or more CPUs.
This command is very useful for multi-processor system to provide details about the CPU usage by every CPU in the server.

HP-UX 11.0:
$ grep Physical /var/adm/syslog/syslog.log
$ df -k
$ sar -w 2 100
$ sar -u 2 100
$ /bin/top
$ vmstat -n 5 100
$ iostat 2 100
$ top

AIX:
$ /usr/sbin/lsattr -E -l sys0 -a realmem
$ /usr/sbin/lsps -s
$ vmstat 5 100
$ iostat 2 100
$ /usr/local/bin/top # May not be installed by default in the server

Linux [RedHat 7.1 and RedHat AS 2.1]:
$ dmesg | grep Memory
$ vmstat 5 100
$ /usr/bin/top

Please take at least 10 snapshots of the "top" command to get an idea aboud most OS resource comsuming processes in the server and the different snapshot might contain a few different other processes and that will indicate that the use of resouces are varying pretty quickly amound many processes.

Regards,
Marcello Travassos.