MySQL server keeps a lot of deleted temporary files
Posted by: Тарас Лужной
Date: August 04, 2023 07:15AM

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]

Options: ReplyQuote


Subject
Written By
Posted
MySQL server keeps a lot of deleted temporary files
August 04, 2023 07:15AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.