Multi region database guide

greg · March 29, 2023, 8:47pm

@pier Hmm … yes if your request was going to the AMS vm or the LAX vm was actually connecting to the AMS database, that would explain slowness. As for how to see which database vm the app’s vm is actually using for a read (for writes, you know of course!) I’m not sure . I don’t know if the logs for the database (after all, a database on Fly is just another app behind the scenes - you can even clone their repo and make your own version of it … at least you could with v1, not sure if that’s the case with the v2) show.

I was just looking how I revealed debugging info when trying using Planetscale’s read replicas (this time with an Express app, also Node, and since they use MySQL-only, with the mysql2 client). The same idea, connecting to the closest one, except instead of using Fly’s internal magic to decide which is the closest one, here it does some logic Ignore that though - you can see here I made a read and write route, and after doing a query they return the region they were served from:

github.com

fly-apps/nodejs-planetscale-read-replicas/blob/main/server.js#L21


      
              usingPrimaryRegion,
              usingDatabaseHost
          } = require('./database');  // database.js
          
          
app.disable('x-powered-by');
          
          
app.get('/', (req, res) => {
              res.send('hello world')
          })
          
          
app.get('/read', (req, res, next) => {
              var startTime = new Date().getTime();
          
          
    db.query('SELECT * FROM fruits ORDER BY id DESC LIMIT 3', function (err, rows) {
                  if (err) {
                      return next(err);
                  }
          
          
        // show what's going on:
                  res.json({
                      time:  new Date().getTime() - startTime, // how long the query took (ms)

The Fly region being got from an environment variable that Fly apps provide you with at run-time:

github.com

fly-apps/nodejs-planetscale-read-replicas/blob/main/database.js#L7


      
          'use strict';
          
          
const url = require('url')
          const mysql = require('mysql2')
          
          
// 1. where is the app running?
          var usingFlyRegion = process.env.FLY_REGION ? process.env.FLY_REGION : "unknown" // e.g "lhr"
          
          
// 2. is the app running in the same general location as the primary database? If so don't waste time
          // picking a database from a list of preferences. Use the first (possibly only) one (which is the primary):
          var usingPrimaryRegion = (process.env.FLY_REGION && process.env.PRIMARY_REGION && process.env.FLY_REGION === process.env.PRIMARY_REGION);
          
          
// 3. which database should be connected to?
          var databaseUrl = process.env.DATABASE_URL; // first: assume no choice and there's only one
          if (databaseUrl.includes(',')) {
              // ah, there's more than one (we expect a comma-separated sting). So pick the closest one
              databaseUrl = nearestDatabaseUrl(usingFlyRegion, usingPrimaryRegion, databaseUrl.split(','))

The PRIMARY_REGION is manually provided in the fly.toml, as of course you know the region your primary database is in.

Here I returned JSON however you could return those region values in headers instead.

Topic		Replies	Views
Early look at Elixir packages for globally distributed deployments with Postgres DBs Phoenix elixir , distributed , postgres , live_view	15	3604	February 15, 2023
Are there any Managed Postgres providers located in the same data centres as Fly’s servers?	1	216	September 1, 2023
Price of running Phoenix LiveView in multiple regions – Fly vs AWS live_view	2	1160	August 2, 2022
Multi-region postgres + elixir using fly_postgres and fly_rpc Phoenix	11	1163	November 17, 2022
Django in multiple regions with Postgres Django postgres	3	507	June 11, 2023

Multi region database guide

Related topics