What is Diskquota

Diskquota extension is a Diskquota management tool provided by Greenplum6.0 that allows you to control disk usage for database schemas and roles. After a DBA sets the diskquota upper limit for a schema or role, the diskquota worker process monitors the disk usage of the schema and role and maintains a blacklist of schemas and roles that exceed the quota upper limit. When a user tries to insert data into a schema or role in the blacklist, the operation is prohibited.

Diskquota is typically used in an enterprise where multiple departments share a Greenplum cluster and allocate disk resources in the cluster to different departments. The Resource Group function of Greenplum supports allocation of CPU, Memory, and other resources. Diskquota is a fine-grained allocation of disk resources, supports disk usage control at the Schema and role levels, and supports second-level delay in real-time disk usage detection, which cannot be achieved by traditional Cron Job-based disk management tools. An enterprise can assign its own schema to different departments to implement disk quota allocation for each department.

Diskquota is a soft limit on the number of disks that can be used. It is soft in two ways: 1. There is a delay (in seconds) in calculating the real-time usage of schemas and roles. Therefore, the disk usage of schemas and Roles may exceed the quota. The delay corresponds to the minimum refresh rate of the Diskquota model, which can be adjusted by using GUC diskquota. Naptime. 2. For insert statements, Diskquota only performs pre-query check. If the statement loading data dynamically exceeds the disk quota limit during execution, the query is not aborted. You can run show_fast_schemA_quota_view and show_fast_ROLE_quota_view to query the quota and current usage of each schema and role, and process the exceeding quota.

Diskquota architecture

At the beginning of Diskquota design, the following considerations were taken into account:

  1. Use a separate process to manage the DiskQuota model, or store real-time disk usage of database objects in system tables. System table PG_class stores metadata information about the data table. For example, relPages records the number of pages of the data table after analyze. We did not manage the DiskQuota model in this way. On the one hand, updating relpages and other information in real time for every data load and delete operation would degrade the database performance. On the other hand, Diskquota is based on the Extension framework and does not want to change the DB kernel, so it chooses to use a separate monitor process to manage the DiskQuota model (maintaining real-time disk usage for all tables in the Disk” worker process).
  2. How to implement communication between Greenplum Master and Segment for a single process that manages the DiskQuota model. For DiskQuota, the communication between Master and Segment is mainly real-time disk usage data for each Segment. Since diskquota is a cluster-level diskquota management tool, we only care about the global disk usage of the tables maintained by the Master node. Therefore, based on the SPI framework of Greenplum, the Diskquota worker process on Master can periodically query the disk usage of active tables on all segments by SPI, and calculate and summarize the disk usage of active tables on all segments in real time. Segments do not need to reside in the Diskquota worker process. All you need is a shared memory to store the active table.
  3. Diskquota performance and its impact on the Greenplum database. The Diskquota worker process only calculates disk usage for active tables in each query cycle, so its time complexity is proportional to the number of active tables. Diskquota does not consume I/O resources when the database is not loading data. The read-only operation of Greenplum does not interact with diskQuota; The relfilenode information of the data table will be written to the shared memory each time the data falls to the disk (new page application).

The final DiskQuota Extension architecture consists of the following four parts.

  1. The Quota Status Checker. Maintains the DiskQuota model, calculates real-time disk usage of schemas and roles, and generates blacklists of schemas and roles that exceed quotas.
  2. Quota Change the Detector. Monitor disk changes. INSERT, COPY, DROP, VACUUM FULL and other statements Change the size of the table. We call the changed table an active table. The Quota Change Detector stores the active table in shared memory for use by the Quota Status Checker.
  3. Quota Enforcement Operator. Responsible for canceling queries. When you perform INSERT, UPDATE, or COPY operations, if the schema or role of the table exceeds the disk quota, the query will be canceled.
  4. Quota Setting Store. Responsible for storing disk quotas for the schemas and roles defined by the DBA.

Quota Status Checker

