Flite Careers

Simplifying MySQL Partition Management Using Common_schema

Maintaining partitioned tables in MySQL can be tedious. In a reporting database that uses monthly date partitions, I’ve found that I can simplify many frequent partition operations using the common_schema QueryScript language. Below I will describe two specific examples.

Removing old data

I like to keep a 2-3 month window of data in some of my reporting tables. In order to maintain that window I periodically purge data more than 3 months old using the TRUNCATE PARTITION command. Since I want to apply the same purge logic to all tables matching a consistent naming pattern, I can use the common_schema foreach command to iterate over the tables based on an information_schema query and run the same TRUNCATE PARTITION command on all of the tables:

1
2
3
4
5
6
7
8
call common_schema.foreach(
  "select table_schema,table_name,partition_name 
    from information_schema.partitions 
    where table_schema = 'reporting' 
    and table_name like '%\_fact' 
    and table_rows != 0
    and partition_name <= concat('p',date_format(current_date() - interval 3 month,'%Y%m'))",
  "alter table ${1}.${2} truncate partition ${3};");

Adding new partitions

Recently I discovered that some of the partitioned tables in this reporting database did not have any partitions for 2016, so all of this year’s data ended up in the “pmax” partition. In order to fix this I used the common_schema foreach command to iterate over another information_schema query and run a REORGANIZE PARTITION command on all of the tables:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
call common_schema.foreach(
  "select p1.table_schema,p1.table_name,p1.partition_name
    from information_schema.PARTITIONS p1 
    where p1.PARTITION_NAME = 'pmax' 
    and p1.table_schema = 'reporting' 
    and p1.table_name like '%\_fact' 
    and exists (
      select NULL 
      from information_schema.partitions p2 
      where p2.table_Schema = p1.table_schema 
      and p2.table_name = p1.table_name 
      and p2.partition_name like 'p2015__'
    )
    and not exists (
      select NULL 
      from information_schema.partitions p2 
      where p2.table_Schema = p1.table_schema 
      and p2.table_name = p1.table_name 
      and p2.partition_name like 'p2016__'
    )",
    "ALTER TABLE ${1}.${2}
    REORGANIZE PARTITION ${3} INTO (
      PARTITION p201601 VALUES LESS THAN ('2016-02-01') ENGINE = InnoDB,
      PARTITION p201602 VALUES LESS THAN ('2016-03-01') ENGINE = InnoDB,
      PARTITION p201603 VALUES LESS THAN ('2016-04-01') ENGINE = InnoDB,
      PARTITION p201604 VALUES LESS THAN ('2016-05-01') ENGINE = InnoDB,
      PARTITION p201605 VALUES LESS THAN ('2016-06-01') ENGINE = InnoDB,
      PARTITION p201606 VALUES LESS THAN ('2016-07-01') ENGINE = InnoDB,
      PARTITION p201607 VALUES LESS THAN ('2016-08-01') ENGINE = InnoDB,
      PARTITION p201608 VALUES LESS THAN ('2016-09-01') ENGINE = InnoDB,
      PARTITION p201609 VALUES LESS THAN ('2016-10-01') ENGINE = InnoDB,
      PARTITION p201610 VALUES LESS THAN ('2016-11-01') ENGINE = InnoDB,
      PARTITION p201611 VALUES LESS THAN ('2016-12-01') ENGINE = InnoDB,
      PARTITION p201612 VALUES LESS THAN ('2017-01-01') ENGINE = InnoDB,
      PARTITION pmax VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB
  );");

Comments