maria-developers team mailing list archive

[Mattias Jonsson <mattias.jonsson@xxxxxxxxxxx>]

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 <holyfoot@xxxxxxxxxxxx>
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 <holyfoot@xxxxxxxxxxx>
> 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 <jacob.mathew@xxxxxxxxxxx>
>> 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.mathew@xxxxxxxxxxx
>>>
>>>
>>> On Tue, Apr 17, 2018 at 9:11 AM, Mattias Jonsson <
>>> mattias.jonsson@xxxxxxxxxxx> wrote:
>>>
>>>> Hi Holyfoot,
>>>>
>>>> On Mon, Apr 16, 2018 at 3:38 PM, Alexey Botchkov <holyfoot@xxxxxxxxxxxx>
>>>> 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_open    999
>>>>
>>>>
>>>> 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
>>>> >
>>>> >
>>>> >
>>>> >
>>>>
>>>
>>>
>>
>
>
> --
> Mattias Jonsson
> IT Team Lead - Development
>
> Booking.com B.V.
> Herengracht 597 Amsterdam 1017 CE Netherlands
> <https://maps.google.com/?q=Herengracht+597+Amsterdam+1017+CE+Netherlands&entry=gmail&source=g>
> Direct +31207125646
> [image: Booking.com] <http://www.booking.com/>
> The world's #1 accommodation site
> 43 languages, 198+ offices worldwide, 120,000+ global destinations,
> 1,550,000+ room nights booked every day
> No booking fees, best price always guaranteed
> Subsidiary of Booking Holdings Inc. (NASDAQ: BKNG)
>
>
>


-- 
Mattias Jonsson
IT Team Lead - Development

Booking.com B.V.
Herengracht 597 Amsterdam 1017 CE Netherlands
Direct +31207125646
[image: Booking.com] <http://www.booking.com/>
The world's #1 accommodation site
43 languages, 198+ offices worldwide, 120,000+ global destinations,
1,550,000+ room nights booked every day
No booking fees, best price always guaranteed
Subsidiary of Booking Holdings Inc. (NASDAQ: BKNG)