Fixing `ghost 500 select `products`.*...`
I've been tracking analytics ever since I launched a new VM to host my blog. I was surprised that there was no traffic since.
I was wondering if whether it was an issue with Google: perhaps not referencing my blog any longer since it somehow detected that the server IP had changed. Unlikely since it should be completely normal that a server's IP changes.
Intrigued, I googled something on my site, clicked the link and got this error:
If I went to www.wafrat.com directly, then I'd get a mysql error trace:
ghost 500 select `products`.*, `posts_products`.`post_id` as `_pivot_post_id`, `posts_products`.`product_id` as `_pivot_product_id`, `posts_products`.`sort_order` as `_pivot_sort_order` from `products` inner join `posts_products` on `posts_products`.`product_id` = `products`.`id` where `posts_products`.`post_id` in...
So I ssh'ed into the VM and ran top to see CPU and memory usage:
top - 05:43:55 up 2 days, 22:10, 1 user, load average: 8.41, 8.78, 8.56
Tasks: 108 total, 6 running, 102 sleeping, 0 stopped, 0 zombie
%Cpu(s): 0.3 us, 97.7 sy, 0.0 ni, 0.0 id, 0.0 wa, 0.0 hi, 2.0 si, 0.0 st
MiB Mem : 976.8 total, 51.2 free, 886.1 used, 39.5 buff/cache
MiB Swap: 0.0 total, 0.0 free, 0.0 used. 11.3 avail Mem
81 root 20 0 0 0 0 R 39.4 0.0 1607:41 kswapd0
32905 root 20 0 735020 15840 0 S 28.8 1.6 89:08.03 snapd
10 root 20 0 0 0 0 S 6.6 0.0 122:09.92 ksoftirqd/0
2470 root 0 -20 0 0 0 S 6.0 0.0 138:43.52 loop4
46769 mysql 20 0 1263340 348784 0 R 6.0 34.9 1:16.08 mysqld
5721 ghost 20 0 1013644 267416 0 R 4.0 26.7 203:52.35 node It looked like kswapd0 and snapd hogged the CPU. I googled them and learned that snapd is the system's package manager, and that kswapd is what runs when the system runs out of memory and starts swapping memory around. One website suggested that the fix was to stop snapd altogether. Not a good idea!
Instead I went for installing swap just like I did in https://www.wafrat.com/resizing-swap/#setting-up-the-swap.
As soon as set up the swap, the system stabilized: snapd and kswapd0 stopped using resources, and everything went back down.
top - 05:46:23 up 2 days, 22:13, 1 user, load average: 2.61, 6.89, 7.93
Tasks: 102 total, 1 running, 101 sleeping, 0 stopped, 0 zombie
%Cpu(s): 0.0 us, 0.0 sy, 0.0 ni,100.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
MiB Mem : 976.8 total, 168.0 free, 576.6 used, 232.1 buff/cache
MiB Swap: 4096.0 total, 3828.0 free, 268.0 used. 251.0 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
46769 mysql 20 0 1346884 234284 0 S 0.3 23.4 1:19.94 mysqld
1 root 20 0 168816 7608 5264 S 0.0 0.8 52:10.74 systemd
2 root 20 0 0 0 0 S 0.0 0.0 0:00.02 kthreadd 
I refreshed the page and voilà, the blog is back up!