Re: [Maria-developers] [External] Re: Questions regarding closing partitions in MDEV-11084

[Mattias Jonsson]

Hi Jacob,

Sorry for the confusion between the partitioning engine and spider.

To add to the confusion:
There are also different 'share' objects, table share (meaning to
share info between different instances of the same table) and also
partition share (meaning to share information for different instances
of the same partition). And they can also be on server level
(TABLE_SHARE, ha_share) as well as handler level (ha_partition +
ha_spider, Partition_share, SPIDER_SHARE,
SPIDER_PARTITION_HANDLER_SHARE, SPIDER_PARTITION_SHARE).

So what I mean is that the state of these shared objects may rely on
the order of opening/closing partitions, especially the first opened
partition (and probably relies on when closing the first opened
partition there will be no more open partitions used).

For pushing the partition logic into each storage engine, you can take
a look into what was done in WL#4807/WL#6035 in MySQL where the
partition engine was splitted into helper functions which was reused
for both the partition engine as well as for the native partitioning
in InnoDB. I think this would make a lot of sense to do for the spider
engine as well.

Regards
Mattias

On Thu, Apr 26, 2018 at 10:14 PM, Jacob Mathew  wrote:
>
> Hi Mattias,
>
> There is an instance of Spider for each partition in the Spider node, so each 
> instance of Spider handles exactly one partition/shard.  So please elaborate 
> on what you mean by Spider opening/closing specific partitions.
>
> Note that if we push the partitioning logic into each storage engine, then 
> there could be one instance of Spider in the Spider node that handles all 
> partitions.
>
> Thanks,
> Jacob
>
> Jacob B. Mathew
> Spider and Server Developer
> MariaDB Corporation
> +1 408 655 8999  (mobile)
> jacob.b.mathew(Skype)
> jacob.mat...@mariadb.com
>
>
> On Thu, Apr 26, 2018 at 3:59 AM, Mattias Jonsson 
>  wrote:
>>
>> Hi Holyfoot, Sergei and Jacob,
>>
>> Thank you for looking into this.
>>
>> Jacob there are no Jira ticket (that I am aware of) also I do see it as two 
>> different problems with the same source.
>> 1) Spider engine does not handle opening/closing specific partitions (as I 
>> understand it currently relies on opening/closing all partitions at once in 
>> order). Currently this is a crashing bug!
>> 2) MDEV-11084 makes explicit partition selection work like FLUSH TABLE, due 
>> to the closing partitions part.
>>
>> I think it would be nice if you could create tickets for those two issues 
>> (or at least for the first one).
>>
>> Regards
>> Mattias
>>
>> On Thu, Apr 26, 2018 at 11:26 AM, Alexey Botchkov  
>> wrote:
>>>
>>> No, there's no JIRA task for that problem.
>>> So i guess you can create one :)
>>>
>>> Best regards.
>>> HF
>>>
>>> On Thu, Apr 26, 2018 at 4:50 AM, Jacob Mathew  
>>> wrote:

 Hi Mattias and Holyfoot,

 Is there a Jira bug for this problem?  If not I can create a bug for it 
 and assign it to Holyfoot.

 Thanks,
 Jacob

 Jacob B. Mathew
 Spider and Server Developer
 MariaDB Corporation
 +1 408 655 8999  (mobile)
 jacob.b.mathew(Skype)
 jacob.mat...@mariadb.com


 On Tue, Apr 17, 2018 at 9:11 AM, Mattias Jonsson 
  wrote:
