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