Scaling question, increasing DB connections and querying

I was looking at the scaling guide.
Ignition Server Sizing and Architecture Guide | Inductive Automation]

  1. Is each of the front-end gateways running the same projects unaware of each other?

My thought is that I want to let many people run queries to my DB very often, but I only have 8 DB connections.

  1. What is the best way to scale up the capacity to run more queries and more frequently?

I think I would need multiple DBs with the same information?

  1. For peak load times, if I have a few hundred users, do I need a few hundred db connections?

It's the gateways are making the database connections - not the users.

1 Like

The idea is that each front-end gateway would be running the same project, unaware of the existence of the other front-end gateways. They would all have a GAN connection to the back-end gateways, and ideally their own connection(s) to the database service. If they do not have a direct database connection, then any queries to the databases would have to be routed through the back-end gateways which (IIRC) only supports history and alarm journal queries. (Querying other non-standard tables requires non-standard handling that's not built into Ignition.)

Getting things to scale up requires learning how to best scale-up your database service. I know that with MySQL/MariaDB, the recommended path is to create a read-only replica, give all Ignition gateways connections to both, and design your queries so that the SELECT queries all go to the read-only clone while the UPDATE/INSERT queries go to the read-write master DB. And, obviously, scaling the databases onto larger servers (CPU/RAM) to let them handle more connections at once.

Where is the limit of 8 DB connections being imposed from? Is that from your IT team? Or somewhere else?

Ignition does a pretty good job with DB connection pools. Each gateway keeps a number of connections continuously open and on an as-needed basis allocates them to scripts or queries. Like @Transistor said, it's on a per-server basis, not a per-user basis. The only time I've had issues with the pool is when all of the connections in the pool are blocked behind a specific slow query that locked up a critical table, but that's arguably bad design on that query and not the pool.

In the absolute worst-case scenario, I created another connection from Ignition to the same DB with it's own tiny pool, and moved all the queries for that one critical table onto that connection. It didn't resolve the issue of that one table blocking up it's pool, but it did allow access to all the other tables in the same DB to continue using other pools while that one pool was jammed.

1 Like

It looks like 8 is the default, set on the Gateway as far as I know.

Okay. That default pool size in Ignition is a bit on the conservative side, since Ignition doesn't really know how big your SQL server is going to be (or how big the Ignition server is going to be). How high you could scale it sort of depends mostly on your DB server, but also a little bit on what sort of queries you're going to be throwing at it (long-running vs short-running, etc). Again, I only really know MariaDB, but the guidelines from AWS have worked well for me in that the server itself can nominally handle a number of connections on the order of 1-per-12MB-of-RAM. So, a MariaDB server with 4GB of RAM would max out in the ballpark of 4096/12=341 connections. If Ignition is the only thing going to that SQL server, and Ignition has more RAM to manage a bigger connection pool, 8 connections is definitely a lowball number.

Anecdotally, I've been running just fine with a pool sized roughly around half my number of active users. But most of the connection traffic is actually being used by history insertion, not user activity.

1 Like

Database connection status in the gateway will show you basic stats on how busy a give DB source is--queries per second and average duration. You can figure from that what your average pool size needs to be.

2 Likes
  1. Are ERP systems using a single DB to write to and then a number of other read only DBs?

  2. Or is their structure different for modularity?

  3. What is the standard practice for getting the data from the write DB to the read only ones?

1 & 2) Serious applications push the question to the DB itself. If you want super-high-availability, make such a DB cluster. If you want front ends to scale well, make a cluster with read-only replicas. If you want read-write scaling, set up a multi-master DB cluster. You want super cheap, use a single DB.

  1. Branded DB replication technology (in the DB). All the major brands have support for various kinds of replication.
2 Likes