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?

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.

  • 1
    a read query may cause writes as well – akuzminsky Nov 14 '15 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 '15 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) – Michael - sqlbot Nov 15 '15 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? – Oliver Holmberg Nov 15 '15 at 4:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.