Whenever there are application performance issues or availability problems, seemingly (from the DBA’s perspective), the first question asked is, “What is wrong with the database?” Regardless of the source of the problem, the DBA is often required to look if the database is behaving well.
Approaching these issues when looking at the database and system-wide resources is an important part of the research and troubleshooting.
For displaying system-wide resource usage, the tools that are useful are the following:
• vmstat
• top
The vmstat (virtual memory statistics) tool is intended to help you quickly identify bottlenecks on your server.
The top utility provides a dynamic, real-time view of system resource usage.
Using vmstat
The vmstat utility displays real-time performance information about processes, memory, paging, disk I/O, and CPU usage.
Now we just need to know what to look for in the output. Here are some suggestions:
• If the wa (time waiting for I/O) column is high, this usually indicates that the storage subsystem is overloaded.
• If b (processes blocked) is consistently greater than 0, then you may not have enough CPU processing power.
• If so (memory swapped out to disk) and si (memory swapped in from disk) are consistently greater than 0, you may have a memory bottleneck.
The one line of the server statistics is returned and gives the average statistics calculated from the last time the system was rebooted.
You can also gather vmstat over a period of time with parameters <interval in seconds> and <number of intervals>.
$ vmstat 2 10
Or you can use the watch tool to run the vmstat command regularly on the screen to capture the differences between each snapshot.
This is useful only if you are working on finding the root cause of the issue, because in a large database environment you are not going to be able to watch the screen for one database server.
This is also why it is good to work with the server administrators and leverage the tools they might be using.
Being a DBA means understanding the server configuration and databases, but in large environments working with other teams and using tools for monitoring is essential.
$ watch -n 5 -d vmstat
To see the memory statistics in megabytes, then use the S m parameters:
$ vmstat -S m
Leave a Reply