php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #74971 (HY000/2002): Resource temporarily unavailable
Submitted: 2017-07-22 14:10 UTC Modified: -
Votes:7
Avg. Score:4.4 ± 0.7
Reproduced:7 of 7 (100.0%)
Same Version:4 (57.1%)
Same OS:6 (85.7%)
From: spam2 at rhsoft dot net Assigned:
Status: Open Package: MySQLi related
PHP Version: 7.0.21 OS: Linux
Private report: No CVE-ID: None
 [2017-07-22 14:10 UTC] spam2 at rhsoft dot net
Description:
------------
mysqlnd/mysqli scales badly on unix-socket under load

that can only be rproduced on fast machines and with http-keep-alive because otherwise the connection overhead mask the issue and which extremely optimized code 

but originally i have seen the issue on our production cms with exactly the benchmark below - the reproducer just makes it easier beause it's even more lightweight than the core-cms code
__________________________________

my.cnf (MariaDB 10.2)

max_connections = 300
thread_handling = pool-of-threads
thread_pool_idle_timeout = 900
__________________________________

<?php declare(strict_types=1);
require __DIR__ . '/php/serverconf.inc.php';
$conn = mysqli_init();
mysqli_options($conn, MYSQLI_OPT_INT_AND_FLOAT_NATIVE, true);
if(mysqli_real_connect($conn, 'localhost', $sql_user, $sql_pwd, $sql_db, 3600, '', 0) === true)
{
 echo 'OK';
}
else
{
 echo 'FAILED';
}
__________________________________

ab -c 250 -n 1000000 -k http://corecms/connect-bench.php
__________________________________

[22-Jul-2017 15:57:49 Europe/Vienna] PHP Warning:  mysqli_real_connect() [<a href='http://at.php.net/manual/de/function.mysqli-real-connect.php'>function.mysqli-real-connect.php</a>]: (HY000/2002): Resource temporarily unavailable in /mnt/data/www/corecms.rhsoft.net/connect-bench.php on line 5
[22-Jul-2017 15:57:49 Europe/Vienna] PHP Warning:  mysqli_real_connect() [<a href='http://at.php.net/manual/de/function.mysqli-real-connect.php'>function.mysqli-real-connect.php</a>]: (HY000/2002): Resource temporarily unavailable in /mnt/data/www/corecms.rhsoft.net/connect-bench.php on line 5
[22-Jul-2017 15:57:50 Europe/Vienna] PHP Warning:  mysqli_real_connect() [<a href='http://at.php.net/manual/de/function.mysqli-real-connect.php'>function.mysqli-real-connect.php</a>]: (HY000/2002): Resource temporarily unavailable in /mnt/data/www/corecms.rhsoft.net/connect-bench.php on line 5
__________________________________

MariaDB guys state that this is a client issue (while that is likely true i don't believe in timeouts given the 11696 requests/second)

Am 17.07.2017 um 15:02 schrieb Vladislav Vaintroub:
> You are getting HY000/2002, the range starting with 2000 (ending iirc 3000) is the error originating on client.  If I was to guess it is a client-side connection timeout



Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2017-07-23 16:21 UTC] spam2 at rhsoft dot net
see also https://jira.mariadb.org/browse/MDEV-13375

DUNNO on which side the problem exists but as long as a benchmarked CMS responds to additional browser requests where the whole desktop also runs on said machine responds within 0.003 seconds there is no good reason to talk about overloading and failed requests - and 250 concurrent requests are not really much at all
 [2018-10-26 12:40 UTC] pierrie at gmail dot com
I am experiencing the same problem conditions on my server with all custom code, i was using PHP5 before upgrading to PHP7 and on PHP5 i did not have this problem.

I have started porting all my PHP MySql connections to PDO is i have tried many suggestions, all to no avail. I believe there is a problem with MySqli on PHP7. It cannot handle high volume of multiple connections.

I have +- 300 connections per second with php calls and Mysqli could not handle it, the error log was filled with (HY000/2002): Resource temporarily unavailable.

I have a Mysql connection limit of 10000 and according to Mysql stats the highest amount of concurrent connections i have ever had is +-7000, normal load +-600 connections open at any time.

Hope this helps someone as i have spend days searching the web with this.
 [2018-10-26 13:10 UTC] spam2 at rhsoft dot net
it turns out that MariaDB 10.2.8 upwards improves that
https://jira.mariadb.org/browse/MDEV-13375

-------- Weitergeleitete Nachricht --------
Betreff: Re: [Maria-discuss] connection handling is buggy (HY000/2002): Resource temporarily unavailable)
Datum: Sun, 20 Aug 2017 00:02:13 +0200

Am 17.07.2017 um 15:22 schrieb Vladislav Vaintroub:
> On 17.07.2017 15:14, Reindl Harald wrote:
>>
>> Am 17.07.2017 um 15:02 schrieb Vladislav Vaintroub:
>>> You are getting HY000/2002, the range starting with 2000 (ending iirc 
>>> 3000) is the error originating on client.  If I was to guess it is a 
>>> client-side connection timeout
>>
>> are you aware that the strace output below with "EAGAIN (Resource 
>> temporarily unavailable)" is from running strace on the myslqd PID?
> And what does it prove? With server doing non-blocking socket reads, 
> EAGAIN is expected , and handled by switching to poll(). Since long time 
> (if memory serves me right 10.0/MySQL 5.6), this is how 
> net_read_timeout/net_write_timeout are handled on Unixes,on the server 
> side

after 10.2.8 fixed https://jira.mariadb.org/browse/MDEV-13375 it proves that all the talking about timeouts and issue on the client side was nonsense as i said from the very begin

[harry@srv-rhsoft:~]$ ab -c 150 -n 1000000 -k http://corecms
Concurrency Level:      150
Time taken for tests:   214.193 seconds
Complete requests:      1000000
Failed requests:        0
Keep-Alive requests:    1000000
Total transferred:      4188197222 bytes
HTML transferred:       3777430423 bytes
Requests per second:    4668.69 [#/sec] (mean)
Time per request:       32.129 [ms] (mean)
Time per request:       0.214 [ms] (mean, across all concurrent requests)
Transfer rate:          19095.09 [Kbytes/sec] received
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Tue Oct 15 15:01:28 2024 UTC