>
> Hi Holyfoot,
>
> On Mon, Apr 16, 2018 at 3:38 PM, Alexey Botchkov  
> wrote:
> > Hi, Mattias, guys!
> >
> > While investigating the crash, i'd like to discuss that
> >
> >> it seems to close partitions whenever it
> >> is not used in a statement (i.e. require it to be reopened in the next
> >> statement that would use another partition
> >
> >
> > Yes, it does that, handling statements with the specified 'PARTITION'
> > option.
> > The patch supposed to solve the problem when there are too many 
> > partitions
> > opened,
> > so i think it must close the unused partitions sometime.
> > No, it doesn't have to happen that often. I planned to check the
> > table_open_cache
> > variable before the forced close. But decided not to do that initially 
> > - as
> > it simplified testing,
> > and i thought if someone uses the PARTITION option, he would stick to 
> > using
> > this partition
> > anyway. And  i forgot about that issue.
>
> The reason for not closing partitions in this case is that it turns
> the 'SELECT col FROM t PARTITION(p)' almost into a 'FLUSH TABLES t'
> which is kind of unexpected.
> I did a test mixing PK selects with and without 'PARTITION (p)' clause
> and that shows it will close all but 1 partition and then (re)open all
> but 1 partition.
>
> Think of the case when a server runs in production with a heavily
> partitioned table serving simple PK queries and then someone runs a
> query with explicit PARTITION 

Re: [Maria-developers] [External] Re: Questions regarding closing partitions in MDEV-11084

[Mattias Jonsson]

Hi Holyfoot,

Thank you for the fix. Btw should the tests be included as well or did you
add them outside the commit?

Regards
Mattias

On Sat, Apr 28, 2018 at 1:50 PM, Alexey Botchkov 
wrote:

> Hello, guys.
>
> > 2) MDEV-11084 makes explicit partition selection work like FLUSH TABLE,
> due to the closing partitions part.
> After the discussion, I removed that part of the fix, so now the explicit
> statement won't force the partition close.
> So now the 'explicit_partition.test' passes.
>
> Best regards.
> HF
>
>
>
>
>
> 26.04.2018 14:59, Mattias Jonsson wrote:
>
> Hi Holyfoot, Sergei and Jacob,
>
> Thank you for looking into this.
>
> Jacob there are no Jira ticket (that I am aware of) also I do see it as
> two different problems with the same source.
> 1) Spider engine does not handle opening/closing specific partitions (as I
> understand it currently relies on opening/closing all partitions at once in
> order). Currently this is a crashing bug!
> 2) MDEV-11084 makes explicit partition selection work like FLUSH TABLE,
> due to the closing partitions part.
>
> I think it would be nice if you could create tickets for those two issues
> (or at least for the first one).
>
> Regards
> Mattias
>
> On Thu, Apr 26, 2018 at 11:26 AM, Alexey Botchkov 
> wrote:
>
>> No, there's no JIRA task for that problem.
>> So i guess you can create one :)
>>
>> Best regards.
>> HF
>>
>> On Thu, Apr 26, 2018 at 4:50 AM, Jacob Mathew 
>> wrote:
>>
>>> Hi Mattias and Holyfoot,
>>>
>>> Is there a Jira bug for this problem?  If not I can create a bug for it
>>> and assign it to Holyfoot.
>>>
>>> Thanks,
>>> Jacob
>>>
>>> Jacob B. Mathew
>>> Spider and Server Developer
>>> MariaDB Corporation
>>> +1 408 655 8999  (mobile)
>>> jacob.b.mathew(Skype)
>>> jacob.mat...@mariadb.com
>>>
>>>
>>> On Tue, Apr 17, 2018 at 9:11 AM, Mattias Jonsson <
>>> mattias.jons...@booking.com> wrote:
>>>
 Hi Holyfoot,

 On Mon, Apr 16, 2018 at 3:38 PM, Alexey Botchkov 
 wrote:
 > Hi, Mattias, guys!
 >
 > While investigating the crash, i'd like to discuss that
 >
 >> it seems to close partitions whenever it
 >> is not used in a statement (i.e. require it to be reopened in the
 next
 >> statement that would use another partition
 >
 >
 > Yes, it does that, handling statements with the specified 'PARTITION'
 > option.
 > The patch supposed to solve the problem when there are too many
 partitions
 > opened,
 > so i think it must close the unused partitions sometime.
 > No, it doesn't have to happen that often. I planned to check the
 > table_open_cache
 > variable before the forced close. But decided not to do that
 initially - as
 > it simplified testing,
 > and i thought if someone uses the PARTITION option, he would stick to
 using
 > this partition
 > anyway. And  i forgot about that issue.

 The reason for not closing partitions in this case is that it turns
 the 'SELECT col FROM t PARTITION(p)' almost into a 'FLUSH TABLES t'
 which is kind of unexpected.
 I did a test mixing PK selects with and without 'PARTITION (p)' clause
 and that shows it will close all but 1 partition and then (re)open all
 but 1 partition.

 Think of the case when a server runs in production with a heavily
 partitioned table serving simple PK queries and then someone runs a
 query with explicit PARTITION selection, then it will introduce a
 short stall. First for the query itself (closing all but 1 partitions)
 and then for the next simple PK query using the same table (opening
 all but 1 partition).

 As I read the bug report: the reporter wants to avoid opening all
 partitions. Not that it keeps the partitions open in the table open
 cache (which is an issue on the architectural level of partitioning
 not really fitting into the open table cache).

 I attached a diff with the test and results (I also added handler
 status variables to show my point). The diff is against
 b4a2baffa82e5c07b96a1c752228560dcac1359b.

 Here is the part of the result file that shows what I mean with extra
 comments prepended by MJ>:
 CREATE TABLE t1 (a int PRIMARY KEY)
 ENGINE = InnoDB
 PARTITION BY HASH (a) PARTITIONS 1000;
 INSERT INTO t1 VALUES (0), (1), (2), (3);
 FLUSH STATUS;

 SELECT a FROM t1 PARTITION(p0) WHERE a = 0;
 a
 0
 SHOW SESSION STATUS
 WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VALUE > 0;
 Variable_name   Value
 MJ> Here it closes all but one partitions.
 Handler_close   999

 SELECT a FROM t1 WHERE a = 0;
 a
 0
 SHOW SESSION STATUS
 WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VALUE > 0;
 Variable_name   Value
 Handler_close   1000
 Handler_external_lock   2006
 MJ> and here it 

Re: [Maria-developers] [External] Re: Questions regarding closing partitions in MDEV-11084

[Alexey Botchkov]

Hello, guys.

> 2) MDEV-11084 makes explicit partition selection work like FLUSH 
TABLE, due to the closing partitions part.
After the discussion, I removed that part of the fix, so now the 
explicit statement won't force the partition close.

