MySQL – Resolving General Error 1205 Lock Wait Timeout Exceeded

MySQLperformancequery-performance

I have database session enable, when website got thousands of visitor. It gives me error

[30-Apr-2014 04:27:12 America/Denver] PHP Fatal error:  Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded;
 try restarting transaction' in public_html/lib/Cake/Model/Datasource/DboSource.php:458
Stack trace:
#0 public_html/lib/Cake/Model/Datasource/DboSource.php(458): PDOStatement->execute(Array)
#1 public_html/lib/Cake/Model/Datasource/DboSource.php(424): DboSource->_execute('UPDATE `yuldi_d...', Array)
#2 public_html/lib/Cake/Model/Datasource/Database/Mysql.php(400): DboSource->execute('UPDATE `yuldi_d...')
#3 public_html/lib/Cake/Model/Model.php(1821): Mysql->update(Object(cake_sessions), Array, Array)
#4 public_html/lib/Cake/Model/Datasource/Session/DatabaseSession.php(117): Model->save(Array)
#5 [internal function]: DatabaseSession->write('66336365f5fc91c...', 'Config|a:3:{s:9...')
#6 [internal function]: session_write_close()
#7 {main}
  thrown in public_html/lib/Cake/Model/Datasource/DboSource.php on line 458

I want to know, how can i check MySQL insert queries per second, is there any commands to check that using ssh ?
is there changes in my.cnf required ?

Best Answer

If you wan't to check the queries/s in live you can use the mysqladmin tool with some options:

[root@db-prod-01 ~]# mysqladmin extended-status -uroot -p -i 1 -r | grep 'Com_insert '
Enter password: 
| Com_insert                            | 59863746      |
| Com_insert                            | 1             |
| Com_insert                            | 2             |
| Com_insert                            | 9             |
| Com_insert                            | 2             |
| Com_insert                            | 2             |
| Com_insert                            | 3             |
| Com_insert                            | 2             |
| Com_insert                            | 3             |
| Com_insert                            | 6             |
  • extended-status: return the result of SHOW GLOBAL STATUS;
  • -i 1 -r: Execute the SHOW GLOBAL STATUS; every second (one line by second)
  • | grep 'Com_insert': Shows only INSERTS statements

Note that Com_insert is a cumulativ counter the first line shows the nominal counter value and at each iteration of mysqladmin, a substraction is made to show you how there were insertions during the time you setted (here -i 1 is each 1 second).

By the way, if you want some infos for your error (1205) it's the InnoDB Engine that should check because it's the only way to see which of your query (or queries) is/are stucking your DB.

You can find infos on current and past transactions that lock your DB with:

SHOW ENGINE INNODB STATUS; -- In a MySQL prompt

Max.

Related Question