Slow Instance Reboot Due to Too Many Inodes

When a large number of complex SQL statements are executed on an RDS for PostgreSQL instance, temporary files are stacked and an out of memory (OOM) exception occurs. In this case, the instance reboots slowly and services are unavailable for a long time.

If the SQL statements involve operations such as sorting, hash joins, and aggregation and the memory usage exceeds the value of work_mem, temporary files are generated. If a large number of such SQL statements are executed, an OOM exception occurs and the OS kills the database process. The kernel does not clear the temporary files, causing stacked temporary files. Too many temporary files slow down the database startup. That is because all temporary files need to be deleted before the database process can start.

Optimize the SQL statements or increase the value of work_mem to reduce the number of temporary files generated.

There are a large number of tables in an RDS for PostgreSQL instance. At a certain time, connections to the instance and workloads increase sharply. The database process memory is used up and an OOM occurs. Then the instance reboots slowly, and services are unavailable for a long time.

During the reboot, the kernel process traverses all tables and flushes data in the OS cache to disks (fsync). If there are too many table objects, the traversal takes a long time and slows down the reboot.

• Ensure that there are no more than 20,000 tables in a single instance and no more than 4,000 tables in a single database. For details, see Instance Usage Suggestions.
• Configure memory monitoring on the service side and expand the memory specifications if necessary to avoid OOM issues. Pay attention to the Inodes metric and control the number of objects.