So now the 'explicit_partition.test' passes.

Best regards.
HF




26.04.2018 14:59, Mattias Jonsson wrote:

Hi Holyfoot, Sergei and Jacob,

Thank you for looking into this.

Jacob there are no Jira ticket (that I am aware of) also I do see it 
as two different problems with the same source.
1) Spider engine does not handle opening/closing specific partitions 
(as I understand it currently relies on opening/closing all partitions 
at once in order). Currently this is a crashing bug!
2) MDEV-11084 makes explicit partition selection work like FLUSH 
TABLE, due to the closing partitions part.


I think it would be nice if you could create tickets for those two 
issues (or at least for the first one).


Regards
Mattias

On Thu, Apr 26, 2018 at 11:26 AM, Alexey Botchkov 
> wrote:


No, there's no JIRA task for that problem.
So i guess you can create one :)

Best regards.
HF

On Thu, Apr 26, 2018 at 4:50 AM, Jacob Mathew
> wrote:

Hi Mattias and Holyfoot,

Is there a Jira bug for this problem?  If not I can create a
bug for it and assign it to Holyfoot.

Thanks,
Jacob

Jacob B. Mathew
Spider and Server Developer
MariaDB Corporation
+1 408 655 8999  (mobile)
jacob.b.mathew    (Skype)
jacob.mat...@mariadb.com


On Tue, Apr 17, 2018 at 9:11 AM, Mattias Jonsson
> wrote:

Hi Holyfoot,

