open_files_limit

I recently had a problem with a couple of websites that randomly threw the following error

Out of resources when opening file '/var/www/somelocation' (Errcode: 400 - Too many open files)

1	Out of resources when opening file '/var/www/somelocation' (Errcode: 400 - Too many open files)

The culprit being the MySQL server behind the webserver, hitting the ‘open_files_limit’ -limit.

You can get the limit seen by MySQL by querying SHOW VARIABLES like ‘open_files_limit’; and the current status from SHOW STATUS LIKE ‘Open_files’

I’m not going to go into how you change this value (mysql conf obviously and possibly system conf), nor what it should be set to – as both those things depends heavily on your specific environment.

What I will focus on is how you can log the usage from PHP. Here goes.

First we’ll connect to the MySQL server in question:

$servername = "server.example.com";
$username = "myusername";
$password = "mypassword";
$db = "mydatabase";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $db);
// Check connection
if (!$conn){
    die("Connection failed: " . mysqli_connect_error());
}

$servername = "server.example.com";

$username = "myusername";

$password = "mypassword";

$db = "mydatabase";

// Create connection

$conn = mysqli_connect($servername, $username, $password, $db);

// Check connection

if (!$conn){

die("Connection failed: " . mysqli_connect_error());

}

Once those formalities are out of the way, lets get the two datapoints we’re after. Namely 1) how many files are currently open and 2) What’s the upper limit. And the lets log that – with a timestamp obviously – to a text file, for analyzing.

We’ll create a variable to contain the log data, called $logData. Then we’ll run the two queries: SHOW STATUS LIKE ‘Open_files’ and SELECT @@open_files_limit; (which is the shorthand way to get the system variable) and save those results on variables of their own ($getFilelimit + $getCurrentOpenFiles). Additionally we’ll get the timestamp and join it all together into a comma separated value in $logEvent.

$logData = "";

$getFilelimit= mysqli_query($conn, "SHOW STATUS LIKE 'Open_files'");
while($valueArr1 = mysqli_fetch_array($getFilelimit)){
    $logData = $valueArr1&#91;1];
}

$getCurrentOpenFiles= mysqli_query($conn, "SELECT @@open_files_limit;");
while($valueArr2 = mysqli_fetch_array($getCurrentOpenFiles)){
    $logData = $logData . "/" . $valueArr2&#91;0];
}

$logEvent = date("Y-m-d H:i:s",time()) . ";" . $logData;

$logData = "";

$getFilelimit= mysqli_query($conn, "SHOW STATUS LIKE 'Open_files'");

while($valueArr1 = mysqli_fetch_array($getFilelimit)){

$logData = $valueArr1[1];

}

$getCurrentOpenFiles= mysqli_query($conn, "SELECT @@open_files_limit;");

while($valueArr2 = mysqli_fetch_array($getCurrentOpenFiles)){

$logData = $logData . "/" . $valueArr2[0];

}

$logEvent = date("Y-m-d H:i:s",time()) . ";" . $logData;

Then we’ll write the event to a log using a custom function writeTolog().

writeToLog($logEvent);

1	writeToLog($logEvent);

Which we’ll create as follows

function writeToLog($data){
	$file = '/full/path/to/log.txt';
	
	// Open the file to get existing content
	$current = file_get_contents($file);

	// Append data
	$data = "$data\n" . $current;

	// Write the contents back to the file
	file_put_contents($file, $data);
}

function writeToLog($data){

$file = '/full/path/to/log.txt';

// Open the file to get existing content

$current = file_get_contents($file);

// Append data

$data = "$data\n" . $current;

// Write the contents back to the file

file_put_contents($file, $data);

}

And finally we’ll close our MySQL connection and output the result for good measure, in case we’ll want to browse it manually.

mysqli_free_result($getFilelimit);
mysqli_free_result($getCurrentOpenFiles);

mysqli_close($conn);

echo ("Logged: " . $logEvent);

mysqli_free_result($getFilelimit);

mysqli_free_result($getCurrentOpenFiles);

mysqli_close($conn);

echo ("Logged: " . $logEvent);

The complete script

That leaves us with a script that looks like this

&lt;?php

// ############################
// Function: WRITE TO LOG
// ############################

function writeToLog($data){
	$file = '/full/path/to/log.txt';

	// Open the file to get existing content
	$current = file_get_contents($file);

	// Append data
	$data = "$data\n" . $current;

	// Write the contents back to the file
	file_put_contents($file, $data);
}

