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 <holyf...@askmonty.org> 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 <holyf...@mariadb.com> > 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.mat...@mariadb.com> >> 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 <holyf...@askmonty.org> >>>> 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)
_______________________________________________ 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
