I've been struggling with a performance issue on one of my server for a few weeks.
Configuration: Dedicated server with Ubuntu 16.04, Mysql 5.7.21 (all MyISAM), Apache 2.4.18 (mpm prefork), Php 7.0.25. 128GB Ram
The cpu usage pattern is really weird and I've found nothing explaining it. https://imgur.com/a/PjRSG
As you can see, there are those successive levels of cpu usage, which are pretty constant. Also, when Apache cpu usages increases, Mysql usage cpu increases, and when one decreases, the other one decreases. Also, as you can see, the whole difference happens in CPU system usage.
I've had a look at the number of queries/s of mysql, which is basically constant. Same thing for the number of requets/s on apache2. Pretty constant. Btw the apache server takes around 100req/s and mysql is ~300queries/s on mysql (so the spikes in cpu does not seem to be linked to a regular high amount of requests on apache or mysql)
I have ahd a look at slow queries, nothing in particular. When I do a SHOW PROCESSLIST, no query is still around. Same thing for apache. Maximum page load time is <1s.
When I restart the apache2 service the pattern seems to disappear for a few hours. When I restart the mysql service, the pattern seems to disappear for a few hours too.
Also, I have a few other java services which also use this db (using the latest jdbc driver, and I don't see any cpu pattern change with them). There were initially creating their connection to mysql at the start of the services, but I changed this behavior to close the connection/start a new one every 5min ... This did not change anything
My my.cnf file:
[mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = 0 [mysqld] user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp lc-messages-dir = /usr/share/mysql skip-external-locking bind-address = 0.0.0.0 key_buffer_size = 36G max_allowed_packet = 64M tmp_table_size = 256M max_heap_table_size = 256M max_connections = 512 table_open_cache = 8192 bulk_insert_buffer_size = 512M thread_stack = 192K thread_cache_size = 8 sort_buffer_size = 64M myisam_sort_buffer_size = 64M myisam-recover-options = BACKUP query_cache_limit = 1M query_cache_size = 16M log_error = /var/log/mysql/error.log expire_logs_days = 10 max_binlog_size = 100M [isamchk] key_buffer_size = 36G sort_buffer_size = 8M read_buffer = 4M write_buffer = 4M
My apache has pretty much the default configuration, except it has (i can have about 350 workers used, that's why I put this value)
MaxRequestWorkers 1024 ServerLimit 1024
I'm really not sure what to do next to investigate.
Any idea of what could be going wrong ?
Edit: I did not see anything suspicious in apache or mysql logs
Edit: Some data asked in the comments
core file size (blocks, -c) 0 data seg size (kbytes, -d) unlimited scheduling priority (-e) 0 file size (blocks, -f) unlimited pending signals (-i) 514833 max locked memory (kbytes, -l) 64 max memory size (kbytes, -m) unlimited open files (-n) 50000 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) 514833 virtual memory (kbytes, -v) unlimited file locks (-x) unlimited
Linux 4.4.0-112-generic (ns340707.ip-37-187-250.eu) 02/21/2018 _x86_64_ (12 CPU) avg-cpu: %user %nice %system %iowait %steal %idle 14.89 0.29 10.42 1.41 0.00 72.98 Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %util sdb 0.70 51.90 18.37 11.86 1179.98 1894.25 203.33 1.40 46.40 10.29 102.33 2.35 7.11 sda 0.73 51.89 25.27 11.87 1847.55 1894.25 201.50 1.22 32.72 4.37 93.05 2.14 7.93 md0 0.00 0.00 9.63 62.19 758.90 1889.96 73.77 0.00 0.00 0.00 0.00 0.00 0.00 nvme1n1 0.00 0.00 429.64 1214.09 4755.77 6215.04 13.35 0.36 0.19 0.18 0.19 0.03 5.00 md2 0.00 0.00 1567.33 1204.69 14913.87 6204.69 15.24 0.00 0.00 0.00 0.00 0.00 0.00 nvme0n1 0.00 0.00 1204.85 1215.25 10676.20 6219.68 13.96 1.57 0.62 0.14 1.10 0.05 11.93
SHOW GLOBAL STATUS -> https://pastebin.com/AehMqQQq
SHOW GLOBAL VARIABLES -> https://pastebin.com/JyGquqFx
Mysql tuner output: https://pastebin.com/F8wvbHec
I just noticed that, during those "high system cpu" phase, I have a high % of cpu in isolate_freepages_block (I got that using
perf top). Nevertheless I'm not sure how to solve this