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;
};