maria-discuss team mailing list archive

[Geunsik Lim <leemgs@xxxxxxxxx>]

I have summarized a solution after finding out some hints by running
"mytop" command and digging a related source code of mariadb server.
I have started running an existing our web service with the below two
solutions for aging test.

* EVALUATION ENVIRONMENT:
--------------------------------------
invain@mytizen:~$ uname -a
Linux mytizen 4.4.0-45-generic #66-Ubuntu SMP Wed Oct 19 14:12:37 UTC 2016
x86_64 x86_64 x86_64 GNU/Linux

invain@mytizen:~$ cat /etc/lsb-release
DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=16.04
DISTRIB_CODENAME=xenial
DISTRIB_DESCRIPTION="Ubuntu 16.04.1 LTS"

invain@mytizen:~$ mysql --version
mysql  Ver 15.1 Distrib 10.0.29-MariaDB, for debian-linux-gnu (x86_64)
using readline 5.2



* PROBLEM:
-------------------
The MySQL server has gone away (error 2006) has generated to a client.

events.js:160
      throw er; // Unhandled 'error' event
      ^

Error: MySQL server has gone away
    at Error (native)



* SOLUTION:
-------------------
>From our experience, this issue results from two main causes such as 1)
"timed out", 2) "too large packet".
We recommend that you modify the existing configuration file of mariadb
server as following:

FIRST, Mariadb server timed out and closed the connection.
https://dev.mysql.com/doc/refman/5.6/en/server-system-
variables.html#sysvar_wait_timeout

a. interactive_timeout : interactive time out for mysql shell sessions in
seconds like mysqldump or mysql command line tools.
b. wait_timeout: the amount of seconds during inactivity that MySQL will
wait before it will close a connection on a non-interactive connection in
seconds.

To fix this issue, check that wait_timeout mysql variable in your my.cnf
configuration file is large enough.

mariadb> SHOW SESSION VARIABLES LIKE 'wait_timeout'
mariadb> SHOW GLOBAL   VARIABLES LIKE 'wait_timeout'
mariadb> SELECT @@global.wait_timeout, @@global.interactive_timeout,
@@session.wait_timeout, @@session.interactive_timeout;
# 8 hours (=28,800 secs) or maximum wait time out that you need.
mariadb> set global wait_timeout = 28800
mariadb> show full processlist;
mariadb> show variables like "%timeout%";
$  sudo systemctl restart mysql


SECOND, Server dropped an incorrect or too large packet.
https://dev.mysql.com/doc/refman/5.6/en/server-system-
variables.html#sysvar_max_allowed_packet
If mysqld gets a packet that is too large or incorrect, it assumes that
something has gone wrong with the client and closes the connection.
To fix this issue, you can increase the maximal packet size limit
'max_allowed_packet' in my.cnf file,
eg. "set max_allowed_packet = 128M", then restart your MySQL server: sudo
/etc/init.d/mysql restart


$> sudo vi /etc/my.cnf
(/etc/mysql/conf.d/mysqldump.cnf)
(/etc/mysql/mariadb.conf.d/50-server.cnf)
(/etc/mysql/mysql.conf.d/mysqld.cnf)

[mysqld]
max_allowed_packet=128M ( from 8, 16MB to 128MB)

Use "set global max_allowed_packet=104857600" (100MiB)
mariadb> SHOW VARIABLES LIKE 'max_allowed_packet'
mariadb> set global max_allowed_packet=104857600;

-------------------

mariadb> show full processlist;
+------+------+-----------------+-----------------+---------
+-------+-------+-----------------------+----------+
| Id   | User | Host            | db              | Command | Time  | State
| Info                  | Progress |
+------+------+-----------------+-----------------+---------
+-------+-------+-----------------------+----------+
|  751 | tic  | localhost:56454 | pdk             | Sleep   |  1043 |
| NULL                  |    0.000 |
|  781 | root | localhost       | api_development | Sleep   | 20237 |
| NULL                  |    0.000 |
| 1065 | root | localhost       | NULL            | Query   |     0 | init
 | show full processlist |    0.000 |
+------+------+-----------------+-----------------+---------
+-------+-------+-----------------------+----------+
3 rows in set (0.00 sec)

mariadb>
$  sudo systemctl restart mysql



End of line.

On Tue, Apr 11, 2017 at 3:12 PM, Geunsik Lim <leemgs@xxxxxxxxx> wrote:

> Thank you for telling me the webpage. I have got the more appropriate
> method thanks to you. :)
>
> On Tue, Apr 11, 2017 at 2:48 PM, Daniel Black <daniel.black@xxxxxxxxxxx>
> wrote:
>
>>
>>
>> On 11/04/17 15:15, Geunsik Lim wrote:
>> > For reference, We are running web-based front service
>> > with nodejs package + pm2 (http://pm2.keymetrics.io/) on Ubuntu 16.04.1
>> > server.
>> >
>> > It seems that this issue results from the too big packet size and too
>> > long waiting time
>> > between mariadb server and our nodejs-based web-service.
>>
>> its probably one or the other but not both..
>>
>> MySQL document is still applicable
>> https://dev.mysql.com/doc/refman/5.7/en/gone-away.html
>>
>>
>> _______________________________________________
>> Mailing list: https://launchpad.net/~maria-discuss
>> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
>> Unsubscribe : https://launchpad.net/~maria-discuss
>> More help   : https://help.launchpad.net/ListHelp
>>
>
>
>
> --
> http://leemgs.fedorapeople.org
> Don't try to avoid pain if you fail.
> If you decided to face the challenges in life,
> you can gain a lot by giving your best.
> Cheolsang Jeong's Book & life
> --
>



-- 
http://leemgs.fedorapeople.org
Don't try to avoid pain if you fail.
If you decided to face the challenges in life,
you can gain a lot by giving your best.
Cheolsang Jeong's Book & life
--