The Quota Status Checker is implemented based on the Greenplum Background Worker framework and contains two bgWorkers: DiskQuota Launcher and DiskQuota Worker.

The Diskquota Launcher manages the Diskquota worker. Each cluster has only one launcher and runs on the Master node. The Laucher process is registered and runs when the database is started (when Postmaster loads the DiskQuota link library).

The Laucher process is responsible for:

  1. When the Launcher starts, start the Diskquota worker process for each database in the list of databases that have diskquota Extension started in the database Diskquota.
  2. When Laucher is running, listen for database Create Extension Diskquota and Drop Extension Diskquota requests, start or stop worker processes. And change the list of databases where DiskQuota Extension has been started in database DiskQuota.
  3. When the launcher exits properly, all diskquota worker processes are notified to exit.

The Diskquota worker process acts as the Quota Status Checkers. Each database that starts diskQuota Extension has its own worker process. One worker process is not used to monitor multiple databases because Greenplum and Postgres have a restriction that one process can access only one database. Since each worker process consumes database connections and resources, we set a limit of 10 on the number of databases that can be started simultaneously with Disk Extension. The Diskquota worker interacts with the Segment via the SPI, so the Diskquota worker also only runs on the Master node.

The Worker process is responsible for:

  1. Initialize the Diskquota model, read the disk usage of all tables from table diskquote. table_size, and calculate the disk usage of schema and role. For a non-empty database starting diskquota Extension for the first time, the DBA needs to call UDF diskquota.init_table_size_table() to initialize the table diskquota.table_size. This initialization needs to calculate the size of all the data files in the database, so depending on the database size, it can be a time-consuming operation. After initialization, table Diskquota.table_size will be automatically updated by the worker process.
  2. Naptime is used to periodically maintain the Diskquota model, including all tables, schemas, and roles whose disk usage exceeds the Diskquota quota.

The algorithm for refreshing the DiskQuota model is as follows:

  1. Get the latest disk quotas for schema and role. The quotas are recorded in table ‘diskquota.quota_config’.
  2. To get the disk usage of active tables, first call the SPI function to get the list of global active tables from the shared memory of all segments. Pg_total_relation_size (table_OID), pg_total_relation_size will automatically calculate the data table, index table, toast table, Total disk usage of FSM tables).
  3. Traverse the PG_class system table:
  4. If table is in an active table, calculate the change in disk usage of table and update table_size_map, namespace_size_map, and ROLE_size_map.
  5. If table is in an active table, the need_TO_flush flag for the table is true
  6. If the schema of the table changes, move the disk usage of the table from the old schema to the new schema.
  7. If the owner(role) of the table changes, move the disk usage of the table from the old role to the new role.
  8. Is_existed Flag of the table is true
  9. Walk through table_size_map to identify deleted tables based on IS_existed Flag and reduce disk usage of associated schemas and roles.
  10. Pass through the PG_NAMESPACE system table:
  11. Delete the corresponding schema from namespace_size_map.
  12. Compares the disk usage and quota of each schema, and blacklists the schemas that exceed the quota.
  13. Traverse the PG_ROLE system table:
  14. Delete roles from role_size_map.
  15. Compare the disk usage and quota of each role, and blacklist the role that exceeds the quota.
  16. Iterate over table_size_map and write the disk usage of the table to diskquota.table_size based on need_to_flush flag. The Update operation requires one SQL statement for each data item. To speed up the operation, batch Delete+ batch Insert is used instead of batch Update. Specifically, all tables that change in size are processed using the following two SQL statements: Delete from diskquota.table_size where tableoid in (need_to_flush OID list) and insert into diskquota.table_size Values (need_to_flush OID and size list).

Quota Change Detector

