Let your MySQL partition breathe

Today I noticed my MySQL partition was taking over 86 GB of the available 120 GB. So I got worried and I wrote this little script to tell me how much space each DB I have is taking:

<?php
function format($size) {
	$unit = 'B';
	$units = array(
		'GB' => 1024 * 1024 * 1024
		, 'MB' => 1024 * 1024
		, 'KB' => 1024
	);

	foreach($units as $currentUnit => $value) {
		if ($size > 2 * $value) {
			$size /= $value;
			$unit = $currentUnit;
			break;
		}
	}

	return number_format($size, 1) . ' ' . $unit;
}

$settings = array(
	'host' => 'localhost'
	, 'user' => 'root'
	, 'password' => 'password'
);

$databases = array();

mysql_connect($settings['host'], $settings['user'], $settings['password']);

$result = mysql_query('show databases');
while($row = mysql_fetch_array($result)) {
	$databases[] = $row['Database'];
}

foreach($databases as $database) {
	$sizes[$database] = 0;

	mysql_select_db($database);
	$result = mysql_query('show table status');
	while($row = mysql_fetch_array($result)) {
		$sizes[$database] += $row['Data_length'] + $row['Index_length'];
	}
}

mysql_close();

foreach($sizes as $database => $size) {
	echo $database . ' = ' . format($size) . '<br />';
}

echo '<br />TOTAL: ' . format(array_sum($sizes));

?>

When I ran it, I saw all my DBs where taking a total of 10.8 GB, much less than the 86 GB occupied in the partition. So it hit me, it has to be the binary logs, a set of files that store log events (more about them here). Indeed, if you would list the contents of /var/lib/mysql I would find a ton of .bin files, a lot of them as old as from 2007. Therefore, I realized I had to flush the logs.

In order to flush the binary logs, I logged in to the MySQL console as an administrator, and issued (if you are on a server with replication, you want to purge the binary logs instead):

FLUSH LOGS;
RESET MASTER;

After doing so, the MySQL partition is now taking a total of 12 GB. Much better!

No related posts.