Linux Performance Management for the DBA

Sometimes a DBA doesn't have the luxury of a Linux administrator when help is needed diagnosing performance problems.  It doesn't matter if you are an accidental DBA for PostgreSQL, MySQL, Vertica, or any other database manager (NoSQL included)...you really should know how to detect if your OS is performing its best.  This post is a quick guide for a DBA (or anyone really) who needs to get up-to-speed quickly on Linux from a performance management perspective.  

This post is for the poor IT professional who isn't a Linux expert but needs to quickly diagnose whether his Linux server's configuration is contributing to his poor performance.  These tips are just about universal regardless of which database manager you are running on Linux.  The Linux distro doesn't matter either.  There may be some exceptions, such as SELinux, which only runs on rpm-based distros (red hat, centos, et al) and isn't really available on Debian-based distros (ubuntu et al).  This list is not meant to be exhaustive, but it's a good starting point.  This list also doesn't show you the nuances of sudo and it isn't a Linux tutorial.  If you are not Linux proficient but find yourself acting as the de facto Linux administrator, this quick guide will show you where to begin looking if you have performance problems.  

crontabs

crontabs are scheduled tasks in Linux.  It's always best to see if you have any scheduled tasks that may be running that may be affecting your performance.  I list this first because I'm always surprised how many perf issues can be distilled down to a scheduled task that someone forgot is running.  Generally, you need to check the following files:  

/var/spool/cron/crontabs
/etc/crontab
/etc/cron.d

SELinux

This is a security "watcher" under rpm-based distros.  It adds significant overhead.  My recommendation is to disable it, if possible.  

sudo cat /etc/selinux/config

Kernel Messages

This is roughly equivalent to the System log in Event Viewer in Windows.  Always check this to see if there is a systemic problem.  

sudo dmesg | less

OS Error Logs

This is roughly equivalent to the Application log in Event Viewer in Windows.  Best to check this too.  

less /var/log/messages

Memory

There are many different memory-related metrics you should be aware of in Linux, just like in Windows.  Hard faults, soft faults, dirty pages, swap (which is similar to the page file), etc etc.  You can google this.  Just be aware that free -m is how you check almost all memory-related metrics.  

CPU

Like memory, CPU performance has the same characteristics on both Windows and Linux.  The top command is a good place to start looking at CPU metrics.  More info.  

Disk Metrics

iostat is the command that will show you just about everything you need.  Again, certain patterns of performance indicate the same issues in Linux as in Windows.  Example:  A high idle time in top output simultaneously with a high count of read blocks in iostat will generally mean your database server is disk-bound.  

I like to run the following command:

iostat -dx 5

Essentially every 5 seconds you'll get a nice output of your io metrics.  %util will show you io channel saturation.  But the real metric you want to look at, IMHO, is the await column.  This is the equivalent of latency (queue time + service time).  Just as with Windows, good latencies are the most critical io metric.  Rotational local media should be under 10ms, SAN should be similar but never is, and SSDs should be undetectable.  

Defaults to Change

Here are some quick settings in Linux that you can check where the Linux defaults are suboptimal for database servers.  Changing these settings will likely yield you noticeable performance improvements if your performance is already horrendous.  

Readahead Buffer

This is set to 256 by default on all distros I've ever worked with.  And it's a horrible setting if you care about sequential read performance, and you do care about this if you run ANY database manager.  Sure, this may mean you have some wasted data in RAM as the block size gets bigger, but memory is cheap, might as well use it.  1024 is a good number for PostgreSQL and MySQL.  Vertica has an even higher recommendation.  

blockdev --report  ## shows you what the current setting is.  

blockdev --setra 1024 /dev/sdxx  ## sets it until the next reboot.  Do this for every device you have (sdxx)

cat blockdev --setra 1024 /dev/sdxx > /etc/rc.local  ## makes this a permanent change

Swappiness

Swappiness is the relative weight given to swapping out runtime memory to the swap space (similar to the page file).  In every distro I've worked on this is defaulted to 60, which essentially means memory is swapped out to disk fairly aggressively.  Database servers tend to run only a handful of processes and the only one that is memory-intensive is the database manager process itself, which you do not want swapped out at any price.  The right setting then is likely 0 or 1, meaning never swap out memory, or only do it under extreme duress so the server doesn't crash.  

cat /proc/sys/vm/swappiness  ##check the current setting
echo 1 > /proc/sys/vm/swappiness  ##set it to 1
## write it to /etc/rc.local so it survives reboots

Do not update access times on database disks

Windows has the same problem.  The access times on files are updated when a file is "touched".  This means that even a disk read results in a disk write, which isn't efficient.  No one really cares about updating file access times on database files anyway.  

vi /etc/fstab
# add the following
errors=remount-ro,noatime 0 1

Change the IO Scheduler

The Completely Fair Scheduler (cfq) is the default io scheduler, which is better suited for a laptop. We want our db to perform as fast as possible.  I don't believe Windows has the concept of a "scheduler", which is merely how the OS handles and prioritizes disk requests.  In Linux the "deadline scheduler" has a goal to reduce latency which is better suited for a database server.

/etc/rc.local
echo 'deadline' > /sys/block/sdxx/queue/scheduler

Summary

This post is definitely not meant to be a definitive guide to Linux performance.  In a quick 5 minute read I hope I have given the "accidental Linux administrator" enough info to check the obvious Linux settings that are probably negatively affecting performance, as well as how to quickly monitor OS performance.  Good luck.  

You have just read "[[Linux Performance Management for the DBA]]" on davewentzel.com. If you found this useful please feel free to subscribe to the RSS feed.