Sergei, On Mon, Feb 17, 2020 at 8:23 PM Sergei Golubchik <s...@mariadb.org> wrote: > > Hi, Aleksey! > > On Feb 17, Aleksey Midenkov wrote: > > Hi Sergei! > > > > On Sat, Nov 9, 2019 at 12:03 AM Sergei Golubchik <s...@mariadb.org> 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 secur...@mariadb.org -- All the best, Aleksey Midenkov @midenok _______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
