mariadb 5.5 innodb keeps opening files up to 200k until system hangs completely

Question

Mariadb 5.5 is installed on CentOS and serves a database in the total volume of around 40GBs (as measured per database files on a disk) and 506 tables. This database is being queried by php-fpm, and the issue is that at some point, when traffic increases, the number of open files opened by mysqld process (as per lsof states) grows up to around 200k and system hangs (specifically web requests ar being extremely slow, 5mins for TTFB).

The server itself is should have enough capacity (Supermicro; X10DRH with 125gb of RAM and SSD) to be able to perform quickly on such a modest load.

Examination of lsof output shows that mysqld process keeps open tables on and where: [root@mail proc]# lsof | wc -l 95592 most of them are open tables:

[root@mail proc]# lsof | grep .ibd | wc -l 57331

before that, lsof showed very big qty of /[aio] and putting innodb_use_native_aio=0 improved the situation a bit

mysql_slow_log does not show queries that are running longer than 5s,

| Innodb_mem_total | 33061601280

my MySQL config is:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

symbolic-links=0
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
innodb_use_native_aio=0
skip-name-resolve=1
max_heap_table_size=256M
tmp_table_size=256M 
slow-query-log=1
long_query_time=1

for some tables mysql.log shows some errors on 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 is that might be a reason for low performance.

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

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

any hint on where to dig would be highly appreciated.

Answer 1

Several possibilities:

• Some queries are taking a long time (and hanging onto files (that is, tables) meanwhile). -- Look for slow queries; let's work on speeding them up. You have the slowlog turned on; use pt-query-digest to identify the 'worst' queries.
• The APP uses the "EAV" model, which joins lots of tables. -- If this is the case, let's discuss that.
• The web server allows a huge number of connections. If it is allowing "too many", then will just stumble over each other. "5mins for TTFB" is a clue of this. -- Throttle connections at the web server layer.
• Not effectively using RAM. You have 125GB of RAM, yet innodb_buffer_pool_size is only 1G. -- Change that to 80G.

Meanwhile, delete this Q&A and stick with the better site: dba.stackexchange.com .