Blanche
Engineer
Engineer
  • UID619
  • Fans3
  • Follows2
  • Posts59
Reads:2477Replies:0

[MySQL]MySQL database troubleshooter

Created#
More Posted time:Sep 7, 2016 9:55 AM
Problem 1: A dog in the manger
In case of any problem, we should check the server error log first.
We frequently saw such an exception error in the error log: Error:  ER_CON_COUNT_ERROR:  Too many connections.  This error occurs because all connections in the database are occupied by clients, leaving no idle connection. As the maximum number of concurrent connections of MySQL is 100 by default, while the maximum possible concurrence in our application involves only 30~40 tasks, it is less likely to have such an error. Therefore, we guess that it is probably because the connection is not closed in a timely manner after the connection is established in the code. Then we mainly check the code executing SQL statements, which is probably as follows (with node-mysql database):
var mysql = require('mysql');
//Establish the connection pool
var pool = mysql.createPool({
    host: 'host',
    user: 'user',
    password: 'password',
    database: 'db'
});

exports.query = function(sql, cb) {
   //Select an available connection from the pool
    pool.getConnection(function(err, connection) {
        if (err) throw err;
        // Execution SQL
        connection.query(sql, function(err, rows, fields) {
            if (err) {
                return cosole.error(err);
            }
            cb(rows);
        });
       //Release this connection
        connection.release();
    });

};


I did not see any problem at the beginning, but later I read the node-mysql document and issue carefully, and finally found that there is something wrong with our writing.
Look at the above code again. We executed connection.query following pool.getConnection, and invoked connection.release before SQL statement execution was completed, but the connection will not be released at this time due to the asynchronous nature of JavaScript (although SQL statements may be executed quickly, we must check callback in connection.query to see clearly whether SQL execution has completed)! All the execution of SQL statements in the code will require invocation of this function, which means that we are occupying many databases without release, and other databases are connected constantly, directly causing blocking of the other connections, and leading to the exception of too many connections. This is really a typical behavior of "being a dog in the manger". Therefore, we must release the connection in a timely manner after the SQL statement execution is completed. As SQL statement execution generally goes very quickly (a few tenths of a second), if we do not release the connection after the execution, it is likely to cause blocking of connections when many databases are generated at the same time, and thus lead to the exception of too many connections. Then we made the following modifications to the code:
exports.query = function(sql, cb) {
   //Select an available connection from the pool
    pool.getConnection(function(err, connection) {
        if (err) throw err;
        //Execute SQL
        connection.query(sql, function(err, rows, fields) {
                        //Release the connection (It must be done before error processing, otherwise the connection will not be released when an error occurs).
           connection.release();

            if (err) {
                return cosole.error(err);
            }
            cb(rows);
        });
    });

};


We can also make this a bit simpler using: pool.query. This method will release the connection internally at a right time, without requiring any manual operation.
After these modifications, this exception does not recur, but the slow response still does not get better.
Problem 2: A 'murder case' caused by an UPDATE statement
We checked the error log again, and found another exception error: Error:  ER_LOCK_WAIT_TIMEOUT:  Lock wait timeout exceeded; try restarting transaction. This error is very confusing, and the cause is lock wait timeout, that is, the current transaction is waiting for other transactions to release the locked resources.
Let's talk about what a transaction is first. A transaction should have four attributes:
• Atomicity (The transaction should be executed as a whole, either in full or not at all.)
• Consistency (The transaction should ensure the database can transit from one consistent state to another consistent state.)
• Isolation (The execution of one transaction should not affect the execution of other transactions upon concurrent execution of multiple transactions.)
• Durability (After the transaction is committed, all modifications to the database should be permanently stored in the database.)
For isolation, multiple isolation levels can also be separated:


• Dirty Read: Transaction A reads some uncommitted modifications of Transaction B.
• Nonrepeatable Read: Transaction B also accesses the same data before Transaction A is done with it. The data read in two reads by Transaction A may be different due to the modifications to Transaction B.
• Phantom Read: Transaction A modifies the data in a table, and such modification involves all data rows in the table. Transaction B also modifies the data in this table, and such modification inserts a new row of data into the table. The user managing Transaction A sees the row inserted by Transaction B appear in the table as if a phantom has occurred.
The default level in MySQL is Repeatable Read, which means that "Dirty Read" and "Unrepeatable Read" will not happen in MySQL by default. This requires us to implement necessary locking upon update (InnoDB will apply the row-level locking mode) to ensure consistency. It is important to note that the row locking of InnoDB is to be implemented by locking the index entries of the index. This characteristic means that InnoDB will use row-level locking only if the data is retrieved by the index conditions, or it will use table locking.
Our database table is the table of InnoDB engine, while the InnoDB engine of MySQL is an engine that supports transactions, and its default operating mode is the autocommit mode. What does that mean? Unless we start a transaction explicitly, each query will be executed automatically as a single transaction.
Let's go back to the above error. The SQL statements executed when an exception appears in the error log are similar to such an UPDATE statement: update testScore set status=1,executionId='946012' where token='f7900c40-8f4b-11e5-b2f1-6feca76a1bf5'.
Then the cause of the problem can be described as such: When we execute an UPDATE statement, MySQL will take it as a transaction to lock the rows in the table, then the other connections to UPDATE or SELECT the same table will have to wait for the lock resources, and the waiting time is so long,causing timeout.
What? An UPDATE statement would be so slow! I can hardly accept it! I have no other choice, but to check why this statement is so slow.
I checked the corresponding query information in the slow_queries.log.
# Query_time: 56.855324  Lock_time: 48.054343 Rows_sent: 0  Rows_examined: 29400
update testScore set uiTaskId=81041 where token='e7d7d8f0-8f4b-11e5-99be-9dfbb419755e';


Such an UPDATE statement takes 56 seconds to scan 29,400 table records. Seeing such an execution log, we can probably guess the cause - no index is added to the query field token. So MySQL will lock the entire table directly without line locking upon update, while the update statement itself is slow enough (scanning the entire table) to cause lock wait timeout upon concurrence of multiple updates.
After indexes were added to the token field of the testScore table, this exception did not recur, and the response time began to return to normal.
Guest