0

I have a server that suddenly peaks between 4 to 6 load average at random times of the day and can't pinpoint what the cause is. Majority of the time, the load average would be hovering between 1.5 to 2 and suddenly jump to 4 or 6 and back down again. I have mariadb running and apache processes but when I check my apache to see if any jump in traffic, it looks about the same amount along with the amount of queries being processed with mariadb.

here is top:

top - 23:58:16 up 2 days,  7:05,  1 user,  load average: 3.64, 3.07, 2.93
Tasks: 290 total,   1 running, 289 sleeping,   0 stopped,   0 zombie
%Cpu(s): 18.5 us, 10.8 sy,  0.0 ni, 70.3 id,  0.0 wa,  0.0 hi,  0.4 si,  0.0 st
KiB Mem : 13174942+total, 11137099+free,  3200604 used, 17177824 buff/cache
KiB Swap:  4190204 total,  4190204 free,        0 used. 12759236+avail Mem 

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND                       
  934 mysql     20   0 8965860 1.360g   9376 S 353.8  1.1   4240:34 mysqld                        
    9 root      20   0       0      0      0 S   0.3  0.0   5:48.98 rcu_sched                     
   43 root      20   0       0      0      0 S   0.3  0.0   0:04.45 ksoftirqd/7                   
  798 root      20   0   24192   1700   1380 S   0.3  0.0   0:09.10 systemd-logind                
17627 apache    20   0  586792  22372   8652 S   0.3  0.0   0:00.61 httpd                         
24715 root      20   0       0      0      0 S   0.3  0.0   0:04.30 kworker/u24:0                 
    1 root      20   0  191404   4356   2404 S   0.0  0.0   0:20.04 systemd                       
    2 root      20   0       0      0      0 S   0.0  0.0   0:00.01 kthreadd                      
    3 root      20   0       0      0      0 S   0.0  0.0   0:00.80 ksoftirqd/0                   
    5 root       0 -20       0      0      0 S   0.0  0.0   0:00.00 kworker/0:0H                  
    7 root      rt   0       0      0      0 S   0.0  0.0   0:00.17 migration/0       

A few more details:

  • MariaDB does lots of reads and writes, approximately 2 GiB an hour of traffic received and sent.
  • Query statistics show around 55% SELECTS and 27% UPDATES. I want to make sure it's my database server causing the spikes before I switch over to another engine like INNODB.
  • Memory looks plentiful.
              total        used        free      shared  buff/cache   available
Mem:         128661        3534      104646          88       20480      124164
Swap:          4091           0        4091
  • iotop shows the following, I have an SSD on Raid 1.
Total DISK READ :   0.00 B/s | Total DISK WRITE :    1574.60 K/s
Actual DISK READ:   0.00 B/s | Actual DISK WRITE:       0.00 B/s
  • My whole database is actually just around 250MB in size.

It looks to be a CPU-bound issue as I still have plenty of ram and SSD hasn't hit anything near it's read/write speeds. My suspicions are that I am running my whole database tables on MyISAM engine and I see lots of "waiting for table-level locks" occurring but at the same time I feel that the reads/writes are consistent throughout the day so I am not sure why it would just suddenly spike. Also doesn't seem like throwing more memory or SSDs would help as it's hardly using any of that. Maybe my MariaDB configuration is bad?

  • 2
    It's very obviously the database. I don't understand why you even doubt it. – Michael Hampton Feb 7 '17 at 22:09
0
  1. Try using the general query log to see if you have any cron processes or other queries https://mariadb.com/kb/en/mariadb/general-query-log/
  2. A tool like Monit, newrelic, or datadog can be used to confirm what is using your CPU, how much, and when

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.