// ############################
// CONNECT TO MYSQL
// ############################

$servername = "server.example.com";
$username = "myusername";
$password = "mypassword";
$db = "mydatabase";
	// Create connection
	$conn = mysqli_connect($servername, $username, $password, $db);
	// Check connection
	if (!$conn){
		die("Connection failed: " . mysqli_connect_error());
	}

// ############################
// Collect datapoints
// ############################

$logData = "";

$getFilelimit= mysqli_query($conn, "SHOW STATUS LIKE 'Open_files'");
while($valueArr1 = mysqli_fetch_array($getFilelimit)){
	$logData = $valueArr1&#91;1];
}

$getCurrentOpenFiles= mysqli_query($conn, "SELECT @@open_files_limit;");
while($valueArr2 = mysqli_fetch_array($getCurrentOpenFiles)){
	$logData = $logData . "/" . $valueArr2&#91;0];
}

$logEvent = date("Y-m-d H:i:s",time()) . ";" . $logData;

// ############################
// Log
// ############################

writeToLog($logEvent);

// ############################
// Close connection and output
// ############################

mysqli_free_result($getFilelimit);
mysqli_free_result($getCurrentOpenFiles);

mysqli_close($conn);

echo ("Logged: " . $logEvent);
	
?>

<?php

// ############################

// Function: WRITE TO LOG

// ############################

function writeToLog($data){

$file = '/full/path/to/log.txt';

// Open the file to get existing content

$current = file_get_contents($file);

// Append data

$data = "$data\n" . $current;

// Write the contents back to the file

file_put_contents($file, $data);

}

// ############################

// CONNECT TO MYSQL

// ############################

$servername = "server.example.com";

$username = "myusername";

$password = "mypassword";

$db = "mydatabase";

// Create connection

$conn = mysqli_connect($servername, $username, $password, $db);

// Check connection

if (!$conn){

die("Connection failed: " . mysqli_connect_error());

}

// ############################

// Collect datapoints

// ############################

$logData = "";

$getFilelimit= mysqli_query($conn, "SHOW STATUS LIKE 'Open_files'");

while($valueArr1 = mysqli_fetch_array($getFilelimit)){

$logData = $valueArr1[1];

}

$getCurrentOpenFiles= mysqli_query($conn, "SELECT @@open_files_limit;");

while($valueArr2 = mysqli_fetch_array($getCurrentOpenFiles)){

$logData = $logData . "/" . $valueArr2[0];

}

$logEvent = date("Y-m-d H:i:s",time()) . ";" . $logData;

// ############################

// Log

// ############################

writeToLog($logEvent);

// ############################

// Close connection and output

// ############################

mysqli_free_result($getFilelimit);

mysqli_free_result($getCurrentOpenFiles);

mysqli_close($conn);

echo ("Logged: " . $logEvent);

We can then browse this manually to get the currect state, or simply run it automatically as a Cron job – perhaps every 10 seconds or so. That gives us a dataset similar to this…

