Page tree

problem

With migration scripts affecting large tables like LEK_segment_data it can happen that the script fails with:

exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1206 The total number of locks exceeds the lock table size' in /webs/in cludes/ZendFramework-1.12.9-minimal/library/Zend/Db/Statement/Pdo.php:228

problem 2: freezed mysql filling its error log with

2019-08-21T12:06:25.090830Z 2 [Warning] InnoDB: Difficult to find free blocks in the buffer pool (3901 search iterations)! 3901 failed attempts to flush a page! Consider increasing the buffer pool size. It is also possible that in your Unix version fsync is very slow, or completely frozen inside the OS kernel. Then upgrading to a newer version of your operating system may help. Look at the number of fsyncs in diagnostic info below. Pending flushes (fsync) log: 0; buffer pool: 0. 19595 OS file reads, 1256 OS file writes, 18 OS fsyncs. Starting InnoDB Monitor to print further diagnostics to the standard output.


solution

Increase the innodb_buffer_pool_size to a suitable value. Running translate5 on a linux productive server 512MB should be fine (currently the default is 128MB).

  1. edit /etc/my.cnf
  2. add or change the line
    innodb_buffer_pool_size=512MB
  3. Restart the MySQL server

Finally the value of the pool_size depends on the use case. If you do not expect tasks with more as 50000 segments, the default of 128MB should be fine.

See: https://stackoverflow.com/questions/6901108/the-total-number-of-locks-exceeds-the-lock-table-size

  • No labels