maria-developers team mailing list archive
Mailing list archive
Re: bd98ef106c8: MDEV-17554 Auto-create new partition for system versioned tables with history partitioned by INTERVAL/LIMIT
On Sat, Nov 9, 2019 at 12:03 AM Sergei Golubchik <serg@xxxxxxxxxxx> wrote:
> Hi, Aleksey!
> 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"
I have to. There can be partitions created by hand.
> * 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.
Fast ADD is performance consideration. Making data copy on
auto-creation is feature killer.
> 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.
The whole point of thread was to use parser. For direct alter I'd
prefer to call it at the end of the main statement. Besides:
1. I'm not sure if it is possible to work under MDL ticket from other
thread. There will be assertion failures
2. The threads must synchronized, so no big difference from
single-threaded solution. I don't see how separate thread helps.
> 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.
> 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.
All the best,