Oracle SGA and PGA Memory Sizing and MySQL Memory Buffers - Oracle to Aurora MySQL Migration Playbook
Oracle UsageMySQL UsageSummary
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Oracle SGA and PGA Memory Sizing and MySQL Memory Buffers

Feature compatibility Amazon SCT / Amazon DMS automation level Amazon SCT action code index Key differences

Two star feature compatibility

N/A

N/A

Different cache names, similar usage.

Oracle Usage

An Oracle instance allocates several individual pools of server RAM used as various caches for the database. These include the Buffer Cache, Redo Buffer, Java Pool, Shared Pool, Large Pool, and others. The caches reside in the System Global Area (SGA) and are shared across all Oracle sessions.

In addition to the SGA, each Oracle session is granted an additional area of memory for session-private operations (sorting, private SQL cursors elements, and so on) called the Private Global Area (PGA).

You can control cache size for individual caches or globally, and automatically, by an Oracle database. Setting a unified memory size parameter enables Oracle to automatically manage individual cache sizes.

  • All Oracle memory parameters are set using the ALTER SYSTEM command.

  • Some changes to memory parameters require an instance restart.

Some of the common Oracle parameters that control memory allocations include:

  • db_cache_size — The size of the cache used for database data.

  • log_buffer — The cache used to store Oracle redo log buffers until they are written to disk.

  • shared_pool_size — The cache used to store shared cursors, stored procedures, control structures, and other structures.

  • large_pool_size — The caches used for parallel queries and RMAN backup/restore operations.

  • Java_pool_size — The caches used to store Java code and JVM context.

While these parameters can be configured individually, most database administrators choose to let Oracle automatically manage RAM. Database administrators configure the overall size of the SGA, and Oracle sizes individual caches based on workload characteristics.

  • sga_max_size — Specifies the hard-limit maximum size of the SGA.

  • sga_target — Sets the required soft-limit for the SGA and the individual caches within it.

Oracle also provides control over how much private memory is dedicated for each session. Database Administrators configure the total size of memory available for all connecting sessions, and Oracle allocates individual dedicated chunks from the total amount of available memory for each session.

  • pga_aggregate_target — A soft-limit controlling the total amount of memory available for all sessions combined.

  • pga_aggregate_limit — A hard-limit for the total amount of memory available for all sessions combined (Oracle 12c only).

In addition, instead of manually configuring the SGA and PGA memory areas, you can also configure one overall memory limit for both the SGA and PGA and let Oracle automatically balance memory between the various memory pools. This behavior is configured by the memory_target and memory_max_target parameters.

For more information, see Memory Architecture and Database Memory Allocation in the Oracle documentation.

MySQL Usage

Such as other databases, MySQL uses different memory buffers for different purposes. In MySQL, there are several storage engines that use different memory buffers. This section refers to InnoDB only.

MySQL provides control over how server RAM is allocated. Some of the most important MySQL memory parameters include:

Memory pool parameter Description

innodb_buffer_pool_size

The memory area where InnoDB caches table and index data.

optimizer_trace_max_mem_size

Buffer for optimizer traces.

binlog_cache_size

The size of the cache holding changes to the binary log during a transaction.

host_cache_size

Buffer area to store data on connections.

innodb_ft_cache_size

Very similar to innodb_buffer_pool_size but only for data related to FULL_TEXT indexes.

stored_program_cache

Cached stored routines per connection.

sort_buffer_size

Size of sort buffers used to sort data during creation of an InnoDB index.

Total memory available for a MySQL cluster

Controlled by selecting the DB Instance Class during instance creation:

DB Instance Class

Note

You can configure cluster-level parameters such as innodb_buffer_pool_size and binlog_cache_size using parameter groups in the Amazon RDS Management Console.

Examples

View the configured values for database parameters.

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'binlog_cache_size';
SHOW VARIABLES LIKE 'stored_program_cache';

View the configured values for all database parameters.

SELECT * FROM information_schema.GLOBAL_VARIABLES

Use the SET SESSION command to modify the value of parameters that support session-specific settings. Changing the value for one session has no effect on other sessions.

SET SESSION sort_buffer_size = 1000000;

Summary

Use the table below as a general reference only. Functionality may not be identical across Oracle and MySQL.

Description Oracle MySQL

Memory for caching table data

db_cache_size

innodb_buffer_pool_size

Memory for transaction log records

log_buffer

binlog_cache_size

Memory for parallel queries

large_pool_size

N/A

Java code and JVM

Java_pool_size

N/A

Maximum amount of physical memory available for the instance

sga_max_size or memory_max_size

Configured by the Amazon RDS or Amazon Aurora Instance class.

Consider the following example:

db.r3.large: 15.25GB
db.r3.xlarge: 30.5GB

Total amount of private memory for all sessions

pga_aggregate_target and pga_aggregate_limit

max_digest_length

View values for all database parameters

 
SELECT * FROM v$parameter;
 
SELECT * FROM information_schema.GLOBAL_VARIABLES

Configure a session-level parameter

 
ALTER SESSION SET ...
 
SET SESSION ...

Configure instance-level parameter

 
ALTER SYSTEM SET ...

Configured through parameter groups in the Amazon RDS Management Console.

For more information, see InnoDB Startup Options and System Variables and How MySQL Uses Memory in the MySQL documentation.