Database size shows up sudden increase in utilitzation while all the schemas are contributing to only a small percentage of the size
Article ID: 296749
Updated On:
Products
Issue/Introduction
This fix applies to the following situations:
-The database suddenly takes up a large amount of space
-The database is utilized much more than usual. Database is growing at regular rate over only a few minutes
-Overall cluster utilization growing
If some or all of these situations arises, and the corresponding schemas contribute to a fraction of the total DB projected size, this may cause users to worry about what is happening.
Environment
Greenplum Database.
Resolution
If a database suddenly shows abnormally large size, the first thing to check for is spill.
SELECT * FROM gp_toolkit.gp_workfile_usage_per_query;
Lots of inner workfiles being created means lots of spill files are generated. It is likely in this circumstance that a few transactions are generating spill files, possibly by using multiple temp tables within the session.
Killing the transactions or terminating the session will both resolve the issue.
User-created temp tables will not always show up as spill. To help prevent this, unfortunately Postgres doesn’t support the read-uncommitted isolation level. T
here is a plan to implement the hard-limit feature to diskquota (which allows Greeplum to terminate a running query which takes up too much disk space. However, there’re some challenges when implementing the hard-limit feature:
1. Diskquota is an extension and Greenplum doesn’t provide easy hooks that allow us to inspect/monitor uncommitted tables' meta-data. We recommend customers use the object_access_hook to inspect uncommitted tables' meta-data. The basic idea is that we can get the reloid and the underlying relfilenode . We put the reloid-relfilenode pair to shared memory so that other backend can get the table’s meta-data and fetch the size of the table via stat(2) .
2. Some tables may use toast/AO/CO table as underlying storage and they may have indexes. The object_access_hook triggers when the primary table is built and then it triggers when the index is built. Hence, it’s difficult for us to build the table’s meta-data in one object_access_hook trigger. Filesystem level (relfilenode based) usage some disk tools can provide but won’t be able to connect it to particular table until it is committed. A hard limit is under development, but not yet implemented.
Feedback
