Tuning a Linux system for Database Server
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:
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
Why deadline ? Just read this articles:
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
and after that run
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:
5. Use a separate partition/disk for your Database storage mounted with noatime to do that in /etc/fstab just
/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
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:
I hope this informations will be useful for you.
Good Luck !