This page describes how to configure memory usage for a Cloud SQL instance.
Introduction
When you create a Cloud SQL instance, you select an amount of memory for the instance. As a PostgreSQL database's workload increases, the instance's memory usage increases. Instances that consume lots of memory can create a performance bottleneck that can sometimes lead to out-of-memory issues.
When a Cloud SQL instance runs out of memory because of an increased demand, it can cause database downtime. Therefore, it's important to configure the instance memory and the memory-related database flags properly and monitor the memory usage so that the instance operates in a healthy state.
PostgreSQL memory components are broadly divided into two sections:
- Global memory: this is shared across all processes to execute queries; for example,
shared_buffers
andmax_connections
. - Local memory: this is dedicated memory assigned to each connection; for example,
work_mem
,maintenance_work_mem
, andtemp_buffers
.
For other configuration considerations, see General best practices and Operational guidelines.
Memory usage and flags
Whenever there's high memory usage by Cloud SQL instances, the following questions might arise:
- Which query or process is using high memory?
- Are the memory settings adequate for the database activity?
- How do you change the memory settings?
When a PostgreSQL database operates, most memory usage occurs in a few areas:
Shared buffer: this is the shared memory that PostgreSQL allocates to hold table data for
read
andwrite
operations. For theread
operation, any data that's requested from disk is first fetched to RAM and then it's given to the client. Similarly, in PostgreSQL, when the data is requested (for example,SELECT * from emp
), it's first fetched from disk toshared_buffers
for caching, and then it's given to the client. The same thing happens with thewrite
operation.Shared buffer is also the shared memory area for all processes and connections for database activities such as data caching, connection caching, and Data Manipulation Language (DML) operations. The maximum that this area can allocate is specified by the
shared_buffers
flag and the default is 33% of the instance's memory. If the value ofshared_buffers
is high, the size of the data cached in memory is high.- Query work memory: as a query is run, PostgreSQL allocates
local memory for each operation such as sorting and hashing. The maximum it can
allocate for each operation of a query before writing to temporary disk files is configured by the
work_mem
flag, and the default value is 4 MB. If the value ofwork_mem
is high, the amount of data that can be sorted in the memory is high. - Maintenance work memory: some maintenance operations such as
VACUUM
,CREATE INDEX
,ALTER TABLE
, andADD FOREIGN KEY
require separate local memory that PostgreSQL allocates. The maximum amount for the back-end process that these operations use can be configured by themaintenance_work_mem
flag and the default value is 64 MB. Note that autovacuum workers also use maintenance work memory and the maximum can be overridden by theautovacuum_work_mem
flag. If the value ofmaintenance_work_mem
is high, the performance speed of theVACUUM
operation is high. - Temporary buffers: when a temporary table is used in a database
session, PostgreSQL allocates temporary buffers to hold the session-local
temporary table. The maximum amount can be specified by the
temp_buffers
flag and the default value is 8 MB. - Database connection: when a client connects to the database,
PostgreSQL creates a back-end process to serve the client session. Besides the
memory to run the query, PostgreSQL allocates additional memory to
maintain information such as system catalog cache and prepared query plans. The
maximum number of concurrent connections allowed to the database server can be configured by the
max_connections
flag. Each idle connection uses approximately 2 MB to 3 MB of shared memory. If the value ofmax_connections
is high, the instance can make more connections, but at the cost of the memory.
For the complete list of memory components in PostgreSQL, see the PostgreSQL documentation. To change or modify the flags listed in this section, see Configure database flags.
Monitor memory usage
Monitor your instance's memory in Cloud Monitoring regularly and keep it below the memory limit. A good practice is to set an alert in Cloud Monitoring to alert when the usage exceeds 90% of the limit for 6 hours. This alert can warn you when your memory usage is close to the limit constantly.
Additionally, monitor for out-of-memory incidents.
To do that, set up a log-based metric
for the server process .* was terminated by signal 9: Killed
message in Cloud Monitoring to count the out-of-memory events, and then alert each time such an event happens.
If your instance operates constantly above 90% of the memory's limit or an out-of-memory
event occurs, you can increase the instance's memory.
Alternatively, you can reduce the memory usage by limiting the number of
database connections or lowering database flags such as
shared_buffers
, work_mem
, or max_connections
. Lowering these
flags can limit the performance of your instance.
Out of memory
When there's insufficient memory to handle the database workload, as a last resort, the
underlying Linux operating system uses the out-of-memory (OOM) killer
to end a process to release memory. Cloud SQL is
configured so that the OOM killer
targets only the PostgreSQL worker
processes. The postmaster process is preserved in this situation so that it
only has to end all existing database connections and run a recovery
to protect the integrity of the database. If this happens, there are moments of
service disruption and downtime to the database. In the PostgreSQL database log,
messages like the following appear:
2021-10-24 23:34:22.265 UTC [7]: [663-1] db=,user= LOG: server process (PID 1255039) was terminated by signal 9: Killed 2021-10-24 23:34:22.265 UTC [7]: [664-1] db=,user= DETAIL: Failed process was running: SELECT * FROM tab ORDER BY col 2021-10-24 23:34:22.277 UTC [7]: [665-1] db=,user= LOG: terminating any other active server processes 2021-10-24 23:34:22.278 UTC [1255458]: [1-1] db=postgres,user=postgres WARNING: terminating connection because of crash of another server process 2021-10-24 23:34:22.278 UTC [1255458]: [2-1] db=postgres,user=postgres DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2021-10-24 23:34:22.278 UTC [1255458]: [3-1] db=postgres,user=postgres HINT: In a moment you should be able to reconnect to the database and repeat your command. 2021-10-24 23:34:22.278 UTC [1255458]: [4-1] db=postgres,user=postgres CONTEXT: while updating tuple (27,18) in relation "tab" ... 2021-10-24 23:34:22.558 UTC [1255477]: [1-1] db=postgres,user=postgres FATAL: the database system is in recovery mode ... 2021-10-24 23:34:25.579 UTC [7]: [666-1] db=,user= LOG: all server processes terminated; reinitializing ... 2021-10-24 23:34:25.691 UTC [1255482]: [1-1] db=,user= LOG: database system was interrupted; last known up at 2021-10-24 23:31:53 UTC 2021-10-24 23:34:25.776 UTC [1255482]: [2-1] db=,user= LOG: database system was not properly shut down; automatic recovery in progress 2021-10-24 23:34:25.789 UTC [1255482]: [3-1] db=,user= LOG: redo starts at 227/AB359400 2021-10-24 23:34:38.957 UTC [1255482]: [4-1] db=,user= LOG: redo done at 229/4621F508 2021-10-24 23:34:38.959 UTC [1255482]: [5-1] db=,user= LOG: last completed transaction was at log time 2021-10-24 23:34:18.5535+00 2021-10-24 23:34:39.290 UTC [7]: [667-1] db=,user= LOG: database system is ready to accept connections
What's next
- Learn more about configuring memory usage in PostgreSQL.
- Tuning your PostgreSQL server.