Scaling SQL Read Operations: Effective Strategies and Detailed Examples

Part of the Domain Knowledge Series: If you haven’t read my other articles yet, please refer to the following links:
1. Load Balancing in Distributed Systems: Exploring Concepts and Practical Demonstration
2. Exploring Caching in Distributed Systems: Concepts and Practical Demonstration

Recently, I’ve been thinking a lot about improving my Domain Knowledge. In the software engineering world, having good Domain Knowledge is essential for becoming a better software engineer. Today, I will write down some strategies to make SQL read operations more efficient as part of my self-directed learning.

Scaling SQL read operations involves optimizing your database and infrastructure to efficiently handle increasing read loads. Here are some strategies to scale SQL read operations.

Table of Strategies

1. Optimize SQL Queries
2. Use Read Replicas
3. Caching
4. Load Balancing
5. Vertical Scaling
6. Data Sharding
7. Use Content Delivery Networks (CDNs)
8. Asynchronous Processing

Optimize SQL Queries

Ensure that the SQL queries are well-optimized for performance. Use appropriate indexes, avoid unnecessary joins, and select only the required columns.

Example

Let’s consider a scenario where we have 2 tables: customers and orders. The orders table has a foreign key customer_id that references the id column in the customers table.

In case we want to retrieve orders for a specific customer whose name contains “Micheal”.

SELECT * FROM orders
JOIN customers ON orders.customer_id = customers.id
WHERE customers.name LIKE '%Micheal%';

In this query, the customer_name column is being used in a LIKE clause without an index. This can result in a slow query, especially when the customers table contains a large number of rows.

To optimize the query, we can add an index on the name column in the customers table and modify the query to select only the required columns from both tables.

-- MySQL / MariaDB
-- Adding an index on the 'name' column of the 'customers' table
ALTER TABLE customers ADD INDEX idx_customers_name (name);

-- Optimized query with an indexed column and specific columns selected
SELECT orders.order_id, orders.order_date, customers.name
FROM orders
JOIN customers ON orders.customer_id = customers.id
WHERE customers.name = 'Micheal';

In this optimized query, we:

• Use the customers.name column in the WHERE clause with an exact match = instead of a LIKE clause. This is more efficient when searching for specific values.
• Select only the required columns (orders.order_id, orders.order_date, customers.name) instead of usingSELECT *. Selecting only the necessary columns reduces data transfer and improves query performance.
• Add an index name idx_customers_name on the name column of the customers table. In this case, the database engine can use this index to quickly find the rows with the specified customer name, leading to faster query execution.

Use Read Replicas

Read replicas are copies of the primary database that are synchronized with the primary to handle read queries. The primary database handles write operations (INSERT, UPDATE, DELETE), while read replicas handle read operations (SELECT).

Read replicas can help offload read traffic from the primary database, distribute the read workload, and improve overall read scalability and performance.

Example

Suppose you have a primary MySQL database server with the IP address primary_db_ip, and you want to create a read replica of this database on another server. Steps you might do:

1. Make sure the MySQL server version on both the primary and replica servers is compatible and supports replication.

2. On the primary database server, you need to enable binary logging. Binary logging is essential for the replication process as it records changes to the data that will be replicated to the read replica.
In your MySQL configuration file (my.cnf or my.ini), make sure the following settings are present or added:

[mysqld]
log-bin=mysql-bin
server-id=1

log-bin=mysql-bin enables binary logging, and server-id=1 uniquely identifies the primary server. If you have multiple servers in a replication setup, each server should have unique server-id. After making changes to the configuration file, restart the MySQL server to apply the changes.

3. On the server where you want to create the read replica, configure the MySQL server:

[mysqld]
server-id=2

Set a unique server-id for the read replica. The value can be any positive integer, but it should be different from the server-id of the primary server.

4. Once the primary and replica servers are appropriately configured, you need to initiate the replication process. On the replica server, execute the following commands in the MySQL shell:

-- Replace 'primary_db_ip', 'replication_user', and 'replication_password' with appropriate values
CHANGE MASTER TO MASTER_HOST='primary_db_ip', MASTER_USER='replication_user',
MASTER_PASSWORD='replication_password', MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=123456;

