0

I am in the process of upgrading/selecting resources for an application powered by a MySQL database running on Amazon RDS. I am considering introducing read replicas and some other strategies to increase availability under increasingly heavy loads. (The oldest story in the book.) As such, I have been monitoring my DB server and trying to get a grasp on where bottle necks are and such, and I am getting some very conflicting information on what the read/write ratio is:

Firstly, I will say that my application is relatively write heavy compared to many other types of applications. I know that much.

Using cloud watch in the AWS RSD control panel, I am typically seeing the following metrics:

Read Operation to Write Operation ratio: 1:20 (1 read for every 20 writes)

Read and write ratios for throughput and latency are generally consistent with that ratio.

This seems utterly absurd to me. I can't imagine how or why my app would be doing so many more writes than reads.

As such, I went looking for a second opinion, in the form of monitoring my DB in real time with MySQL Work Bench Administration Dashboard. There, the evidence is different. I am seeing the following on average:

Read Query Execution to Write Query Execution: 6:1 (6 reads queries executed for every 1 write query executed)

Either one of these monitoring tools is wildly inaccurate, or I have a fundamental misunderstanding of the data.

On to the questions:

  1. Why would these two tools be showing such wildly different results?

  2. Is there a better way for me to accurately determine the effective read/write ratio of my Database so that I can make prudent scaling decisions?

1 Answer 1

2

Read/write operations != read/write queries.

One "write" query may result in several disk IO write operations depending on the nature of your query, what engine you're using, what indices you have, etc.

4
  • 1
    a read query may cause writes as well
    – akuzminsky
    Nov 14, 2015 at 23:04
  • 2
    also maybe RDS cache takes some read I/O, which would lower this value from the disk point of view. Not sure if RDS includes those reads within the IOPS count though
    – Tom
    Nov 14, 2015 at 23:13
  • 2
    An appropriately-sized InnoDB buffer pool, once warned up, will of course dramatically reduce disk I/O on read queries, and write I/O is, conversely, further amplified out of proportion to queries by the writes to the redo log and binary log. (The officially supported storage engine in RDS for MySQL is InnoDB). (+1) Nov 15, 2015 at 2:23
  • Roger that. I do acknowledge that the two metrics I presented are different. It's just that the discrepancy seems staggering. My goal is to determine the potential efficacy of scaling read queries independent of writes. From what i am reading, select queries that are causing the storage engine to perform write operations could benefit from being routed to a read replica freeing up capacity to the master for additional write operations. Thoughts? Nov 15, 2015 at 4:36

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .