Planning how much storage is needed for MySQL databases is an issue that always comes up when buying new servers.
There’s 2 different general cases, OLTP and logging or Data Warehouse databases.
OLTP Database Storage Capacity Planning
Based on experience, I usually recommend total disk space available to MySQL of around 4x the size of the existing OLTP database to ensure enough working space and to make operations, mgmt. and recovery easier.
Let’s analyze why …
Disk space is needed for:
- size of database data and index files ( du -sh /var/lib/mysql )
- size of largest table x 2 if tempfiles or sortfiles are needed, per simultaneous process doing this – hopefully one “rogue” process at a time. (This is one of the main reasons to avoid ad hoc queries on production databases.)
- size of each backup file, for backups that are stored locally (in a disaster, copying a large database over the network just takes too long for Internet services)
- size of binlogs if stored outside /var/lib/mysql
- 5% free for general OS processes
- 5% to 20% is needed for LVM snapshot space if used
Sample Calculation
1 database + 2 backups + largest table (50% of db) * 2 = 3x, plus some binlog growth and 5% = 4x original database size.
For most users with basic hardware, all of that will be on 1 filesystem.
Surprisingly, even with separate filesystems for binlogs and backups, the maximum filesystem size doesn’t get reduced much:
- one backup needs to be on the main data filesystem, unless you want to spend several minutes copying it to /var/lib/mysql instead of a 1 second move. Possible exceptions are if you have a small database backup on SSD or other really fast device. But copying 50 GB at 400 MB/s would still take 125 seconds, or 2 minutes – just for the copy operation alone.
- table copies and sorts will be done in /var/lib/mysql unless $TMPDIR is set and working correctly.
Logging/Data Warehouse Database Storage Capacity Planning
For logging and Data Warehouse databases, capacity planning depends heavily on your initial planning and environment:
- depending on SLA, a local copy may not be needed for quick recovery – just copy from the backup server over the network
- some DW databases are effectively backed up by keeping the original load files available
- canned reports can be EXPLAINed in advance to avoid tempfiles and sortfiles
- storage engines (table types) consume space differently: Archive < MyISAM < Innodb
- some storage engines, like Archive and MyISAM (but not Partitions in 5.1), allow filesystem-level read-only permissions, reducing recovery time substantially in case of power loss or fsck
I get alarmed when I see a filesystem with MySQL data on it more than 50% full, but far more so with OLTP usage than logging.
Regardless of planning, a cross-join on two multi-million row tables is guaranteed to consume all space. In that case, see the MySQL manual link below for the expected behavior.
