An incomplete guide to linux system configuration for MySQL

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/




Share the love!

Subscribe to RSS feed or by email to automatically receive real-time notifications
Oracle, MySQL, and InnoDB are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners

6 Comments

  1. Regarding the I/O scheduler, your example uses sda, but you need to determine which disk(s) this should be set for. Also note, that this method of setting does NOT persist after a hardware restart.

    Reply
    • Thx Ronald, I’ve just replaced sda by DEVICE.
      Regarding this setting, I’ve never had any problem following a restart.
      And… why do you want to restart a MySQL server ;-)

      Reply
  2. >How to set a new persistent value : echo noop >/sys/block/[DEVICE]/queue/scheduler

    it’s non-persistent

    Reply

Leave a Comment.