26 Apr
2018
26 Apr
'18
6:59 a.m.
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@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.mathew@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.mathew@MariaDB.com >> >> >> On Tue, Apr 17, 2018 at 9:11 AM, Mattias Jonsson < >> mattias.jonsson@booking.com> wrote: >> >>> Hi Holyfoot, >>> >>> On Mon, Apr 16, 2018 at 3:38 PM, Alexey Botchkov <holyfoot@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 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)