Prewarm an Amazon RDS for Oracle database to reduce the impact of lazy loading

Amazon Relational Database Service (Amazon RDS) uses Amazon Elastic Block Store (Amazon EBS) as an underlying storage. RDS snapshots (automated and manual) are saved in Amazon Simple Storage Service (Amazon S3). For RDS instances that are restored from snapshots, the instances are made available as soon as the needed infrastructure is provisioned. However, there is an ongoing process that continues to copy the storage blocks from Amazon S3 to the EBS volume; this is called lazy loading or initialization.

In this post, we deep dive in to the options available to reduce the time taken for lazy loading in your Amazon RDS for Oracle instance.

The Causes and Impact of Lazy Loading

Lazy loading can occur in various scenarios that require a restore from snapshots. The following list of activities require an EBS volume restore from RDS snapshot:

• Point-in-time recovery (PITR)
• Converting the RDS instance from Single-AZ to Multi-AZ
• Restoring from a snapshot
• Creating a new read replica

RDS instance creation from a snapshot typically takes few minutes, whereas PITR can take longer based on the amount of data generated in the transaction logs. After RDS instance creation, data blocks start to load from Amazon S3 to Amazon EBS in a process known as lazy loading. The process takes longer the larger the database size but doesn’t affect the availability of the database during lazy loading.

Once the RDS instance is restored and made available, clients can connect to it and the RDS instance is open for both read and write activity. During the query or transaction processing, if a requested data block is already in Amazon EBS, there is no additional latency to fetch it. When a requested data block is not in Amazon EBS, it is immediately loaded from Amazon S3 into the EBS volume, which may cause additional I/O latency. Subsequent requests for previously accessed data blocks don’t introduce extra I/O latency.

This latency may not impact all applications, but to minimize slowness due to lazy loading, it’s recommended to read all data blocks as soon as the restore process is complete.

However, in cases where certain applications must meet performance SLAs before releasing the database to users, a trade-off rises between immediate availability and sub-optimal performance until lazy loading finishes.

Depending on the specific use cases, you can adopt strategies outlined in this post to hasten lazy loading process, ensuring the system is released with optimal performance or balancing immediate availability with performance tolerance levels.

Approaches to decrease lazy loading time

The following table lists approaches to decrease the duration of lazy loading in an RDS for Oracle instance and the situation in which this approach is applicable. Each approach has its own advantages and disadvantages.

Note that read I/O occurs solely from the primary host in case of Multi-AZ scenarios. If lazy loading was due to enabling Multi-AZ, then prewarming is required only on the secondary host. You may need to reboot with failover and repeat the prewarming process on the new primary to prewarm both underlying hosts.

When comparing the performance impact between the preceding approaches, consider the following:

• RMAN validation:
  • RMAN validation operates at the block level and doesn’t require a data export.
  • It checks data block integrity directly within the database files.
  • For the most optimized method, it is recommended to use parameters p_parallel and p_validation_type set to PHYSICAL.
  • Typically, it is more efficient and less resource-intensive compared to Data Pump.
  • It offers a lower performance impact
  • It’s preferred for data integrity checks in large tables due to its optimized block-level operations.
• Explicit SELECT on all the large tables:
  • The SELECT statement retrieves only table blocks. However, you need to include additional hints or verification that a full scan is being performed and the blocks are not skipped based on any optimization in place.
  • The performance impact depends on the size of the table and the complexity of the query.
  • It can be resource-intensive, particularly for large tables, resulting in increased I/O and CPU usage.
• DBMS Gather Statistics:
  • DBMS_STATS can help to scan both tables and indexes
  • You can use the following parameters for optimization
    • estimate_percent => 100
    • cascade => TRUE
    • degree => 4 –> You can specify the desired degree of parallelism here
    • stattab => ‘YourStatsTable’ –> Replace ‘YourStatsTable’ with the name of the statistics table where you want to store the gathered statistics. This is optional, and if not specified, statistics are stored in the data dictionary.

