Project

General

Profile

Actions

Defect #23318

closed

#lock_nested_set very slow on mysql with thousands of subtasks

Added by Stephane Evr over 7 years ago. Updated 4 months ago.

Status:
Closed
Priority:
Normal
Category:
Database
Target version:
-
Start date:
Due date:
% Done:

0%

Estimated time:
Resolution:
Duplicate
Affected version:

Description

I have a complex hierarchy of around 15000 issues in redmine, where an issue of this set could potentially have 3000 subtasks.

When doing CRUD operations on such issue, I notified significant slow downs, caused by the lock_nested_set function.

Here is the profiling for the query actually run in lock_nested_set:

SELECT `issues`.`id` FROM `issues` WHERE (root_id IN (SELECT root_id FROM issues WHERE id IN (70395,70389)))  ORDER BY `issues`.`id` ASC FOR UPDATE;

+-------+
|       |
.........
| 70371 |
| 70373 |
| 70375 |
| 70377 |
| 70379 |
| 70381 |
| 70383 |
| 70385 |
| 70387 |
| 70389 |
| 70391 |
| 70393 |
+-------+
2932 rows in set (2.70 sec)

mysql> show profile for QUERY 1;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000025 |
| Waiting for query cache lock   | 0.000004 |
| checking query cache for query | 0.000081 |
| checking permissions           | 0.000003 |
| checking permissions           | 0.000004 |
| Opening tables                 | 0.000038 |
| System lock                    | 0.000017 |
| init                           | 0.000056 |
| optimizing                     | 0.000013 |
| statistics                     | 0.000023 |
| preparing                      | 0.000009 |
| executing                      | 0.000002 |
| Sorting result                 | 0.000005 |
| Sending data                   | 0.000049 |
| optimizing                     | 0.000015 |
| statistics                     | 0.000047 |
| preparing                      | 2.690165 |
| end                            | 0.000009 |
| query end                      | 0.000067 |
| closing tables                 | 0.000010 |
| freeing items                  | 0.000038 |
| logging slow query             | 0.000002 |
| logging slow query             | 0.000163 |
| cleaning up                    | 0.000003 |
+--------------------------------+----------+
24 rows in set (0.00 sec)

It takes around 3 seconds to execute the whole query.

I think the main problem is with the nested SELECT statement. If I execute it separately, then paste its results directly into the main query, the query is much faster:

mysql> SELECT root_id FROM issues WHERE id IN (70395,70389);
+---------+
| root_id |
+---------+
|   45083 |
+---------+
1 row in set (0.00 sec)

SELECT `issues`.`id` FROM `issues` WHERE (root_id IN (45083))  ORDER BY `issues`.`id` ASC FOR UPDATE;

+-------+
|       |
.........
| 70371 |
| 70373 |
| 70375 |
| 70377 |
| 70379 |
| 70381 |
| 70383 |
| 70385 |
| 70387 |
| 70389 |
| 70391 |
| 70393 |
+-------+
2932 rows in set (0.01 sec)

I am not an expert in sql queries, and don't want to break anything... Shouldn't we use a JOIN instead?

Environment:
  Redmine version                3.3.0.stable
  Ruby version                   2.2.2-p95 (2015-04-13) [x86_64-linux]
  Rails version                  4.2.6
  Environment                    development
  Database adapter               Mysql2

Files

issue_nested_set.patch (834 Bytes) issue_nested_set.patch Stephane Evr, 2016-10-13 12:06
mysql-deadlock-02.diff (1.31 KB) mysql-deadlock-02.diff Toshi MARUYAMA, 2017-03-21 16:35

Related issues

Related to Redmine - Defect #19344: MySQL 5.6: IssueNestedSetConcurrencyTest#test_concurrency : always failsClosed

Actions
Related to Redmine - Defect #39437: MySQL / MariaDB issue nested set deadlocks and consistencyClosedMarius BĂLTEANU

Actions
Actions

Also available in: Atom PDF