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