Re: [Maria-developers] bd98ef106c8: MDEV-17554 Auto-create new partition for system versioned tables with history partitioned by INTERVAL/LIMIT
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@mariadb.org On Nov 07, Aleksey Midenkov wrote:
revision-id: bd98ef106c8 (mariadb-10.4.7-55-gbd98ef106c8) parent(s): 6d1fa01e8f1 author: Aleksey Midenkov <midenok@gmail.com> committer: Aleksey Midenkov <midenok@gmail.com> 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.
Hi Sergei! On Sat, Nov 9, 2019 at 12:03 AM Sergei Golubchik <serg@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.
* 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.
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: 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.
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.
Done.
2. don't overload operators, use, say,
bool greater_than(size_t seconds)
Done.
But overall it's pretty good. A nice idea with a separate thread.
Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org
On Nov 07, Aleksey Midenkov wrote:
revision-id: bd98ef106c8 (mariadb-10.4.7-55-gbd98ef106c8) parent(s): 6d1fa01e8f1 author: Aleksey Midenkov <midenok@gmail.com> committer: Aleksey Midenkov <midenok@gmail.com> 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, Aleksey Midenkov @midenok
Hi, Aleksey! On Feb 17, Aleksey Midenkov wrote:
Hi Sergei!
On Sat, Nov 9, 2019 at 12:03 AM Sergei Golubchik <serg@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.
* 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@mariadb.org
Sergei, On Mon, Feb 17, 2020 at 8:23 PM Sergei Golubchik <serg@mariadb.org> wrote:
Hi, Aleksey!
On Feb 17, Aleksey Midenkov wrote:
Hi Sergei!
On Sat, Nov 9, 2019 at 12:03 AM Sergei Golubchik <serg@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 security@mariadb.org
-- All the best, Aleksey Midenkov @midenok
Hi, Aleksey! On Feb 18, Aleksey Midenkov wrote:
On Mon, Feb 17, 2020 at 8:23 PM Sergei Golubchik <serg@mariadb.org> wrote:
On Feb 17, Aleksey Midenkov wrote:
On Sat, Nov 9, 2019 at 12:03 AM Sergei Golubchik <serg@mariadb.org> wrote:
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.
I'm just saying, the implementation can be made notably simpler if we won't allow manually and automatically created partitions in the same table. But if you want to support that and solve problems that it creates - okay.
* 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.
Great, I'll do a new review when it's done, then.
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?
Not "run SQL command" yet. But "parse SQL command" — yes. Just grep for "parse_sql" for examples. Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org
Sergei, On Wed, Feb 19, 2020 at 8:27 PM Sergei Golubchik <serg@mariadb.org> wrote:
Hi, Aleksey!
On Feb 18, Aleksey Midenkov wrote:
On Mon, Feb 17, 2020 at 8:23 PM Sergei Golubchik <serg@mariadb.org> wrote:
On Feb 17, Aleksey Midenkov wrote:
On Sat, Nov 9, 2019 at 12:03 AM Sergei Golubchik <serg@mariadb.org> wrote:
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.
I'm just saying, the implementation can be made notably simpler if we won't allow manually and automatically created partitions in the same table. But if you want to support that and solve problems that it creates - okay.
* 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.
Great, I'll do a new review when it's done, then.
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?
Not "run SQL command" yet. But "parse SQL command" — yes. Just grep for "parse_sql" for examples.
Yes, I'm familiar with such isolated parsing. I'm not familiar with isolated parsing and running, that's what I meant and what the code does. It looked simplest way to run in separate thread to me like any connection does.
Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org
-- All the best, Aleksey Midenkov @midenok
participants (2)
-
Aleksey Midenkov -
Sergei Golubchik