maria-developers team mailing list archive

[Jacob Mathew <jacob.mathew@xxxxxxxxxxx>]

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