maria-developers team mailing list archive

[Aleksey Midenkov <midenok@xxxxxxxxx>]

Sergei,

On Mon, Feb 17, 2020 at 8:23 PM Sergei Golubchik <serg@xxxxxxxxxxx> wrote:
>
> 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.

I believe current implementation is better: compatibility with old
syntax and existing tables require just to add one keyword to make
things work. I don't believe one little loop makes it more complex.
OTOH forced syntax difference is something users nor programmers don't
like.

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

Agree. The old behavior will be returned in MDEV-21747.

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

I'm not familiar with that. Can we run SQL command from another SQL
command easily?

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

Rotation and auto-increment are different things. There is buffer of
ready empty partitions for rotation.

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



-- 
All the best,

Aleksey Midenkov
@midenok