On Mon, Apr 16, 2018 at 3:38 PM, Alexey Botchkov
> wrote:
> Hi, Mattias, guys!
>
> While investigating the crash, i'd like to discuss that
>
>> it seems to close partitions whenever it
>> is not used in a statement (i.e. require it to be
reopened in the next
>> statement that would use another partition
>
>
> Yes, it does that, handling statements with the
specified 'PARTITION'
> option.
> The patch supposed to solve the problem when there are
too many partitions
> opened,
> so i think it must close the unused partitions sometime.
> No, it doesn't have to happen that often. I planned to
check the
> table_open_cache
> variable before the forced close. But decided not to do
that initially - as
> it simplified testing,
> and i thought if someone uses the PARTITION option, he
would stick to using
> this partition
> anyway. And  i forgot about that issue.

The reason for not closing partitions in this case is that
it turns
the 'SELECT col FROM t PARTITION(p)' almost into a 'FLUSH
TABLES t'
which is kind of unexpected.
I did a test mixing PK selects with and without 'PARTITION
(p)' clause
and that shows it will close all but 1 partition and then
(re)open all
but 1 partition.

Think of the case when a server runs in production with a
heavily
partitioned table serving simple PK queries and then
someone runs a
query with explicit PARTITION selection, then it will
introduce a
short stall. First for the query itself (closing all but 1
partitions)
and then for the next simple PK query using the same table
(opening
all but 1 partition).

As I read the bug report: the reporter wants to avoid
opening all
partitions. Not that it keeps the partitions open in the
table open
cache (which is an issue on the architectural level of
partitioning
not really fitting into the open table cache).

I attached a diff with the test and results (I also added
handler
status variables to show my point). The diff is against
b4a2baffa82e5c07b96a1c752228560dcac1359b.

Here is the part of the result file that shows what I mean
with extra
comments prepended by MJ>:
CREATE TABLE t1 (a int PRIMARY KEY)
ENGINE = InnoDB
PARTITION BY HASH (a) PARTITIONS 1000;
INSERT INTO t1 VALUES (0), (1), (2), (3);
FLUSH STATUS;

SELECT a FROM t1 PARTITION(p0) 

Re: [Maria-developers] [External] Re: Questions regarding closing partitions in MDEV-11084

[Jacob Mathew]

Hi Mattias,

There is an instance of Spider for each partition in the Spider node, so
each instance of Spider handles exactly one partition/shard.  So please
elaborate on what you mean by Spider opening/closing specific partitions.

Note that if we push the partitioning logic into each storage engine, then
there could be one instance of Spider in the Spider node that handles all
partitions.

Thanks,
Jacob

Jacob B. Mathew
Spider and Server Developer
MariaDB Corporation
+1 408 655 8999  (mobile)
jacob.b.mathew(Skype)
jacob.mat...@mariadb.com


On Thu, Apr 26, 2018 at 3:59 AM, Mattias Jonsson <
mattias.jons...@booking.com> wrote:

> Hi Holyfoot, Sergei and Jacob,
>
> Thank you for looking into this.
>
> Jacob there are no Jira ticket (that I am aware of) also I do see it as
> two different problems with the same source.
> 1) Spider engine does not handle opening/closing specific partitions (as I
> understand it currently relies on opening/closing all partitions at once in
> order). Currently this is a crashing bug!
> 2) MDEV-11084 makes explicit partition selection work like FLUSH TABLE,
> due to the closing partitions part.
>
> I think it would be nice if you could create tickets for those two issues
> (or at least for the first one).
>
> Regards
> Mattias
>
> On Thu, Apr 26, 2018 at 11:26 AM, Alexey Botchkov 
> wrote:
>
>> No, there's no JIRA task for that problem.
>> So i guess you can create one :)
>>
>> Best regards.
>> HF
>>
>> On Thu, Apr 26, 2018 at 4:50 AM, Jacob Mathew 
>> wrote:
>>
>>> Hi Mattias and Holyfoot,
>>>
>>> Is there a Jira bug for this problem?  If not I can create a bug for it
>>> and assign it to Holyfoot.
>>>
>>> Thanks,
>>> Jacob
>>>
>>> Jacob B. Mathew
>>> Spider and Server Developer
>>> MariaDB Corporation
>>> +1 408 655 8999  (mobile)
>>> jacob.b.mathew(Skype)
>>> jacob.mat...@mariadb.com
>>>
>>>
>>> On Tue, Apr 17, 2018 at 9:11 AM, Mattias Jonsson <
>>> mattias.jons...@booking.com> wrote:
>>>
 Hi Holyfoot,

 On Mon, Apr 16, 2018 at 3:38 PM, Alexey Botchkov 
 wrote:
 > Hi, Mattias, guys!
 >
 > While investigating the crash, i'd like to discuss that
 >
 >> it seems to close partitions whenever it
 >> is not used in a statement (i.e. require it to be reopened in the
 next
 >> statement that would use another partition
 >
 >
 > Yes, it does that, handling statements with the specified 'PARTITION'
 > option.
 > The patch supposed to solve the problem when there are too many
 partitions
 > opened,
 > so i think it must close the unused partitions sometime.
 > No, it doesn't have to happen that often. I planned to check the
 > table_open_cache
 > variable before the forced close. But decided not to do that
 initially - as
 > it simplified testing,
 > and i thought if someone uses the PARTITION option, he would stick to
 using
 > this partition
 > anyway. And  i forgot about that issue.

 The reason for not closing partitions in this case is that it turns
 the 'SELECT col FROM t PARTITION(p)' almost into a 'FLUSH TABLES t'
 which is kind of unexpected.
 I did a test mixing PK selects with and without 'PARTITION (p)' clause
 and that shows it will close all but 1 partition and then (re)open all
 but 1 partition.

 Think of the case when a server runs in production with a heavily
 partitioned table serving simple PK queries and then someone runs a
 query with explicit PARTITION selection, then it will introduce a
 short stall. First for the query itself (closing all but 1 partitions)
 and then for the next simple PK query using the same table (opening
 all but 1 partition).

 As I read the bug report: the reporter wants to avoid opening all
 partitions. Not that it keeps the partitions open in the table open
 cache (which is an issue on the architectural level of partitioning
 not really fitting into the open table cache).

 I attached a diff with the test and results (I also added handler
 status variables to show my point). The diff is against
 b4a2baffa82e5c07b96a1c752228560dcac1359b.

 Here is the part of the result file that shows what I mean with extra
 comments prepended by MJ>:
 CREATE TABLE t1 (a int PRIMARY KEY)
 ENGINE = InnoDB
 PARTITION BY HASH (a) PARTITIONS 1000;
 INSERT INTO t1 VALUES (0), (1), (2), (3);
 FLUSH STATUS;

 SELECT a FROM t1 PARTITION(p0) WHERE a = 0;
 a
 0
 SHOW SESSION STATUS
 WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VALUE > 0;
 Variable_name   Value
 MJ> Here it closes all but one partitions.
 Handler_close   999

 SELECT a FROM t1 WHERE a = 0;
 a
 0
 SHOW SESSION STATUS
 WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VALUE > 0;
 Variable_name   Value
 Handler_close   1000
 

Re: [Maria-developers] [External] Re: Questions regarding closing partitions in MDEV-11084

[Mattias Jonsson]

Hi Holyfoot, Sergei and Jacob,

Thank you for looking into this.

Jacob there are no Jira ticket (that I am aware of) also I do see it as two
different problems with the same source.
1) Spider engine does not handle opening/closing specific partitions (as I
understand it currently relies on opening/closing all partitions at once in
order). Currently this is a crashing bug!
2) MDEV-11084 makes explicit partition selection work like FLUSH TABLE, due
to the closing partitions part.

