0

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:

https://pastebin.com/jsjyyExz

Global status:

https://pastebin.com/ArQvBRYm

mysqltuner report:

https://pastebin.com/JmyBkZCh

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.

Improve this question
6
  • How does the number of open connections behave over time (show processlist or something like that)? Does the client application commit its transactions? Does it use a connection pool?
    – mustaccio
    Dec 5, 2019 at 14:52
  • some extra Sleep | 0 | | NULL processes showing up and besides that, queries of the webapp but nothing suspicious there..
    – Ilja
    Dec 5, 2019 at 16:08
  • Additional information request. Post on pastebin.com and share the links. From SSH login root, Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) complete MySQLTuner report, if available. AND Optional very helpful information, if available includes - htop OR top for most active apps, ulimit -a for a Linux/Unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, for server workload tuning analysis to provide suggestions.
    – Wilson Hauck
    Jan 12, 2020 at 0:05
  • thank you @WilsonHauck I've added additional information to the post, much appreciate any feedback
    – Ilja
    Jan 27, 2020 at 21:33
  • Still need, posted to pastebin.com please, SHOW FULL PROCESSLIST; and very helpful information, if available includes - htop OR top for most active apps for server workload tuning analysis to provide suggestions.
    – Wilson Hauck
    Jan 27, 2020 at 23:09

2 Answers 2

Reset to default
2

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.
Improve this answer
2
  • Thank you very much, Rick, indeed, the longest requests are those which do inserts, specifically, insert into ... from ... and another one, which toke 100s was just some innocent insert, at a first look! This is Magento instance, its EAV and it's using a lot of joins indeed, I will try to do the rate limit for requests per second in nginx and thinking on how to increase innodb_buffer_pool_size without putting the database down. I am considering to build latest mariadb from sources with TokuDB (rn we have 5.5.60) do you have any thoughts on this, could this help?
    – Ilja
    Dec 6, 2019 at 10:13
  • @Ilja - Alas, dynamic resizing of innodb_buffer_pool_size is not available until version 5.7 / 10.2.13(?). Caution... If you are using TokuDB, don't give innodb that much space (80G); perhap half that, and give Toku the other half. (Sorry, I don't have specifics.)
    – Rick James
    Dec 6, 2019 at 23:14
0

Rate per Second = RPS

Suggestions for your my.cnf [mysqld] section,

innodb_io_capacity=1900  # from 200 to enable higher IOPS on your SSD 
read_buffer_size=512K  # from 512M to be a reasonable limit
read_rnd_buffer_size=128K  # from 512M to reduce handler_read_rnd_next RPS
innodb_buffer_pool_size=48G  # from ~ 80G
max_write_lock_count=16  # from 1 to allow RD after nn lcks

You will find these configuration changes will REDUCE CPU busy significantly.

This is just the tip of the iceberg, many more Global Variables need to be changed.

Drop tables can cause significant delay in response time.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.