Mysql >=8.0.23 too many open files

MySQL & MariaDB
, ,
1

Hi all,

We’ve encountered a performance decline following the upgrade from MySQL 8.0.21 to any other version that is >=8.0.23 on Ubuntu 20 (and subsequently on 22). We’ve observed a notable discrepancy in the execution time of a select query between these two versions. To illustrate, we’ve conducted tests using mysqlslap with parameters (–concurrency=2000 --iterations=5). While the query completed within a few seconds in version 8.0.21, it now fails after approximately 2 seconds in mysql >=8.0.23, accompanied by the following error:

mysqlslap: Error when storing result: 1 Can’t create/write to file ‘/ob/mysql/tmpdir/MY0ELoKE’ (OS errno 24 - Too many open files)

Despite both servers sharing the same open file limit (63000), there appears to be a change in behavior between the two versions. We suspect that something related to “Created_tmp_files” might be at play here.

This was tested on 8.0.23/8.0.27/8.0.33/8.1/8.2

Any suggestions on how we could address this issue?

2

Hi Ariel, I suggest you deploy PMM monitor and check both servers metrics during the load test. There might be some clues as to why you are hitting the open files limit. Also make sure mysqld process is indeed taking the limit correctly.

3

Thanks for the update, @Ivan_Groenewold . We’ve already taken care of those steps. There’s nothing out of the ordinary in PMM, and the open file limit is correctly configured.

Our only option now is to proceed with upgrading to Ubuntu 20 (and not version 22), while also maintaining MySQL version 8.0.21.