How can I dynamically choose which MySQL server to point to?

Question

TL;DR: Vertical or Horizontal scaling for this system design?

I have NGINX running as a load balancer for my application. It distributes across 4 EC2 (t2.micro's cuz I'm cheap) to route traffic and those are all currently hitting one server for my MySQL database (also a t2.micro, totalling 6 separate EC2 instances for the whole system).

I thinking about horizontally scale my database via Source/Replica distribution, and my thought is that I should route all read queries/GET requests (the highest traffic volume I'll get) to the Replicas and all write queries/POST requests to the Source db.

I know that I'll have to programmatically choose which DB my servers point to based on request method, but I'm unsure of how best to approach that or if I'm better off vertically scaling my DB at that point and investing in a larger EC2 instance.

Currently I'm connecting to the Source DB using an express server and it's handling everything. I haven't implemented the Source/Replica configuration just yet because I want to get my server-side planned out first.

Here's the current static connection setup:

const mysql = require('mysql2');
const Promise = require('bluebird');

const connection = mysql.createConnection({
  host: '****',
  port: 3306,
  user: '****',
  password: '*****',
  database: 'qandapi',
});

const db = Promise.promisifyAll(connection, { multiArgs: true });

db.connectAsync().then(() =>
  console.log(`Connected to QandApi as ID ${db.threadId}`)
);

module.exports = db;

What I want to happen is I want to either:

set up an express middleware function that looks at the request method and connects to the appropriate database by creating 2 configuration templates to put into the createConnection function (I'm unsure of how I would make sure it doesn't try to reconnect if a connection already exists, though)
if possible just open two connections simultaneously and route which database takes which method (I'm hopeful this option will work so that I can make things simpler)

Is this feasible? Am I going to see worse performance doing this than if I just vertically scaled my EC2 to something with more vCPUs?

Please let me know if any additional info is needed.

so you have two connection and create two db(what you call promise) so when you decide whoch query should do what create a if clause and use one or the other ,. i don't see what the problem is, you coild also open 10 connctions at the same time and the write a scheduler to fullfill the the request to a specifc server. More problematic is the replication — nbk
– nbk, Commented Dec 1, 2021 at 23:13
@nbk I'm very new to using DBs so I didn't know what the limitations were with integrating them in express. — Nate Schroeder
– Nate Schroeder, Commented Dec 1, 2021 at 23:23
@nbk would having multiple connections open impact performance at all ? — Nate Schroeder
– Nate Schroeder, Commented Dec 1, 2021 at 23:28
every connection needs resources, so you you have a there a limizt dependuing on the system you use. there is nothing in the way to do self experimenting, with two connections you will not notice anything. but for starters look for a sql proxy and replication as mentionedm when you fear that a server will not handle the strain — nbk
– nbk, Commented Dec 1, 2021 at 23:28

Colin McCormack · Accepted Answer · 2021-12-01 23:48:43Z

Simultaneous MySQL Database Connection

I would be hesitant to use any client input to connect to a server, but I understand how this could be something you would need to do in some scenarios. The simplest and quickest way around this issue would be to create a second database connection file. In order to make this dynamic, you can simply require the module based on conditions in your code, so sometimes it will be called and promised at only certain points, after certain conditions. This process could be risky and requires requiring modules in the middle of your code so it isn't ideal but can get the job done. Ex :

const dbConnection = require("../utils/dbConnection");

//conditional {

         const controlledDBConnection = require("../utils/controlledDBConnection");

         var [row] = await controlledDBConnection.execute("SELECT * FROM `foo`;")

}

Although using more files could potentially have an effect on space constraints and could potentially slow down code while waiting for a new promise, but the overall effect will be minimal. controlledDBConnection.js would just be something close to a duplicate to dbConnection.js with slightly different parameters depending on your needs.

Another path you can take if you want to avoid using multiple files is to export a module with a dynamically set variable from your controller file, and then import it into a standard connection file. This would allow you to change up your connection without rewriting a duplicate, but you will need diligent error checks and a default.

Info on modules in JS : https://javascript.info/import-export

Some other points

Use Environment Variables for your database information like host, etc. since this will allow for you to easily change information for your database all in one place, while also allowing you to include your .env file in .gitignore if you are using github
Here is another great stack overflow question/answer that might help with setting up a dynamic connection file : How to create dynamically database connection in Node.js?
How to set up .env files : https://nodejs.dev/learn/how-to-read-environment-variables-from-nodejs
How to set up .gitignore : https://stackabuse.com/git-ignore-files-with-gitignore/

Collectives™ on Stack Overflow

How can I dynamically choose which MySQL server to point to?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related