problem

The materialized view of a task with 119000 segments could not be created.
The whole mysql server was freezed, therefore the whole server was not reachable anymore.

New connections to mysql were possible, show full processlist have shown the INSERT SELECT statement to fill the materialized view. The query could not be killed, to get back a running mysql the mysql server had to been killed.

In the mysql error log there was logged:

2019-08-21T12:06:25.256682Z 2 [Warning] InnoDB: Difficult to find free blocks in the buffer pool (3916 search iterations)! 3916 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 useful value. The definition of "useful" is a little bit tricky. Some time ago (I assume it was under windows with xampp) we defined with TRANSLATE-999 that the  innodb_buffer_pool_size should be increased to 64MB. Today the default is already 128MB which was already to low for the above task. So we set it to 512MB.

In the above case the finally created view table had about 130MB, so I assume that the whole data is loaded into memory, therefore the buffer size should at least two times as big as the biggest tasks, according to their final table size of the view table. The raw sdlxliff files of the above task had about 350MB.

As maximum value for innodb_buffer_pool_size can be 80% of the available memory if the server is a pure DB Server! So in conclusion the 512MB should be fine for a server where apache, mysql and the termtagger is hosted.