Home » Databases, Featured, Headline, How-to, Linux, Shell, Tuning

Tuning a Linux system for Database Server

16 July 2009 4 Comments

In my experiments with databases I was constrained to tune the system together with the databases. A system what will run a database is quite different from any other server system, because databases put a lot of stress on the IO and especially on the disks (probably the file servers too). Hardware is not all you should tune your system as much as possible. In this post I will talk about Linux with Ext3 (is what I have right now), but on future I will test OpenSolaris with ZFS.

1. From start on a Database system you will need to choose RAID1 or RAID 10. RAID1 is if you want redundancy and RAID10 if you want redundancy and speed. Is better to choose a hardware RAID solution (if you have enough money), but also the software one is quite good.

Some benchmarks and other useful informations you will found here:
http://linux-raid.osdl.org/index.php/Performance

2. Set your disk schedulers on deadline or even noop, but not CFQ or anticipatory. If you have RAID you should do that for every disk from your RAID. Deadline scheduler maintains a good disk throughput and this is why is perfect for a database system. Also Noop and CFQ are recommended in some cases, but deadline have a average throughput in all situations.

To set the deadline scheduler from a Linux shell just run:

[rb@randombugs]# echo deadline > /sys/block/sda/queue/scheduler

If you want it everytime when your system boot then is a good thing to set it directly from kernel parameters. In GRUB this is easy, just add elevator=deadline at the end of your kernel line or add

kopt=elevator=deadline

Why deadline ? Just read this articles:
http://blog.endpoint.com/2008/09/filesystem-io-what-we-presented.html
http://www.mysqlperformanceblog.com/2009/01/30/linux-schedulers-in-tpcc-like-benchmark/

3. Beacuse we are running a Database system and no other important programs will run there is better to turn off the swappiness of your system. Is very useful if you want your system to swap only when the memory is full. To do that just add in /etc/sysctl.conf

vm.swappiness=0

and after that run

[rb@randombugs]#sysctl -p

For more info read:
http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/
http://feedblog.org/2007/09/29/using-o_direct-on-linux-and-innodb-to-fix-swap-insanity/

4. Increase the “read_ahead” for your RAID and for your disks. This will help you in some case:
concurrent sequential scans, sequential scan on a fragmented table, index scan on majority rows.
To do that for your raid just run

[rb@randombugs]# blockdev –setra 65536 /dev/md0

And for your disks

[rb@randombugs]# echo 4096 > /sys/block/sda/queue/read_ahead_kb

For more info read:
http://insights.oetiker.ch/linux/raidoptimization/
http://linux-raid.osdl.org/index.php/Performance
http://archives.postgresql.org/pgsql-performance/2006-04/msg00613.php
http://blog.endpoint.com/2008/09/filesystem-io-what-we-presented.html

5. Use a separate partition/disk for your Database storage mounted with noatime to do that in /etc/fstab just
add/modify

/dev/md0 / ext3 errors=remount-ro,noatime 0 1

Doing that will instruct your kernel to don’t update every time the access time of your files and that can increase your database performance.

6. Increase your linit for open files and processes. Just add to /etc/limits/security.conf

mysql soft nofile 4096
mysql hard nofile 65535
mysql soft nproc 2047
mysql hard nproc 16384

OR

postgres soft nofile 4096
postgres hard nofile 65535
postgres soft nproc 2047
postgres hard nproc 16384

7. Also you can tune a lot of other parameters in a Linux system. Just be sure to read the following documents:

http://www.redbooks.ibm.com/abstracts/redp4285.html
http://www.mysqlperformanceblog.com/2009/01/30/linux-schedulers-in-tpcc-like-benchmark/
http://blog.endpoint.com/2008/09/filesystem-io-what-we-presented.html
http://insights.oetiker.ch/linux/raidoptimization/
http://linux-raid.osdl.org/index.php/Performance
http://archives.postgresql.org/pgsql-performance/2006-04/msg00613.php

I hope this informations will be useful for you.

Good Luck !


4 Comments »

  • Ricardo said:

    Thanks for the article, very informative.
    Most of the changes I have sucessfully tested on my production oracle systems, and they have worked nicely.
    Wish to know if you have any information on changing/tuning the linux process scheduling system.
    I will try to explain but english is not my native language, so please be patient.

    I wish to increase the time the kernel “loan” my processor to the oracle processes. By example he “lease” 10ms of my processor to any process in the running queue, then he put the process out of the processor to make room for another one.

    The thing is my processors are often idle and I wish to “lease” them more time to the processes so I could test if this speed them up.

    Any information will be helpfull I could not find almost anything of the subject.

    Thanks in advance for your time,

    Ricardo Rodriguez

  • aylin said:

    tnkss very god….
    Thanks for the post.

  • SAB said:

    For RH based distros, you can install ktune with #yum install ktune

    It can be started with #service ktune start

    This will set your disk schedulers on deadline

  • Techno__Mage said:

    I think this path/file may be backwards…
    /etc/limits/security.conf

    Mine is named…
    /etc/security/limits.conf

Leave your response!

Add your comment below, or trackback from your own site. You can also subscribe to these comments via RSS.

Be nice. Keep it clean. Stay on topic. No spam.

You can use these tags:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

This is a Gravatar-enabled weblog. To get your own globally-recognized-avatar, please register at Gravatar.