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...
Activity
Ok, finally it became clear that A) other linked MDEV issues are different from this one, and B) although some solutions are possible for this particular issue (will file an MDEV), all of them will be too intrusive to be put into a 10.1 release.
So, thinking of backporting the @@range_optimizer_max_mem_size fix from MySQL.
The patch in MySQL is:
https://github.com/mysql/mysql-server/commit/a7571080513ed2107919ebb1f5633566d3605189
Possible concerns when applying this on MariaDB 10.1:
- Adding members into MEM_ROOT structure causes the ABI check to fail (I am not sure how much of an issue that actually is)
- The patch adds new errors into sql/share/errmsg-utf8.txt. Can we do that in the middle of the stable 10.1 series (probably Yes, as here's an example of a patch that did that: https://github.com/MariaDB/server/commit/825f51d1aab51d363dc07ec9fe0829af33063883 ?
) - With the patch, the queries will produce warnings like this:
Warning 3170 Memory capacity of 1536000 bytes for
|
'range_optimizer_max_mem_size' exceeded. Range optimization was not done for this query.
|
The wording is not entirely correct, because range optimization may be still done for other tables. Do we want the warning to be printed at all?
- I suppose we want to preserve the current behavior, so the default should be "no limits" (unlike with MySQL 5.7 that has 8M as the default).
Added a new server option/variable --max-session-mem-used that implements a
soft limit for the server session status variable mem_used.
Is it possible for this bug to affect version 10.0.30?
I'm having some random OOM issues, and I've seend queries with megabytes (literaly) of "IN"/"NOT IN" clause.
Yes, it affects all versions below what is listed in FixVersion/s field. So, all 5.5 and 10.0 versions.
Queries of this kind, with long IN () lists, are usually dynamically generated by software that may be out of DBA's control. Here hard limits (using any of the way implemented in upstream MySQL (eq_range_index_dive_limit and/or range_optimizer_max_mem_size) help DBA to keep server more safe.
Also, what about that part of the original report related to time spent on optimization (for no real gain, when number of items in the IN list is approaching number of rows in the table)? Hard limits solve this problem as well.