User data and write-ahead logging (WAL) data occupy most of the disk space in PolarDB for Oracle.
Query the disk space occupied by user data
Query the disk space occupied by the user data in databases and tables:
Run the \l command on your PostgreSQL client to view the disk space occupied by the data in each database. To perform this operation, the system must traverse the directory tree. This causes high CPU overhead and I/O overhead.
You can use your client to connect to a database in which the data occupies a large amount of disk space. Then, you can run the \d command to view the disk space occupied by the data from each table in the database.
Query the disk space occupied by WAL logs
SELECT pg_size_pretty(SUM(size)) FROM pg_ls_waldir();
- The wal_keep_segments parameter is set to an excessively large value. The wal_keep_segments
parameter specifies the minimum number of retained WAL files.
Solution: You can execute the SHOW wal_keep_segments statement to check the parameter value. You can also execute the SHOW wal_segment_size statement to view the size of each WAL file. The size can be used to estimate the minimum space that is occupied by WAL files.
- When the archive_mode parameter is set to on, WAL files are not archived at a proper
point in time.
Solution: You can check whether the archive_command parameter is properly configured.
- The synchronization latency for the pg_replication_slots view is long or an unused
replication slot exists.
Solution: Check whether the replication slot is connected to the database.
- The checkpoint process does not flush a specific amount of data in WAL logs to disks
at proper points in time.
Solution: Clear the data at proper points in time.