5

I have created read replica of RDS production database for reporting purposes. Cross-team is using the replica for their use.

Consumers of this replica are looking for additional indexes for faster search, but the developers are not in favour of that (adding these indexes to master) concerning more time for the insertion of records in the master database.

Is it possible to add index only on read replica of MySQL database? How this is practised in the industry? Or what other ways this problem could be solved?

1 Answer 1

4

If by any chance you are using AWS MySQL RDS as your database infra, you can refer this:

To further maximize read performance, Amazon RDS for MySQL allows you to add table indexes directly to Read Replicas, without those indexes being present on the master.

5
  • Thanks, that link was useful. I tried doing this on testing environment. Definitely, this requires to allow modifications to read replica.
    – K D
    Mar 18, 2019 at 11:50
  • How can I allow modifications on the read replica? Modifications made on the read replica are copied automatically to the Master? Jun 27, 2019 at 10:14
  • Amazon RDS console allows to configure replica settings to accept changes. This way replica is not a truly a read only database (accepting changes ONLY from master). Architects has to be very cautious for this kind of configuration but it is helpful in certain scenarios.
    – K D
    Aug 1, 2022 at 13:51
  • @KD how is what you describe achievable? Experimenting with the parameters group of a read replica, I observed that although the read_only parameter is modifiable, the innodb_read_only parameter (which is the one that actually needs to change in mysql in order to write to a database) is not modifiable.
    – billias
    Dec 21, 2022 at 21:19
  • By default innodb_read_only parameter of MySQL is set to 0 and thus you can make modification in the database. This question was around the restriction from AWS on read replica. Obviously, if you have set innodb_read_only=1 in the master database there will be no effect of changes in the replica settings in AWS console. Ref: dev.mysql.com/doc/refman/5.7/en/innodb-read-only-instance.html
    – K D
    Mar 7, 2023 at 2:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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