Last week I updated mysql max_connections to 1000 from 151 using

use mysql;
set global max_connections = 1000;

Today there was a power outage at the hosting company and the servers shut down and were then restarted. After restart, the max_connections variable was reset to 151.

Is this default behavior? How can I prevent max_connections global from getting reset?

Reference:

MySQL 5.7
up vote 1 down vote accepted

Yes this is the default behavior. How ? The default value for max_connections is 151.

This is true for MySQL 5.7, MySQL 5.6, and MySQL 5.5

If /etc/my.cnf does not have max_connections, then 151 comes up as max_connections.

You can just run set global max_connections = 1000;.

Your hosting company must put that entry in /etc/my.cnf for you

[mysqld]
max_connections=1000

Otherwise, every restart of mysqld will reset max_connections to 151.

I have suggested this before

To address the other part of your question...

Whether or not the hosting company lets you change the variable, let's discuss why more than 151 is needed.

If this is a web site then each page should connect, do some stuff, and quickly disconnect. It is a rare web site that need more than 20 connections.

If the web server, such as Apache, is configured poorly, such as MaxClients = some big number, that that should be fixed. 152 (or even 30) web clients vying for resources will simply bog down, stretching out latency, without imporoving throughput.

If you have "slow" queries that keep connections open a long time; let's look at them; speeding them up may solve your "need" for more than 151 connections.

If you have a seriously overloaded system, consider Replication, wherein you could have multiple Slaves, each providing 151 connections for readonly queries.

PS: MySQL 8.0 will let you set globals in a different way.

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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