Slow MySQL perfomance via named pipe Description: ------------ On PHP v5.4.x mysqli_connect() and mysqli_query() worked momentally via named pipes for all version of windows (at least for MySQL v5.5, v5.6, v5.7), but in PHP v5.6.x, v7.0.x, v7.1.x, v7.2.x works very slowly: ~ 50 msec for mysqli_connect() ~ 16 msec for mysqli_query() Tested on: PHP: v5.6.36-x64, v7.0.30-x64, v7.1.18-x64, v7.2.6-x64 MySQL: v5.6.24 x64, v5.7.22 x64 OS: Windows Server 2008 R2, Windows Server 2016 32-bit versions of PHP have not yet been tested === Output of Test script === PHP version : 7.2.6 MySQL version : 5.7.22 MySQL server : \\.\pipe\MySQL via named pipe Latency (connect): 46.875 ms Latency (close) : 0 ms Latency (query) : 16.15625 ms Speed (select) : 64 req/sec Speed (insert) : 63 req/sec Speed (update) : 62 req/sec PHP version : 7.2.6 MySQL version : 5.7.22 MySQL server : localhost via TCP/IP Latency (connect): 3.90625 ms Latency (close) : 0 ms Latency (query) : 0.03125 ms Speed (select) : 21333 req/sec Speed (insert) : 12800 req/sec Speed (update) : 10667 req/sec I think this is a bug, because in the previous version of PHP everything worked is perfect! Test script: --------------- $cli = PHP_SAPI == 'cli'; $ln = chr(13).chr(10); if (!$cli) echo '
';
echo bench_db_format(bench_db('.', 'root', 'pass', 'db_name'), $ln).$ln;
echo bench_db_format(bench_db('localhost', 'root', 'pass', 'db_name'), $ln).$ln;
if (!$cli) echo '
';

