mysql – mariadb 5.5 innodb keeps open open files up to 200k until the system hangs completely

Mariadb 5.5 is installed on CentOS and serves a database in a total volume of about 40 GB (measured per database file on a disk) and 506 tables. Php-fpm queries this database and the problem is that as traffic increases, the number of open files opened by mysqld process (according to lsof grows to around 200,000 and the system crashes (especially web requests are extremely slow, 5 minutes for TTFB).

The server itself should have enough capacity (Supermicro; X10DRH with 125 GB of RAM and SSD) to quickly run such a modest load.

Examining the output of lsof shows that the mysqld process keeps tables open on and where:

(root@mail proc)# lsof | wc -l

most are open tables:

(root@mail proc)# lsof | grep .ibd | wc -l

before that, lsof showed a very large amount of / (aio) and putting innodb_use_native_aio=0 improved the situation a bit

mysql_slow_log do not display queries that last longer than 5 seconds,

| Innodb_mem_total | 33061601280

my MySQL configuration is:


innodb_file_per_table = 1
innodb_buffer_pool_size = 1G
innodb_io_capacity = 2000
innodb_read_io_threads = 64
innodb_thread_concurrency = 0
innodb_write_io_threads = 64

for some tables mysql.log displays some errors at startup:

191205 7:21:25 InnoDB: Error: trying to open a table, but could not
InnoDB: open the tablespace file './staging/yotpo_rich_snippets.ibd'!
but I'm not sure that could explain the poor performance.

also, the database data files are located in /home/ score, which is:

/dev/md2 /home ext4 grpquota,usrquota,data=ordered,relatime,rw 0 2

any indication of where to dig would be highly appreciated.