As the database is growing bigger, there is a higher chance for latency and low efficiency to come into consideration. The heavy workload should not be handled by one main database anymore. Instead, a database replica is designed to cover and divide the workload into small pieces.
Note: This document will not go in-depth with the replica technology.
Transaction Timeout: Transaction timeout refers to a mechanism in a database system that automatically cancels or rolls back a transaction if it exceeds a specified time limit. It is a way to prevent long-running or stuck transactions from holding resources indefinitely and causing performance issues or deadlocks in the system. There are some reasons behind this problem:
- Long-running queries: If a transaction includes one or more queries that take significant time to execute, it can exceed the transaction timeout.
- Lock contention: If a transaction waits for a lock on a resource held by another transaction for an extended period, it can exceed the transaction timeout. This can occur when multiple transactions access the same resources simultaneously, leading to contention and delays.
Read query can take up access to a resource, so other write operations must wait before the resource is released. As more users or processes attempt to access data simultaneously, the strain on the database's CPU, memory, and I/O capacity can result in slower response times and a degraded user experience. Furthermore, maintaining data consistency and accuracy under such loads adds another layer of complexity to database management.
To ease the query workload on the tables, we apply the database replication technique. The concept is instead of having only one database, one or many replicas of the database are created. Therefore any Read requests to the database server will be directed to the replicas and master (optional). The write operation does not necessarily wait for the read operation to work on the resource anymore. However, any other write operations can still take up the resources.
.png)
The reference to AWS
Firstly, let’s discuss the way we interact with our database in the project. We are leveraging GORM which is an ORM library providing a set of APIs and tools to automate the mapping process and handle the communication between the application and the database.
Simply explain, we will make any query requests to the database by a GORM instance. This instance is a singleton which is used by all the usecases that need to communicate with the database server. From the coding perspective, the singleton instance being accessed concurrently can lead to a race condition, so GORM already handles this problem by cloning the instance by itself (hidden in the package).
Note: What we mean by cloning is creating an entirely new object with remaining important information except the statement that has been built before.
Inherit from the idea of cloning a new database instance, we now apply the approach from GORM’s document suggestion which explicitly creates a new session. The session is simply a way of creating a new ORM instance that has some customized configuration. In addition, the session config contains a field - NewDB - which allows the instance to clone itself. Then when we make a query request to the database through the GORM package, we assign the database node to handle that query.
From the sequence diagram, we can find three places to assign the replica for the query to operate on.
GORM Query Redirection
At the beginning of our project, while the traffic is still low, only one primary database is enough. However, there is still a limit and when it comes, to some defined thresholds have been reached followed by a decrease in performance. Therefore, one of the best solutions we concluded is applying the replication technique to solve the problem. Under our observation, the database connections have been reduced for the master node which eases the stress for our database. Even if the issue reappears, thanks to replicas, we simply scale out the database without the effect on our application.
In conclusion, applying the replica technique offers notable advantages such as improved performance due to load distribution, higher availability in case of outages, and better data protection. However, these benefits come at the cost of increased complexity in system management, potential data consistency issues, and higher operational expenses. Ultimately, while replicas can greatly enhance a website's reliability and user experience, they require careful implementation and ongoing maintenance to ensure they deliver their intended advantages effectively.
