Re: Can the use of Ephemeral ports be disabled?
Posted by:
Rick James
Date: April 08, 2014 05:28PM
I don't know the details of XOJO. Here are some general statements...
Clients come in two flavors:
* One-off, such as the mysql commandline. When you run the client, it makes a connection, when you exit, the connection is terminated.
* Web servers -- These allow multiple 'users' to connect, and they maintain a pool of connections. Furthermore, in big installations, there may be multiple machines each with a web server. In that case the number of pooled connections would be the number of machines times the number of pooled connections each.
On another topic...
As you observed, wait_timeout and max_connections interact with each other. I like to keep wait_timeout at 30 (seconds) or less and then criticize any user who complains about "loosing his connection". Sitting there for more than 30 seconds without performing any SQL is sloppy. (Sure, there are exceptions.)
So what to do?
Dig into XOJO to see if they have some sort of connection pool that could add up to more than 150 connections. First consider decreasing XOJO's setting for such. If that fails, then consider increasing max_connections.
What's the cost of large/small values for max_connections and wait_timout?
General answer: Not much cost.
High max_connections: wasting RAM for keeping "Sleep"ing connections.
Low max_connections: unnecessarily denying access to MySQL.
High wait_timeout: well, this leads to max_connections being too low or high.
Low wait_timout: In your case, clutter of warning messages. In other applications, unexpected connection dropping.
max_connections = 1000 is probably OK. (I have seen that on a lot of machines.) The _median_ value I have seen on these forums is 500. However, the median Max_used_connections (from SHOW GLOBAL STATUS) is only 70; not many go beyond 151.
Nearly everyone leaves back_log at 50. I think this is the overflow beyond max_connections, and before actually rejecting the connection.