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:
(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 displays some errors at startup:
191205 7:21:25 InnoDB: Error: trying to open a table, but could not but I'm not sure that could explain the poor performance.
InnoDB: open the tablespace file './staging/yotpo_rich_snippets.ibd'!
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.