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 <mailto: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 <mailto: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
<mailto:mattias.jonsson@xxxxxxxxxxx>> wrote:
Hi Holyfoot,
On Mon, Apr 16, 2018 at 3:38 PM, Alexey Botchkov
<holyfoot@xxxxxxxxxxxx <mailto: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
<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
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)
