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?