Hello all!
Wanted to type up some initial thoughts on changes to enable horizontal scaling just so I could start a conversation and try to get some feedback.
I wanted to ask because, although I’ve done my best to try to study the code, I’m relatively new to this project while there are many people in this community who are experts.
I would greatly appreciate if you could please offer honest and direct feedback so I can contribute to this project I’ve grown quite fond of.
Session Management:
Editing the Startup Script to Allow OpenMRS to Boot Safely as a Replica:
In Regard to General Shared Storage:
In Regard to Shared Database Usage:
// 1. XA DataSource Configuration Class with horizontal scaling considerations
package com.example.config;
import com.mysql.cj.jdbc.MysqlXADataSource;
import org.apache.commons.dbcp2.managed.BasicManagedDataSource;
import javax.sql.XADataSource;
import javax.transaction.TransactionManager;
import org.apache.geronimo.transaction.manager.GeronimoTransactionManager;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
public class XADataSourceConfig {
// Use environment variables for configuration to support multiple instances
private static final String DB_URL = System.getenv("DB_URL");
private static final String DB_USER = System.getenv("DB_USER");
private static final String DB_PASSWORD = System.getenv("DB_PASSWORD");
// Instance identifier for logging and monitoring
private static final String INSTANCE_ID = System.getenv("INSTANCE_ID");
public void configureDataSource() throws Exception {
// Create and configure the XA DataSource
MysqlXADataSource xaDataSource = new MysqlXADataSource();
xaDataSource.setUrl(DB_URL);
xaDataSource.setUser(DB_USER);
xaDataSource.setPassword(DB_PASSWORD);
// Critical for XA transaction consistency across replicas
xaDataSource.setPinGlobalTxToPhysicalConnection(true);
// Enable replication-aware settings
xaDataSource.setRewriteBatchedStatements(true);
xaDataSource.setAllowMultiQueries(false); // Security consideration for distributed systems
xaDataSource.setInteractiveClient(false);
// Create the transaction manager with unique instance name
Properties txProps = new Properties();
txProps.setProperty("InstanceName", INSTANCE_ID);
TransactionManager transactionManager = new GeronimoTransactionManager(txProps);
// Create the managed data source with connection pooling
BasicManagedDataSource managedDataSource = new BasicManagedDataSource();
managedDataSource.setXaDataSourceInstance(xaDataSource);
managedDataSource.setTransactionManager(transactionManager);
// Configure pool sizes based on instance capacity
int maxConnections = calculateMaxConnections();
managedDataSource.setInitialSize(maxConnections / 4);
managedDataSource.setMaxTotal(maxConnections);
managedDataSource.setMaxIdle(maxConnections / 2);
managedDataSource.setMinIdle(maxConnections / 4);
// Shorter wait times for distributed environment
managedDataSource.setMaxWaitMillis(5000);
// Aggressive connection validation for distributed systems
managedDataSource.setTestOnBorrow(true);
managedDataSource.setTestWhileIdle(true);
managedDataSource.setValidationQuery("SELECT 1");
managedDataSource.setValidationQueryTimeout(2);
// More aggressive abandoned connection handling
managedDataSource.setRemoveAbandonedOnBorrow(true);
managedDataSource.setRemoveAbandonedTimeout(30); // 30 seconds
managedDataSource.setLogAbandoned(true);
// Add JMX monitoring for distributed environment
managedDataSource.setJmxName("pool.xa." + INSTANCE_ID);
// Bind to JNDI with instance-specific name
bindToJNDI(managedDataSource);
}
private int calculateMaxConnections() {
// Calculate based on available system resources
int availableProcessors = Runtime.getRuntime().availableProcessors();
long maxMemory = Runtime.getRuntime().maxMemory() / (1024 * 1024); // MB
// Conservative connection count based on resources
return Math.min(availableProcessors * 10, (int)(maxMemory / 100));
}
private void bindToJNDI(BasicManagedDataSource dataSource) throws NamingException {
Context ctx = new InitialContext();
// Include instance ID in JNDI name for isolation
ctx.bind("java:comp/env/jdbc/XADataSource/" + INSTANCE_ID, dataSource);
}
}
// 2. Transaction Service with retry logic for distributed environment
@Service
public class TransactionService {
private static final int MAX_RETRIES = 3;
private static final Logger logger = LoggerFactory.getLogger(TransactionService.class);
@Resource(name="java:comp/env/jdbc/XADataSource")
private DataSource dataSource;
@Transactional
public void performDistributedTransaction() throws SQLException {
int attempts = 0;
while (attempts < MAX_RETRIES) {
try {
executeTransaction();
break;
} catch (SQLException e) {
attempts++;
if (attempts == MAX_RETRIES) {
throw e;
}
// Exponential backoff
try {
Thread.sleep((long) Math.pow(2, attempts) * 100);
} catch (InterruptedException ie) {
Thread.currentThread().interrupt();
throw new SQLException("Transaction interrupted", ie);
}
}
}
}
private void executeTransaction() throws SQLException {
try (Connection conn = dataSource.getConnection()) {
try (PreparedStatement ps = conn.prepareStatement(
"INSERT INTO my_table (column, instance_id) VALUES (?, ?)",
PreparedStatement.RETURN_GENERATED_KEYS)) {
ps.setString(1, "value");
ps.setString(2, System.getenv("INSTANCE_ID"));
ps.executeUpdate();
}
}
}
}
// 3. Configuration in context.xml for Tomcat
<?xml version="1.0" encoding="UTF-8"?>
<Context>
<Resource name="jdbc/XADataSource/${INSTANCE_ID}"
auth="Container"
type="javax.sql.DataSource"
factory="org.apache.commons.dbcp2.managed.BasicManagedDataSourceFactory"
transactionManager="org.apache.geronimo.transaction.manager.GeronimoTransactionManager"
xaDataSource="com.mysql.cj.jdbc.MysqlXADataSource"
# Database Connection Settings
url="${DB_URL}"
user="${DB_USER}"
password="${DB_PASSWORD}"
# XA and Transaction Settings
pinGlobalTxToPhysicalConnection="true"
rewriteBatchedStatements="true"
allowMultiQueries="false"
interactiveClient="false"
# Instance Configuration
instanceId="${INSTANCE_ID}"
jmxName="pool.xa.${INSTANCE_ID}"
# Connection Pool Settings - Dynamic based on instance size
initialSize="#{calculateMaxConnections()/4}"
maxTotal="#{calculateMaxConnections()}"
maxIdle="#{calculateMaxConnections()/2}"
minIdle="#{calculateMaxConnections()/4}"
maxWaitMillis="5000"
# Connection Validation
testOnBorrow="true"
testWhileIdle="true"
validationQuery="SELECT 1"
validationQueryTimeout="2"
# Abandoned Connection Handling
removeAbandonedOnBorrow="true"
removeAbandonedTimeout="30"
logAbandoned="true"
# Performance Optimizations
timeBetweenEvictionRunsMillis="5000"
minEvictableIdleTimeMillis="60000"
# Transaction Manager Properties
transactionManagerProperties="InstanceName=${INSTANCE_ID}"
# Additional Safety Settings
fastFailValidation="true"
disconnectionSqlCodes="08S01,40001"
# Monitoring and Metrics
jmxEnabled="true"
enableMetrics="true"/>
<!-- Transaction Manager Configuration -->
<Transaction
factory="org.apache.geronimo.transaction.manager.GeronimoTransactionManagerFactory"
instanceName="${INSTANCE_ID}"
defaultTransactionTimeout="300"/>
</Context>
Thanks @jacob.t.mevorach for sharing all this progress here 
I think best would be for @raff to skim through it and chime in 
Thanks @jacob.t.mevorach for your continued work on this! A few comments from me.
Session management can be done with different levels of failure resistance. The easiest for us might be to just put a load balancer in front and have it connect a client always to the same instance. This approach doesn’t even require any changes to openmrs-core. One drawback is that if a node fails, the session is lost and user starts a new session on a different node. It’s not such a big issue as most operations are short-running and it’s fine in most cases for the user to login again and repeat. As long as the user sees a failure massage instructing him to do that. We could go a level higher and introduce session replication and as you suggest put session data in Redis. However, it’s not strictly necessary to support OpenMRS replicas. It simply adds to HA. In storing session data we might look for a solution that is Spring based instead of Tomcat (though I think pretty much everyone deploys OpenMRS with Tomcat these days).
In regards to Shared Database Usage. Database can be shared by design by multiple instances. A different thing is when you have a DB cluster as your backend with multiple masters or slaves. Then I’d put ProxySQL in front to load-balance connections and remove failed connections from the pool. It is generally not needed to add more complexity to the application to control, which DB node to use for writes and reads and have ProxySQL handle all that. I’ll be adding ProxySQL to our helm charts when choosing Galera cluster as a DB to have a proof of concept. In AWS you have RDS proxy. Aurora RDS might be able to deal with that on its own. The only remaining thing for HA is retrying queries in case of failures by getting a new connection to a working node, which your code tries to accomplish. It’s nice to have, but not strictly necessary for scaling OpenMRS instances.
Now comes the real challange with running multiple OpenMRS replicas that requires substantial amount of work. I don’t think we need anything like “swarm mode” for an OpenMRS instance. OpenEMR might have a specific use case to do that distinction, but in general it shouldn’t be needed. It doesn’t really matter for OpenMRS, which instance is the leader as long as we store data properly. What it means in practice is that we don’t just write data to a shared directory or use instance memory for caching (e.g. in the form of static fields on controllers with HashMaps), but use services that ideally can be distributed instead:
I’m planning to suggest a GSoC project to go through code and address 1st and the 2nd point, which is mostly straightforward, but tedious. 3rd might require someone who is familiar with Lucene and OpenSearch to make adjustments so it might be hard to find such a GSoC student.
If you are interested in trying the load-balancer approach with nginx for example to handle sticky sessions and then maybe adding session data persistence with Redis, I’m happy to work with you. I’m thinking of nginx, because we already have the gateway service that is proxying all requests that we could use for that specific purpose. Due to limitations in openmrs-core I would test the approach without using a shared data file system and have each instance run with its own data store, but connected to the same DB. It should mostly work as long as replicas are started after the system is running and DB is created.
Augmenting the data source to retry queries would also be a nice contribution. I don’t think it needs to be as complex as AI suggests 
See e.g. How to create custom retry logic for Spring Datasource? - Stack Overflow
Well, another thought is that adjusting the gateway service might not make much sense. Kubernetes has its own load balancer setup that we could use enabling sticky sessions so it’s even simpler and easier to handle with auto-scale.
Hello!
Thanks @raff and @mksd; it’s been really fun to get involved and dig into things and everyone has been really kind and supportive so far.
Regarding sticky sessions:
Regarding shared database usage:
On Everything Else:
I’ll be away for a few days but will available again on Tuesday.
All sounds right! I agree that with auto-scaling enabled session replication is a must. It’s always a question what kind of scale we are talking about.
I think that most OpenMRS installations will rely mostly on scaling DB. When it comes to OpenMRS instance replicas, it might be more about high availability and no downtime upgrades thus having maybe 2-3 replicas running at all times rather than auto-scaling to dozens of instances.
Based on what you wrote we won’t be recommending RDS proxy for Aurora since it provides single endpoint for connections already and distributes traffic, but we do need ProxySQL for Galera deployments in Kubernetes and for regular RDS instances. Is that right? Do we still need the code to remove broken connections from the connection pool in case Aurora node fails or is it handled by Aurora?
Let’s touch base on 1+2 points next week. I said it could be a GSoC project, but if you have spare cycles and interest in diving into Java code to help us out then great. I’ll start a thread on the design of the new storage service to abstract away the underlying implementation (writing to local dir, s3 or whatever). We could probably discuss that on a platform call next week. It would be great if you could join us.
Hello @raff ! Hope this message finds you well and that you’re having a good day.
The primary use case for RDS Proxy is when you have multiple apps using a single database. If I have three different apps each spinning up their own connections to a single database, it’s possible that bad connection handling logic could result in this overwhelming the resources of the database even if I have good application logic, monitoring and observability in place for the other two applications. By outsourcing how apps handle and create connections to RDS proxy you can get nice and predictable connection handling and do so effectively for multiple apps. It’s basically a managed service for the role that is served by a connection handler.
RDS Proxy does other things:
There are also some drawbacks:
It depends on the architecture you’re using and how your application is written on whether RDS Proxy makes sense to use. If it improves performance enough that it significantly lowers resources strain on the database, it might be cheaper to use RDS Proxy than not in some cases.
Having said that for our use case where we’re using Aurora Serverless v2 for our architecture I would say it doesn’t make sense to use RDS proxy. We already get a single endpoint and the version of the MySQL compatible aurora engine we’re using (for reference it’s version 3.08; which gets set as a variable first on line 65 and gets set as the engine for our database on line 734) is pretty advanced as is Aurora Serverless v2. It shouldn’t be that taxing on the database to spin up a bunch of connections quickly (it’s built for scale) and so if we use RDS proxy my prediction would be that we’d spend more money and get added latency on database calls (although I can be proved wrong in testing, I’d be willing to bet money I’m right on this).
In general, I’d recommend using Aurora Serverless v2 because 1/ it works for OpenMRS 2/ it’s performant and autoscales automatically 3/ most importantly, regarding cost, anything that’s using engine v3.07+ (we use v3.08) allows for scale to zero.
What this means is that if no one is using the database for more than 5 minutes (this can be set as a parameter if you’d prefer a different interval) the compute for the database will scale to zero and you’ll only pay for the data storage costs of the data written. The compute starts back up in seconds when someone goes to make a request after it’s been idle and scaled to zero.
I don’t think it’s possible to be more cost effective for OpenMRS while using RDS because any of the other options you can use will have some amount of fixed compute cost you’ll have to pay even when you’re not using the database. We don’t have that so if no one is using the database at 3 A.M. we won’t pay compute costs; they’ll be $0.
I also set up the database to have a reader serverless instance that scales along with the writer. In the event the primary serverless writer fails the serverless reader (which is already scaled up to where the writer was before it failed) will be promoted to become the new writer and then a new reader instance will be provisioned and scaled up automatically to where the new writer is and then in the event the writer failed again the process would repeat again. For reference this is implemented on line 746. The user shouldn’t notice a node failure. If the reader fails it’ll be replaced as well automatically; no need for manual intervention.
We also use multiple availability zones for the database as well. This means that there are multiple datacenters powering our database within a specific region so even if something happens with one datacenter, we will be resilient to that failure (same goes for compute with Fargate and file storage with EFS).
The other cool thing for Aurora Serverless is that it automates and manages the upgrades for the database engine for you. You can set it to do automatic minor version upgrades and even for major upgrades it’s pretty easy to do from the console. There’s documentation that outlines what the process looks like here: Upgrading the major version of an Amazon Aurora MySQL DB cluster - Amazon Aurora .
In general, I’ll try my best to make it to the platform calls. I’ll add it to my calendar. This week I won’t be able to make it but next week I should be able to.
It’s going to be hard for me to commit some sort of regular amount of time to coding. However, in general I’ll try to do my best to produce code example and answer questions asynchronously upon request.
In general (and this is true for anyone reading this thread as well) please don’t hesitate to reach out in the event you think I could be helpful with anything. I’m always happy to help 
:
Thanks Jacob. Let’s reschedule the discussion for next week platform call.