function bench_db($host, $user, $pass, $db, $cycles = 5, $count = 200, $innodb = True, $pause = 100) {
	$innodb = $innodb?'INNODB':'MYISAM';
	$table = 'opti_db_benchmark_tmp';

	$result['connect']['awsf'] = 0;
	$result['close']['awsf'] = 0;
	$i = 0;
	while ($i++ < $cycles) {
		$time = microtime(true);
		$link = mysqli_connect($host, $user, $pass);
		$time = (microtime(true) - $time) * 1000;
		if (!$link) return null;
		$result['connect']['data'][] = $time;
		$result['connect']['awsf'] += $time;

		if (!isset($info)) $info = mysqli_get_host_info($link);

		$time = microtime(true);
		mysqli_close($link);
		$time = (microtime(true) - $time) * 1000;
		$result['close']['data'][] = $time;
		$result['close']['awsf'] += $time;

		if ($pause) usleep($pause);
	};
	
	foreach ($result as &$r) {
		$r['frst']  = $r['data'][0];
		$r['awof']  = ($r['awsf'] - (($cycles>1)?$r['frst']:0)) / ((($cycles>1)?$cycles:2) - 1);
		$r['awsf'] /= ($cycles>0)?$cycles:1;
		$r['last']  = $r['data'][(($cycles>0)?$cycles:1)-1];
	};
	unset($r);
	
	$result['server'] = $info;

	$link = mysqli_connect($host, $user, $pass, $db);
	if (!$link) return null;
	if (mysqli_query($link, "SHOW TABLES LIKE '$table'"))
		mysqli_query($link, "DROP TABLE `$table`");
	
    $res = mysqli_query($link, 'SELECT VERSION() as version;');
	if (!$res) return mysqli_error($link);
	$result['version'] = mysqli_fetch_all($res)[0][0];
    
	$time = microtime(true);
	$query = "CREATE TABLE `$table` (`ID` int(18) NOT NULL, `REFERENCE_ID` int(18) DEFAULT NULL, `NAME` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL) ENGINE=$innodb DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
	if (!mysqli_query($link, $query)) return mysqli_error($link);
	$time = (microtime(true) - $time) * 1000;
	$result['create_table'] = $time;
	
	$time = microtime(true);
	$query = "ALTER TABLE `$table` ADD PRIMARY KEY (`ID`), ADD KEY `IX_$table` (`REFERENCE_ID`)";
	if (!mysqli_query($link, $query)) return mysqli_error($link);
	$time = (microtime(true) - $time) * 1000;
	$result['create_index'] = $time;

	$time = microtime(true);
	$query = "ALTER TABLE `$table` MODIFY `ID` int(18) NOT NULL AUTO_INCREMENT";
	if (!mysqli_query($link, $query)) return mysqli_error($link);
	$time = (microtime(true) - $time) * 1000;
	$result['create_autoinc'] = $time;

	$sql['insert'] = "insert into `$table` (REFERENCE_ID, NAME) values (#i#-1, '".str_repeat("x", 200)."')";
	$sql['select'] = "select * from `$table` WHERE ID = #i#";
	$sql['simple'] = "select #i#";
	$sql['update'] = "update `$table` set REFERENCE_ID = ID+1, NAME = '".str_repeat("y", 200)."' WHERE ID = #i#";

	foreach ($sql as $sk => $sv) {
		$result[$sk]['min'] = 100000;
		$result[$sk]['max'] = 0;
		$result[$sk]['avg'] = 0;
		for($j = 0; $j < $cycles; $j++) {
			$fetch = ($sk == 'no fetch for measure');
			$time1 = microtime(true);
			for($i = 0; $i < $count; $i++) {
				$query = str_replace("#i#", $i, $sv);
				$rs = true;
				if (!$rs) return mysqli_error($link);
				if ($fetch) mysqli_fetch_all($rs);
			};	
			$time1 = microtime(true) - $time1;

			$fetch = ($sk[0] == 's'); // fetch for select queries
			$time2 = microtime(true);
			for($i = 0; $i < $count; $i++) {
				$query = str_replace("#i#", $i, $sv);
				$res = mysqli_query($link, $query);
				if (!$res) return mysqli_error($link);
				if ($fetch) mysqli_fetch_all($res);
			};
			$time2 = microtime(true) - $time2;
			
			$time = ($time2 - $time1) * 1000 / $count;
			$result[$sk]['data'][] = $time;
			if ($result[$sk]['min'] > $time) $result[$sk]['min'] = $time;
			if ($result[$sk]['max'] < $time) $result[$sk]['max'] = $time;
		};
		$result[$sk]['avg'] = array_sum($result[$sk]['data']) / $cycles;
		$result['bitrix'][$sk] = round(1000 / $result[$sk]['avg']);
	};

	$time = microtime(true);
	$query = "SELECT * FROM `$table` as t1, `$table` as t2 limit 1000000";
	$res = mysqli_query($link, $query);
	if (!$res) return mysqli_error($link);
	if ($fetch) mysqli_fetch_all($res);
	$time = (microtime(true) - $time);
	$result['select']['big'] = $time;
	
	$time = microtime(true);
	if (!mysqli_query($link, "DROP INDEX `IX_$table` ON `$table`")) return null;
	$time = (microtime(true) - $time) * 1000;
	$result['drop_index'] = $time;

	$time = microtime(true);
	if (!mysqli_query($link, "DROP TABLE `$table`")) return null;
	$time = (microtime(true) - $time) * 1000;
	$result['drop_table'] = $time;

	$time = microtime(true);
	if (!mysqli_query($link, "SELECT BENCHMARK(1000000,ENCODE('hello',RAND()))")) return null;
	$time = (microtime(true) - $time);
	$result['benchmark'] = $time;

	mysqli_close($link);

	return $result; // in seconds/milliseconds/reqests
};

function bench_db_format($bench_db, $ln) {
	$res = '';	
	$res .= 'PHP version      : '.PHP_VERSION.$ln;
	$res .= 'MySQL version    : '.$bench_db['version'].$ln;
	$res .= 'MySQL server     : '.$bench_db['server'].$ln;
	$res .= 'Latency (connect): '.$bench_db['connect']['awof'].' ms'.$ln;
	$res .= 'Latency (close)  : '.$bench_db['close']['awof'].' ms'.$ln;
	$res .= 'Latency (query)  : '.$bench_db['simple']['avg'].' ms'.$ln;
	$res .= 'Speed (select)   : '.$bench_db['bitrix']['select'].' req/sec'.$ln;
	$res .= 'Speed (insert)   : '.$bench_db['bitrix']['insert'].' req/sec'.$ln;
	$res .= 'Speed (update)   : '.$bench_db['bitrix']['update'].' req/sec'.$ln;
	return $res;
};