Mysql – MariaDB – cannot set max_connections and wait_timeout through the.cnf

mariadbMySQL

I am struggling to set max_connections and wait_timeout parameters in /etc/my.cnf but MariaDB does not seem to read the parameters from the file (it reads some of the others, I haven't checked all).

My /etc/my.cnf file:

[mysqld]
#skip-grant-tables
datadir=/data/mysql
socket=/data/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# network
connect_timeout = 61
wait_timeout = 86400
max_connections = 100000
max_allowed_packet = 64M
max_connect_errors = 1000

# limits
tmp_table_size = 512M
max_heap_table_size = 256M
table_cache = 512

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

[client]
port = 3306
socket= /data/mysql/mysql.sock

But when I check max_connections and wait_timeout variables in MariaDB, it shows the default value:

MariaDB [(none)]> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 214   |
+-----------------+-------+
1 row in set (0.00 sec)

MariaDB [(none)]> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.00 sec)

However, other parameters in my.cnf are correct:

MariaDB [(none)]> show variables like 'max_allowed_packet';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 67108864 |
+--------------------+----------+
1 row in set (0.00 sec)


MariaDB [(none)]> show variables like 'max_connect_errors';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 1000  |
+--------------------+-------+
1 row in set (0.00 sec)

MariaDB [(none)]> show variables like 'connect_timeout';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| connect_timeout | 61    |
+-----------------+-------+
1 row in set (0.00 sec)

I can set max_connections variable from mysql command line but it resets itself when I restart the service:

MariaDB [(none)]> set global max_connections := 10000;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 10000 |
+-----------------+-------+
1 row in set (0.00 sec)

Hower, for wait_timeout this does not work:

MariaDB [(none)]> set global wait_timeout = 86400;                                                                            Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show variables like 'wait_timeout';                                                                         +---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.00 sec)

wait timeout is set as a session variable:

MariaDB [(none)]> SELECT * FROM information_schema.global_variables WHERE variable_name='wait_timeout' UNION SELECT * FROM information_schema.session_variables WHERE variable_name='wait_timeout';
+---------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+---------------+----------------+
| WAIT_TIMEOUT  | 86400          |
| WAIT_TIMEOUT  | 28800          |
+---------------+----------------+
2 rows in set (0.00 sec)

Checked all my.cnf files, none of the is overriding the parameters:

[/] # locate my.cnf
/etc/my.cnf
/etc/my.cnf.d
/etc/my.cnf~
/etc/my.cnf.d/client.cnf
/etc/my.cnf.d/mysql-clients.cnf
/etc/my.cnf.d/server.cnf
/root/.my.cnf

OS: RHEL 7

MariaDB version: mariadb-server-5.5.47-1.el7_2.x86_64

Best Answer

wait_timeout is a screwball one:

"On thread startup, the session wait_timeout value is initialized from the global wait_timeout value or from the global interactive_timeout value, depending on the type of client (as defined by the CLIENT_INTERACTIVE connect option to mysql_real_connect()). See also interactive_timeout. "

max_connections claims to have an upper bound of 100000, but I would say that even 10000 is unreasonably large. Does it work to put max_connections=1000 in my.cnf?

You understand that you need to, after changing my.cnf,

  1. restart mysqld
  2. disconnect and reconnect to see the change
  3. use SHOW GLOBAL, not SHOW, which defaults to SHOW SESSION.

The interaction between GLOBAL and SESSION (for both VARIABLES and STATUS) varies with the setting. For many, not all things, session is initialized to global when you login. And wait_timeout is probably the quirkiest.

Warning

If you had 100000 connections each running complex SELECTs needing, say, 3 tmp tables, then you might need 100000 * 3 * 256M = 76.8TB of RAM to handle the tmp table! ( 256M = min(tmp_table_size, max_heap_table_size) ). This is a strong reason not to set all those things so high.

Related Question