I have a Multi-AZ AWS RDS instance running MYSQL 5.5
I've noticed that my write ops/sec are growing fairly rapidly, despite low (and sometimes zero) DB connections - see charts for Avg write ops/sec over last 12 months and last 2 weeks, and Avg DB connections over last 2 weeks:
I know the overall level of write ops/sec is low in the scheme of things, but I'm expecting more volume (like 100x) over the next few months and want to make sure I'm on top of any issues.
I'm trying to understand what is causing these write ops. I've tried connecting to the RDS instance and executing:
show full process list
and this shows:
+--------+----------+---------------------------------------------------+-------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+----------+---------------------------------------------------+-------+---------+------+-------+------------------+
| 7 | rdsadmin | localhost:51067 | mysql | Sleep | 9 | | NULL |
| 705938 | rdsuser | ip-xx-xx-xx-xx.eu-west-1.compute.internal:xxxx | NULL | Query | 0 | NULL | show processlist |
+--------+----------+---------------------------------------------------+-------+---------+------+-------+------------------+
ie no active threads.
Appreciate any thoughts on what's going on.
UPDATE Thanks to Roland's response, I did some further digging. In particular, I adding general logging and slow query logging to the RDS instance and saw that a cron job I had running every minute was making unnecessary queries that was making the instance do quite a lot of un-needed work.
I switched on logging by creating a new Parameter Group in RDS Management console and setting:
general_log = 1
slow_query_log = 1
long_query_time = 1
Note that I also used
CALL mysql.rds_rotate_slow_log;
and
CALL mysql.rds_rotate_general_log;
to rotate the log files - as they get pretty large pretty quickly.