This document introduces the automated partition management policies and usage in YMatrix.
In production environments, data partitioning is often done according to time. YMatrix provides some UDFs (User Defined Functions) used to manage partitions, and you can manually maintain partitions according to your needs. For example: one partition every day, one partition every 8 hours, etc.
But time is an infinitely extended dimension. When creating partition tables, administrators usually just create partitions in the future period in advance, and then regularly add new time period partitions. This undoubtedly increases operation and maintenance costs.
Automated partition management is to automate the work of manually maintaining partitions, thereby reducing the cost of administrators to maintain databases. include:
The automated partition management feature is included in the matrixts extension, so first create the matrixts extension:
=# CREATE EXTENSION matrixts;By calling set_policy, setting policies for the target table, partition maintenance automation is achieved:
Currently, there are 3 strategies:
Notes!
Starting from version 5.0, YMatrix has added theauto_partitioning_expolicy. It is recommended to use this policy instead ofauto_partitioningto implement advanced features such as batch creation of partitions, forced preservation of specific historical partitions, and customizing automatic partition operation periods.
The auto_partitioning policy is applied to the automated management of ordinary partition tables, and implements the following 2 automation functions:
Example, create a test partition table metrics, as follows:
=# CREATE TABLE metrics(
time timestamp with time zone,
tag_id int,
read float,
write float
)
DISTRIBUTED BY (tag_id)
PARTITION BY RANGE (time);Create an auto_partitioning automatic partitioning policy for the metrics table:
=# SELECT set_policy(
'metrics'::regclass,
'auto_partitioning'
);If you need to carry mode information, just pass the parameter
schema_name.table_name.
After setting the policy, call list_policy to view the policy:
=# SELECT * FROM list_policy('metrics'::regclass);
reloid | relname | class_id | class_name | action | seq | disabled | check_func | check_args | act_func | act_args | version
--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
17798 | metrics | 2 | auto_partitioning | retention | 1 | t | matrixts_internal.apm_generic_expired | {"after": "1 year"} | matrixts_internal.apm_generic_drop_partition | {} | 1.0
17798 | metrics | 2 | auto_partitioning | auto_create | 2 | f | matrixts_internal.apm_generic_incoming | {"before": "3 days"} | matrixts_internal.apm_generic_append_partition | {"period": "8 hours"} | 1.0
(2 rows)As you can see, auto_partitioning contains two operations in total:
The detection parameter of retention is {"after": "1 year"}, which means detecting whether the partition has been more than one year and then deleting it.
The detection parameter of auto_create is {"before": "3 days"}, which means to check whether a new partition needs to be created 3 days in advance; the execution parameter is {"period": "8 hours"}, which means to create a new partition with a span of 8 hours. This is the default setting for the auto_partitioning policy.
If you want to adjust these parameters, you need to call set_policy_action.
Example, adjust retention to automatically delete after 3 months; auto_create is adjusted 2 days in advance, with a time span of 1 day:
=# SELECT set_policy_action(
'metrics'::regclass,
'retention',
'3 months'
);
=# SELECT set_policy_action(
'metrics'::regclass,
'auto_create',
'{
"before": "2 days",
"period": "1 day"
}'
);Notes!
For action with only one parameter, just write the target value string directly, such asretention. For actions containing multiple parameters, the target value needs to be completed through JSON splicing. Such asauto_create.
The auto_splitting policy implements the default partition cutting operation for partition tables containing default partitions.
Example, create a partition table and create a default partition for it:
=# CREATE TABLE metrics(
time timestamp with time zone,
tag_id int,
read float,
write float
)
DISTRIBUTED BY (tag_id)
PARTITION BY RANGE (time);
=# CREATE TABLE metrics_others PARTITION OF metrics DEFAULT;Set the auto_splitting policy:
=# SELECT set_policy(
'metrics'::regclass,
'auto_splitting'
);View the policy:
=# SELECT * FROM list_policy('metrics'::regclass);
reloid | relname | class_id | class_name | action | seq | disabled | check_func | check_args | act_func | act_args | version
--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
17798 | metrics | 1 | auto_splitting | retention | 1 | t | matrixts_internal.apm_generic_expired | {"after": "1 year"} | matrixts_internal.apm_generic_drop_partition | {} | 1.0
17798 | metrics | 1 | auto_splitting | auto_split | 2 | f | matrixts_internal.apm_generic_older_than | {"age": "1 month 2days"} | matrixts_internal.apm_generic_split_default_partition | {"period": "1 month"} | 1.0
(2 rows)As you can see, the auto_splitting policy contains 2 operations, expired partition deletion retention and default partition automatic cutting auto_split.
Among them, {"age": "1 month 2days"} means that the oldest data in the default partition is earlier than January 2 days ago, and the current time is cut. {"period": "1 month"} means that the partition granularity after cutting is 1 month.
The auto_partitioning_ex policy is a partitioning policy that upgrades functions based on auto_partitioning. It contains some advanced management operations on partition tables:
The above operations will be described in detail in the following examples.
Create a partition table and create an apmtrap partition for it:
=# CREATE TABLE metrics(
time timestamp with time zone,
tag_id int,
read float,
write float
)
DISTRIBUTED BY (tag_id)
PARTITION BY RANGE (time);
=# CREATE TABLE metrics_invalid_apmtrap PARTITION OF metrics FOR VALUES FROM ('-infinity') TO ('2023-01-01');Set the auto_partitioning_ex policy:
=# SELECT set_policy(
'metrics'::regclass,
'auto_partitioning_ex'
);View the policy:
=# SELECT * FROM list_policy('metrics');
reloid | relname | class_id | class_name | action | seq | disabled | check_func | check_args
| act_func | act_args | version
--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
34917 | metrics | 4 | auto_partitioning_ex | retention | 1 | t | matrixts_internal.apm_generic_expired | {"after": "1 year"}
| matrixts_internal.apm_generic_drop_partition | {} | 1.0
34917 | metrics | 4 | auto_partitioning_ex | auto_create | 2 | f | matrixts_internal.apm_generic_incoming_ex | {"before": "3 days", "period": "8 hours", "end_time": "00:00:00", "batch_size": "0", "start_time": "00:00:00", "stor
age_type": "heap"} | matrixts_internal.apm_generic_append_partition | {"period": "8 hours"} | 1.0
(2 rows)auto_partitioning policy has been set "before": "3 days", "period": "8 hours", that is, the automatic partition management operation will create all partitions in the next 3 days of this table in advance, and the size of each partition is 8 hours. For example, if the current time is March 1 0:00, then automatic partition creation is triggered, and all partitions from March 1 0:00 to 8:00 to March 4 0:00 to 8:00. By 8:01 on March 1, the automatic partition management operation will start creating a new 8-hour partition, that is, the partition from 8:00 to 16:00 on March 4. The above design of the auto_partitioning strategy can ensure that all partitions within the next 3 days will always be ready in advance.
However, since other tasks may occupy this table, the SQL statement that triggers the partition creation by the automatic partition management operation may compete with other tasks for locks. To reduce the number of lock competitions, the batch_size option is added in auto_partitioning_ex. Settings with the following statement:=# SELECT public.set_policy_action('metrics'::regclass, 'auto_create', '{ "batch_size": "4" }');This means that once the automatic partition management operation starts creating a partition, more future partitions of batch_size will be created. Therefore, when the partitions from 0:00 to 8:00 on March 4 will be created at 0:00 to 8:00 on March 4, this time it will create four future partitions at one time, from 0:00 to 8:00on March 4,8:00 to 16:00,16:00 to 24:00, and0:00 to 8:00` on March 5.
This avoids triggering partition creation again between March 1 and March 4, reducing the number of times of lock competition.
0, which means that batch creation is not performed. At this time, the behavior of auto_partitioning_ex is consistent with auto_partitioning.100.Notes!
Although the batch creation function can reduce the frequency of lock competition from once a day to once a few days, once the automatic partition management operation executes specific SQL commands, it still cannot fundamentally avoid lock competition with other SQL commands. This is determined by the working principle of the database itself.
Therefore, from the perspective of database usage, still needs to set necessary monitoring and alarms for query and lock, automatic partition management operations, etc.
Force the preservation of specific historical partitions
As you can see from the table, we have created the apmtrap partition. This means that when you enable the automatic partition retention function of auto_partitioning/auto_partitioning_ex, the partition name contains the apmtrap string (such as `"sss_amptrap_sss""), and the partition will not be deleted even if it has met the recycling conditions, thus achieving permanent retention of these historical partitions.
This feature is designed to maintain a "trap" partition that receives data outside the regular time range (such as 1970-01-01). This trap partition will swallow useless data for the default partition, thereby suppressing the rapid growth of the default partition. At the same time, the trap partition will not participate in query and DDL operations, protecting the query performance of the table.
Customize automatic partitioning working period
From the above list_policy result, you can see "end_time": "00:00:00" and "start_time": "00:00:00". In fact, the start_time / end_time parameter is used to control the period of automatic partition management, thereby avoiding creating partitions during busy business periods. For example, start_time = 01:00:00, end_time = 04:00:00 means that automatic partition management is allowed from 1:00 to 4:00 am every day. For example, start_time = 22:00:00, end_time = 03:00:00 means that automatic partition management is allowed from 22:00 to 3:00 am every night.
start_time = 00:00:00, end_time = 00:00:00. The same timestamp means no restrictions, that is, work is allowed 24 hours a day.SELECT public.set_policy_action('table_name'::regclass, 'auto_create', '{ "start_time": "00:00:00", "end_time": "04:00:00" }');Notes!
The set time range should not be less than 1 hour, because the check period for automatic partition management is 1 hour by default. If the start and end time interval is less than 1 hour, it is very likely that the trigger opportunity will be missed. Arriving at theend_timedeadline does not mean that the partition processing will end immediately at this time. The triggered automatic partition management work will continue until it is completed.end_timecan only ensure that after this time, there will be no new round of automatic partition management checks to be triggered, but the automatic partition management work that has been triggered will not be forced to end. Therefore, when setting theend_timeparameter, you need to leave enough buffering time for the upcoming business peak.
From the above list_policy result you will notice that there is a column called disabled. This column is used to set the operation status. No matter which strategy currently exists, the status of the retention operation is disabled.
To enable this operation, call enable_policy_action:
=# SELECT enable_policy_action('metrics'::regclass, 'retention');To disable an action, call disable_policy_action:
=# SELECT disable_policy_action('metrics'::regclass, 'auto_split');drop_policy is the partition table deletion policy:
=# SELECT drop_policy('metrics'::regclass);Notes!
Each partition table can only set one partition policy, so the deletion policy only provides the partition table name.
apm_operation_log records each operation history of the user's configuration of APM. Examples are as follows:
=# SELECT username = CURRENT_USER, relname, class_id, class_name, action, operation, mod_field, old_val, new_val
FROM matrixts_internal.apm_operation_log ORDER BY ts;
?column? | relname | class_id | class_name | action | operation | mod_field | old_val | new_val
----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
t | apm_test.split_set | | | test_split | add | check_args | | {"age": "1 month"}
t | apm_test.split_set | | | test_split | add | act_args | | {"period": "2 weeks"}
t | apm_test.split_set | | | test_split | mod | act_args | {"period": "2 weeks"} | {"period": "12 hours"}
t | apm_test.split_set | | | test_split | mod | check_args | {"age": "1 month"} | {"age": "15 days"}
t | apm_test.split_set | | | test_split | mod | check_args | {"age": "15 days"} | {"age": "5 days"}
t | apm_test.split_set | | | test_split | mod | act_args | {"period": "12 hours"} | {"period": "2 days"}
t | apm_test.split_set | | | test_split | drop | | |
t | apm_test.split_set | | | test_split | add | check_args | | {"age": "10 days"}
t | apm_test.split_set | | | test_split | add | act_args | | {"period": "1 hour"}
(9 rows)apm_action_log records the history of automatic partition operation in the APM background. Examples are as follows:
=# (select * from matrixts_internal.apm_action_log order by ts limit 6) union all (select * from matrixts_internal.apm_action_log order by ts desc limit 6) order by 1;
ts | pid | reloid | relname | nspname | class_id | class_name | action | check_func | check_args | act_func
| act_args | state | message | details | addopt | client
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2022-11-18 15:48:45.963264 | 15776 | 18360 | mx_query_execute_history | matrixmgr_internal | 2 | auto_partitioning | auto_create | matrixts_internal.apm_generic_incoming | {"before": "3 days"} | matrixts_internal.apm_generic_append_part
ition | {"period": "1 day"} | START | action start | | | schedule
2022-11-18 15:48:46.165418 | 15776 | 18360 | mx_query_execute_history | matrixmgr_internal | 2 | auto_partitioning | auto_create | matrixts_internal.apm_generic_incoming | {"before": "3 days"} | matrixts_internal.apm_generic_append_part
ition | {"period": "1 day"} | SUCCESS | do action success | | | schedule
2022-11-18 15:48:46.172096 | 15776 | 18373 | mx_query_usage_history | matrixmgr_internal | 2 | auto_partitioning | auto_create | matrixts_internal.apm_generic_incoming | {"before": "3 days"} | matrixts_internal.apm_generic_append_part
ition | {"period": "1 day"} | START | action start | | | schedule
2022-11-18 15:48:46.332781 | 15776 | 18373 | mx_query_usage_history | matrixmgr_internal | 2 | auto_partitioning | auto_create | matrixts_internal.apm_generic_incoming | {"before": "3 days"} | matrixts_internal.apm_generic_append_part
ition | {"period": "1 day"} | SUCCESS | do action success | | | schedule
2022-11-19 00:48:45.992494 | 24930 | 18360 | mx_query_execute_history | matrixmgr_internal | 2 | auto_partitioning | auto_create | matrixts_internal.apm_generic_incoming | {"before": "3 days"} | matrixts_internal.apm_generic_append_part
ition | {"period": "1 day"} | START | action start | | | schedule
2022-11-19 00:48:46.064604 | 24930 | 18360 | mx_query_execute_history | matrixmgr_internal | 2 | auto_partitioning | auto_create | matrixts_internal.apm_generic_incoming | {"before": "3 days"} | matrixts_internal.apm_generic_append_part
ition | {"period": "1 day"} | SUCCESS | do action success | | | schedule
2022-11-21 00:48:47.708846 | 14091 | 18373 | mx_query_usage_history | matrixmgr_internal | 2 | auto_partitioning | auto_create | matrixts_internal.apm_generic_incoming | {"before": "3 days"} | matrixts_internal.apm_generic_append_part
ition | {"period": "1 day"} | START | action start | | | schedule
2022-11-21 00:48:47.812423 | 14091 | 18373 | mx_query_usage_history | matrixmgr_internal | 2 | auto_partitioning | auto_create | matrixts_internal.apm_generic_incoming | {"before": "3 days"} | matrixts_internal.apm_generic_append_part
ition | {"period": "1 day"} | SUCCESS | do action success | | | schedule
2022-11-22 00:48:46.552925 | 8539 | 18360 | mx_query_execute_history | matrixmgr_internal | 2 | auto_partitioning | auto_create | matrixts_internal.apm_generic_incoming | {"before": "3 days"} | matrixts_internal.apm_generic_append_part
ition | {"period": "1 day"} | START | action start | | | schedule
2022-11-22 00:48:53.25854 | 8539 | 18360 | mx_query_execute_history | matrixmgr_internal | 2 | auto_partitioning | auto_create | matrixts_internal.apm_generic_incoming | {"before": "3 days"} | matrixts_internal.apm_generic_append_part
ition | {"period": "1 day"} | SUCCESS | do action success | | | schedule
2022-11-22 00:48:53.261857 | 8539 | 18373 | mx_query_usage_history | matrixmgr_internal | 2 | auto_partitioning | auto_create | matrixts_internal.apm_generic_incoming | {"before": "3 days"} | matrixts_internal.apm_generic_append_part
ition | {"period": "1 day"} | START | action start | | | schedule
2022-11-22 00:48:55.625125 | 8539 | 18373 | mx_query_usage_history | matrixmgr_internal | 2 | auto_partitioning | auto_create | matrixts_internal.apm_generic_incoming | {"before": "3 days"} | matrixts_internal.apm_generic_append_part
ition | {"period": "1 day"} | SUCCESS | do action success | | | schedule
(12 rows)