When to use read replica

We want to avoid overloading the primary by using read replica whenever possible:

  • Read queries
  • When we do not care about the latency of read replica update (1-2 seconds)

When to use primary instance

An example scenario where using the read replica for queries cause a problem

The retailer submits a new payment type form, and gets back to the payment type list page. Thee newly added payment type wasn’t there, since the latency between primary<->secondary was longer than the time between the form submission API call and the subsequent list payment types API call.

Question to ask:

Will the read call immediately come after a write?

The MySQL store maintains two connections - read-only and write

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
// NewMySQL creates the MySQL DB.
func NewMySQL() Store {
	var replica *sqlx.DB
	var primary *sqlx.DB
	if config.Bool("READ_REPLICA", false) {
		replicaConnector, err := db.NewConnector("read-only")
		if err != nil {
			log.Global().WithError(err).Fatal("failed creating database replica connector")
		}
		replica = replicaConnector.Db()
		go reportStats(replica, "read")

		primaryConnector, err := db.NewConnector("write")
		if err != nil {
			log.Global().WithError(err).Fatal("failed creating database primary connector")
		}
		primary = primaryConnector.Db()
		go reportStats(primary, "write")
	} else {
		dbInit(false)
		client := db.Client()
		go reportStats(client, "both")
		replica = client
		primary = client
	}
	return &mySQL{
		replica:         replica,
		primary:         primary,
		mySQLTimer:      vetrics.Metrics().Timer("mysql.timer"),
		mySQLMeterError: vetrics.Metrics().Counter("mysql.meter.error"),
	}
}