Apr
11
2013
An incomplete guide to linux system configuration for MySQL
Posted by Cedric PEINTRE on Thu 11 Apr 2013 21:35 UTC
Tags:

As a former Oracle DBA, I know how the system and the database are linked.
The first one who told me that the installation of Oracle has never been a problem is a liar!
Yes, your database and your system are the best of friends, you must respect that.

I’d make a list of linux system settings to configure a MySQL databases server and share my sources with you.
In return, I would like you to share your sources with the community by publishing your tips in the comments.

swappiness

 

  • This parameter allows to specify how the kernel must manage the memory swap
  • Default value : 60 (Range 0 to 100)
  • Value to set : 0 (it will swap only to avoid an out of memory condition)
  • How to set a new non-persistent value :  sysctl -w vm.swappiness=0
  • How to store a new persistent value : add vm.swappiness=0 in the /etc/sysctl.conf file

 

I/O Scheduler

 

  • The I/O scheduler manages and optimizes priorities of I/O requests
  • Default value : device driver dependent
  • Range of values : noop / deadline / anticipatory / cfq
  • Value to set : noop
  • How to set a new persistent value : echo noop >/sys/block/[DEVICE]/queue/scheduler

 

Mounting options

 

  • These options are set during the filesystem mount process
  • Default value : rw
  • Value to set ext4 : rw,nosuid,noatime,data=ordered
  • Value to set xfs : nobarrier
  • How to store a new persistent value : add these options in the /etc/fstab file

 

I/O queue size

 

  • This queue stores incoming I/O requests
  • Default value : 128 (No range)
  • Value to set : Hey Dude, you have to test, no magic value for this parameter
  • How to set a new persistent value :  echo 100000 > /sys/block/[DEVICE]/queue/nr_requests

 

Dirty ratio

 

  • These settings are used to tune the VM subsystem about dirty data
  • Default value : 10 and 20
  • Value to set : Again, you should test. You can try 5 and 60 to begin
  • How to set a new non-persistent value :
    • echo 5 > /proc/sys/vm/dirty_background_ratio
    • echo 60 > /proc/sys/vm/dirty_ratio
  • How to store a new persistent value : Add these parameters to the /etc/sysctl.conf file
    • vm.dirty_background_ratio = 5
    • vm.dirty_ratio = 60

NUMA Architecture

 

Note that all of these tips focus on optimizing I/O.
The first three tips can be considered as really interesting.
I recommend you to read the sources before any change.

Remember to test every changes before to put them in production.

Now, let us know how you setup your system for MySQL.

Sources :
- http://en.wikipedia.org/wiki/Swappiness
- http://yoshinorimatsunobu.blogspot.fr/2009/04/linux-io-scheduler-queue-size-and.html
- http://rackerhacker.com/2008/08/07/reduce-disk-io-for-small-reads-using-memory
- http://assets.en.oreilly.com/1/event/21/Mastering%20the%20Art%20of%20Indexing%20Presentation.pdf
- http://cdn.oreillystatic.com/en/assets/1/event/56/Linux%20and%20H_W%20optimizations%20for%20MySQL%20Presentation.pdf
- http://www.mysqlperformanceblog.com/2013/01/03/is-there-a-room-for-more-mysql-io-optimization/
- http://www.percona.com/files/presentations/UC2011-MySQL-and-SSD-Usage-Patterns.pdf
- http://blog.jcole.us/2010/09/28/mysql-swap-insanity-and-the-numa-architecture/
- http://blog.jcole.us/2012/04/16/a-brief-update-on-numa-and-mysql/