Have you ever spent a lot of time thinking about how to install and configure a Linux server for MySQL database? I will try to highlight all the critical steps and some of the decisions you may need to make.
Unless you have a really good experience in systems administration, choose a widely supported Linux distribution. The best choices usually are RedHat or its free cousin called CentOS. Compatible alternatives you could also consider are Scientific Linux and Oracle Linux. Make sure you will be installing a 64-bit version, unless you have a very good reason not to.
If you have multiple disks available in the server, create a single array from all disks. Choose RAID level that offers better performance rather than more disk space, so either RAID 1 or RAID 10 depending on the number of disks (“Should RAID 5 be used in a MySQL server?”). Use stripe (chunk) size of at least 128KB.
You may skip it if database carries InnoDB tables only (other than system tables). Otherwise, or if unsure, LVM should be configured as it may help in creating consistent and lock-free MySQL backups. Make sure to leave some free space in the volume group where MySQL logical volume will be. 10% of the logical volume size could be a good default. An example of what you should see after installation:
garfield ~ # vgdisplay <your volume group name> --- Volume group --- VG Name <your volume group name> System ID Format lvm2 [..] VG Size 1,58 TiB PE Size 4,00 MiB Total PE 413626 Alloc PE / Size 394426 / 1,51 TiB Free PE / Size 19200 / 75,00 GiB VG UUID aZcyWM-B1mc-5PEc-VeNf-W0fw-hLvV-12w29x
For a dedicated database server, it is commonly enough to create four partitions:
- /boot – tiny, to hold boot files – please refer to the system documentation for details
- / – 20GB or 30GB for the system files and logs
- swap – nothing larger than a few gigabytes makes sense
- /mnt/db – all remaining space for MySQL data and binary logs
Use whatever you want for system partitions. For MySQL partition, ext3 or ext4 are fine, but if you need to squeeze that last ounce of performance out of the system, go with xfs. It can deal much better with concurrent I/O.
All your servers should have their clocks updated periodically. Install NTP client and make sure it updates time daily. How to check whether clock is out of sync or not? Once you install
ntpdate package, try the following:
garfield ~ # ntpdate -q pool.ntp.org server 220.127.116.11, stratum 2, offset -29.763656, delay 0.04489 server 18.104.22.168, stratum 2, offset -29.776960, delay 0.08609 server 22.214.171.124, stratum 2, offset -29.761839, delay 0.04979 12 Apr 12:21:25 ntpdate: step time server 126.96.36.199 offset -29.763656 sec
The clock in my computer was clearly not synchronized :-)
Prevent needless swap activity. Add
vm.swappiness = 0 into
/etc/sysctl.conf. Please refer to “How to prevent swapping on a MySQL server?” for details.
By default any Linux uses CFQ algorithm for I/O scheduling. CFQ does not really care about I/O latency and may serialize requests, which means it can work against database performance. This is why it should be replaced with deadline, or even noop when server has a good hardware RAID controller, on physical volumes where MySQL data files will be stored.
In order to change the I/O scheduler at run time, simply write into a special pseudo-file
/sys/block/<block device>/queue/scheduler, for example:
echo "noop" > /sys/block/sda/queue/scheduler
The change can be made permanent by adding such line into an init script such as
/etc/rc.local or other that is appropriate.
- Create MySQL data directory inside the volume mount point (e.g.
/mnt/db/mysql). Do not use the mount point directly or you may be seeing the annoying lost+found database inside MySQL.
- Create a directory for binary logs (e.g.
Alternatively, instead of making new
/mnt/db/mysql, you may simply move the data directory created by the install script from a MySQL package (e.g.
mv /var/lib/mysql /mnt/db/).
And do not forget about changing these directories ownership to mysql user!
my.cnf, set the following:
At this point the server is ready for use and you can begin taking care of setting up privileges and tuning the MySQL configuration.