• Using Oracle Data Pump:
  • Data movement (export) is involved, making it resource-intensive.
  • Higher I/O and CPU usage may occur during the export.
  • The performance impact can be higher comparing to other approaches.
  • You can expect a substantial performance and space impact, depending on the size of the table and data volume when using Oracle Data Pump.

In summary, RMAN validation, which operates at the block level, is generally more efficient and has a lower impact on performance when compared to Data Pump for data integrity checks in large tables. Running explicit SELECT queries to retrieve data from large tables and export using Oracle Data Pump both have significant performance impacts, especially for large tables. The choice should align with your specific requirements and performance considerations for your Oracle database.

Note that along with the above approaches, using RDS instance type and EBS volume with higher EBS throughput and IOPS will help to complete lazy loading faster, you may downgrade the instance class type and decrease the value of IOPS/Throughput once the lazy loading is completed.

Read replica approach while converting from Single-AZ to Multi-AZ

When converting from Single-AZ to Multi-AZ, lazy loading happens on the secondary host only. However, lazy loading still impacts write I/O latency because write IOPS are synchronously written on the secondary host. After you enable the Multi-AZ option, you can reboot with failover so the secondary host becomes the primary and then perform one of the prewarming scenarios discussed in this post.

To avoid the impact of lazy loading during a direct Multi-AZ conversion, an alternative solution is to employ a read replica approach. This involves creating a replica with a Multi-AZ configuration, allowing ample time for the completion of lazy loading, and subsequently promoting a read replica. With this method, you can effectively mitigate the potential negative effects caused by lazy loading in a direct Multi-AZ conversion scenario.

Note that this solution is only possible with Oracle Enterprise editions and Active Data Guard licenses in case of opening the read replica instance in read-only mode.

Decreasing lazy loading impact with RMAN validation

The following is a test scenario to evaluate the impact of lazy loading on RDS for Oracle instances. It also shows how prewarming an RDS for Oracle instance using RMAN validation can significantly enhance the performance of the database after restoring from an RDS for Oracle snapshot.

This example includes two RDS for Oracle instances. One instance was prewarmed by RMAN validation just after restoring from a snapshot, and the second instance was kept running without prewarming. The instances had the same configuration, with 1000 GiB database. The snapshot was restored on both instances, and RMAN validation was run on one instance only.

During prewarming with RMAN validation, The ReadLatency metric was high due to lazy loading. The RMAN validation process completed in 60 minutes on the prewarmed instance (1000 GiB). The following graph illustrates the ReadLatency and WriteLatency observed during the RMAN validation process on the prewarmed instance.

After the RMAN validation was complete, a SELECT query was run on both instances. On the prewarmed instance, the SELECT query completed within 40 seconds. In contrast, the same SELECT query, with the same number of physical IOPS, completed in 740 seconds on the non-prewarmed instance.

As shown in the following ReadLatency graph, the non-prewarmed instance reached up to 75 milliseconds, whereas it only took 2 milliseconds for the prewarmed instance.

Conclusion

In this post, we outlined the various approaches to minimize lazy loading duration within your RDS for Oracle instance. We also delved into the pros and cons of each approach. We trust that this information has offered valuable insights into your inquiries about lazy loading.

Your feedback is greatly appreciated. Should you have any questions or recommendations, share them in the comments section.

About the authors

Vetrivel is a Cloud Support DBA at Amazon Web Services, specialized in Amazon RDS, Amazon EBS, and Oracle workloads in the AWS cloud. With 12+ years of IT expertise, he leverages his experience to offer guidance and technical support to customers, empowering them to build resilient, reliable, and secure solutions within the AWS Cloud. Beyond his professional life, Vetrivel’s passions include traveling, discovering new destinations, relishing diverse cuisines, and participating in adventure activities.

Anas is a Cloud Support DBA at Amazon Web Services. He is specialized in relational databases, specially Oracle database architecture and performance tuning. Anas has a vast experience in database design, deployment, optimization and automation. Outside of work, Anas likes going on hikes, cycling and playing squash.