3

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

2 Answers 2

1

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

0
0

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

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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