Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL)
-
10.2.1-1, 10.2.1-2, 5.5.50, 10.1.15, 10.2.4-1, 10.1.21
Description
`Unlike MySQL 5.6 (that provides eq_range_index_dive_limit server variable) and MySQL 5.7 (that additionally provides range_optimizer_max_mem_size server variable) MariaDB 10.x does NOT limit memory used for range optimization in any way (neither directly nor indirectly).
As a result, for some queries with huge list of value in the IN () list in the WHERE clause (having number of items comparable to the number of rows in the table) MariaDB 10.x does not only execute the query longer than MySQL 5.6 and 5.7 with default settings (as it spends more time on query optimization), but also uses a lot of memory in the process (see https://bugs.mysql.com/bug.php?id=78973 for some estimations and links). With many concurrent connections running this kind of queries we may easily end up with OOM condition for the mysqld process.
Consider a table like this:
create table t (id bigint not null auto_increment, ts datetime, primary key(id,ts)) engine=InnoDB; |
insert into t(ts) values(now()); |
insert into t(ts) values(now()); |
replace into t(ts) select now() from t t1, t t2, t t3, t t4, t t5, t t6, t t7, t t8, t t9, t t10, t t11, t t12, t t13, t t14, t t15,t t16, t t17, t t18; |
analyze table t; |
and a query like this:
SELECT * FROM t WHERE id IN (10000,10001,10002, ... 54106,54107); |
Run the query on different versions of MySQL and MariaDB, compare execution times and memory used in the process. It may be easier to see the memory impact on versions that do not allow to monitor memory used per session or "state" by running many copies of the same query concurrently using mysqlslap, for example.
Attachments
Issue Links
- relates to
-
-
- Closed
-
-
-
- Closed
-
-
-
- Open
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
- mentioned in
-
Page Loading...
(1 relates to, 1 mentioned in)
Activity
| Field | Original Value | New Value |
|---|---|---|
| Fix Version/s | 10.1 [ 16100 ] | |
| Assignee | Sergei Petrunia [ psergey ] |
| Affects Version/s | 10.1.13 [ 21803 ] |
| Status | Open [ 1 ] | Confirmed [ 10101 ] |
| Priority | Major [ 3 ] | Critical [ 2 ] |
| Sprint | 10.1.14 [ 51 ] |
| Rank | Ranked lower |
| Sprint | 10.1.14 [ 51 ] | 10.2.1-1 [ 56 ] |
| Rank | Ranked higher |
| Sprint | 10.2.1-1 [ 56 ] | 10.2.1-1, 10.2.1-2 [ 56, 63 ] |
| Priority | Critical [ 2 ] | Blocker [ 1 ] |
| Link | This issue relates to MDEV-10046 [ MDEV-10046 ] |
| Link |
This issue relates to |
| Sprint | 10.2.1-1, 10.2.1-2 [ 56, 63 ] | 10.2.1-1, 10.2.1-2, 5.5.50 [ 56, 63, 71 ] |
| Rank | Ranked higher |
| Sprint | 10.2.1-1, 10.2.1-2, 5.5.50 [ 56, 63, 71 ] | 10.2.1-1, 10.2.1-2, 5.5.50, 10.1.15 [ 56, 63, 71, 75 ] |
| Rank | Ranked lower |
| Priority | Blocker [ 1 ] | Major [ 3 ] |
| Description |
Unlike MySQL 5.6 (that provides eq_range_index_dive_limit server variable) and MySQL 5.7 (that additionally provides range_optimizer_max_mem_size server variable) MariaDB 10.x does NOT limit memory used for range optimization in any way (neither directly nor indirectly).
As a result, for some queries with huge list of value in the IN () list in the WHERE clause (having number of items comparable to the number of rows in the table) MariaDB 10.x does not only execute the query longer than MySQL 5.6 and 5.7 with default settings (as it spends more time on query optimization), but also uses a lot of memory in the process (see https://bugs.mysql.com/bug.php?id=78973 for some estimations and links). With many concurrent connections running this kind of queries we may easily end up with OOM condition for the mysqld process. Consider a table like this: create table t (id bigint not null auto_increment, ts datetime, primary key(id,ts)) engine=InnoDB; insert into t(ts) values(now()); insert into t(ts) values(now()); replace into t(ts) select now() from t t1, t t2, t t3, t t4, t t5, t t6, t t7, t t8, t t9, t t10, t t11, t t12, t t13, t t14, t t15,t t16, t t17, t t18; analyze table t; and a query like this: SELECT * FROM t WHERE id IN (10000,10001,10002, ... 54106,54107); Run the query on different versions of MySQL and MariaDB, compare execution times and memory used in the process. It may be easier to see the memory impact on versions that do not allow to monitor memory used per session or "state" by running many copies of the same query concurrently using mysqlslap, for example. |
Unlike MySQL 5.6 (that provides eq_range_index_dive_limit server variable) and MySQL 5.7 (that additionally provides range_optimizer_max_mem_size server variable) MariaDB 10.x does NOT limit memory used for range optimization in any way (neither directly nor indirectly).
As a result, for some queries with huge list of value in the IN () list in the WHERE clause (having number of items comparable to the number of rows in the table) MariaDB 10.x does not only execute the query longer than MySQL 5.6 and 5.7 with default settings (as it spends more time on query optimization), but also uses a lot of memory in the process (see https://bugs.mysql.com/bug.php?id=78973 for some estimations and links). With many concurrent connections running this kind of queries we may easily end up with OOM condition for the mysqld process. Consider a table like this: {code:sql} create table t (id bigint not null auto_increment, ts datetime, primary key(id,ts)) engine=InnoDB; insert into t(ts) values(now()); insert into t(ts) values(now()); replace into t(ts) select now() from t t1, t t2, t t3, t t4, t t5, t t6, t t7, t t8, t t9, t t10, t t11, t t12, t t13, t t14, t t15,t t16, t t17, t t18; analyze table t; {code} and a query like this: {code:sql} SELECT * FROM t WHERE id IN (10000,10001,10002, ... 54106,54107); {code} Run the query on different versions of MySQL and MariaDB, compare execution times and memory used in the process. It may be easier to see the memory impact on versions that do not allow to monitor memory used per session or "state" by running many copies of the same query concurrently using mysqlslap, for example. |
| Priority | Major [ 3 ] | Critical [ 2 ] |
| Labels | upstream-fixed |
| Assignee | Sergei Petrunia [ psergey ] | Vicentiu Ciorbaru [ cvicentiu ] |
| Assignee | Vicentiu Ciorbaru [ cvicentiu ] | Sergei Petrunia [ psergey ] |
| Sprint | 10.2.1-1, 10.2.1-2, 5.5.50, 10.1.15 [ 56, 63, 71, 75 ] | 10.2.1-1, 10.2.1-2, 5.5.50, 10.1.15, 10.1.20 [ 56, 63, 71, 75, 119 ] |
| Rank | Ranked higher |
| Priority | Critical [ 2 ] | Blocker [ 1 ] |
| Sprint | 10.2.1-1, 10.2.1-2, 5.5.50, 10.1.15, 10.1.20 [ 56, 63, 71, 75, 119 ] | 10.2.1-1, 10.2.1-2, 5.5.50, 10.1.15, 10.2.4-1 [ 56, 63, 71, 75, 121 ] |
| Rank | Ranked lower |
| Attachment | mariadb-vs-mysql-memory-usage.png [ 42980 ] |
| Assignee | Sergei Petrunia [ psergey ] | Sergei Golubchik [ serg ] |
| Priority | Blocker [ 1 ] | Critical [ 2 ] |
| Link |
This issue relates to |
| Link |
This issue relates to |
| Sprint | 10.2.1-1, 10.2.1-2, 5.5.50, 10.1.15, 10.2.4-1 [ 56, 63, 71, 75, 121 ] | 10.2.1-1, 10.2.1-2, 5.5.50, 10.1.15, 10.2.4-1, 10.1.21 [ 56, 63, 71, 75, 121, 130 ] |
| Rank | Ranked higher |
| Fix Version/s | 10.1.21 [ 22113 ] | |
| Fix Version/s | 10.2.4 [ 22116 ] | |
| Fix Version/s | 10.1 [ 16100 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Confirmed [ 10101 ] | Closed [ 6 ] |
| Affects Version/s | 5.5 [ 15800 ] | |
| Affects Version/s | 10.0 [ 16000 ] | |
| Affects Version/s | 10.1 [ 16100 ] | |
| Affects Version/s | 10.2 [ 14601 ] | |
| Affects Version/s | 10.1.8 [ 19605 ] | |
| Affects Version/s | 10.1.13 [ 21803 ] |
| Description |
Unlike MySQL 5.6 (that provides eq_range_index_dive_limit server variable) and MySQL 5.7 (that additionally provides range_optimizer_max_mem_size server variable) MariaDB 10.x does NOT limit memory used for range optimization in any way (neither directly nor indirectly).
As a result, for some queries with huge list of value in the IN () list in the WHERE clause (having number of items comparable to the number of rows in the table) MariaDB 10.x does not only execute the query longer than MySQL 5.6 and 5.7 with default settings (as it spends more time on query optimization), but also uses a lot of memory in the process (see https://bugs.mysql.com/bug.php?id=78973 for some estimations and links). With many concurrent connections running this kind of queries we may easily end up with OOM condition for the mysqld process. Consider a table like this: {code:sql} create table t (id bigint not null auto_increment, ts datetime, primary key(id,ts)) engine=InnoDB; insert into t(ts) values(now()); insert into t(ts) values(now()); replace into t(ts) select now() from t t1, t t2, t t3, t t4, t t5, t t6, t t7, t t8, t t9, t t10, t t11, t t12, t t13, t t14, t t15,t t16, t t17, t t18; analyze table t; {code} and a query like this: {code:sql} SELECT * FROM t WHERE id IN (10000,10001,10002, ... 54106,54107); {code} Run the query on different versions of MySQL and MariaDB, compare execution times and memory used in the process. It may be easier to see the memory impact on versions that do not allow to monitor memory used per session or "state" by running many copies of the same query concurrently using mysqlslap, for example. |
`Unlike MySQL 5.6 (that provides eq_range_index_dive_limit server variable) and MySQL 5.7 (that additionally provides range_optimizer_max_mem_size server variable) MariaDB 10.x does NOT limit memory used for range optimization in any way (neither directly nor indirectly).
As a result, for some queries with huge list of value in the IN () list in the WHERE clause (having number of items comparable to the number of rows in the table) MariaDB 10.x does not only execute the query longer than MySQL 5.6 and 5.7 with default settings (as it spends more time on query optimization), but also uses a lot of memory in the process (see https://bugs.mysql.com/bug.php?id=78973 for some estimations and links). With many concurrent connections running this kind of queries we may easily end up with OOM condition for the mysqld process. Consider a table like this: {code:sql} create table t (id bigint not null auto_increment, ts datetime, primary key(id,ts)) engine=InnoDB; insert into t(ts) values(now()); insert into t(ts) values(now()); replace into t(ts) select now() from t t1, t t2, t t3, t t4, t t5, t t6, t t7, t t8, t t9, t t10, t t11, t t12, t t13, t t14, t t15,t t16, t t17, t t18; analyze table t; {code} and a query like this: {code:sql} SELECT * FROM t WHERE id IN (10000,10001,10002, ... 54106,54107); {code} Run the query on different versions of MySQL and MariaDB, compare execution times and memory used in the process. It may be easier to see the memory impact on versions that do not allow to monitor memory used per session or "state" by running many copies of the same query concurrently using mysqlslap, for example. |
| Link |
This issue relates to |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 31352 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 31378 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 31378 ] |
| Labels | upstream-fixed | ServiceNow upstream-fixed |
| Labels | ServiceNow upstream-fixed | 76qDvLB8Gju6Hs7nk3VY3EX42G795W5z upstream-fixed |
| Labels | 76qDvLB8Gju6Hs7nk3VY3EX42G795W5z upstream-fixed | upstream-fixed |
| Workflow | MariaDB v3 [ 74600 ] | MariaDB v4 [ 150244 ] |
| Zendesk Related Tickets | 201658 | |
| Zendesk active tickets | 201658 |