Mysql – AWS RDS showing large growth in Write Ops/sec, despite low # DB connections

amazon-rdsinnodbMySQL

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:

write ops last 12 months
write ops last 2 weeks
db connections 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.

Best Answer

If those processes are the only DB Connections in the MySQL Instance, then you can only look to two sources of Write I/O

The VM housing MySQL

Since you are using Amazon MySQL RDS, you can access only the MySQL Instance inside their provisioned VM. There can be lots of reads from MySQL just for their internal purpose. Note that process ID 7 is rdsadmin. They could be rolling up stats on disk for MySQL Usage to report back to the RDS API they provide to have stats to present.

I am sure you could eliminate that by moving our data to MySQL on an Amazon EC2 instance.Of course, you would have to be responsible for your own stats.

InnoDB Architecture

InnoDB Architecture

InnoDB has many moving parts that require writes.

  • Buffer Pool
    • Data and Index Pages From InnoDB tables That Have Been Modified (Dirty) Must Be Systematically Written to the Double Write Buffer within System Tablespace (a.k.a. ibdata1)
    • Insert Buffer, which are changes to Secondary Indexes, Must Be Systematically Written to the Insert Buffer within System Tablespace
  • Log Buffer: When full, the changes within the Log Buffer must written to the Redo Logs (a.k.a. ib_logfile0, ib_logfile1). In a high write system, you should increase innodb_log_buffer_size to 256M to reduce writes to Redo Logs.
  • Rollback Segments / Undo Space: There are 1023 rollback segments inside System Tablespace. Every time you do INSERTs, UPDATEs, and DELETEs, one transaction will use a single Rollback Segment to save the previous state of data the transaction is about to change. There are internal threads dedicated to cleaning up the UNDO space. Therefore, lots of big writes (or even little writes) will require disk I/O from those internal threads that do the UNDO space cleanup.

I have written about this before

Related Question