2

here's a sixcore with 32 GB RAM. I've installed MySQL 5.1.47 (backport). Config is nearly standard, except max_connections, which is set to 2000. On the other hand there is PHP5.3/FastCGI on nginx. There is a very simple php application which should be served. NGINX can handle thousands of request parallel on this machine. This application accesses MySQL via mysqli. When using non-persistent connections in mysqli there is a problem when reaching 100 concurrent connections.

[error] 14074#0: *296 FastCGI sent in stderr: "PHP Warning: mysqli::mysqli(): [2002] Resource temporarily unavailable (trying to connect via unix:///tmp/mysqld.sock) in /var/www/libs/db.php on line 7

I've no idea to solve this. Connecting via tcp to mysql is terrible slow. The interesting thing is, when using persistent connections (add 'p:' to hostname in mysqli) the first 5000-10000 thousand requests fail with the same error as above until max connections (from webserver, set to 1500) is reached. After the first requests MySQL keeps it 1500 open connections and all is fine, so that I can make my 1500 concurrent requests. Huh? Is it possible, that this is a problem with PHP FastCGI?

Improve this question
4
  • 1
    I wrote, that the "resource temporarily unavailable" error occurs every time, already when using only some hundred non-persistent connections. Now, I've added usleep(rand(500000,1500000)); before mysql connect call in my php script, so that this script sleeps for a random time (0.5-1.5 seconds) before connecting to mysql. And tadaaa, the problem doesn't occur with non-persistent connection. So could it possibly be, that MySQL has a problem with massive concurrent thread creation?
    – user48303
    Jul 12, 2010 at 6:43
  • Or maybe it's that MySQL (sensibly) rate-limits your connections to protect itself? See mysqlha.blogspot.com/2007/08/rate-limiting-for-innodb.html
    – Borealid
    Jul 12, 2010 at 6:55
  • I don't think so, because the problem is already the connection to MySQL and not a query. At this point it doesn't have to do something with InnoDB (IMHO).
    – user48303
    Jul 14, 2010 at 5:16
  • Thanks for your replies. For me it looks like it's a problem with concurrent thread creating by MySQL. Because when 1500 persistent connections are established no more error occurs and these connections can be used by 1500 scripts at one time, so I don't believe in a problem with parallel access to socket or a TCP problem. To sum up - ulimit=32768, max_connections=2000, php_fastcgi_children=1500 - execution of 1500 concurrent requests with non-persistent connections always fails with this "Resource temporarily unavailable" (only a small portion of request is handled) - when adding random delay
    – user48303
    Jul 14, 2010 at 5:33

4 Answers 4

Reset to default
1

This sounds like maybe connections aren't being cleaned up in a timely manner. "show processlist" should show the connections if that's the case. Looking at the MySQL documentation, it appears this may be common with PHP unless certain PHP parameters are tweaked. You may want to look through the article and comments at http://dev.mysql.com/doc/refman/5.0/en/too-many-connections.html

Another possibility brought up by the very slow TCP connections is perhaps MySQL (or tcpwrappers) is trying to do hostname lookup for access and the lookup is slow. This seems unlikely when using unix sockets, but in case it's trying to match localhost or system hostnames it may be worth looking into whether any access rules could be moved to IP or removed.

Improve this answer
0

It's possible that, if you connect 10,000 times at once, 1500 of those 10,000 will be accepted and the remaining 8500 will be rejected (as in, will not be delayed until the persistent connection is available). Then, subsequent requests will use the 1500-large persistent pool.

Is there a problem with opening the persistent connections once when you start up the server?

Improve this answer
3
  • Hi, thanks for reply. I use apache bench (ab) for testing and make only 1000 requests per second. Response is send in 0.7-0.8 seconds, so the limit of 1500 connections is never reached. Yes, when the first connections arrive the fresh started server, most of the requests are rejected until max connections (1500) are established. After all connections are established the failure never occurs again with persistent connections.
    – Anonymous
    Jul 12, 2010 at 6:38
  • @cuti123: so why don't you set up the persistent connections before starting the application for real? The problem may be that your DB server is limiting the maximum rate of opening new connections.
    – Borealid
    Jul 12, 2010 at 6:39
  • I thought about that, sure. But I want to fathom,why this error occurs.
    – Anonymous
    Jul 12, 2010 at 6:49
0

what OS is this running on top of? Windows has a terribly long time out on TCP connections. Something on the order of 90 seconds. You have to make some registry edits to bring back down to something shorter. Also helps to raise the port limit of 10k all to something higher.

Improve this answer
1
  • I'm using Debian Lenny. The following additional settings were made: echo 131072 > /proc/sys/net/nf_conntrack_max echo 131072 > /proc/sys/net/netfilter/nf_conntrack_max echo 3 > /proc/sys/net/netfilter/nf_conntrack_tcp_timeout_time_wait echo 1 > /proc/sys/net/ipv4/tcp_tw_recycle echo 1 > /proc/sys/net/ipv4/tcp_tw_reuse But remember, all interprocess communication is made via sockets, so these settings doesn't affect this.
    – user48303
    Jul 14, 2010 at 6:20
0

Have you also adjusted the max_user_connections parameter? It could be that nginx is treated as a single user, and is capped by that parameter.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .