MySQL active connections at once, Windows Server

Question

I have read every possible answer to this question and searched via Google in order to find the correct answer to the following question, but I am rather a novice and don't seem to get a clear understanding. A lot I've read has to do with web servers, but I don't have a web server, but an intranet database.

I have a MySQL dsatabase in a Windows server at work.

I will have many users accessing this database constantly to perform simple queries and writting back to it new records. The read/write will not be that heavy (chances are 50-100 users will do so exactly at the same time, even if 1000's could be connected). The GUI will be either via Excel forms and/or Access.

What I need to know is the maximum number of active connections I can have at any given time to the database. I know I can change the number on Mysql Admin however I really need to know what will really work... I don't want to put 1000 users if the system will really handle 100 correctly (after that, although connected, the performance will be too slow, for example)

Any ideas or own experiences will be appreciated

Answer 1

I think something like 1000 should work ok, as long as you use 64 bit MySQL server. With 32 bit, too many connections may create virtual memory pressure - a connection has an own thread, and every thread needs a stack, so the stack memory will reduce possible size of the buffer pool and other buffers.

MySQL generally does not slow down if you have many idle connections, however special commands e.g "show processlist" or "kill", that enumerate every connection will be somewhat slower.

If idle connection stays idle for too long (idle time exceeds wait_timeout parameter), it is dropped by the server. If this is the case in your possible scenario, you might want to increase wait_timeout (its default value is 8 hours)

Answer 2

This depends mainly on your server hardware (RAM, cpu, networking) and server load for other processes if not dedicated to the database. I think you won't have an absolute answer and the best way is testing.