12

Running an Ubuntu server with MySQL for a high traffic production database server. Nothing else is running on the machine except the MySQL instance.

We store daily database backups on the DB server, is there any performance hit or reason why we should keep the hard disk relatively empty? If the disk is filled up to 86%+ with the database and all of the backups, does it hurt performance at all?

So would the DB server running with 86-90%+ full capacity perform less well in any way than the server running with only a 10% full disk?

The total disk size on the server is over 1 TB so even 10% of the disk should be enough for basic O/S swapping and such.

3
  • 1
    MySQL data on the same parition as root (/)? You really don't want that to fill up; crash city.
    – gravyface
    Aug 31, 2012 at 18:48
  • 1
    I don't think there's any inherent reason to keep the disk space cleared as long as the data is being managed well. Speaking of which, why are you backing up locally? The first thing I'd do is push those backups off to another box.
    – ceskib
    Aug 31, 2012 at 18:52
  • Keep in mind that a disk nearly full has a downtime risk for services depending on the database. If DB disk is full DB will stop. So to less space left will result in higher downtime risk.
    – Mr.T
    Aug 31, 2012 at 22:06

5 Answers 5

11

First of all you DO NOT want to keep your database backups on the same physical drive or RAID group as your database. The reason for this is that a disk failure (if you are running without any RAID protection) or a catastrophic RAID failure (if you are using RAID-1 or RAID-5) will cause you to lose your database and your database backups.

Your question about disk performance relates to how full a disk drive is depends on how the data on the disk is accessed. For spinning disks there are two physical factors that affect I/O performance. They are:

  • seek time - which is the time it takes the disk drive to move the disk head from its current track position to the track that contains the requested data

  • rotational latency - which is the average time it takes for the desired data to reach the read head as the drive spins - for a 15K RPM drive this is 2 ms (milliseconds)

How full your drive is can affect the average seek time your server's I/Os are experiencing. For example, if your drive is full and you have database tables that are physically located on the drive on the extreme opposite ends of the disk's platters, then as you perform I/Os accessing data from each of these tables those I/Os will experience the maximum seek time of the drive.

That being said, however, if your drive is full and your application accesses only a small fraction of the data stored on the drive and all of this data is located contiguously on the drive, then these I/Os will be minimally impacted by seek time.

Unfortunately, the answer to this question is that "your mileage will vary", meaning that how your application accesses the data and where that data is located will determine what your I/O performance will be.

Also, as mentioned by @gravyface, it would be "best practice" to separate your operating system storage requirements from your database. Again, this would be to help minimize head movement on the disk surface as having both on the same drive could cause constant seeking between the operation system and database areas of the drive as both the operating system and database software make I/O requests.

8

There are two angles to consider here: Performance and Robustness.

Performance-wise, it is generally recommended to have separate disk spindles (or RAID groups / drive sets) for:

  1. The OS stuff (binaries, logs, home directories, etc.)
  2. Swap space (which may be combined with (1) if you don't expect to use swap)
  3. The Production DB
  4. The Production DB's transaction logs (if used)
  5. Database Dumps/backups

The reasoning behind this is pretty straightforward: You don't want the DB performance impacted by "other stuff" demanding the disk (e.g. if the machine starts swapping heavily and the swap partition is on the other side of the disk from the DB data you have long disk seeks to contend with).


From a robustness standpoint you want the same sort of breakdown, but for a different reason: As others have pointed out you don't want a failed disk to take out both your DB and its backups (though realistically you should be copying the backups off the server anyway in the event of a catastrophic failure).

You also want to avoid any configuration with a monolithic / partition that contains everything -- this is an unfortunate, tragic, and alarmingly common mistake made in the Linux world which is not shared by the other Unix-like systems.
As Gravyface mentioned in his comment, if you somehow manage to fill up / your system will almost certainly crash, and cleanup/recovery can be time consuming and costly if the system has a single / partition rather than a well-structured hierarchy of mount points.

2
  • sad that a lot of distros still setup the partitions with an uber / by default.
    – gravyface
    Aug 31, 2012 at 20:03
  • @gravyface Agreed - I know Ubuntu now (12.04) gives you the choice between that and a properly segmented partition layout. Not sure what it's default is, but IMHO this may be one of the worst things Linux has done in terms of damage to the Unix community: tens of thousands of "sysadmins" who think a single giant / partition is perfectly fine & have to be retrained...
    – voretaq7
    Aug 31, 2012 at 20:09
5

I'd recommend moving the database and temporary (see below) backups to a different partition than root (/).

Also, come up with a sensible rotation/retention scheme for your (assumed) compressed database dump backups. There's (usually) no reason to keep that many copies of the backups on the local disk. Does nothing for disaster recovery and when moved off-site, should be removed off the disk.

That's pretty much standard operating procedure.

4

This reminded me of a bug on NetApp where file systems that are near full had their performance drop significantly (like half). (admittedly that was a few years ago).

The answer as everyone said is it depends, but it is worth thinking it through.

The main downside of full file systems is the list of free inodes are likely to be fragmented and all over the place.

There are three types of data that sit on the hard disk for a database.

  1. Your actual database file. This will be a large preallocated file that generally grows in large chunks (10% for example).
  2. Logs, your transaction log that is being continuously written to, deleted, written to, etc...
  3. Temporary files for large queries that cannot run in memory.

(1) only needs free space when allocating more space for your file set. If your database is not growing it should be unaffected by a low disk space file system. If it is allocating though, it could ask for a very large chunk that doesn't fit into any free list you have immediately fragmenting your database and causing seeking when it needs data to ready into memory.

(2) a naive impelementation of logs where it uses the OS to manage allocating space and deleting it will suffer. Assuming your database is not read only, there will be a constant stream of logs, they will frequently be fragmented on a low hard disk space. Ultimately this will hurt your write performance.

(3) tempDB, if the DB needs it for shoddy written queries, or not enough RAM, then you've got bigger problems than low disk space causing performance problems as even your read performance could become disk bound then. You also run the risk of an outage if MySql needed to allocate disk space for tempDB and the hard disk ran out.

About backups...

  1. Every enterprise I have worked in keeps backups on the same machine. When it comes to a restore (who cares about backups, it's the restores that count). Nothing is going to beat the speed of having the db file right there on the same disk.
  2. Hopefully obvious, ensure the backups are not just local.

In short I'd say you'll survive providing your DB is not write heavy. If it is, then low disk space is a problem. But if I was you I'd work on the following sooner rather than later.

  1. Confirming I have enough RAM
  2. Segregating logs and all transient data from your DB.
  3. Segregating your OS, your MySql install from the remainder.

Use separate spindles and controllers if you can for 1.

Followed by separate spindles

Followed by a poor man's separate partitions.

0

I had a similar problem recently when I used up all the disk space on one of my replication servers. The immediate effect was for replication to crash and then I could not log into MySQL because the mysqld.sock file could not be opened.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .