[MDEV-29415] status and variables history via system-versioned tables parttion Created: 2022-08-30  Updated: 2022-08-30

Status: Open
Project: MariaDB Server
Component/s: Plugin - feedback
Fix Version/s: None

Type: Task Priority: Trivial
Reporter: VAROQUI Stephane Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Blocks
is blocked by MDEV-22168 Supporting multiple engines with tabl... Stalled

 Description   

This can allow DBA to navigate historical global status and variables history and offer a post mortem analyse without any monitoring in place

A new SQL service plugin
The plugin schedule no bin logged queries

CREATE TABLE mysql.versioning_status( 
(
   VARIABLE_NAME varchar(64) PRIMARY KEY NOT NULL,
   VARIABLE_VALUE varchar(2048) NOT NULL
)  ENGINE=ARIA DEFAULT CHARSET=utf8mb3
WITH SYSTEM VERSIONING
PARTITION BY SYSTEM_TIME INTERVAL 1 MONTH AUTO;
 
REPLACE INTO mysql.versioning_status FROM SELECT  *  FROM information_schema.GLOBAL_STATUS ;
REPLACE INTO mysql.versioning_variables FROM SELECT *  FROM information_schema.GLOBAL_VARIABLES;

Thanks to auto partition and purge the size and fragmentation could be avoid and enabling compression on old partitions or storage in remote S3 can be a future improvement .

A cloud take care package can be offer from SkySQL or foundation to generate extra revenu and store metrics out of the user infrastructure

In case the plugin is unconnected to cloud storage
ALTER TABLE versioning_XXX DROP PARTITON pX to limit the table size


Generated at Thu Feb 08 10:08:24 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.