2020-03-03 15:59:11;1086/10000
2020-03-03 15:59:01;1073/10000
2020-03-03 15:58:51;1073/10000
2020-03-03 15:58:41;1073/10000
2020-03-03 15:58:31;1073/10000
2020-03-03 15:58:21;1075/10000
2020-03-03 15:58:11;1085/10000
2020-03-03 15:58:01;1087/10000
2020-03-03 15:57:51;1087/10000
2020-03-03 15:57:41;1087/10000
2020-03-03 15:57:31;1116/10000
2020-03-03 15:57:21;1115/10000
2020-03-03 15:57:11;1115/10000
2020-03-03 15:57:01;1115/10000
2020-03-03 15:56:51;1115/10000
2020-03-03 15:56:41;1107/10000
2020-03-03 15:56:31;1107/10000
2020-03-03 15:56:21;1107/10000
2020-03-03 15:56:11;1105/10000
2020-03-03 15:56:01;1105/10000
2020-03-03 15:55:51;1105/10000
2020-03-03 15:55:41;1105/10000
2020-03-03 15:55:31;1120/10000
2020-03-03 15:55:21;1120/10000
2020-03-03 15:55:11;1131/10000
2020-03-03 15:55:01;1146/10000
2020-03-03 15:54:51;1150/10000
2020-03-03 15:54:41;1161/10000
2020-03-03 15:54:31;1161/10000
2020-03-03 15:54:21;1159/10000
2020-03-03 15:54:11;1159/10000
2020-03-03 15:54:01;1161/10000
2020-03-03 15:53:51;1161/10000
2020-03-03 15:53:41;1161/10000
2020-03-03 15:53:31;1157/10000
2020-03-03 15:53:21;1157/10000
2020-03-03 15:53:11;1157/10000
2020-03-03 15:53:01;1157/10000
2020-03-03 15:52:51;1157/10000
2020-03-03 15:52:41;1157/10000
2020-03-03 15:52:31;1157/10000
2020-03-03 15:52:21;1161/10000
2020-03-03 15:52:11;1163/10000
2020-03-03 15:52:01;1165/10000
2020-03-03 15:51:51;1167/10000
2020-03-03 15:51:41;1169/10000
2020-03-03 15:51:31;1169/10000
2020-03-03 15:51:21;1170/10000
2020-03-03 15:51:11;1170/10000
2020-03-03 15:51:01;1171/10000
2020-03-03 15:50:51;1171/10000
2020-03-03 15:50:41;1173/10000
2020-03-03 15:50:31;1173/10000
2020-03-03 15:50:21;1173/10000
2020-03-03 15:50:11;1195/10000
2020-03-03 15:50:01;1195/10000
2020-03-03 15:49:51;1197/10000
2020-03-03 15:49:41;1197/10000
2020-03-03 15:49:31;1201/10000
2020-03-03 15:49:21;1221/10000
2020-03-03 15:49:11;1231/10000
2020-03-03 15:49:01;1232/10000
2020-03-03 15:48:51;1231/10000
2020-03-03 15:48:41;1303/10000
2020-03-03 15:48:31;1305/10000
2020-03-03 15:48:21;1335/10000
2020-03-03 15:48:11;1336/10000
2020-03-03 15:48:01;1335/10000
2020-03-03 15:47:52;1335/10000
2020-03-03 15:47:42;1335/10000
2020-03-03 15:47:32;1345/10000
2020-03-03 15:47:22;1345/10000
2020-03-03 15:47:12;1345/10000
2020-03-03 15:47:02;1347/10000
2020-03-03 15:46:51;1347/10000
2020-03-03 15:46:41;1351/10000
2020-03-03 15:46:31;1377/10000
2020-03-03 15:46:21;1397/10000
2020-03-03 15:46:11;1369/10000
2020-03-03 15:46:01;1346/10000
2020-03-03 15:45:51;1364/10000
2020-03-03 15:45:41;1364/10000
2020-03-03 15:45:31;1364/10000
2020-03-03 15:45:21;1368/10000
2020-03-03 15:45:11;1368/10000
2020-03-03 15:45:01;1368/10000
2020-03-03 15:44:51;1368/10000
2020-03-03 15:44:41;1368/10000
2020-03-03 15:44:31;1368/10000
2020-03-03 15:44:21;1368/10000
2020-03-03 15:44:11;1360/10000
2020-03-03 15:44:01;1352/10000
2020-03-03 15:43:51;1340/10000
2020-03-03 15:43:41;1345/10000
2020-03-03 15:43:31;1353/10000
2020-03-03 15:43:21;1357/10000
2020-03-03 15:43:11;1357/10000
2020-03-03 15:43:01;1350/10000
2020-03-03 15:42:51;1351/10000
2020-03-03 15:42:41;1342/10000
2020-03-03 15:42:31;1327/10000
2020-03-03 15:42:21;1327/10000
2020-03-03 15:42:11;1327/10000
2020-03-03 15:42:01;1295/10000
2020-03-03 15:41:51;1295/10000
2020-03-03 15:41:41;1295/10000
2020-03-03 15:41:31;1295/10000
2020-03-03 15:41:21;1295/10000
2020-03-03 15:41:11;1296/10000
2020-03-03 15:41:01;1309/10000
2020-03-03 15:40:51;1305/10000
2020-03-03 15:40:41;1305/10000
2020-03-03 15:40:31;1305/10000
2020-03-03 15:40:21;1305/10000
2020-03-03 15:40:11;1305/10000
2020-03-03 15:40:01;1305/10000
2020-03-03 15:39:51;1305/10000
2020-03-03 15:39:41;1305/10000
2020-03-03 15:39:31;1293/10000
2020-03-03 15:39:21;1293/10000
2020-03-03 15:39:11;1293/10000
2020-03-03 15:39:01;1313/10000
2020-03-03 15:38:51;1313/10000
2020-03-03 15:38:41;1313/10000
2020-03-03 15:38:31;1313/10000
2020-03-03 15:38:21;1313/10000
2020-03-03 15:38:11;1313/10000
2020-03-03 15:38:01;1312/10000
2020-03-03 15:37:51;1310/10000
2020-03-03 15:37:41;1298/10000
2020-03-03 15:37:31;1298/10000
2020-03-03 15:37:21;1298/10000
2020-03-03 15:37:11;1298/10000
2020-03-03 15:37:01;1298/10000
2020-03-03 15:36:51;1298/10000
2020-03-03 15:36:41;1321/10000
2020-03-03 15:36:31;1323/10000
2020-03-03 15:36:21;1331/10000
2020-03-03 15:36:11;1331/10000
2020-03-03 15:36:01;1331/10000
2020-03-03 15:35:51;1331/10000
2020-03-03 15:35:41;1335/10000
2020-03-03 15:35:31;1337/10000
2020-03-03 15:35:21;1335/10000
2020-03-03 15:35:11;1337/10000
2020-03-03 15:35:01;1337/10000
2020-03-03 15:34:51;1337/10000
2020-03-03 15:34:41;1337/10000
2020-03-03 15:34:31;1337/10000
2020-03-03 15:34:21;1337/10000
2020-03-03 15:34:11;1305/10000
2020-03-03 15:34:01;1305/10000
2020-03-03 15:33:52;1304/10000
2020-03-03 15:33:42;1268/10000
2020-03-03 15:33:32;1266/10000
2020-03-03 15:33:22;1267/10000
2020-03-03 15:33:12;1269/10000
2020-03-03 15:33:02;1267/10000
2020-03-03 15:32:51;1267/10000
2020-03-03 15:32:41;1267/10000
2020-03-03 15:32:31;1270/10000
2020-03-03 15:32:21;1270/10000
2020-03-03 15:32:11;1270/10000
2020-03-03 15:32:01;1271/10000
2020-03-03 15:31:51;1283/10000
2020-03-03 15:31:41;1301/10000
2020-03-03 15:31:31;1301/10000
2020-03-03 15:31:21;1272/10000
2020-03-03 15:31:11;1272/10000
2020-03-03 15:31:01;1272/10000
2020-03-03 15:30:51;1272/10000
2020-03-03 15:30:41;1272/10000
2020-03-03 15:30:31;1256/10000
2020-03-03 15:30:21;1256/10000
2020-03-03 15:30:11;1255/10000
2020-03-03 15:30:01;1254/10000
2020-03-03 15:29:51;1254/10000
2020-03-03 15:29:41;1254/10000
2020-03-03 15:29:31;1254/10000
2020-03-03 15:29:21;1254/10000
2020-03-03 15:29:11;1254/10000
2020-03-03 15:29:01;1262/10000
2020-03-03 15:28:51;1264/10000
2020-03-03 15:28:41;1264/10000
2020-03-03 15:28:31;1264/10000
2020-03-03 15:28:21;1264/10000
2020-03-03 15:28:11;1264/10000
2020-03-03 15:28:01;1264/10000
2020-03-03 15:27:51;1264/10000
2020-03-03 15:27:41;1266/10000
2020-03-03 15:27:31;1266/10000
2020-03-03 15:27:21;1266/10000
2020-03-03 15:27:11;1266/10000
2020-03-03 15:27:01;1266/10000
2020-03-03 15:26:51;1266/10000
2020-03-03 15:26:41;1265/10000
2020-03-03 15:26:31;1264/10000
2020-03-03 15:26:21;1264/10000
2020-03-03 15:26:11;1264/10000
2020-03-03 15:26:01;1264/10000
2020-03-03 15:25:51;1266/10000
2020-03-03 15:25:41;1266/10000
2020-03-03 15:25:31;1266/10000
2020-03-03 15:25:21;1266/10000
2020-03-03 15:25:11;1266/10000
2020-03-03 15:25:01;1268/10000
2020-03-03 15:24:51;1266/10000
2020-03-03 15:24:41;1180/10000
2020-03-03 15:24:31;987/10000
2020-03-03 15:24:21;987/10000
2020-03-03 15:24:11;992/10000
2020-03-03 15:24:01;992/10000
2020-03-03 15:23:51;992/10000

Which we can then present in the form we please. The simplest being imported into excel of course and then made into a chart.

Monitoring MySQL open_files_limit from PHP and logging usage over time

The complete script