In my opinion, one of the greatest barriers to wider adoption of Linux for SQL Server platforms is not concern about how well the engine works under Linux. I believe it is actually the absence of tools that SQL Server DBAs have become accustomed to. No, I am not really talking about SSMS (at least not today). Today I’m actually talking about how to survive without the Windows Performance Monitor (Perfmon) that we’ve all grown to love over the years.
First the easier part. When we talk about configuring the Windows performance monitor on a Windows-based SQL Server we are usually worried both about general Windows performance counters and also SQL Server-specific performance counters. The good news here is that the SQL Server performance counters (page lifetime, connections per second, etc) are also available from the sys.dm_os_performance_counters dynamic management view. This DMV is still available when we are on a Linux platform so this data can be obtained from there instead. I may write about this in later parts of this series, but until/unless I do Dave Bland and Louis Davidson (among others) have written on the topic. We still need a way to get operating-system level stuff like CPU usage, I/O performance, etc., … which will be the focus of the rest of this post.
Second, any time we are discussing ongoing performance monitoring it is wise to ask ourselves if we really want to set up our own monitoring framework or not. There are some great third party tools out there which are worth a look, and that is even more true if we are branching out into a less familiar (to us) operating system. Even if none of the traditional third party SQL Server monitoring packages are in budget, there are some great frameworks for monitoring Linux that may fit the bill.
If you are still reading that must mean that you, like me, are used to leaning heavily on perfmon on Windows and have some infrastructure around saving the data and are looking for something close to a drop in replacement … without installing an agent or really any more software than is necessary. The rest of this post will walk through the installation of sar (sysstat) as one option that, while not actually pre-installed, is lightweight and easy to get up and running. Examples will be in Ubuntu, I may provide walkthroughs for other distros in the future. Bear in mind that things are still early, it is possible that a year or so from now I would have a different suggestion.
Step 1 : Install the sysstat package
As of the time of this writing sysstat is not installed by default. Make sure repositories are up to date and install. In Unbuntu packages are installed with the apt command, in other distributions the manager will be different (yum or zypper)
sudo apt-get update
sudo apt-get install sysstat
Step 2 : Set retention period in config file
Edit the configuration file ( /etc/sysstat/sysstat in Ubuntu, may be located in /etc/sysconfig/sysstat if you are using a different distribution ). Decide how many days worth of data you would like for sysstat to retain on the filesystem. Be aware that the data file name convention is different if a value greater than 28 is specified for history, so in this example I am editing from it’s default length of 7 to 28.
sudo vi /etc/sysstat/sysstat
Step 3 : Consider updating crontab to gather data ever 5 minutes
Data collection does work a bit differently. On Windows, we typically configure perfmon to start every day at midnight and run for 24 hours regardless of sample frequency. With sysstat, we instead schedule each sample collections. I personally prefer this because it is more tolerant of events like mid-day reboots.
The scheduler in UNIX is called cron, and a default crontab file is usually installed with sysstat. The default will save data every 10 minutes. I typically adjust this to save a sample every 5 minutes. This can be done by updating the rightmost column of the line calling debian-sa1 to be */5 (* in this column would mean run every minute, */5 means every 5). This file is located at /etc/cron.d/sysstat and can be edited with any text editor (I’m using vi in this example)
sudo vi /etc/cron.d/sysstat
Step 4 : Enable automated data collection
It’s easier to manage data collection if it’s enabled as an Ubuntu service. This can be done in the /etc/default/sysstat file
sudo vi /etc/default/sysstat
And finally, restart the service using this command
sudo service sysstat restart
Viewing some data
The next couple of posts in this series will work through some examples of how we can retrieve historical data from sysstat and insert it into SQL Server. But until then here are some examples of how we can view historical data. A few notes on filenames
- Assuming we configured a history length of 28 or less, the last two digits of the filenames refer to a day of the month. All of my examples are using data from the 27th so I use /var/log/sysstat/sa28 – to see data from a different day change the last two digits.
- Some distributions may place this data in /var/log/sa/ instead of /var/log/sysstat/
- If the “-f <filename>” parameter is omitted, data for the current day will be shown
- Be aware when using tab completion that the /var/log/sysstat/ directory contains files other than data files. If sar complains about an invalid file it’s possible that a report file was passed in instead of a data file.
A few notes on parameters
- The first parameter in the below examples specifies the type of data we want
- The -f parameter specifies which data file (which day) we’re interested in
- The -e parameter specifies an ending time. I personally like to specify 11:59PM as an ending time to prevent data timestamped at midnight of the following day.
- The -t parameter specifies that times should be show in the local time zone rather than UDC.
# Show cpu data for all cores for the 27th day of the month
sar -P ALL -f /var/log/sysstat/sa27 -e 23:59:00 -t
# Show memory utilization for the 27th day of the month
sar -r -f /var/log/sysstat/sa27 -e 23:59:00 -t
# Show disk (block device) info for the 27th day of the month
sar -dp -f /var/log/sysstat/sa27 -e 23:59:00 -t
# Show network device utilization
sar -n DEV -f /var/log/sysstat/sa27 -e 23:59:00 -t
# Show paging activity
sar -B -f /var/log/sysstat/sa27 -e 23:59:00 -t
Viewing some easier to process data
The sar commands discussed in the last section display data formatted for human readability. This is Linux and it is, of course, entirely possible for us to use shell scripting tools such as sed and awk to reformat this data into a delimited list which would be easier to insert into SQL Server. Unfortunately those are not tools in the typical SQL Server professional’s tool belt. There is good news – we can get computer-friendly output from the sadf command. The following examples will give semi-colon delimited versions of the same data.
# Show cpu data for all cores for the 27th day of the month. The -d option specifies ';' separator
sadf -P ALL /var/log/sysstat/sa27 -e 23:59:00 -td
# Show memory utilization for the 27th day of the month. The -- indicates sar options follow
sadf /var/log/sysstat/sa27 -e 23:59:00 -td -- -r
# Show disk (block device) info for the 27th day of the month
sadf /var/log/sysstat/sa27 -e 23:59:00 -td -- -dp
# Show network device utilization
sadf /var/log/sysstat/sa27 -e 23:59:00 -td -- -n DEV
# Show paging activity
sadf /var/log/sysstat/sa27 -e 23:59:00 -td -- -B
Data FLowe Solutions - Rick Lowe 