5

I have MySQL 5.1.35 on Linux Centos. The Linux server has 2GB RAM with 14GB of disk space.

I have created some web services using the Restlet framework in Java that has 1,000+ users.

What should I set max_connections to for maximum concurrent connections?

5

There's no formula here. You should limit your database according to what you think is reasonable according to your application needs.

Typically, servers with applications using a connection pool shouldn't need more than a few hundred concurrent connections. Small-Medium sized websites may suffice with 100 - 200.

I usually setup a new server with some 500 - 800 value of max_connections and see how it goes. You can always change dynamically via

set global max_connections := 567;

Make sure, though, you set up a proper open_files_limit. On linux, your process is limited to 1024 files, by default. This is very low, since every thread, connection, and, of course, file -- make for a file handle in linux. So set open_files_limit to some generous number (say 8192) to clear up your many connections with the operating system.

I should note I have worked with MySQL servers with thousands of open connections - it's cool. But, most of the time, the vast majority of these connections would sit and do nothing (be idle).

To sum up, I would use what appears to be normal application needs + some threshold for spike events.

  • My open_files_limit and max_connections are 1024 and 214 respectively. I want to avoid unix server reboot so i have dynamically changed max_connections to 4000 in mysql. Is this enough to avoid max_connections issue. Or server reboot is necessary? – kasi Jul 20 '18 at 9:21
  • You do not need to reboot the unix server, you will need to restart the mysql daemon. – Shlomi Noach Oct 6 '18 at 14:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.