• MASTER_HOST: Replace this with the IP address or hostname of the primary database server.
• MASTER_USER and MASTER_PASSWORD: Set these to the MySQL user credentials that have replication privileges on the primary server.
• MASTER_LOG_FILE and MASTER_LOG_POS: These values represent the binary log file name and position on the primary server at which the replication should start. You may obtain these values from the primary server using the SHOW MASTER STATUS; command.

5. After executing the CHANGE MASTER TO command, start the replication process on the replica server:

START SLAVE;

6. To ensure that the replication is working correctly, you can check the replication status on the replica server:

SHOW SLAVE STATUS\G

Look for the Slave_IO_Running and Slave_SQL_Running fields. Both should be set to Yes, indicating that replication is running without errors.

Caching

Implement caching mechanisms to store frequently accessed data in-memory. Caching solutions like Redis or Memcached can significantly reduce the number of database queries and improve response times.

Example

Let’s utilize Redis for this example.

# Import Redis Client library for Python
# Need to have the Redis library installed (pip install redis)
import redis

# Connect to Redis server
# Redis server running on localhost and listening on the default port 6379.
# The db=0 argument specifies the database number to use.
redis_client = redis.StrictRedis(host='localhost', port=6379, db=0)

def get_data_from_database_or_cache(key):
    # Try fetching data from cache
    data = redis_client.get(key)

    # If data is not found in cache or has expired (data has a TTL, Time To Live)
    if not data:
        # Fetch from the database
        data = fetch_data_from_database(key)
        # Store the fetched data in cache for future use
        # Specify data for the key with a time-to-live of 1 hour
        redis_client.setex(key, 3600, data)

    return data

This example demonstrates a simple caching mechanism using Redis, where frequently accessed data is stored in memory, reducing the need to repeatedly query the database for the same data and improving read performance. Remember that caching is most effective for data that doesn’t change frequently and can be safely stored in memory for a certain period. You should consider cache invalidation strategies to ensure that stale data does not cause inconsistencies in your application.

Load Balancing

Use a load balancer to distribute read queries across multiple database servers or read replicas. This helps distribute the read workload evenly and ensures that no single server is overwhelmed with requests.

Example

In this example, let’s leverage Nginx as a load balancer to distribute requests to three backend database servers. NginX is a powerful web server and reverse proxy that can also act as a load balancer to distribute incoming requests across multiple backend servers.

# Nginx configuration for load balancing
http {
    # Define IP addresses of 3 DB servers that will handle the incoming requests.
    # Nginx will distribute the requests among these servers 
    # based on the load balancing algorithm configured (round-robin by default).
    upstream database_servers {
        server 192.168.0.101;
        server 192.168.0.102;
        server 192.168.0.103;
    }

    # Configure Nginx to listen on port 80 (HTTP) for incoming requests.
    # Any request that comes to Nginx on port 80 will be processed by this server block.
    server {
        listen 80;

        # Configure to handle all incoming requests for any URL path(`/`).
        # Specify the backend servers defined in `database_servers` upstream block.
        location / {
            proxy_pass http://database_servers;
        }
    }
}

For further details, refer to the official documentation here: Load Balancing

Vertical Scaling

Consider upgrading the database server’s hardware to increase its capacity, such as adding more CPU, RAM, storage, or other hardware components. Vertical scaling can handle a certain level of read load growth.

Example

Let’s assume you have a PostgreSQL database server, and you want to increase its shared memory buffer size (shared_buffers) to improve performance. The shared buffers in PostgreSQL are used to cache frequently accessed data, reducing the need to read data from disk.

-- Upgrading the database server's RAM for PostgreSQL
ALTER SYSTEM SET shared_buffers = '16GB';

We’re using a SQL command ALTER SYSTEM SET to modify the shared_buffers parameter in PostgreSQL's configuration. The shared_buffers parameter determines the amount of memory allocated to the shared memory buffer pool.

The shared_buffers parameter is specified in PostgreSQL’s postgresql.conf configuration file. However, using the ALTER SYSTEM SET command allows you to modify the configuration dynamically without requiring a restart of the entire database server.

