Is it a good idea / possible to separate sql database from the rest of the site? Does it make any difference?
Yes, this is absolutely a good idea. The webserver, PHP, and your MySQL instance are all fighting for limited resources, and disk IO quickly becomes a bottleneck on busy websites.
Unfortunately, this won’t be easy. In my experience, data centers have only limited staff that understand RAID setup and configuration. You might even have trouble finding a hardware RAID controller that’s compatible with your operating system. This move is still worth your troubles.
If going ahead with separating would we need to look at pathnames in coding to check they are relative and not absolute?
I’d review those, anyway. Your code should be as portable as is reasonably possible.
(We are not putting the db on a different server, it will be on the same server.)
Or is this pointless - should we concentrate on caching for mysql?
Improving MySQL configurations is always a good plan. (see below for my preferred configurations)
Here’s my process for moving MySQL to a new RAID mount on Ubuntu 12.04 LTS…
- Apply software updates
- format RAID array as
ext4 partition (
parted commands used to find RAID device)
- mount RAID array with
- confirm connectivity and functionality of the block device
/etc/fstab to mount the RAID array on startup
- restart the computer to confirm the array is automatically mounted
mysql/tmp directories on RAID mount
cp existing MySQL data to
mysql/data on RAID mount
- symbolically link old data location to RAID mount with
- symbolically old MySQL
tmp location to RAID directory with
/etc/mysql/my.cnf to point
tmp directory at new location
- confirm connectivity and functionality of MySQL server
- remove old mysql files from
Additional configuration details here: https://gist.github.com/parhamr/6177160