This product or feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA products and features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

This page describes how to configure memory usage for a Cloud SQLinstance.

Introduction

When you create a Cloud SQL instance, you select an amount ofmemory 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, seeGeneral best practices andOperational 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 holdtable data forread 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 isspecified by theshared_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 thework_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 asVACUUM,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_memflag and the default value is 64 MB. Note that autovacuum workers also usemaintenance 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 databasesession, PostgreSQL allocates temporary buffers to hold the session-localtemporary table. The maximum amount can be specified by thetemp_buffersflag 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 thememory to run the query, PostgreSQL allocates additional memory tomaintain information such as system catalog cache and prepared query plans. Themaximum number of concurrent connections allowed to the database server can be configured by themax_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 thePostgreSQL documentation. To change or modify the flags listed in this section, seeConfigure database flags.

Monitor memory usage

Monitor your instance's memoryinCloud Monitoringregularly and keep it below the memory limit. A good practice is toset an alertin Cloud Monitoring to alert when the usage exceeds 90% of the limit for 6hours. This alert can warn you when your memory usage is close to thelimit constantly.

Additionally, monitor for out-of-memory incidents.To do that, set up alog-based metricfor theserver process .* was terminated by signal 9: Killedmessage 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-memoryevent occurs, you can increase the instance's memory.Alternatively, you can reduce the memory usage by limiting the number ofdatabase connections or lowering database flags such asshared_buffers,work_mem, ormax_connections. Lowering theseflags can limit the performance of your instance.

Out of memory

When there's insufficient memory to handle the database workload, as a last resort, theunderlying Linux operating system uses theout-of-memory (OOM) killerto end a process to release memory. Cloud SQL isconfigured so that theOOM killer targets only the PostgreSQL workerprocesses. The postmaster process is preserved in this situation so that itonly has to end all existing database connections and run a recoveryto protect the integrity of the database. If this happens, there are moments ofservice disruption and downtime to the database. In the PostgreSQL database log, messages like the following appear:

2021-10-2423:34:22.265UTC[7]:[663-1]db=,user=LOG:serverprocess(PID1255039)wasterminatedbysignal9:Killed2021-10-2423:34:22.265UTC[7]:[664-1]db=,user=DETAIL:Failedprocesswasrunning:SELECT*FROMtabORDERBYcol2021-10-2423:34:22.277UTC[7]:[665-1]db=,user=LOG:terminatinganyotheractiveserverprocesses2021-10-2423:34:22.278UTC[1255458]:[1-1]db=postgres,user=postgresWARNING:terminatingconnectionbecauseofcrashofanotherserverprocess2021-10-2423:34:22.278UTC[1255458]:[2-1]db=postgres,user=postgresDETAIL:Thepostmasterhascommandedthisserverprocesstorollbackthecurrenttransactionandexit,becauseanotherserverprocessexitedabnormallyandpossiblycorruptedsharedmemory.2021-10-2423:34:22.278UTC[1255458]:[3-1]db=postgres,user=postgresHINT:Inamomentyoushouldbeabletoreconnecttothedatabaseandrepeatyourcommand.2021-10-2423:34:22.278UTC[1255458]:[4-1]db=postgres,user=postgresCONTEXT:whileupdatingtuple(27,18)inrelation"tab"...2021-10-2423:34:22.558UTC[1255477]:[1-1]db=postgres,user=postgresFATAL:thedatabasesystemisinrecoverymode...2021-10-2423:34:25.579UTC[7]:[666-1]db=,user=LOG:allserverprocessesterminated;reinitializing...2021-10-2423:34:25.691UTC[1255482]:[1-1]db=,user=LOG:databasesystemwasinterrupted;lastknownupat2021-10-2423:31:53UTC2021-10-2423:34:25.776UTC[1255482]:[2-1]db=,user=LOG:databasesystemwasnotproperlyshutdown;automaticrecoveryinprogress2021-10-2423:34:25.789UTC[1255482]:[3-1]db=,user=LOG:redostartsat227/AB3594002021-10-2423:34:38.957UTC[1255482]:[4-1]db=,user=LOG:redodoneat229/4621F5082021-10-2423:34:38.959UTC[1255482]:[5-1]db=,user=LOG:lastcompletedtransactionwasatlogtime2021-10-2423:34:18.5535+002021-10-2423:34:39.290UTC[7]:[667-1]db=,user=LOG:databasesystemisreadytoacceptconnections

What's next

• Learn more aboutconfiguring memory usage in PostgreSQL.
• Tuning your PostgreSQL server.