The Quota Change Detector is implemented through a series of Hook functions. For the Heap table, set Hook functions at smgrCREATE (), smgrextend(), and smgrTRUNCate () to record active table information. For AO table and CO table, the BufferedAppendWrite copy_append_only_data/TruncateAOSegmentFile position such as setting the Hook function, record the active table information. Active tables are stored in shared memory for each Segment, waiting for periodic queries from the Quota Status Checker. Since the active table is only a subset, the cost of refreshing the DiskQuota model each time is significantly reduced.

Quota Enforcement Operator

The Quota Enforcement Operator is also implemented through Hook functions. With the reuse of Greenplum’s Hook function ExecutorCheckPerms_hook, it can check whether the target schema or role is in the DiskQuota blacklist and abort the query that hits the blacklist before inserting and updating data each time.

Quota Setting Store

The disk quotas of diskquota are classified into schema and role and stored in the data table ‘Diskquota.quota_config’. Each database that starts diskquota stores and manages its own diskquota. It should be noted that although a role does not belong to a database, but is an object in a database cluster, diskquota limits the diskquota of a role to database specific. That is, a role has different quotas for different databases, and the disk usage of a role is calculated independently for different databases. The Quota Setting Store is defined as the following data table.

create table diskquota.quota\_config (targetOid oid, quotatype int, quotalimitMB int8, PRIMARY KEY(targetOid, quotatype));  `
Copy the code

Diskquota quick start

Installation and configuration

1. Open source diskQuota download address: Diskquota repo, the installation procedure is as follows

# source greenplum\_path.sh  
cd $diskquota;   
make;   
make install;
Copy the code

2. Create a database diskQuota that will be used to persist the diskQuota Extension database list.

create database diskquota;  
Copy the code

3. Add DiskQuota to the shareD_preload_libraries list

# enable diskquota in preload shared library.  
gpconfig -c shared\_preload\_libraries -v 'diskquota'  
\# restart database.  
gpstop -ar
Copy the code

4. Set the refresh frequency of DiskQuota Extension

# set naptime (second) to refresh the disk quota stats periodically  
gpconfig -c diskquota.naptime -v 2  
Copy the code

5. Create diskQuota Extension. For example, if you want to enable DiskQota Extension in the Postgres database, run the following statement:

# suppose we are in database 'postgres'  
create extension diskquota;
Copy the code

6. If the DIkquota extension is created on a non-empty database, the DBA will receive a message indicating that the UDF needs to manually initialize table table_size. Depending on the number of files already in the database, this operation may take time.

# after create extension diskquota on non empty database  
select diskquota.init\_table\_size\_table();  
Copy the code

7. Delete DiskQuota Extension. For example, if you want to disable DiskQota Extension in the ‘Postgres’ database, run the following statement:

# login into 'postgres'  
drop extension diskquota;
Copy the code

use

1. Set the disk quota for the schema

create schema s1;
select diskquota.set_schema_quota('s1'.'1 MB');
set search_path to s1;
create table a(i int);
# insert small data succeededInsert into a select generate_series(100);# insert large data succeeded, since diskquota is soft limitInsert into a select generate_series(1 10000000);# insert small data failedInsert into a select generate_series(100);# delete quota configuration
select diskquota.set_schema_quota('s1'.'1');
# insert small data succeededselect pg_sleep(5); Insert into a select generate_series(100); reset search_path;Copy the code

2. Set the disk quota of the role

create role u1 nologin;
create table b (i int);
alter table b owner to u1;
select diskquota.set_role_quota('u1'.'1 MB');
# insert small data succeededInsert into B select generate_series(100);# insert large data succeeded, since diskquota is soft limitInsert into b select generate_series(1,10000000);# insert small data failedInsert into B select generate_series(100);# delete quota configuration
select diskquota.set_role_quota('u1'.'1');
# insert small data succeedselect pg_sleep(5); Insert into a select generate_series(100); reset search_path;Copy the code

3. Query schema for disk usage and quota

select * from diskquota.show_fast_schema_quota_view;
Copy the code

4. Query the disk usage and quota of the ROLE

select \* from diskquota.show\_fast\_role\_quota\_view;

Copy the code