php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login

go to bug id or search bugs for

Bug #76442 Slow MySQL perfomance via named pipe
Submitted: 2018-06-10 10:45 UTC Modified: 2021-07-21 16:33 UTC
From: yaro at opti dot su Assigned: cmb (profile)
Status: Wont fix Package: MySQLi related
PHP Version: 7.2.6 OS: Windows Server 2008 R2
Private report: No CVE-ID: None
View Add Comment Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
You can add a comment by following this link or if you reported this bug, you can edit this bug over here.
(description)
Block user comment
Status: Assign to:
Package:
Bug Type:
Summary:
From: yaro at opti dot su
New email:
PHP Version: OS:

 

 [2018-06-10 10:45 UTC] yaro at opti dot su 
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:
- Windows Server 2008 R2 ~ 50 msec for mysqli_connect()
- Windows Server 2012 R2 ~ 5 msec for mysqli_connect()

I think this is a bug, because in the previous version of PHP everything worked is perfect!
Excuse for bad English.

Test script:
---------------
$time = microtime(true);
$link = mysqli_connect($host, $user, $pass);
$time = (microtime(true) - $time) * 1000;
echo 'Connect = '.$time.' ms.';

$time = microtime(true);
$link = mysqli_query($link, 'select 1');
$time = (microtime(true) - $time) * 1000;
echo 'Select = '.$time.' ms.';

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2018-06-10 10:50 UTC] yaro at opti dot su 
Additional info:
- Windows Server 2008 R2 ~ 15 msec for mysqli_query()
- Windows Server 2012 R2 ~ 1 msec for mysqli_query()
 [2018-06-11 09:57 UTC] yaro at opti dot su 
Additional info:
- Windows Server 2016 ~ 40 msec for mysqli_connect()
- Windows Server 2016 ~ 16 msec for mysqli_query()
php v7.2.5 x64, mysql v5.7.22 x64, VC14.13.26020
 [2018-06-11 23:18 UTC] yaro at opti dot su 
Tested on php x64:
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

=== 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 '<pre>';
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 '<pre>';

more code can be obtained from this link:
http://opti.su/public/bench_db.txt
 [2018-06-12 01:19 UTC] yaro at opti dot su 
Tested on php x86:
PHP: v7.1.11-x86
MySQL: v5.6.24 x64
OS: Windows Server 2012 R2

PHP version      : 7.1.11 (32 bit)
MySQL version    : 5.6.24-log
MySQL server     : \\.\pipe\MySQL via named pipe
Latency (connect): 6.2270164489746 ms
Latency (close)  : 0.030040740966797 ms
Latency (query)  : 1.0011196136475 ms
Speed (select)   : 1039 req/sec
Speed (insert)   : 1000 req/sec
Speed (update)   : 1066 req/sec
 [2018-07-03 11:44 UTC] cmb@php.net 
Related To: Bug #76565
 [2021-06-28 20:05 UTC] cmb@php.net
-Status: Open +Status: Verified -Assigned To: +Assigned To: cmb
 [2021-06-28 20:05 UTC] cmb@php.net 
I can confirm that named pipes are considerably slower than TCP
with PHP-7.4.  And although the MySQL manual mentions[1] (to my
surprise):

| The default is to use TCP/IP regardless of platform because
| named pipes are slower than TCP/IP in many Windows configurations.

I can also confirm that named pipes were slightly faster than TCP
with PHP 5.4.45.

[1] <https://dev.mysql.com/doc/mysql-windows-excerpt/5.7/en/windows-select-server.html>
 [2021-07-21 16:33 UTC] cmb@php.net
-Status: Verified +Status: Wont fix
 [2021-07-21 16:33 UTC] cmb@php.net 
I have not been able to find the root cause, but given that MySQL
defaults to TCP/IP anyway, and this ticket didn't get any traction
for three years, I'm closing as WONTFIX.

Furthermore, especially on the older Windows systems, the measured
times could be incorrect.  Use hrtime() for such measurements
instead.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved. 		Last updated: Wed Apr 24 11:01:30 2024 UTC