I think it would be nice if you could create tickets for those two issues
(or at least for the first one).

Regards
Mattias

On Thu, Apr 26, 2018 at 11:26 AM, Alexey Botchkov 
wrote:

> No, there's no JIRA task for that problem.
> So i guess you can create one :)
>
> Best regards.
> HF
>
> On Thu, Apr 26, 2018 at 4:50 AM, Jacob Mathew 
> wrote:
>
>> Hi Mattias and Holyfoot,
>>
>> Is there a Jira bug for this problem?  If not I can create a bug for it
>> and assign it to Holyfoot.
>>
>> Thanks,
>> Jacob
>>
>> Jacob B. Mathew
>> Spider and Server Developer
>> MariaDB Corporation
>> +1 408 655 8999  (mobile)
>> jacob.b.mathew(Skype)
>> jacob.mat...@mariadb.com
>>
>>
>> On Tue, Apr 17, 2018 at 9:11 AM, Mattias Jonsson <
>> mattias.jons...@booking.com> wrote:
>>
>>> Hi Holyfoot,
>>>
>>> On Mon, Apr 16, 2018 at 3:38 PM, Alexey Botchkov 
>>> wrote:
>>> > Hi, Mattias, guys!
>>> >
>>> > While investigating the crash, i'd like to discuss that
>>> >
>>> >> it seems to close partitions whenever it
>>> >> is not used in a statement (i.e. require it to be reopened in the next
>>> >> statement that would use another partition
>>> >
>>> >
>>> > Yes, it does that, handling statements with the specified 'PARTITION'
>>> > option.
>>> > The patch supposed to solve the problem when there are too many
>>> partitions
>>> > opened,
>>> > so i think it must close the unused partitions sometime.
>>> > No, it doesn't have to happen that often. I planned to check the
>>> > table_open_cache
>>> > variable before the forced close. But decided not to do that initially
>>> - as
>>> > it simplified testing,
>>> > and i thought if someone uses the PARTITION option, he would stick to
>>> using
>>> > this partition
>>> > anyway. And  i forgot about that issue.
>>>
>>> The reason for not closing partitions in this case is that it turns
>>> the 'SELECT col FROM t PARTITION(p)' almost into a 'FLUSH TABLES t'
>>> which is kind of unexpected.
>>> I did a test mixing PK selects with and without 'PARTITION (p)' clause
>>> and that shows it will close all but 1 partition and then (re)open all
>>> but 1 partition.
>>>
>>> Think of the case when a server runs in production with a heavily
>>> partitioned table serving simple PK queries and then someone runs a
>>> query with explicit PARTITION selection, then it will introduce a
>>> short stall. First for the query itself (closing all but 1 partitions)
>>> and then for the next simple PK query using the same table (opening
>>> all but 1 partition).
>>>
>>> As I read the bug report: the reporter wants to avoid opening all
>>> partitions. Not that it keeps the partitions open in the table open
>>> cache (which is an issue on the architectural level of partitioning
>>> not really fitting into the open table cache).
>>>
>>> I attached a diff with the test and results (I also added handler
>>> status variables to show my point). The diff is against
>>> b4a2baffa82e5c07b96a1c752228560dcac1359b.
>>>
>>> Here is the part of the result file that shows what I mean with extra
>>> comments prepended by MJ>:
>>> CREATE TABLE t1 (a int PRIMARY KEY)
>>> ENGINE = InnoDB
>>> PARTITION BY HASH (a) PARTITIONS 1000;
>>> INSERT INTO t1 VALUES (0), (1), (2), (3);
>>> FLUSH STATUS;
>>>
>>> SELECT a FROM t1 PARTITION(p0) WHERE a = 0;
>>> a
>>> 0
>>> SHOW SESSION STATUS
>>> WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VALUE > 0;
>>> Variable_name   Value
>>> MJ> Here it closes all but one partitions.
>>> Handler_close   999
>>>
>>> SELECT a FROM t1 WHERE a = 0;
>>> a
>>> 0
>>> SHOW SESSION STATUS
>>> WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VALUE > 0;
>>> Variable_name   Value
>>> Handler_close   1000
>>> Handler_external_lock   2006
>>> MJ> and here it re-opens those 'all but one' partitions.
>>> Handler_open999
>>>
>>>
>>> Regards
>>> Mattias
>>> >
>>> >
>>> > Best regards.
>>> > HF
>>> >
>>> >
>>> >
>>> >
>>> > 13.04.2018 19:07, Mattias Jonsson wrote:
>>> >>
>>> >> Hi MariaDB Devs,
>>> >>
>>> >> I tried to evaluate spider engine and found an issue where it crashes,
>>> >> most likely due to MDEV-11084 (Stacktrace and reproducible test case
>>> >> attached).
>>> >>
>>> >> That also leads me to wonder about the performance for partitioned
>>> >> tables after MDEV-11084, when it seems to close partitions whenever it
>>> >> is not used in a statement (i.e. require it to be reopened in the next
>>> >> statement that would use another partition, effectively not using the
>>> >> 

Re: [Maria-developers] [External] Re: Questions regarding closing partitions in MDEV-11084

[Jacob Mathew]

Hi Mattias and Holyfoot,

Is there a Jira bug for this problem?  If not I can create a bug for it and
assign it to Holyfoot.

Thanks,
Jacob

Jacob B. Mathew
Spider and Server Developer
MariaDB Corporation
+1 408 655 8999  (mobile)
jacob.b.mathew(Skype)
jacob.mat...@mariadb.com


On Tue, Apr 17, 2018 at 9:11 AM, Mattias Jonsson <
mattias.jons...@booking.com> wrote:

> Hi Holyfoot,
>
> On Mon, Apr 16, 2018 at 3:38 PM, Alexey Botchkov 
> wrote:
> > Hi, Mattias, guys!
> >
> > While investigating the crash, i'd like to discuss that
> >
> >> it seems to close partitions whenever it
> >> is not used in a statement (i.e. require it to be reopened in the next
> >> statement that would use another partition
> >
> >
> > Yes, it does that, handling statements with the specified 'PARTITION'
> > option.
> > The patch supposed to solve the problem when there are too many
> partitions
> > opened,
> > so i think it must close the unused partitions sometime.
> > No, it doesn't have to happen that often. I planned to check the
> > table_open_cache
> > variable before the forced close. But decided not to do that initially -
> as
> > it simplified testing,
> > and i thought if someone uses the PARTITION option, he would stick to
> using
> > this partition
> > anyway. And  i forgot about that issue.
>
> The reason for not closing partitions in this case is that it turns
> the 'SELECT col FROM t PARTITION(p)' almost into a 'FLUSH TABLES t'
> which is kind of unexpected.
> I did a test mixing PK selects with and without 'PARTITION (p)' clause
> and that shows it will close all but 1 partition and then (re)open all
> but 1 partition.
>
> Think of the case when a server runs in production with a heavily
> partitioned table serving simple PK queries and then someone runs a
> query with explicit PARTITION selection, then it will introduce a
> short stall. First for the query itself (closing all but 1 partitions)
> and then for the next simple PK query using the same table (opening
> all but 1 partition).
>
> As I read the bug report: the reporter wants to avoid opening all
> partitions. Not that it keeps the partitions open in the table open
> cache (which is an issue on the architectural level of partitioning
> not really fitting into the open table cache).
>
> I attached a diff with the test and results (I also added handler
> status variables to show my point). The diff is against
> b4a2baffa82e5c07b96a1c752228560dcac1359b.
>
> Here is the part of the result file that shows what I mean with extra
> comments prepended by MJ>:
> CREATE TABLE t1 (a int PRIMARY KEY)
> ENGINE = InnoDB
> PARTITION BY HASH (a) PARTITIONS 1000;
> INSERT INTO t1 VALUES (0), (1), (2), (3);
> FLUSH STATUS;
>
> SELECT a FROM t1 PARTITION(p0) WHERE a = 0;
> a
> 0
> SHOW SESSION STATUS
> WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VALUE > 0;
> Variable_name   Value
> MJ> Here it closes all but one partitions.
> Handler_close   999
>
> SELECT a FROM t1 WHERE a = 0;
> a
> 0
> SHOW SESSION STATUS
> WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VALUE > 0;
> Variable_name   Value
> Handler_close   1000
> Handler_external_lock   2006
> MJ> and here it re-opens those 'all but one' partitions.
> Handler_open999
>
>
> Regards
> Mattias
> >
> >
> > Best regards.
> > HF
> >
> >
> >
> >
> > 13.04.2018 19:07, Mattias Jonsson wrote:
> >>
> >> Hi MariaDB Devs,
> >>
> >> I tried to evaluate spider engine and found an issue where it crashes,
> >> most likely due to MDEV-11084 (Stacktrace and reproducible test case
> >> attached).
> >>
> >> That also leads me to wonder about the performance for partitioned
> >> tables after MDEV-11084, when it seems to close partitions whenever it
> >> is not used in a statement (i.e. require it to be reopened in the next
> >> statement that would use another partition, effectively not using the
> >> open table cache).
> >>
> >> I cannot see anything mentioned in the final commit message hinting on
> >> that it closes partitions not used in the current query, but in the
> >> previous patches it was mentioned without any reason.
> >>
> >> Why does it close the already opened partitions?
> >> https://github.com/MariaDB/server/blob/10.3/sql/ha_partition.cc#L8365
> >>
> >> I would not mind opening the partitions only when they are to be used
> >> (even though there are engines that need to be tested more), but
> >> closing them makes no sense to me performance wise. Also notice that
> >> the partitions first will be put back into the open table cache and
> >> then on the next query the non-used partitions will be closed and the
> >> needed ones be (re)-opened.
> >>
> >> Regards
> >> Mattias Jonsson
> >
> >
> >
> >
>
___
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

Re: [Maria-developers] [External] Re: Questions regarding closing partitions in MDEV-11084

[Mattias Jonsson]

Hi Holyfoot,

On Mon, Apr 16, 2018 at 3:38 PM, Alexey Botchkov  wrote:
> Hi, Mattias, guys!
>
> While investigating the crash, i'd like to discuss that
>
>> it seems to close partitions whenever it
>> is not used in a statement (i.e. require it to be reopened in the next
>> statement that would use another partition
>
>
> Yes, it does that, handling statements with the specified 'PARTITION'
> option.
> The patch supposed to solve the problem when there are too many partitions
> opened,
> so i think it must close the unused partitions sometime.
> No, it doesn't have to happen that often. I planned to check the
> table_open_cache
> variable before the forced close. But decided not to do that initially - as
> it simplified testing,
> and i thought if someone uses the PARTITION option, he would stick to using
> this partition
> anyway. And  i forgot about that issue.

The reason for not closing partitions in this case is that it turns
the 'SELECT col FROM t PARTITION(p)' almost into a 'FLUSH TABLES t'
which is kind of unexpected.
I did a test mixing PK selects with and without 'PARTITION (p)' clause
and that shows it will close all but 1 partition and then (re)open all
but 1 partition.

Think of the case when a server runs in production with a heavily
partitioned table serving simple PK queries and then someone runs a
query with explicit PARTITION selection, then it will introduce a
short stall. First for the query itself (closing all but 1 partitions)
and then for the next simple PK query using the same table (opening
all but 1 partition).

As I read the bug report: the reporter wants to avoid opening all
partitions. Not that it keeps the partitions open in the table open
cache (which is an issue on the architectural level of partitioning
not really fitting into the open table cache).

I attached a diff with the test and results (I also added handler
status variables to show my point). The diff is against
b4a2baffa82e5c07b96a1c752228560dcac1359b.

Here is the part of the result file that shows what I mean with extra
comments prepended by MJ>:
CREATE TABLE t1 (a int PRIMARY KEY)
ENGINE = InnoDB
PARTITION BY HASH (a) PARTITIONS 1000;
INSERT INTO t1 VALUES (0), (1), (2), (3);
FLUSH STATUS;

SELECT a FROM t1 PARTITION(p0) WHERE a = 0;
a
0
SHOW SESSION STATUS
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VALUE > 0;
Variable_name   Value
MJ> Here it closes all but one partitions.
Handler_close   999

SELECT a FROM t1 WHERE a = 0;
a
0
SHOW SESSION STATUS
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VALUE > 0;
Variable_name   Value
Handler_close   1000
Handler_external_lock   2006
MJ> and here it re-opens those 'all but one' partitions.
Handler_open999


Regards
Mattias
>
>
> Best regards.
> HF
>
>
>
>
> 13.04.2018 19:07, Mattias Jonsson wrote:
>>
>> Hi MariaDB Devs,
>>
>> I tried to evaluate spider engine and found an issue where it crashes,
>> most likely due to MDEV-11084 (Stacktrace and reproducible test case
>> attached).
>>
>> That also leads me to wonder about the performance for partitioned
>> tables after MDEV-11084, when it seems to close partitions whenever it
>> is not used in a statement (i.e. require it to be reopened in the next
>> statement that would use another partition, effectively not using the
>> open table cache).
>>
>> I cannot see anything mentioned in the final commit message hinting on
>> that it closes partitions not used in the current query, but in the
>> previous patches it was mentioned without any reason.
>>
>> Why does it close the already opened partitions?
>> https://github.com/MariaDB/server/blob/10.3/sql/ha_partition.cc#L8365
>>
>> I would not mind opening the partitions only when they are to be used
>> (even though there are engines that need to be tested more), but
>> closing them makes no sense to me performance wise. Also notice that
>> the partitions first will be put back into the open table cache and
>> then on the next query the non-used partitions will be closed and the
>> needed ones be (re)-opened.
>>
>> Regards
>> Mattias Jonsson
>
>
>
>


mdev-11084_test.diff
Description: Binary data
___
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