[Maria-developers] per-partition attributes in the CREATE TABLE
Hi, we've talked about engine attributes in the CREATE TABLE, and that one should be able to specify them per partition as well. Now, thinking about it, I'm not quite sure what the semantics shuld be. What is your use case ? How do you want them to work ? I see different possibilities. Say, there is create table ... (.....) XXX=1 partition by list (a) ( partition p0 values in (1) YYY=2, partition p1 values in (2) ); 1. We can say that XXX should be listed in the engine's hton->table_options, and YYY - in the hton->partition_options. this works fine because the engine can use table_share->option_struct and it will contain correct values, independent from whether a table is partitioned or not. but it will break when partitioning will support different engines in different partitions. 2. We can say that XXX is partition engine options, and a pluggable engine can only see YYY. YYY should come from hton->partition_options, and the engine's table level attributes are not applicable. The drawback - every engine needs to have special code to take care of the partitioned case, and to duplicate all table-level options in the partition-level options. 3. Same as 2, but YYY can come from either table level or partition level arrays. Every engine still needs the special code for partitioned case, but does not need to duplicate the table_options array. Regards, Sergei
On Tue, May 11, 2010 at 12:05 PM, Sergei Golubchik <sergii@pisem.net> wrote:
Hi,
we've talked about engine attributes in the CREATE TABLE, and that one should be able to specify them per partition as well.
Now, thinking about it, I'm not quite sure what the semantics shuld be.
What is your use case ? How do you want them to work ?
I see different possibilities. Say, there is
create table ... (.....) XXX=1 partition by list (a) ( partition p0 values in (1) YYY=2, partition p1 values in (2) );
1. We can say that XXX should be listed in the engine's hton->table_options, and YYY - in the hton->partition_options.
this works fine because the engine can use table_share->option_struct and it will contain correct values, independent from whether a table is partitioned or not.
but it will break when partitioning will support different engines in different partitions.
2. We can say that XXX is partition engine options, and a pluggable engine can only see YYY. YYY should come from hton->partition_options, and the engine's table level attributes are not applicable.
The drawback - every engine needs to have special code to take care of the partitioned case, and to duplicate all table-level options in the partition-level options.
3. Same as 2, but YYY can come from either table level or partition level arrays. Every engine still needs the special code for partitioned case, but does not need to duplicate the table_options array.
Thinking as an end user only, I would vote for a scheme where the following holds true: i) Anything that can be specified table level, could also be specified partition level. create table ... (.....) XXX=1 partition by list (a) ( partition p0 values in (1), partition p1 values in (2) ); and create table ... (.....) partition by list (a) ( partition p0 values in (1) XXX=1, partition p1 values in (2) XXX=1 ); are both possible and equivalent. ii) anything that can be defined for all partitions, can be instead defined on the table level part. Ie, from the above two create statements, I can always choose to write the former, never forced to repeat XXX=1 for all partitions. iii) For the problem with different engines per partition, and only one engine supports XXX=1 I could argue that it is not allowed to specify it on table level. If all specified engines support XXX=1, then it is allowed to specify it on table level. Is this helpful? henrik -- email: henrik.ingo@avoinelama.fi tel: +358-40-5697354 www: www.avoinelama.fi/~hingo book: www.openlife.cc
Hi, Henrik! On May 11, Henrik Ingo wrote:
On Tue, May 11, 2010 at 12:05 PM, Sergei Golubchik <sergii@pisem.net> wrote:
we've talked about engine attributes in the CREATE TABLE, and that one should be able to specify them per partition as well.
Now, thinking about it, I'm not quite sure what the semantics shuld be.
Thinking as an end user only, I would vote for a scheme where the following holds true:
i) Anything that can be specified table level, could also be specified partition level. ii) anything that can be defined for all partitions, can be instead defined on the table level part. iii) For the problem with different engines per partition, and only one engine supports XXX=1 I could argue that it is not allowed to specify it on table level. If all specified engines support XXX=1, then it is allowed to specify it on table level.
Is this helpful?
Yes, thanks. You're basically saying that there should be no attributes that are valid only on the table or only on the partition level, everything that can be specified per table should work per partition, and vice versa. It is certainly possible and logical. Regards, Sergei
Hi!
"Sergei" == Sergei Golubchik <sergii@pisem.net> writes:
Sergei> Hi, Sergei> we've talked about engine attributes in the CREATE TABLE, Sergei> and that one should be able to specify them per partition as well. Sergei> Now, thinking about it, I'm not quite sure what the semantics shuld be. Sergei> What is your use case ? How do you want them to work ? Sergei> I see different possibilities. Say, there is Sergei> create table ... (.....) XXX=1 Sergei> partition by list (a) Sergei> ( Sergei> partition p0 values in (1) YYY=2, Sergei> partition p1 values in (2) Sergei> ); Sergei> 1. We can say that XXX should be listed in the engine's hton-> table_options, and YYY - in the hton->partition_options. Sergei> this works fine because the engine can use table_share->option_struct Sergei> and it will contain correct values, independent from whether a table is Sergei> partitioned or not. Sergei> but it will break when partitioning will support different engines Sergei> in different partitions. Sergei> 2. We can say that XXX is partition engine options, and a pluggable Sergei> engine can only see YYY. YYY should come from hton->partition_options, Sergei> and the engine's table level attributes are not applicable. Sergei> The drawback - every engine needs to have special code to take care of Sergei> the partitioned case, and to duplicate all table-level options in the Sergei> partition-level options. Sergei> 3. Same as 2, but YYY can come from either table level or partition Sergei> level arrays. Every engine still needs the special code for partitioned Sergei> case, but does not need to duplicate the table_options array. For now I would suggest we go with 1), as this required least code from the engine sides. Reagards, Monty
Hi, Sergei! My use case is 1. ----- my use case start ----- Currently, Spider and Vertical Partitioning engine can use table level "connection", table level "comment", partition level "comment" and sub-partition level "comment" for engine options. This options have following priority. 1. sub-partition level "comment" 2. partition level "comment" 3. table level "comment" 4. table level "connection" If you create a table using following SQL, CREATE TABLE tbl_a (.....) ENGINE=spider COMMENT='database "auto_test_remote2", table "ta_r3"' CONNECTION='socket "mysql_1.sock", host "localhost"' PARTITION BY KEY(a) ( PARTITION pt1 COMMENT='database "auto_test_remote", table "ta_r2"', PARTITION pt2 COMMENT='socket "mysql_2.sock"' ); pt1 options are database "auto_test_remote" table "ta_r2" socket "mysql_1.sock" host "localhost" and pt2 options are database "auto_test_remote2" table "ta_r3" socket "mysql_2.sock" host "localhost" ----- my use case end ----- I think per-partition options should better to work like this. If per-partition options works like this, every engine don't need the special code for partitioned case. Additionally, for supporting different engines in different partitions, table options should better to be added engine names like global/session parameters for avoiding conflicting option names. ----- example start ----- If you create a table using following SQL, CREATE TABLE tbl_a (.....) innodb_XXX=1 pbxt_XXX=2 PARTITION BY KEY(a) ( PARTITION pt1 ENGINE=innodb innodb_YYY=3, PARTITION pt2 ENGINE=pbxt pbxt_ZZZ=4 ); pt1 options are innodb_XXX=1 innodb_YYY=3 and pt2 options are pbxt_XXX=2 pbxt_ZZZ=4 ----- example end ----- What do you think about my opinion? Thanks, Kentoku 2010/5/12 Michael Widenius <monty@askmonty.org>:
Hi!
"Sergei" == Sergei Golubchik <sergii@pisem.net> writes:
Sergei> Hi, Sergei> we've talked about engine attributes in the CREATE TABLE, Sergei> and that one should be able to specify them per partition as well.
Sergei> Now, thinking about it, I'm not quite sure what the semantics shuld be.
Sergei> What is your use case ? How do you want them to work ?
Sergei> I see different possibilities. Say, there is
Sergei> create table ... (.....) XXX=1 Sergei> partition by list (a) Sergei> ( Sergei> partition p0 values in (1) YYY=2, Sergei> partition p1 values in (2) Sergei> );
Sergei> 1. We can say that XXX should be listed in the engine's hton-> table_options, and YYY - in the hton->partition_options.
Sergei> this works fine because the engine can use table_share->option_struct Sergei> and it will contain correct values, independent from whether a table is Sergei> partitioned or not.
Sergei> but it will break when partitioning will support different engines Sergei> in different partitions.
Sergei> 2. We can say that XXX is partition engine options, and a pluggable Sergei> engine can only see YYY. YYY should come from hton->partition_options, Sergei> and the engine's table level attributes are not applicable.
Sergei> The drawback - every engine needs to have special code to take care of Sergei> the partitioned case, and to duplicate all table-level options in the Sergei> partition-level options.
Sergei> 3. Same as 2, but YYY can come from either table level or partition Sergei> level arrays. Every engine still needs the special code for partitioned Sergei> case, but does not need to duplicate the table_options array.
For now I would suggest we go with 1), as this required least code from the engine sides.
Reagards, Monty
On Wed, May 12, 2010 at 2:11 PM, kentoku <kentokushiba@gmail.com> wrote:
Additionally, for supporting different engines in different partitions, table options should better to be added engine names like global/session parameters for avoiding conflicting option names.
----- example start -----
If you create a table using following SQL, CREATE TABLE tbl_a (.....) innodb_XXX=1 pbxt_XXX=2 PARTITION BY KEY(a) ( PARTITION pt1 ENGINE=innodb innodb_YYY=3, PARTITION pt2 ENGINE=pbxt pbxt_ZZZ=4 );
pt1 options are innodb_XXX=1 innodb_YYY=3
and
pt2 options are pbxt_XXX=2 pbxt_ZZZ=4
----- example end -----
In the storage engine summit we discussed that different engines can support the same parameter XXX and it requires coordination to make sure both two engines agree on the semantics of XXX. Hence, while your example above is correct in the sense that it is possible for an engine to choose to prefix its own parameters, the normal use case would be: CREATE TABLE tbl_a (.....) XXX=1 PARTITION BY KEY(a) ( PARTITION pt1 ENGINE=innodb YYY=3, PARTITION pt2 ENGINE=pbxt ZZZ=4 ); pt1 options are XXX=1 YYY=3 and pt2 options are XXX=1 ZZZ=4 -- email: henrik.ingo@avoinelama.fi tel: +358-40-5697354 www: www.avoinelama.fi/~hingo book: www.openlife.cc
Hi, kentoku! On May 12, kentoku wrote:
Hi, Sergei!
My use case is 1.
----- my use case start ----- If you create a table using following SQL, CREATE TABLE tbl_a (.....) ENGINE=spider COMMENT='database "auto_test_remote2", table "ta_r3"' CONNECTION='socket "mysql_1.sock", host "localhost"' PARTITION BY KEY(a) ( PARTITION pt1 COMMENT='database "auto_test_remote", table "ta_r2"', PARTITION pt2 COMMENT='socket "mysql_2.sock"' ); ----- my use case end ----- I think per-partition options should better to work like this. If per-partition options works like this, every engine don't need the special code for partitioned case.
Unfortunately, this change - per partition options - seems to be too big. I have it working (almost, there're still few bugs), but it is already quite an intrusive patch :( I'm hesitant to add it to 5.2 now, as 5.2 was supposed to contain only few safe changes, as compared to 5.1, so that it could stabilize quickly. I am going to do per partition options in 5.3
Additionally, for supporting different engines in different partitions, table options should better to be added engine names like global/session parameters for avoiding conflicting option names.
Right, we thought about it too. I don't remember why it was dropped, but luckily if it will be needed, it can be implemented any time in a completely backward compatible manner. Regards, Sergei
participants (5)
-
Henrik Ingo -
kentoku -
Michael Widenius -
Sergei Golubchik -
Sergei Golubchik