Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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
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.
