MySQL Big Data Management

MySQL Big Data Management#

If you try to insert big data chunks to the MySQL server, you might run into the following error message:

Server has gone away

It may be caused by too small a value in the max_allowed_packet option in the MySQL server.

The issue is especially prevalent on older versions of the MariaDB server. The default value for the max_allowed_packet option differs depending on the MariaDB version:

16777216 (16M) >= MariaDB 10.2.4
4194304 (4M) >= MariaDB 10.1.7
1048576 (1M) < MariaDB 10.1.7

Check the Current max_allowed_packet Value#

To check the current value for the max_allowed_packet use the following command:

echo "SHOW VARIABLES LIKE 'max_allowed_packet'" | mysql

The returned value specifies the maximum size of the package that the MySQL server can receive.

Variable_name   Value
max_allowed_packet  1048576

If the size of data sent to the MySQL server is bigger than the max_allowed_packet, then the server throws an error and closes the connection.

Increasing the New max_allowed_packet Value#

To increase the max_allowed_packet value, add the following lines to the MySQL server configuration file (the configuration file by default is located in the /etc/my.cnf path).

[mysqld]
max_allowed_packet=16M

Then restart MySQL server.

systemctl restart mysql

MySQL Too Many Connections#

If a large number of Squirro services and connector jobs connect to the same MySQL server, you might run into the following error message:

UnknownError: (500, u"(OperationalError) (1040, 'Too many connections') None None")

The error means that the number of open client connections has reached the max_connections limit of the MySQL server.

Check the Current max_connections Value#

To check the current value for max_connections, run the following command as root:

echo "SHOW VARIABLES LIKE 'max_connections'" | mysql

The default value is 151.

To list the open connections and the service users that hold them, run the following command as root:

echo "SHOW PROCESSLIST" | mysql

Increasing the max_connections Value#

To increase the max_connections value, add the following lines to the MySQL server configuration file (the configuration file by default is located in the /etc/my.cnf path).

[mysqld]
max_connections = 251