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,

...

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

On Mon, Apr 16, 2018 at 3:38 PM, Alexey Botchkov wrote: 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