Lambda function “Error: connect ETIMEDOUT” when receive too many requests for MySQL query

Question

I am trying to do testing on my AWS lambda function which makes a query to RDS MySQL (t2.medium). However, if I request the API a large number of times, although I can get query successfully with correct data, it sometimes results "Error: connect ETIMEDOUT".

Is there anything wrong in my code or setting?

I read some suggestions to set some parameters:

MySQL:

wait_timeout 1

max_connections 16000

interactive_timeout 6000

max_allowed_packet 1073741824

Lamdba:

Timeout 60 sec place the Lambda function in the same VPC as your RDS

Added VPC execution policy AWSLambdaVPCAccessExecutionRole

assign a security group to the lambda function

In the security attached to the RDS instance, added an inbound rule for mysql

Confirm that Lambda function has access to the same VPC RDS database

Lambda Error Log

2019-03-28T18:51:47.353Z ab4fbbaf-1ea2-458b-a5b5-781cdfdd80df { Error: connect ETIMEDOUT

at Connection._handleConnectTimeout

(/var/task/node_modules/mysql/lib/Connection.js:411:13)

at Object.onceWrapper (events.js:313:30)

at emitNone (events.js:106:13)

at Socket.emit (events.js:208:7)

at Socket._onTimeout (net.js:420:8)

at ontimeout (timers.js:482:11)

at tryOnTimeout (timers.js:317:5)

at Timer.listOnTimeout (timers.js:277:5)

at Protocol._enqueue (/var/task/node_modules/mysql/lib/protocol/Protocol.js:144:48)

at Protocol.handshake (/var/task/node_modules/mysql/lib/protocol/Protocol.js:51:23)

at Connection.connect (/var/task/node_modules/mysql/lib/Connection.js:118:18)

at Connection._implyConnect (/var/task/node_modules/mysql/lib/Connection.js:453:10)

at Connection.query (/var/task/node_modules/mysql/lib/Connection.js:198:8)

at Promise (/var/task/db.js:62:9)

at new Promise ()

at Object.retrieve (/var/task/db.js:55:10)

at exports.getAlerts (/var/task/index.js:59:24)

errorno: 'ETIMEDOUT',

code: 'ETIMEDOUT',

syscall: 'connect',

fatal: true }

RDS Error Log

2019-03-28T18:18:49.378320Z 9500 [Note] Aborted connection 9500 to db: 'db' user: 'user' host: '123.123.123.123' (Got timeout reading communication packets)

2019-03-28T18:18:49.392514Z 9498 [Note] Aborted connection 9498 to db: 'db' user: 'user' host: '123.123.123.123' (Got timeout reading communication packets)

2019-03-28T18:18:49.470617Z 9499 [Note] Aborted connection 9499 to db: 'db' user: 'user' host: '123.123.123.123' (Got timeout reading communication packets)

2019-03-28T18:18:49.636775Z 9501 [Note] Aborted connection 9501 to db: 'db' user: 'user' host: '123.123.123.123' (Got timeout reading communication packets)

2019-03-28T18:18:49.694669Z 9502 [Note] Aborted connection 9502 to db: 'db' user: 'user' host: '123.123.123.123' (Got timeout reading communication packets)

2019-03-28T18:18:49.803457Z 9503 [Note] Aborted connection 9503 to db: 'db' user: 'user' host: '123.123.123.123' (Got timeout reading communication packets)

2019-03-28T18:18:49.824250Z 9504 [Note] Aborted connection 9504 to db: 'db' user: 'user' host: '123.123.123.123' (Got timeout reading communication packets)

My db.js query on lambda

const mysql = require('mysql')
let retrieve = (sql, objectArr, entityName) => {
      return new Promise((resolve, reject) => {
        let con = mysql.createConnection(dbParams)
        con.query(sql, objectArr, (err2, results) => {
          con.end()
          if (err2) {
            console.log(err2)
            return reject(new apiError.DatabaseError('An error occurred in retrieve'))
          }
          console.log('Data retrieve successfully')
          return resolve(results)
        })
      })
    }

My test.js script

const request = require('request')
let errorCount = 0
let success = 0

for (let i = 0; i < 4000; i++) {
  console.log('Send')
  request.get('https://myapi/users', {
    headers: {
      'client_id': 'app',
      'Content-Type': 'application/json'
    }
  }, (error, response, body) => {
    if (error) {
      console.log('some error')
      errorCount++
    } else {
      let jsonBody = JSON.parse(body)
      if (jsonBody.code === 0) {
        success++
      } else {
        errorCount++
      }
    }

    console.log('Success: ', success)
    console.log('Error: ', errorCount)
  })
}

Edit: I also tried to change the i < 1 in test script, then it always gives me "Error: connect ETIMEDOUT. But with i < 900, it works successfully sometime

index.js

