1

This is the first time I have worked with a read replica so please bare with me. I tried multiple searches on here and google to no avail.

I know how a primary database instance and a read replica on RDS. Replication is working great, I can connect to both instances with no problem. My question is two fold.

  1. Do I need to update my API models so that read only operations are directed to the replica?
  2. Are there any tips to get the best performance from this setup? Can I adjust indexes on the master so that write commands have less indexes than tables I need for read? Lastly, some of my models read a dataset, return the value, then log the activity in an audit log table. Would I need to split this functionality out so the logging activity occurs on the main write database?

Appreciate any guidance here.

OperationVoid
  • 61
  • 8

1 Answers1

3

It depends. The replica will always be slightly behind its source instance, so if you have queries that have a requirement to read current data always, they need to read from the source instance.

Different queries even in your single app will have different needs in that respect. So you really need to decide on a case-by-case basis which query can read from the read-replica instance.

I wrote a presentation about this: Read Write Splitting in MySQL or the video.

Regarding performance, you may have different secondary indexes on the same tables on each instance. Indexes depend on which queries you will run, so you have to decide first which queries you will run on the source versus the replica.

But consider that if the read-replica goes offline for some reason, or falls too far behind or something, you might want to be able to run the same queries on the source instance until the replica is ready again. In that case, you'd want the same indexes on both, or else your query will run very slowly.

Another possibility is that the source instance dies for some reason. In the cloud, you must always have a plan for resources disappearing on short notice, including databases! It happens. So if the source instance dies, the read-replica could take over as your new source. But if it has different indexes, different tables, different instance size, or whatever, then it may not be prepared to be that substitute.

It's simpler to just make the source and replica the same in as many ways as you can. Less details to track, and you're prepared for failover.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • This is extremely helpful, thank you Bill. I reviewed the slides and the pattern out outlined makes sense. I just need to think through the implementation side and rethink all of the query activities today to classify them into the categories you have mentioned. I decided to use a read replica to take IOPS pressure off the master DB. With some effort, I should be able to achieve that by restructuring the queries. – OperationVoid Sep 29 '22 at 18:40
  • Another way to reduce IOPS pressure is to upgrade to an instance size with more RAM, so the frequently-requested data pages are more likely to be in RAM. InnoDB won't use IOPS to read data that's already in the buffer pool. – Bill Karwin Sep 29 '22 at 18:45
  • Really? I thought IOPS was a reflection of storage, not RAM. At least in the AWS RDS console, it notates that. – OperationVoid Sep 29 '22 at 20:57
  • Yes, IOPS is a measure of storage, or really activity reading or writing bytes from or to storage. "I/O operations per second." What I'm saying is if you have more RAM, then more data pages reside in the buffer pool, and a query that needs those pages doesn't need to trigger more IOPS to load the pages from storage. – Bill Karwin Sep 29 '22 at 22:31
  • Brilliant. Do you have a handy link to a connection framework in PHP for master/slave that your slides touched on? I imagine there is a repo somewhere with this framework so I don't have to create it from scratch. – OperationVoid Sep 30 '22 at 16:47
  • Sorry, I don't know of one, and I haven't coded in PHP for years. – Bill Karwin Sep 30 '22 at 16:58
  • As far as I know, almost all PHP projects use Laravel as a framework these days. I think you'd have to have a pretty good reason to use any other framework. You can add multiple database configs, see https://stackoverflow.com/questions/47725453/master-slave-configuration-in-laravel-5-5 – Bill Karwin Sep 30 '22 at 17:02