maria-developers team mailing list archive

[Sergei Golubchik <serg@xxxxxxxxxxx>]

Hi, Aleksey!

1.
It should be possible to enable/disable auto-creation.
For example, CREATE TABLE ... PARTITION BY SYSTEM_TIME ... PARTITIONS AUTO;
this solves few problems at once:
* a user explicitly tells when auto-creation should work
* you don't need to worry "if this name is already occupied"
* you can define that these partitions can never overflow (for INTERVAL)
* if you know that AUTO partitions never overflow, you can keep the old
  behavior for ALTER TABLE ADD PARTITION.

2.
Separate thread is an interesting solution. Nicely avoids lots of
problems. Still:
* it's asynchronous, all tests have to take it into account
* it's racy. "low probability" or not, with our number of users they'll
  hit it and rightfully will report it as a bug
* if LOCK TABLE prevents it, partitions can overflow

But I think these problems are easier to solve than those we'll face if
auto-creation will happen in the main connection thread.

So, let's keep your approach with a thread.

But instead of going through the parser and mysql_execute_command,
create a function that takes a TABLE or a TABLE_SHARE. And adds a
partition there. It'll fix the "racy" part. This function can be called
from a new thread.

As for the LOCK TABLES - if you're under LOCK TABLES, you can simply
call that function (to add a partition) directly at the end of the main
INSERT/UPDATE/DELETE statement. It'll solve the last problem, LOCK
TABLES won't prevent auto-creation.

A couple of style comments:
1. create a one-liner make_partition_name (or something) with:

    sprintf(move_ptr, "p%u", i)

and use it both in create_default_partition_names() and in your new
code. Just to make sure partition names are always generated uniformly
by the same function and if we'll want to change it to "pp%u" it should
be done only in one place.

2. don't overload operators, use, say,

    bool greater_than(size_t seconds)

But overall it's pretty good. A nice idea with a separate thread.

Regards,
Sergei
VP of MariaDB Server Engineering
and security@xxxxxxxxxxx

On Nov 07, Aleksey Midenkov wrote:
> revision-id: bd98ef106c8 (mariadb-10.4.7-55-gbd98ef106c8)
> parent(s): 6d1fa01e8f1
> author: Aleksey Midenkov <midenok@xxxxxxxxx>
> committer: Aleksey Midenkov <midenok@xxxxxxxxx>
> timestamp: 2019-08-19 12:01:14 +0300
> message:
> 
> MDEV-17554 Auto-create new partition for system versioned tables with history partitioned by INTERVAL/LIMIT
> 
> When there are E empty partitions left, auto-create N new empty
> partitions.
> 
> This scheme must not allow partition overflow. I.e. E-fill time must
> not exceed N-creation time. This means that low values for INTERVAL
> and LIMIT must not be allowed for auto-creation. In case when overflow
> is detected there is no need to do anything special: a warning will be
> issued and the user will run manual rebuild to redistribute records
> correctly. This is important because automatic ADD must be done fast,
> without forced rebuild, by the obvious reason.
> 
> Initial version implements hard-coded values of 1 for E and N. As well
> as auto-creation threshold MinInterval = 1 hour, MinLimit = 1000.
> 
> The name for newly added partition will be first chosen as "pX", where
> X is partition number and "p" is hard-coded name prefix. If this name
> is already occupied, the X will be incremented until the resulting
> name will be free to use.
> 
> Auto-creation mechanism is applied to every table having LIMIT or
> INTERVAL clause. Note that there is no much sense in specifying
> explicit partition list in this case and this is covered by
> MDEV-19903. The syntax to explicitly turn it on/off as well as
> user-defined values for E, N and name prefix is subject for further
> discussion and feature requests.
> 
> ALTER TABLE ADD PARTITION is now always fast. If there some history
> partition overflow occurs manual ALTER TABLE REBUILD PARTITION is
> needed.