maria-developers team mailing list archive

[Mattias Jonsson <mattias.jonsson@xxxxxxxxxxx>]

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
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)