maria-developers team mailing list archive

[Sergei Golubchik <serg@xxxxxxxxxxx>]

Hi, Aleksey!

On Feb 17, Aleksey Midenkov wrote:
> Hi Sergei!
> 
> On Sat, Nov 9, 2019 at 12:03 AM Sergei Golubchik <serg@xxxxxxxxxxx> wrote:
> >
> > 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
> 
> Done.
> 
> > * you don't need to worry "if this name is already occupied"
> 
> I have to. There can be partitions created by hand.

Here I meant that partitions can be either AUTO or manually created. So
if a user had specified AUTO there can be no manually added partitions.
Which makes the implementation simpler.

> > * 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.

Agree. I meant that if partitions never overflow, then ADD will never
need copying.

> > 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.
> 
> 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:

I don't understand that. Using the parser in the main thread is easy, we
do that in many places. It would be the last reason for me to do a
separate thread.
 
> 1. I'm not sure if it is possible to work under MDL ticket from other
> thread. There will be assertion failures
> DBUG_ASSERT(thd->mdl_context.is_lock_owner());
> 
> 2. The threads must synchronized, so no big difference from
> single-threaded solution. I don't see how separate thread helps.

Okay. Single-threaded works too.
The tricky part is that you cannot do DDL (adding a partition is DDL)
under a DML metadata lock.

But I've just realized that it's solvable.

PARTITION BY LIMIT can overflow, even now, it's always approximate, so
you can add a new partition in the main thread after the main statement.
It doesn't matter if the statement has inserted more rows than LIMIT.

For PARTITION BY INTERVAL it doesn't work, it has to be rotated before
the main statement. Here you can use the existing fallback-and-retry
approach. Take a lock, check the rotation time. Fallback and retry with
a stronger MDL, if needed. In fact, the same approach will work for
PARTITION BY LIMIT too, so there is no need to have two different
implementations for partition rotation.

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