In this example, we increase the shared_buffers value to '16GB', allocating 16 gigabytes of RAM for the shared buffer pool. The actual value will depend on the available RAM on your server, the database size, and the nature of your workload.

After modifying the shared_buffers setting, it’s a good practice to reload the configuration to apply the changes:

-- Reload PostgreSQL configuration to apply changes
SELECT pg_reload_conf();

For further details, refer to these official documents here: sql-altersystem, runtime-config-resource, functions-admin.

Data Sharding

Sharding involves splitting your database into smaller, more manageable parts called shards. Each shard contains a subset of the data, and data with the same shard key resides on the same server. This allows for distributed storage and processing of data, enabling better scalability and performance.

Example

Let’s assume you have a large database table named orders, and you want to shard this table based on the range of customer IDs. The goal is to distribute orders from different customer ID ranges across multiple shards.

-- Shard 1 (customer IDs 1 to 1000)
CREATE TABLE orders_shard_1 (...) PARTITION BY RANGE (customer_id)
VALUES FROM (1) TO (1000);

In this step, a new table named orders_shard_1 is created, it stores orders for customer IDs ranging from 1 to 1000. The PARTITION BY RANGE clause is used to define that the table should be partitioned based on the customer_id column.

When inserting data into orders_shard_1, the database will automatically determine which partition to store the data in based on the range of customer IDs. All orders with customer IDs between 1 and 1000 (inclusive) will be stored in this shard.

-- Shard 2 (customer IDs 1001 to 2000)
CREATE TABLE orders_shard_2 (...) PARTITION BY RANGE (customer_id)
VALUES FROM (1001) TO (2000);

Similarly, a table named orders_shard_2 is created, and stores orders for customer IDs ranging from 1001 to 2000.

As the number of customers and orders grows, you may need to create additional shards to distribute the data effectively. Similarly, you might remove or merge shards if the data distribution changes over time.

Use Content Delivery Networks (CDNs)

For read-heavy applications with static or semi-static content, consider using CDNs. CDNs are a network of servers distributed across various geographic locations that cache and deliver content, such as CSS files, images, JavaScript files, and other static assets, to users from the server nearest to their location. Using CDN can reduce the load on your database, and significantly improve the performance.

Example

Let’s consider a scenario where you have a website with a frequently accessed blog page that retrieves data from a database. To improve the read performance and scale SQL read operations, you can use a CDN to cache the blog content.

<link rel="stylesheet" href="https://cdn.example.com/styles.css">

In this example, the web application references an external CSS file hosted on a CDN. When a user visits the web application, their browser will encounter the <link> tag and make a request to the specified URL (https://cdn.example.com/styles.css) to fetch the CSS file from the CDN.

Asynchronous Processing

Offload non-critical read operations to asynchronous tasks or background jobs.

Example

Let’s consider a Java example of asynchronous processing using threads to handle a time-consuming task while allowing the main program to continue with other tasks.

class MainClass {
    public static void main(String[] args) {
        String requestData = "request_data";
        System.out.println("Starting the read request asynchronously");
        Thread thread = new Thread(() -> {
            String result = performReadOperation(requestData);
            System.out.println(result);
        });
        thread.start();
        System.out.println("Continuing with other tasks while the read request is being processed");
    }

    public static String performReadOperation(String request) {
        try {
            Thread.sleep(3000);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
        return "Data for request '" + request + "'";
    }
}

1. The performReadOperation method simulates a time-consuming read operation by causing the thread to sleep for 3 seconds (3000 milliseconds).
2. In the main method, we use the Java Thread class to create a new thread for handling the read request asynchronously. The thread.start() method is called to start the new thread’s execution. After starting the thread, the main program continues executing the remaining code without waiting for the read operation to complete.

Output:

Starting the read request asynchronously
Continuing with other tasks while the read request is being processed
Data for request 'request_data'

As you can see, the main program starts the read request asynchronously and proceeds with other tasks while the read operation is being processed in the background. This allows the program to be more responsive and continue performing other tasks concurrently without waiting for the read operation to complete.

I’ve just briefly introduced some strategies for efficiently scaling SQL read operations. Feel free to explore other efficient strategies and share your thoughts in the comments.

I would love to hear your thoughts!

Thank you for reading, and goodbye!