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, 5 mins 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
ulimit -a
output:
core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
scheduling priority (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 515264
max locked memory (kbytes, -l) 64
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
real-time priority (-r) 0
stack size (kbytes, -s) 8192
cpu time (seconds, -t) unlimited
max user processes (-u) 515264
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
[root@mail public_html]# iostat -xm 5 3
Linux 3.14.32-xxxx-grs-ipv6-64 (mail.hostname.com) 01/27/2020 _x86_64_ (32 CPU)
avg-cpu: %user %nice %system %iowait %steal %idle
6.09 0.00 0.29 0.03 0.00 93.59
Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
sda 0.31 51.87 13.18 223.86 0.84 2.38 27.83 0.13 0.54 3.46 0.37 0.09 2.04
sdb 0.30 51.86 12.08 223.86 0.78 2.38 27.48 0.13 0.53 3.70 0.36 0.09 2.06
md2 0.00 0.00 1.34 262.88 0.09 2.23 17.97 0.00 0.00 0.00 0.00 0.00 0.00
md1 0.00 0.00 0.08 6.54 0.00 0.14 43.22 0.00 0.00 0.00 0.00 0.00 0.00
avg-cpu: %user %nice %system %iowait %steal %idle
5.29 0.00 0.47 0.01 0.00 94.23
Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
sda 0.00 33.00 0.00 37.60 0.00 0.43 23.62 0.00 0.09 0.00 0.09 0.09 0.32
sdb 0.00 33.00 0.00 37.60 0.00 0.43 23.62 0.00 0.09 0.00 0.09 0.09 0.32
md2 0.00 0.00 0.00 42.20 0.00 0.28 13.38 0.00 0.00 0.00 0.00 0.00 0.00
md1 0.00 0.00 0.00 20.20 0.00 0.14 13.70 0.00 0.00 0.00 0.00 0.00 0.00
avg-cpu: %user %nice %system %iowait %steal %idle
3.81 0.00 0.60 0.01 0.00 95.58
Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
sda 0.00 43.80 0.40 58.20 0.01 0.82 28.92 0.01 0.14 0.00 0.14 0.12 0.72
sdb 0.00 43.80 0.00 58.20 0.00 0.82 28.68 0.01 0.10 0.00 0.10 0.08 0.48
md2 0.00 0.00 0.00 69.20 0.00 0.62 18.22 0.00 0.00 0.00 0.00 0.00 0.00
md1 0.00 0.00 0.40 21.20 0.01 0.17 17.33 0.00 0.00 0.00 0.00 0.00 0.00
Any hint on where to dig would be highly appreciated.
Global variables:
Global status:
mysqltuner report:
htop
shows that mysqld process eats up to around 80% cpu on peak load time
and show processlist
shows some specific queries which have a lot (thousands) of PKs in WHERE IN ( ... )
statements. Unfortunately, there is no easy way to get rid of those statements.
show processlist
or something like that)? Does the client application commit its transactions? Does it use a connection pool?