MySQL server keeps a lot of deleted temporary files

[Тарас Лужной]

Hello,
we encountered a problem when the server crashed due to exceeding the limit of open files.
(errno: 24 - Too many open files)

Our limit in the configuration:
open_files_limit = 10000

Later, we saw a gradual increase in the number of open temporary files that had already been deleted.

mysqld 100394 mysql 6u REG 253,2 0 134086662 /var/tmp/ibmOkaOm (deleted)
mysqld 100394 mysql 7u REG 253,2 0 134086663 /var/tmp/ib6m7bsN (deleted)
mysqld 100394 mysql 8u REG 253,2 0 134086664 /var/tmp/ib2FCd6d (deleted)
mysqld 100394 mysql 9u REG 253,2 0 134086665 /var/tmp/ibuWLXvn (deleted)
mysqld 100394 mysql 13u REG 253,2 0 134086666 /var/tmp/ibYEfKn9 (deleted)
mysqld 100394 mysql 1073u REG 253,2 67984 134086732 /var/tmp/MY07Vmxf (deleted)
mysqld 100394 mysql 1288u REG 253,2 67856 134086691 /var/tmp/MY2G4AxZ (deleted)
mysqld 100394 mysql 1296u REG 253,2 67984 134086716 /var/tmp/MYgZRBlC (deleted)
mysqld 100394 mysql 1319u REG 253,2 67852 134086692 /var/tmp/MY6gqsxl (deleted)
mysqld 100394 mysql 1383u REG 253,2 0 134086673 /var/tmp/ML6dC0i2 (deleted)
mysqld 100394 mysql 1433u REG 253,2 68028 134086747 /var/tmp/MYS9ub5r (deleted)
mysqld 100394 mysql 1462u REG 253,2 68044 134086756 /var/tmp/MYK0JD3Y (deleted)
mysqld 100394 mysql 1463u REG 253,2 67976 134086731 /var/tmp/MYY7u1a4 (deleted)
mysqld 100394 mysql 1466u REG 253,2 67976 134086730 /var/tmp/MY8LNKr4 (deleted)
mysqld 100394 mysql 1503u REG 253,2 68028 134086748 /var/tmp/MYePC0rs (deleted)

The problem is temporarily solved by restarting mysql. We've decided it's Bug number 28039829. So we decided to upgrade mysql-community-server c 5.7.24 to 5.7.28. But the problem doesn't seem to have been solved and the number of these files continues to increase.

# lsof -u mysql | grep deleted | wc -l
125

Current server version:

# mysql -V
mysql Ver 14.14 Distrib 5.7.28, for Linux (x86_64) using EditLine wrapper

We also use Master-Slave replication.

Current config:
[mysqld]
pid-file    = /var/run/mysqld/mysqld.pid
socket        = /var/run/mysqld/mysqld.sock
datadir        = /var/lib/mysql
log-error    = /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address    = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
open_files_limit = 40000
max_connections = 9000
slave_net_timeout = 1800
skip-external-locking
skip-name-resolve
character_set_server=utf8
collation_server=utf8_general_ci
max_allowed_packet = 64M
thread_stack = 256K
thread_cache_size = 600
tmp_table_size = 2048M
max_heap_table_size = 2048M
innodb_file_per_table
innodb_io_capacity=4000
innodb_log_file_size = 20G
innodb_write_io_threads=16
innodb_read_io_threads=16
innodb_buffer_pool_instances=48
innodb_buffer_pool_size=100G
innodb_online_alter_log_max_size=35067600000
innodb_tmpdir=/var/tmp
tmpdir = /var/tmp
max_connections = 9000
binlog_format=ROW
table_open_cache = 2000
query_cache_limit = 16M
query_cache_size = 1024M
query_cache_type = 1
group_concat_max_len = 128000
slow_query_log = ON
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 3
log_slow_admin_statements = ON
#replication
server-id=100
replicate-do-db=mydatabase
log-bin=/var/log/mysql/bin.log
relay-log=mysqld-relay-bin
binlog-do-db=mydatabase
binlog-ignore-db=mysql
binlog-ignore-db=test
#skip-slave-start
expire_logs_days = 8
log_bin_trust_function_creators = 1
max_binlog_size = 100M
sql_mode = ''
log_slave_updates= ON
eq_range_index_dive_limit=20000
range_optimizer_max_mem_size=16388608
innodb_page_cleaners=48
transaction-isolation = READ-COMMITTED
gtid_mode=ON
enforce_gtid_consistency=ON
#read_only = ON
ssl-ca=ca.pem
ssl-cert=server-cert.pem
ssl-key=server-key.pem
tls_version = TLSv1.1,TLSv1.2
#[client]