0

I'm testing performance using php and mysql. The symptom - after sustaining heavy load, all connections are closed and next trials to connect to db fails for a few seconds, then resume.

I've noticed mysql.error.log has thousand lines like this:

017-04-24 13:20:27 122496980240128 [Warning] Aborted connection 202348 to db: 'db' user: 'dbuser' host: '10.0.0.1' (CLOSE_CONNECTION)

I use this script to check what happens in a simple call to db and I've found that this script generate 10 lines like that

    $connect = mysql_connect($dbhost, $dbuser, $dbpass) or die("Unable to Connect to '$dbhost'");
mysql_select_db($dbname) or die("Could not open the db '$dbname'");
$test_query = "SHOW TABLES FROM $dbname";
$result = mysql_query($test_query);
$tblCnt = 0;
while($tbl = mysql_fetch_array($result)) {
  $tblCnt++;
  #echo $tbl[0]."<br />\n";
}
if (!$tblCnt) {
  echo "There are no tables<br />\n";
} else {
  echo "There are $tblCnt tables<br />\n";
}

my question - why is this behavior and is there any way to reduce it to 1 call or max 2 calls as it should.

Dani
  • 14,639
  • 11
  • 62
  • 110
  • 1
    Search for your error message on dba.stackexchange.com, you'll find lots of questions about it. – Barmar Apr 24 '17 at 17:35
  • 1
    ***Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php).*** [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). – Jay Blanchard Apr 24 '17 at 17:58
  • [Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)***. Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! – Jay Blanchard Apr 24 '17 at 17:58
  • thanks for the comments. I've aware of the mysql_ issue, it's a given code, It will take time to improve it (this code is just a test I wrote trying to understand this "abort" issue. – Dani Apr 24 '17 at 19:08
  • Well, the "aborts" may be related to the security issues in "mysql_*" interface. – Rick James Apr 28 '17 at 20:09
  • Do you reconnect for each query? Do you have a huge number of "clients"? What is the value of `max_connections`? If you are using Apache, what is the setting of `MaxClients`? – Rick James Apr 28 '17 at 20:11

0 Answers0