When an application grows the associated database grows automatically. It needs to be scaled to a larger machine or server and overall configuration needs to be increased to handle the application and database performance requirement.
We need to identify the computing and storage resources required by different workloads and usage levels. There are two ways to solve the scalability problem which can be classified as:
- Vertical Scaling
- Horizontal Scaling
In this article, we will read in detail about scaling and the methods to implement scaling especially will explore horizontal scaling.
Vertical Scaling
Vertical scaling is often referred to as Scale Up process. In this, we are using a single machine or server and increasing resources in it as per the maximum configuration available for the machine or instance. There is a limit for a machine configuration, and we cannot go beyond that. If there is still the requirement for performance increases, we need to shift the application and database to a larger machine with a bigger configuration.
Increasing resources to the same instance or server are simple and more effective because machine resources are instantly available to the growing application. However, we cannot scale up forever and need to explore other options. In a cloud environment scaling the database vertically is very much simpler as we are not hosting the infrastructure and changing the database instance is a comparatively easy process however the movement from one instance to another instance is still required.
Horizontal Scaling
Horizontal database scaling means adding more servers to work on a single workload i.e., combining multiple smaller machines to construct a larger configuration.
There are 3 major ways of doing Horizontal scaling in Oracle.
- Oracle RAC cluster
- Replication
- Database Sharding
We can also combine these techniques to implement a more robust scaling solution for our database. We will see those later in this article.
Compute RAC Cluster
A cluster is a combination of multiple interconnected computers or servers that appear as one server to end users and applications. Oracle RAC enables you to cluster an Oracle database. Oracle RAC uses Oracle Clusterware for the infrastructure to bind multiple servers, so they operate as a single system.
Clusterware is a portable cluster management service that comes with Oracle Database. In addition, Oracle Clusterware enables Oracle high-availability infrastructure. Using Oracle Clusterware, we can create a clustered pool of storage that can be used by any combination of non-cluster and Oracle RAC databases.
Oracle RAC environments have a one-to-many relationship between the database and instances. There can be around 100 instances in an oracle RAC database that uses the same interconnect and, it can be managed by Oracle Clusterware. Combining multiple servers can provide higher throughput compared to a single instance.
Oracle RAC technology provides support for high availability and scalability for various types of applications. Oracle RAC infrastructure plays important role in creating enterprise-level computing architecture. Oracle RAC allows you to assemble or combine smaller commodity servers into a single cluster which helps to deploy business-critical applications.
Horizontal Scalability – Replication
Oracle classifies the replication into two parts:
Basic Replication
Replication means maintaining copies of a database on another system on the network. The simplest form of replication is read-only replicas. A read replica is the exact same copy of the database which is having full data. The updates are applied to the main database and the same is replicated in the replica database.
Using read replicas we can offload the reporting work or various other analysis work from the main database to the replica database. The read replicas are good to offload users i.e., workload scalability. This doesn’t help in terms of offloading the data volume. Below is the architecture for this kind of scaling.
Advance Replication
In Advance replication, data can be read and written on replicas. Advanced, symmetric data replication proves helpful for many types of applications which are having very specific requirements. It can be helpful for an application that works in a disconnected environment and works in OLTP mode. It can also help handle database HA in very critical applications. It can help for an application that requires multiple access points for the database for load distribution for a heavy application. Ensures high availability or more localized access. Oracle uses Oracle Data Guard for this.
Oracle Data Guard is the oracle software that provides the service like creating, managing, and maintaining monitor for multiple standby databases to enable the oracle database to handle disasters and corruption of data. Oracle Data Guard can switch any of the standby databases to the primary database in case of any failure in the primary database which can minimize the outage due to fault. Oracle Data Guard with native backup, restoration, and cluster techniques can provide a high level of availability and data protection. An Oracle Data Guard configuration can contain one primary database and up to thirty destinations.
For Advance replication requirements Oracle supports multi-master replication and snapshot sites. Now we will see further what multi-master replication is.
Multi-master Replication
Multi-master replication is more than the read replica technique. In the multi-master technique, data can be updated on multiple masters and each master records the changes those changes are then replicated to the other master or server. Each master is a supplier and consumer both.
This replication uses a loose consistency model where the same entries are modified simultaneously on both or different servers and when those change logs are sent across there can be a possibility of conflict which need to be resolved.
We cannot define the number of masters supported on it as theoretically it is unlimited and the same is the case for consumers and hubs. However, the supplier’s capacity to replicate the consumer depends on the configuration of the supplier.
Horizontal Scalability – Sharding
Sharding is a data tier architecture in which data is horizontally partitioned across independent databases. Each database is hosted on a separate server with resources like memory, CPU, flash, or disk associated with the database. The databases in this arrangement are called shards. And these shards combined with data disturbed is called single logical databases also known as sharded database.
In horizontal partitioning, the database table splits across other databases or shards so that each shard has the same table structure with a different set of rows in each database. This type of split table is also called a sharded table. Below is the architecture of database sharding.
Key benefits of Sharding include:
- Sharding clears performance issues or bottlenecks and makes it possible to scale performance and capacity by increasing the number of shards.
- It’s the shared-nothing infrastructure that eliminates single-point failure and provides very strong fault isolation. If any shards fail down or slow will not affect other shards’ performance or availability.
- Also, Sharding can work well with regulatory requirements. when there is a requirement of storing data in a specific region or geography. Also, you can place the data basis on the proximity to the consumer.
- Updates on shards can be applied separately. Applying changes to one shard doesn’t impact other shards.
Other than the above three individually we can use a combination of these available options to configure our scalability approach for the database. The below table talks about these options in detail.
Technology | Using Oracle RAC |
Read Replica – replicated from the Primary database to one or more Secondary databases. | Merging RAC’s transparent scalability and failover with the disaster protection provided by read replication is the like having the best out of both. Oracle Active Data Guard can be used with this to provide more scalability for reading Replica. |
Multi-Master Replication – Data is Replicated at secondary nodes where | implementing Oracle RAC with multi-master replication for each replica improves write scalability and allows fewer replicas which helps avoid conflicts. Golden Gate replication service is fully compatible with Oracle RAC and can be integrated easily. |
Database Sharding – Logical database is made of multiple oracle databases where data is shared on multiple databases. | Oracle database sharding provides data storage or sharding across multiple oracle databases delivering both storage and workload scalability on basis of the sharding key. We can use Oracle RAC on every shard or oracle physical database to provide High availability and better maintenance and to leverage the benefit of both technologies. |
Conclusion
We just observed there are multiple options for scaling the Oracle database. We can leverage any techniques or combination of multiple techniques as per our requirement to create a proper scalability solution for oracle. This article provides a reference point, and we can go for more documentation from oracle to identify the best fit solution as per our needs.
Skill summary:
1. SQL Server DB Administration
2. SQL Server DB Model Development
3. SQL Server TSQL or complex query writing
4. MSBI SSIS, SSRS, and SSAS expertise
5. Various other BI tool Expertise
- Understanding the Scalability in Oracle Database - March 9, 2023
- Migrating Oracle Database to Azure SQL Database - February 9, 2023
- Common SQL Interview Questions and Answers - January 6, 2023