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).
- edit /etc/my.cnf
- add or change the line
innodb_buffer_pool_size=512MB - 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