Bugzilla – Bug 3171
Persistent connections option for high load SQL servers
Last modified: 2013-07-04 05:48:12 PDT
I've noticed that AMXX's SQL module doesn't use a persistent connection, meaning that it opens a connection for each query it wants. Is it possible to have 1 persistent connection per AMXX instance? I ask because we have 5 servers using 1 SQL server for both admin authentication and the Sven Co-op experience mod. Without a persistent connection there are far too many separate connections being made causing high load on the SQL server. Annoying for others too as it serves many websites too (the hardware is more than sufficiant). Sometimes queries don't make it because of "too many connections" from the user, this also effects the website as it uses the same SQL user. It would be far nicer for us if we had 1 connection per game server, and all the queries it wants queued in that. Would be even better again if such a feature could be turned on/off via a CVAR.
Did you write the plugin using SQL or is it third party?
One of them is the official administration plugin (admin.sma with #define USING SQL uncommented). The other is 3rd party, but uses the same connection details from admin.sma.
admin.sma only connects once per mapchange. What's the other plugin?
Sven Co-op XP Mod. A query is made every time a player... ...connects (get their XP). ...disconnects (save their XP). ...gains a level (save their XP). ...selects an upgrade (saves their XP). Doesn't make much load for 1 server, but 5 servers of 12 players (especially on maps that provide high scores) will quickly generate multiple 'Too many connections' errors. I've noticed that after a query is performed AMXX doesn't close the link. It leaves it there until it times out.
I'd like to request this issue priority become high. I've noticed that when 'too many connections' happens (because AMXX is making 1 connection per query) that when a user joins, their XP isn't read due to the connection failure. Then later on when the connection levels fall and the player leaves, their level 0 is saved wiping what their XP should have been. If persistent connections won't be an option, why the hell is the module leaving connections sleeping after performing them? Wouldn't it make sense to close the connection right after the query has completed?
The zombie connections sounds like a high priority issue, but the persistent connection request doesn't. That plugin sounds like it's doing entirely too many queries, which just agitates the zombie connection part. I see no reason why the plugin couldn't cache the xp / upgrade information until the client disconnect.
Partly because if the player disconnects unexpectedly (i.e. during a map change) then their XP might not have been saved for a while. You can get a lot of XP within an hour of play. It's not that the SQL server can't handle it, it's just that it's being left with zombie connections as you've said. AMX either needs to close connections once it's finished with them, or re-use a single connection. After all, it's the SQLx module choosing to make a new connection per query and not closing it.
I guess this can be handled creating a Tuple and connecting to mysql at plugin_init. Reconnections would be handled by mysql C api (depending on mysql version but you're safe with >= 5.x.