We are running a few Mysql server on EC2 instances and RDS instances on AWS. From time to time they suddenly spike in CPU, and/or memory usage but when we login into the instances, it's too late and we can't troubleshoot the reason. Sometimes I am fast enough to see problem and run
show full processlist;
but it doesn't show any suspicious activity. slow logs and general logs show 0 info, and the mysqld log on /var/log/mysqld.log hasn't been updated in days, so I can't get any information from there as well. so I would like to ask:
- If I am currently seeing the spike on live, is there anything I can do to see what is exactly causing the problem? (maybe a query stucked, a locked table, or anything else that may happen)
- If it's too late, and the spike happened when I was not seeing the monitoring, is there anyway besides the slow/regular logs where I could look?
- I am replacing the last sysadmin who knew a lot about DBA on mysql, and I am not that familiar with mysql administration, so if you could recommend something for me to read/watch/listen, I really appreciate that.