Abnormal behavior of nodej and MySQL, loss of connection

Question:

Met with abnormal behavior in NodeJS and MySQL. The question is very specific, I will be glad to any guesses.

In general terms, the structure of the work is as follows. The server script on NodeJS connects to MemcacheQ, reads the queue, and if something appears there, it executes a query for fetching in MySQL + several queries to add information to the log table.

The following happens about twice a month. At some point, the request information processing code stops executing.

my.query(select_query, function(error, result, fields) {
  // код обработки информации, полученной из запроса
});

I made the output of console.log (my) before the request and compared the standard and problem situations. The difference was that the value "Queue, Queue, …, Queue" appeared in one of the parameters. Now I will not say for sure, the log has not been preserved. I will bet again, because the question was left in connection with the priorities set.

Create connection: var my = require (script_path + '/ node_modules / mysql2'). CreateConnection ({user: dbuser, password: dbpass, database: dbname, host: dbhost}); At first it was simple mysql, then I used mysql2. The change was unsuccessful.

Then I turned on error handling: my.on ('error', function (err) {

    if(err.code === 'PROTOCOL_CONNECTION_LOST') { // Connection to the MySQL server is usually

        console.log('Lost connection MySQL Database');
        console.log('db error', err);
        sighandle();

    } else {                                      // connnection idle timeout (the wait_timeout

        console.log('Database disconnect. Another error');
        console.log('db error', err);
        sighandle();

    }

});

I thought I'd catch something, but nothing came. It feels like there is a certain parameter, upon reaching which the queue is clogged and requests are not executed.

Moreover, apache + PHP + MySQL is running on the same server, which reads and writes to this database. At the moment when an abnormal situation occurs, the web interface (apache + PHP + MySQL) works correctly.

In terms of load, the NodeJS script reads from MemcacheQ about 20 times in about 1 second. There are about 15-20 such scripts launched. The database is about 300GB in size.

At one time, as I dealt with the problem, I updated the packages on the server through emerge -NuavD world (the server is a gentleman). Did not help.

I tried to update nodejs from version 4 first to the 6th, then I patched the files and was able to update to 7.9. I haven't moved to eighth yet.

There is still a moment when the old information was archived and a bunch of requests to delete and add information were executed (I think 200-250 million requests) this month the failure occurred much earlier than 15 days later ..

It all happened after the move to the new server. Everything was normal on the old one. Old: Intel® Xeon® E5-1650 v2, 128Gb DDR4, 2Tb SATA New: Intel® Xeon® E5-1650 v3, 256Gb DDR4, 480SSD DataCenter Ed But at the time of the move, all MySQL and nodejs versions were the same. The essential difference is tough.

I understand that it is difficult to give an unambiguous answer. But maybe there will be at least some assumptions. I did an automatic restart when this happens. But due to the peculiarities of the system, the downtime is still a couple of minutes. During these couple of minutes, 100-200 requests arrive. And inconveniences are delivered to users.

I would like to return to this question, can someone tell me something. The console.log (my) output was delivered now, but the next. the drop will be in about 10-12 days ..

Thanks in advance.

Answer:

Perhaps the connection to MySQL falls off on timeout. Read about pool connect https://github.com/mysqljs/mysql#pooling-connections , it helped me in a similar situation

The SET SESSION wait_timeout = 604800 regulated by the db.query line ( SET SESSION wait_timeout = 604800 ); If you put the number less, you can catch your mistake faster.

Scroll to Top
AllEscort