Solve Database Performance Tuning Problems by Monitoring CPU Resource Consumption

Being an effective database administrator means that we must at times be able to use system level utilities to find things like CPU, memory, and I/O bottlenecks/issues to help point us in the proper direction.

Database administrators will often dive straight into the database to determine performance problems, forgetting about the operating system and the wealth of information it contains. An effective DBA must be able to use system level utilities to find things like CPU, memory, and I/O bottlenecks/issues to help point us in the proper direction. These three high-level resources are the main limiting factors to database performance that cannot be altered; we have only so many CPU cycles, so much memory, and a specific disk configurations. Without properly understanding how they are currently behaving, before looking inside the database, will almost always get us in trouble. This article looks at how we as DBAs might monitor CPU utilization from the operating system and when it might signal issues we need to look into.

The sar utility is part of the sysstat package and will collect, store in logs, and report on a wide variety of system activities. Of all the system level commands that you can use, the sar utility has multiple switches/options that allow the DBA to quickly and easily monitor those resources (CPU, context switches, interrupts, paging, memory usage, buffer usage, network usage) that can give us quick insight into the health of our database systems. What makes sar very nice to use is that sar constantly collects and logs system activity. To see this, and get some rudimentary, but powerful, commands out of the way, the following examples help show how to call sar for days and time intervals; remembering that sar maintains daily logs for each day in a file such as /var/log/sa/sa<dd>, where <dd> is the day of the month.

Use sar to look at today’s collected activity.

[oracle@wagner ~]$ sar 

Use sar to look at today’s collected activity; reporting every 10 seconds for 15 times.

[oracle@wagner ~]$ sar 10 15

Use sar to look at the daily activity on the 13th.

[oracle@wagner ~]$ sar -f /var/log/sa/sa13

Use sar to look at today’s collected activity between 6:00am and 7:00am.

[oracle@wagner ~]$ sar -s 06:00:00 -e 07:00:00

Use sar to look at the daily activity on the 13th and between 6:00am and 7:00am.

[oracle@wagner ~]$ sar -s 06:00:00 -e 07:00:00 -f /var/log/sa/sa13

View article

» See All Articles by Columnist James Koopmann