const db = require('./db.js')
exports.getUsers = async (event, context) => {
  context.callbackWaitsForEmptyEventLoop = false
  try {
    let sql = 'SELECT * FROM User'
    let abc = await db.retrieve(sql, [], 'user')

    let response = {
      statusCode: 200,
      abc: abc,
      code: 0
    }
    return response
  } catch (err) {
    console.log(err)
    errorHandler(err)
  }
}

db.js with Pool

const mysql = require('mysql')
const constants = require('./constants.js')

let dbParams = {
  host: constants.SQL_CONNECTION_HOST,
  user: constants.SQL_CONNECTION_USER,
  password: constants.SQL_CONNECTION_PASSWORD,
  database: constants.SQL_CONNECTION_DATABASE,
  multipleStatements: true,
  maxConnections: 4
}

const pool = mysql.createPool(dbParams)
let retrieve = (sql, objectArr, entityName) => {
  return new Promise((resolve, reject) => {
    pool.getConnection((err1, con) => {
      if (err1) {
        console.log(err1)
        return reject(new apiError.DatabaseError('An error occurred in retrieve pool'))
      }
      console.log('Pool connect successfully')
      con.query(sql, objectArr, (err2, results) => {
        con.end()
        if (err2) {
          console.log(err2)
          return reject(new apiError.DatabaseError('An error occurred in retrieve'))
        }
        console.log('Data retrieve successfully')
        return resolve(results)
      })
    })
  })
}

Error Log after used pool

2019-03-28T23:35:24.144Z 91b0fc78-e4d1-4fd9-bdf7-923715b165c0 { Error: Handshake inactivity timeout

at Handshake. (/var/task/node_modules/mysql/lib/protocol/Protocol.js:163:17)

at emitNone (events.js:106:13)

at Handshake.emit (events.js:208:7)

at Handshake._onTimeout (/var/task/node_modules/mysql/lib/protocol/sequences/Sequence.js:124:8)

at Timer._onTimeout (/var/task/node_modules/mysql/lib/protocol/Timer.js:32:23)

at ontimeout (timers.js:482:11)

at tryOnTimeout (timers.js:317:5)

at Timer.listOnTimeout (timers.js:277:5)

at Protocol._enqueue (/var/task/node_modules/mysql/lib/protocol/Protocol.js:144:48)

at Protocol.handshake (/var/task/node_modules/mysql/lib/protocol/Protocol.js:51:23)

at PoolConnection.connect (/var/task/node_modules/mysql/lib/Connection.js:118:18)

at Pool.getConnection (/var/task/node_modules/mysql/lib/Pool.js:48:16)

at Promise (/var/task/db.js:72:10)

at new Promise ()

at Object.retrieve (/var/task/db.js:67:10)

at exports.getAlerts (/var/task/index.js:59:24)

code: 'PROTOCOL_SEQUENCE_TIMEOUT',

fatal: true,

timeout: 10000 }

Set with Pool now and test it with request loop:

i < 100 result => Success: 866 and Error: 134 requests.

i < 10 result => Success: 8 and Error: 2 requests.

Error with Handshake inactivity timeout

db.js with con.createConnection outside

const mysql = require('mysql')
// initialize dbParams
let con = mysql.createConnection(dbParams)

let retrieve = (sql, objectArr, entityName) => {
  return new Promise((resolve, reject) => {
    con.query(sql, objectArr, (err2, results) => {
      //con.end() commet out connection end here 
      if (err2) {
        console.log(err2)
        return reject(new apiError.DatabaseError('An error occurred in retrieve'))
      }
      console.log('Data retrieve successfully')
      return resolve(results)
    })
  })
}

How many times is "a large number of times" and over how long of an interval did you do this? (example, 1000 times in 60 seconds) — Michael - sqlbot
SELECT * is mostly NEVER a good choice, especially without any limit... Maybe if you query less data it will take less time to retrieve it and timeout after more iterations. It is a nice test to stress all your system though — Zyigh
@Zyigh I currently have only 3 row data in the User table. I will set a limit after — chan3600
@Michael - sqlbot I have set with Pool now and test it with i < 100 request loop. And resulting Success: 866 and Error: 134 requests. Error with Handshake inactivity timeout — chan3600
So... 1000 attempts over what period of time? Read up on MySQL's thread_cache_size -- while probably not the entire issue, a MySQL server is not able to accept an indefinite number of connections in a short time, due to the overhead of OS thread creation. The thread cache allows the server to keep them around for reuse. — Michael - sqlbot

Ronyis Ronyis · Accepted Answer · 2019-03-28T22:56:06

It is not explained in the question how does the retrieve function is being called inside your lambda function.

Even though, it strongly seems that it will be executed for each iteration inside your test, which creates a very large number of connections and may explain the behavior you see.

I suggest creating the connection at lambda initialization time (aka 'coldstart'), by moving the code line let con = mysql.createConnection(dbParams) outside of any function (so that only a single connection will take place).

Another good option is to use connection pooling.

Lambda function “Error: connect ETIMEDOUT” when receive too many requests for MySQL query

1 Answers