1

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 ?

Thanks !

Edit: I did not see anything suspicious in apache or mysql logs

Edit: Some data asked in the comments

ulimit -a:

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

iostat -x

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

  • Can you provide a longer time-period of your graph containing a httpd/mysql restart and maybe memory statistics? – Andreas Rogge Feb 20 '18 at 21:14
  • 1
    What memory statistics do you want ? – Nisalon Feb 20 '18 at 22:19
  • 1
    Post updated. Please note that i'm using myisam and not innodb – Nisalon Feb 21 '18 at 19:18
  • 1
    No, juste a weird cpu pattern, which makes me fear getting more load on that server. – Nisalon Feb 21 '18 at 23:06
  • 1
    There is no "user" concept in the application. I have 2 NVMe SSD in raid1 – Nisalon Feb 24 '18 at 13:39
0

These are all Dynamic variables that may be set with SET GLOBAL xxx=xxx; please follow requested order. If you only do ONE a day, shutdown restart, you are many days away, but then you are running very well.
RESEARCH and pursue as time permits, weird usage pattern will likely go away during this cycle of tuning.

Suggestions for your my.cnf/ini [mysqld] section

max_connections=325  #from 512 to support 270 max_used_connections
#max_allowed_packet=64M  # lead for 1M default  WHEN YOU NEED more

at start of your SESSION, only when you need it, SET @max_allowed_packet=67108864 # to reduce RAM footprint MySQLTuner reported

query_cache_size=0  # from 16M, not being used, conserve RAM
query_cache_limit=1K  # from 1M, conserve RAM
query_cache_min_res_unit=512  #from 4096, for > results stored, if ever used
thread_cache_size=100  # from 8 to reduce ~ 240,000 threads_created a day

the next 3 (do all 3 on one day) will enhance MyISAM key_buffer management

key_cache_division_limit=50  # from 100 default  for Hot/Warm separation
key_cache_block_size=64K  # from 1K let's clear more RAM when full
key_cache_age_threshold=64800  # from 300 KEEP 18 HRS vs 5M to reduce RD RPS

innodb_lru_scan_depth=128  # from 1024 to conserve CPU cycles
innodb_stats_sample_pages=32  # from 8 to improve statistics cardinality
max_seeks_for_key=32  # from a huge # to limit OPTIMIZER depth
max_write_lock_count=16  # from a huge # to allow RD after NN write locks
table_open_cache=16000  # from 8192 to support ~ 350,000 opened_tables daily

If you have any SSD, there are more opportunities. Asked in a comment a day or so ago.

  • @nisalon Have you made any progress with management of this server? Please share your success or current problems. Thank you for posting your information to allow these Suggestions to be provided in February. See my profile for contact information, please. A new Question should be opened for current concerns, this one has enough baggage. – Wilson Hauck Jun 23 '18 at 20:57
  • @nisalon Has your weird cpu performance pattern improved since February? I any comments or answers helped, please upvote and possibly accept answer so we have some indication of helpfulness. Please view profile, Network profile for contact info including my Skype ID. Please get in touch. – Wilson Hauck Aug 12 '18 at 20:30
  • @nisalon Guessing you just do not want us to know if you are making any progress or not, since upvotes are not evident and no feedback. The very BEST to you. – Wilson Hauck Aug 29 '18 at 0:40
  • @nisalon With 2 NVME's in my.cnf [mysqld] section innodb_io_capacity=20000 # to allow higher IOPS will help. – Wilson Hauck Aug 29 '18 at 0:43
  • @nisalon Have you resolved your rough patch with MySQL? Let us know, please. – Wilson Hauck Nov 1 '18 at 22:42
0
  • Switch from MyISAM to InnoDB. (And change key_buffer_size and innodb_buffer_pool_size.)
  • Lower MaxRequestWorkers 1024 and max_connections = 512 -- It is better to stop new people from entering the grocery store than have the store so crowded that no one can move. Even 270 Max_used_connections may indicate 'thundering herd'.
  • The Query cache is probably hurting more than helping. In busy production systems, with lots of writes, the QC needs to be flushed a lot. So, independent of other tests, change to query_cache_type=0 and query_cache_size=0.
  • Check on Java's garbage collection.

I'll be happy to review SHOW GLOBAL STATUS; and SHOW VARIABLES it you repost them (they have expired).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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