[MDEV-8558] Serious memory leak? Created: 2015-07-29  Updated: 2017-06-26  Resolved: 2017-06-26

Status: Closed
Project: MariaDB Server
Component/s: OTHER
Affects Version/s: 10.1.5, 10.1.6
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: m.rygiel Assignee: Elena Stepanova
Resolution: Incomplete Votes: 0
Labels: need_feedback
Environment:

CentOS 6

Attachments: PNG File eat_up_3.png    

 Description   

I probably found a memory leak problem within MariaDB server. It occurs, when i try to create a table and fill it by doing heavy INSERT+SELECT query (about 1h+).

So what actually happens? After executing heavy query – system memory usage is growing infinitely.

My whole my.cnf is here:

#========
 
[mysql]
 
#===========
 
port                           = 3306
 
socket                         = /var/lib/mysql/mysql.sock
 
 
 
#=============
 
[mysqld]
 
#==================
 
 
 
port                           = 3306
 
user                           = mysql
 
default_storage_engine         = InnoDB
 
socket                         = /var/lib/mysql/mysql.sock
 
pid_file                       = /var/lib/mysql/mysql.pid
 
event_scheduler                = 1
 
thread_stack                   = 256K
 
performance_schema             = 0
 
group_concat_max_len           = 10000
 
 
 
character-set-server           = utf8
 
collation-server               = utf8_polish_ci
 
 
 
key_buffer_size                = 256M
 
myisam-recover-options         = FORCE,BACKUP
 
 
 
max_allowed_packet             = 16M
 
max_connect_errors             = 1000000
 
sysdate_is_now                 = 1
 
 
 
expire_logs_days               = 1
 
sync_binlog                    = 0
 
binlog_format                  = mixed
 
 
 
tmp_table_size                 = 128M
 
max_heap_table_size            = 128M
 
query_cache_type               = 0
 
query_cache_size               = 0
 
max_connections                = 100
 
thread_cache_size              = 20
 
open_files_limit               = 65535
 
table_definition_cache         = 4096
 
table_open_cache               = 4096
 
 
 
# INNODB #
 
innodb_doublewrite             = 0  
innodb_use_native_aio          = 1   
innodb_flush_method            = O_DIRECT   
innodb_log_files_in_group      = 2
 
innodb_log_file_size           = 256M
 
innodb_flush_log_at_trx_commit = 0
 
innodb_file_per_table          = 1
 
innodb_buffer_pool_size        = 1G
 
innodb_file_format             = barracuda
 
innodb_stats_on_metadata       = 0
 
innodb_page_size               = 64k  
innodb_stats_sample_pages      = 128k  
 
 
log_error                      = /var/log/mysql.log
 
log_queries_not_using_indexes  = 0
 
slow_query_log                 = 0
 
slow_query_log_file            = /var/log/mysql-slow.log
 
general_log_file               = /var/log/mysql-general-queries.log

Buffer is 1GB only, temp tables are 128M.
Problem looks like this on system monitor (htop):

I cannot post orginal query, but it looks like this:

# Tables definition: 
 
 
# 3,5M rows 
CREATE TABLE `some_table` ( 
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, 
  `related_entity_id` INT(11) NOT NULL, 
  `status_id` TINYINT(3) NOT NULL DEFAULT '1', 
  `number` TINYINT(3) NOT NULL, 
  `percent` DECIMAL(7,2) UNSIGNED NOT NULL, 
  `actual_date` DATE DEFAULT '0000-00-00', 
  `simulated_date` DATE NOT NULL, 
  `amount` DECIMAL(10,2) UNSIGNED NOT NULL DEFAULT '0.00', 
  `calculated_amount` DECIMAL(10,2) UNSIGNED NOT NULL, 
  `diff_amount` DECIMAL(10,2) NOT NULL DEFAULT '0.00', 
  `cost` DECIMAL(10,2) NOT NULL DEFAULT '0.00', 
  `additional_amount` DECIMAL(10,2) NOT NULL, 
  PRIMARY KEY (`id`), 
  KEY `status_id` (`status_id`), 
  KEY `number` (`related_entity_id`,`number`), 
  KEY `percent` (`percent`) 
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci 
; 
 
 
# 90K rows
 
CREATE TABLE other_db.dates ( 
    related_entity_id INT, 
    purchase_date DATE, 
    end_date DATE, 
    PRIMARY KEY(related_entity_id) 
); 
    
# 26 rows
 
CREATE TABLE other_db.months ( 
    date_interval DECIMAL(6), 
    start_date DATE, 
    end_date DATE 
); 
    
###################################################################### 
 
 
# Query  
 
 
CREATE TABLE 
    other_db.amounts ( date_interval DECIMAL(6), amount DECIMAL(15,2) ); 
INSERT INTO 
    other_db.amounts ( date_interval, amount ) 
SELECT 
    M.date_interval, 
    SUM((SELECT TKRD.amount FROM log_table TKRD WHERE TKRD.related_id = KR.id AND TKRD.created_at <= M.end_date ORDER BY TKRD.created_at DESC LIMIT 1)) AS amount 
FROM 
    some_table KR 
    JOIN other_db.dates PID ON PID.related_entity_id = KR.related_entity_id 
    JOIN other_db.months M 
WHERE    
    M.end_date >= PID.purchase_date 
    AND M.end_date < PID.end_date 
    AND KR.simulated_date >= PID.purchase_date 
    AND ( 
        KR.actual_date = '0000-00-00' 
        OR KR.actual_date > M.end_date 
    ) 
GROUP BY 
    M.date_interval 
;

Same query , same data, same config - executed on sane MySQL 5.6.16 is consuming no more than ~2GB ram. What is happening?

 Comments   
Comment by Elena Stepanova [ 2015-07-29 ]

Hi,

Could you please also give us a hint on what log_table (used in the subquery) might look like?

Thanks.

Comment by m.rygiel [ 2015-07-30 ] 

# 27M rows
 
CREATE TABLE `log_table` (                             
	`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,                    
	`related_id` INT(11) UNSIGNED NOT NULL,                              
	`related_entity_id` INT(11) UNSIGNED NOT NULL,                            
	`status` TINYINT(3) UNSIGNED NOT NULL,                       
	`created_at` DATETIME NOT NULL,                                   
	`agency_id` INT(11) UNSIGNED NOT NULL,                         
	`employee_id` INT(11) UNSIGNED NOT NULL,
 
	`rate` DECIMAL(4,2) UNSIGNED NOT NULL,                  
	`amount` DECIMAL(10,2) NOT NULL DEFAULT '0.00',   
	`costs` DECIMAL(10,2) NOT NULL DEFAULT '0.00',
 
	`last_id` INT(11) UNSIGNED NOT NULL DEFAULT '0',
 
	PRIMARY KEY (`id`), 
	KEY `related_id` (`related_id`), 
	KEY `related_entity_id` (`related_entity_id`),
 
	KEY `status` (`status`), 
	KEY `last_id` (`last_id`)                                                                 
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;

Comment by Elena Stepanova [ 2015-07-31 ]

I tried 10.1.5 binary tarball on Wheezy, with the same amount of data as described. The query indeed takes ~1h 20m, but I don't observe the "infinite growth", neither during the query execution, nor afterwards. Here is how it looks for me:

10352 elenst    20   0 1820m 310m 7840 S   0.0  3.9   0:02.41 mysqld
 
10352 elenst    20   0 1820m 317m 7840 S   2.7  4.0   0:02.49 mysqld
 
10352 elenst    20   0 1820m 323m 7840 S   3.7  4.0   0:02.60 mysqld
 
10352 elenst    20   0 1820m 330m 7840 S   3.3  4.1   0:02.70 mysqld
 
10352 elenst    20   0 1824m 336m 7840 S   2.7  4.2   0:02.78 mysqld
 
10352 elenst    20   0 1824m 343m 7840 S   3.0  4.3   0:02.87 mysqld
 
10352 elenst    20   0 1824m 349m 7840 S   3.3  4.4   0:02.97 mysqld
 
10352 elenst    20   0 1824m 356m 7840 S   3.0  4.5   0:03.06 mysqld
 
10352 elenst    20   0 1828m 363m 7840 S   3.7  4.5   0:03.17 mysqld
 
10352 elenst    20   0 1828m 370m 7840 S   5.3  4.6   0:03.33 mysqld
 
10352 elenst    20   0 1828m 376m 7840 S   9.0  4.7   0:03.60 mysqld
 
10352 elenst    20   0 1832m 383m 7840 S   9.7  4.8   0:03.89 mysqld
 
10352 elenst    20   0 1832m 390m 7840 S   7.7  4.9   0:04.12 mysqld
 
10352 elenst    20   0 1836m 397m 7840 S   9.7  5.0   0:04.41 mysqld
 
10352 elenst    20   0 1836m 404m 7840 S   6.0  5.0   0:04.59 mysqld
 
10352 elenst    20   0 1840m 412m 7840 S   5.0  5.2   0:04.74 mysqld
 
10352 elenst    20   0 1840m 420m 7840 S   6.0  5.3   0:04.92 mysqld
 
10352 elenst    20   0 1840m 427m 7840 S   4.0  5.3   0:05.04 mysqld
 
10352 elenst    20   0 1844m 433m 7840 S   4.7  5.4   0:05.18 mysqld
 
10352 elenst    20   0 1844m 441m 7840 S   3.0  5.5   0:05.27 mysqld
 
10352 elenst    20   0 1844m 448m 7840 S   3.7  5.6   0:05.38 mysqld
 
10352 elenst    20   0 1848m 456m 7840 S   3.7  5.7   0:05.49 mysqld
 
10352 elenst    20   0 1848m 463m 7840 S   4.7  5.8   0:05.63 mysqld
 
10352 elenst    20   0 1852m 476m 7840 S   8.3  6.0   0:05.88 mysqld
 
10352 elenst    20   0 1864m 501m 7844 S  15.6  6.3   0:06.35 mysqld
 
10352 elenst    20   0 1864m 532m 7844 S   7.0  6.7   0:06.56 mysqld
 
10352 elenst    20   0 1864m 570m 7844 S   8.3  7.1   0:06.81 mysqld
 
10352 elenst    20   0 1864m 607m 7844 S   7.7  7.6   0:07.04 mysqld
 
10352 elenst    20   0 1864m 639m 7844 S   6.3  8.0   0:07.23 mysqld
 
10352 elenst    20   0 1864m 676m 7844 S   7.7  8.4   0:07.46 mysqld
 
10352 elenst    20   0 1864m 714m 7844 S   7.3  8.9   0:07.68 mysqld
 
10352 elenst    20   0 1864m 732m 7844 S   6.0  9.2   0:07.86 mysqld
 
10352 elenst    20   0 1864m 770m 7844 S   8.0  9.6   0:08.10 mysqld
 
10352 elenst    20   0 1864m 808m 7844 S   8.7 10.1   0:08.36 mysqld
 
10352 elenst    20   0 1864m 840m 7844 S   8.7 10.5   0:08.62 mysqld
 
10352 elenst    20   0 1864m 877m 7844 S   7.7 11.0   0:08.85 mysqld
 
10352 elenst    20   0 1864m 912m 7844 S   7.0 11.4   0:09.06 mysqld
 
10352 elenst    20   0 1868m 950m 7844 S  14.0 11.9   0:09.48 mysqld
 
10352 elenst    20   0 1904m 1.0g 7844 S  46.3 12.6   0:10.87 mysqld
 
10352 elenst    20   0 1908m 1.0g 7844 S   8.7 13.0   0:11.13 mysqld
 
10352 elenst    20   0 1908m 1.0g 7844 S   5.7 13.3   0:11.30 mysqld
 
10352 elenst    20   0 1908m 1.1g 7844 S   6.7 13.7   0:11.50 mysqld
 
10352 elenst    20   0 1912m 1.1g 7844 S   7.7 14.1   0:11.73 mysqld
 
10352 elenst    20   0 1912m 1.1g 7844 S   7.3 14.4   0:11.95 mysqld
 
10352 elenst    20   0 1912m 1.2g 7844 S  11.3 14.8   0:12.29 mysqld
 
...
 
10352 elenst    20   0 1912m 1.2g 7844 S   7.0 15.9   0:13.47 mysqld
 
10352 elenst    20   0 1912m 1.3g 7844 S   6.7 16.0   0:13.67 mysqld
 
...
 
10352 elenst    20   0 2020m 1.3g 7844 S  24.6 17.2   0:36.12 mysqld
 
10352 elenst    20   0 2032m 1.4g 7844 S  22.6 17.4   0:36.80 mysqld
 
...
 
...
 
10352 elenst    20   0 2152m 1.4g 7844 S   2.3 18.5   1:25.95 mysqld
 
10352 elenst    20   0 2152m 1.5g 7844 S   3.3 18.5   1:26.05 mysqld
 
...
 
10352 elenst    20   0 2268m 1.5g 7844 S  42.9 19.7   2:20.27 mysqld
 
...
 
10352 elenst    20   0 2276m 1.5g 7844 S   6.0 19.8   2:41.21 mysqld
 
10352 elenst    20   0 2280m 1.6g 7844 S   7.0 19.8   2:41.42 mysqld
 
...
 
...
 
10352 elenst    20   0 2388m 1.6g 7844 S   8.0 21.1   3:52.61 mysqld
 
10352 elenst    20   0 2396m 1.7g 7844 S  16.0 21.2   3:53.09 mysqld
 
...
 
10352 elenst    20   0 2572m 1.7g 7844 S   5.0 22.4   5:01.66 mysqld
 
10352 elenst    20   0 2572m 1.8g 7844 S   6.0 22.4   5:01.84 mysqld
 
...
 
10352 elenst    20   0 2744m 1.8g 7844 S  19.3 23.6   6:09.00 mysqld
 
10352 elenst    20   0 2772m 1.9g 7844 S  44.3 23.8   6:10.33 mysqld
 
...
 
10352 elenst    20   0 2912m 1.9g 7844 S  11.0 24.9   6:32.84 mysqld
 
10352 elenst    20   0 2960m 2.0g 7844 S  48.9 25.2   6:34.31 mysqld
 
...
 
10352 elenst    20   0 3064m 2.0g 7844 S   7.0 26.1   6:51.85 mysqld
 
10352 elenst    20   0 3104m 2.1g 7844 S  44.6 26.4   6:53.19 mysqld
 
...
 
10352 elenst    20   0 3176m 2.1g 7844 S  66.6 27.0   6:58.06 mysqld
 
10352 elenst    20   0 3240m 2.2g 7844 S  68.9 27.6   7:00.13 mysqld
 
...
 
10352 elenst    20   0 3348m 2.2g 7844 S  47.6 28.6   7:35.28 mysqld
 
10352 elenst    20   0 3396m 2.3g 7844 S  54.3 29.0   7:36.91 mysqld
 
10352 elenst    20   0 3420m 2.3g 7844 S  29.3 29.2   7:37.79 mysqld
 
10352 elenst    20   0 3436m 1.2g 7884 S  22.3 14.9   7:38.46 mysqld
 
10352 elenst    20   0 3436m 1.2g 7884 S   0.0 14.9   7:38.46 mysqld

That is, virtual grows up to ~3+G and stays there, resident grows to 2+G and drops back to 1.2G after the query is executed. When I execute it again, virtual does not grow anymore, and resident repeats the exercise – up to 2+G during execution, back to 1.2G afterwards. So, it does not look like a memory leak. However, I don't rule out that it's different on CentOS build.
Could you please provide the similar output – not necessarily continuous, but the amount of memory before the query, during the query, after the query, and once again?
Also, please specify whether you are using RPMs from MariaDB, or building from source.

Regardless, what is worth paying attention is that the same query on the same data requires twice as much memory on 10.1 as it does on 10.0. On 10.0 it reaches 1787m/1.2g and does not go any higher. Execution time is the same, EXPLAIN SELECT is the same (EXPLAIN INSERT does not work). We will get back to it when we finish with the alleged memory leak.

Comment by m.rygiel [ 2015-08-03 ]

Here is how it looks for me:

 6116 mysql     21   1 1916m 244m  10m S  0.0  3.1   0:02.32 mysqld             
 6116 mysql     21   1 1916m 256m  11m S  0.0  3.3   0:03.16 mysqld             
 6116 mysql     21   1 1916m 361m  11m S  9.9  4.6   0:05.53 mysqld             
 6116 mysql     21   1 1916m 447m  11m S 13.9  5.7   0:08.48 mysqld             
 6116 mysql     21   1 1916m 535m  11m S 87.5  6.8   0:13.93 mysqld             
 6116 mysql     21   1 1916m 559m  11m S 95.4  7.1   0:23.34 mysqld             
 6116 mysql     21   1 1916m 621m  11m S 11.9  7.9   0:26.47 mysqld             
 6116 mysql     21   1 1916m 724m  11m S 19.9  9.2   0:27.89 mysqld             
 6116 mysql     21   1 1916m 842m  11m S 25.8 10.7   0:30.04 mysqld             
 6116 mysql     21   1 1916m 994m  11m S 37.8 12.6   0:33.47 mysqld             
 6116 mysql     21   1 1916m 1.1g  11m S 15.9 14.1   0:37.60 mysqld             
 6116 mysql     21   1 1924m 1.2g  11m S 57.6 15.1   0:42.10 mysqld             
 6116 mysql     21   1 1936m 1.2g  11m S 65.6 15.8   0:46.51 mysqld             
 6116 mysql     21   1 1956m 1.3g  11m S 57.6 16.3   0:51.98 mysqld             
 6116 mysql     21   1 1968m 1.3g  11m S 29.8 16.6   0:55.65 mysqld             
 6116 mysql     21   1 1992m 1.3g  11m S 57.6 16.9   1:01.12 mysqld             
 6116 mysql     21   1 2012m 1.3g  11m S 57.6 17.2   1:06.54 mysqld             
 6116 mysql     21   1 2032m 1.3g  11m S 65.7 17.4   1:12.03 mysqld             
 6116 mysql     21   1 2052m 1.4g  11m S 59.6 17.7   1:16.74 mysqld             
 6116 mysql     21   1 2076m 1.4g  11m S 71.5 18.0   1:22.29 mysqld             
 6116 mysql     21   1 2096m 1.4g  11m S 47.7 18.2   1:27.53 mysqld             
 6116 mysql     21   1 2116m 1.4g  11m S 57.6 18.5   1:33.35 mysqld             
 6116 mysql     21   1 2136m 1.4g  11m S 47.7 18.7   1:39.32 mysqld             
 6116 mysql     21   1 2160m 1.5g  11m S 49.7 19.0   1:44.71 mysqld             
 6116 mysql     21   1 2188m 1.5g  11m S 53.7 19.4   1:50.47 mysqld             
 6116 mysql     21   1 2212m 1.5g  11m S 61.6 19.7   1:55.63 mysqld             
 6116 mysql     21   1 2236m 1.5g  11m S 33.8 20.0   2:00.92 mysqld             
 6116 mysql     21   1 2260m 1.6g  11m S 59.6 20.3   2:06.36 mysqld             
 6116 mysql     21   1 2284m 1.6g  11m S 57.6 20.6   2:12.15 mysqld             
 6116 mysql     21   1 2308m 1.6g  11m S 51.7 20.9   2:17.06 mysqld             
 6116 mysql     21   1 2332m 1.6g  11m S 69.7 21.2   2:22.71 mysqld             
 6116 mysql     21   1 2356m 1.7g  11m S 53.7 21.5   2:28.12 mysqld             
 6116 mysql     21   1 2376m 1.7g  11m S 65.6 21.8   2:33.34 mysqld             
 6116 mysql     21   1 2400m 1.7g  11m S 23.9 22.1   2:38.72 mysqld             
 6116 mysql     21   1 2420m 1.7g  11m S 37.8 22.4   2:43.75 mysqld             
 6116 mysql     21   1 2440m 1.7g  11m S 51.7 22.6   2:48.37 mysqld             
 6116 mysql     21   1 2460m 1.8g  11m S 53.7 22.9   2:53.47 mysqld             
 6116 mysql     21   1 2476m 1.8g  11m S 55.7 23.1   2:58.18 mysqld             
 6116 mysql     21   1 2508m 1.8g  11m S 57.6 23.5   3:03.41 mysqld             
 6116 mysql     21   1 2540m 1.8g  11m S 49.7 23.9   3:07.38 mysqld             
 6116 mysql     21   1 2580m 1.9g  11m S 69.6 24.4   3:12.59 mysqld             
 6116 mysql     21   1 2620m 1.9g  11m S 25.8 24.9   3:17.52 mysqld             
 6116 mysql     21   1 2660m 2.0g  11m S 51.7 25.4   3:22.60 mysqld             
 6116 mysql     21   1 2700m 2.0g  11m S 53.7 25.9   3:28.16 mysqld             
 6116 mysql     21   1 2740m 2.0g  11m S 49.7 26.4   3:33.30 mysqld             
 6116 mysql     21   1 2780m 2.1g  11m S 55.7 26.9   3:38.77 mysqld             
 6116 mysql     21   1 2824m 2.1g  11m S 49.7 27.5   3:44.55 mysqld             
 6116 mysql     21   1 2872m 2.2g  11m S 61.6 28.1   3:50.86 mysqld             
 6116 mysql     21   1 2908m 2.2g  11m S 67.6 28.5   3:56.36 mysqld             
 6116 mysql     21   1 2952m 2.2g  11m S 55.7 29.1   4:02.87 mysqld             
 6116 mysql     21   1 2992m 2.3g  11m S 59.6 29.6   4:08.47 mysqld             
 6116 mysql     21   1 3032m 2.3g  11m S 27.8 30.1   4:14.01 mysqld             
 6116 mysql     21   1 3061m 2.3g  11m S 11.9 30.5   4:18.35 mysqld             
 6116 mysql     21   1 3101m 2.4g  11m S 53.7 31.0   4:24.07 mysqld             
 6116 mysql     21   1 3141m 2.4g  11m S 23.9 31.5   4:29.94 mysqld             
 6116 mysql     21   1 3173m 2.5g  11m S 61.6 31.9   4:35.36 mysqld             
 6116 mysql     21   1 3213m 2.5g  11m S 79.5 32.4   4:41.22 mysqld             
 6116 mysql     21   1 3249m 2.5g  11m S 49.7 32.9   4:46.85 mysqld             
 6116 mysql     21   1 3277m 2.6g  11m S 37.8 33.2   4:51.68 mysqld             
 6116 mysql     21   1 3309m 2.6g  11m S 23.8 33.6   4:56.64 mysqld             
 6116 mysql     21   1 3341m 2.6g  11m S 53.7 34.0   5:02.27 mysqld             
 6116 mysql     21   1 3365m 2.6g  11m S 45.7 34.3   5:06.69 mysqld             
 6116 mysql     21   1 3397m 2.7g  11m S 47.7 34.8   5:12.04 mysqld             
 6116 mysql     21   1 3429m 2.7g  11m S 43.7 35.1   5:17.12 mysqld             
 6116 mysql     21   1 3457m 2.7g  11m S 59.6 35.5   5:22.43 mysqld             
 6116 mysql     21   1 3489m 2.8g  11m S 47.7 35.9   5:27.62 mysqld             
 6116 mysql     21   1 3517m 2.8g  11m S 23.8 36.3   5:32.31 mysqld             
 6116 mysql     21   1 3545m 2.8g  11m S 63.6 36.6   5:37.37 mysqld             
 6116 mysql     21   1 3573m 2.8g  11m S 59.6 37.0   5:42.99 mysqld             
 6116 mysql     21   1 3601m 2.9g  11m S 57.6 37.4   5:48.04 mysqld             
 6116 mysql     21   1 3629m 2.9g  11m S 43.8 37.7   5:53.00 mysqld             
 6116 mysql     21   1 3657m 2.9g  11m S 17.9 38.1   5:58.30 mysqld             
 6116 mysql     21   1 3681m 2.9g  11m S 55.7 38.4   6:03.04 mysqld             
 6116 mysql     21   1 3705m 3.0g  11m S 45.7 38.7   6:07.82 mysqld             
 6116 mysql     21   1 3737m 3.0g  11m S 57.6 39.1   6:13.05 mysqld             
 6116 mysql     21   1 3765m 3.0g  11m S 43.7 39.4   6:18.19 mysqld             
 6116 mysql     21   1 3793m 3.1g  11m S 65.7 39.8   6:23.63 mysqld             
 6116 mysql     21   1 3813m 3.1g  11m S 27.8 40.0   6:28.14 mysqld             
 6116 mysql     21   1 3841m 3.1g  11m S 65.6 40.4   6:33.24 mysqld             
 6116 mysql     21   1 3869m 3.1g  11m S 51.7 40.7   6:38.33 mysqld             
 6116 mysql     21   1 3889m 3.2g  11m S 25.8 41.0   6:42.32 mysqld             
 6116 mysql     21   1 3913m 3.2g  11m S 43.7 41.3   6:47.10 mysqld             
 6116 mysql     21   1 3941m 3.2g  11m S 39.7 41.6   6:52.15 mysqld             
 6116 mysql     21   1 3965m 3.2g  11m S 49.7 42.0   6:57.16 mysqld             
 6116 mysql     21   1 3989m 3.3g  11m S 21.9 42.3   7:01.81 mysqld             
 6116 mysql     21   1 4017m 3.3g  11m S 43.7 42.6   7:06.64 mysqld             
 6116 mysql     21   1 4041m 3.3g  11m S 57.6 42.9   7:11.77 mysqld             
 6116 mysql     21   1 4069m 3.3g  11m S 39.7 43.3   7:17.08 mysqld             
 6116 mysql     21   1 4089m 3.3g  11m S 49.7 43.5   7:21.57 mysqld             
 6116 mysql     21   1 4113m 3.4g  11m S 61.7 43.9   7:26.55 mysqld             
 6116 mysql     21   1 4137m 3.4g  11m S  9.9 44.2   7:31.85 mysqld             
 6116 mysql     21   1 4161m 3.4g  11m S 21.9 44.5   7:37.02 mysqld             
 6116 mysql     21   1 4189m 3.4g  11m S 43.7 44.8   7:42.60 mysqld             
 6116 mysql     21   1 4213m 3.5g  11m S 59.6 45.1   7:47.75 mysqld             
 6116 mysql     21   1 4233m 3.5g  11m S 51.7 45.3   7:52.01 mysqld             
 6116 mysql     21   1 4253m 3.5g  11m S 55.7 45.5   7:56.83 mysqld             
 6116 mysql     21   1 4277m 3.5g  11m S 59.6 45.8   8:01.32 mysqld             
 6116 mysql     21   1 4297m 3.5g  11m S 28.0 46.1   8:06.26 mysqld             
 6116 mysql     21   1 4321m 3.6g  11m S 49.7 46.3   8:11.01 mysqld             
 6116 mysql     21   1 4345m 3.6g  11m S 57.6 46.6   8:15.85 mysqld             
 6116 mysql     21   1 4365m 3.6g  11m S 47.7 46.8   8:20.30 mysqld             
 6116 mysql     21   1 4385m 3.6g  11m S 51.7 47.1   8:25.02 mysqld             
 6116 mysql     21   1 4405m 3.6g  11m S 51.7 47.3   8:29.58 mysqld             
 6116 mysql     21   1 4433m 3.7g  11m S 43.7 47.7   8:35.09 mysqld             
 6116 mysql     21   1 4453m 3.7g  11m S 25.8 48.0   8:39.83 mysqld             
 6116 mysql     21   1 4477m 3.7g  11m S 27.8 48.2   8:44.35 mysqld             
 6116 mysql     21   1 4497m 3.7g  11m S 63.7 48.5   8:49.26 mysqld             
 6116 mysql     21   1 4521m 3.8g  11m S 49.7 48.8   8:54.19 mysqld             
 6116 mysql     21   1 4553m 3.8g  11m S 77.5 49.2   8:59.50 mysqld             
 6116 mysql     21   1 4617m 3.8g  11m S 23.9 49.6   9:05.16 mysqld             
 6116 mysql     21   1 4673m 3.8g  11m S 11.9 49.9   9:10.40 mysqld             
 6116 mysql     21   1 4729m 3.9g  11m S 15.9 50.3   9:16.16 mysqld             
 6116 mysql     21   1 4777m 3.9g  11m S 63.6 50.6   9:21.63 mysqld             
 6116 mysql     21   1 4849m 3.9g  11m S 65.6 51.1   9:28.46 mysqld             
 6116 mysql     21   1 4913m 4.0g  11m S 67.6 51.5   9:35.18 mysqld             
 6116 mysql     21   1 4977m 4.0g  11m S 73.5 51.9   9:41.64 mysqld             
 6116 mysql     21   1 5033m 4.0g  11m S 59.6 52.3   9:48.21 mysqld             
 6116 mysql     21   1 5097m 4.1g  11m S 75.5 52.7   9:54.85 mysqld             
 6116 mysql     21   1 5145m 4.1g  11m S 19.9 53.1  10:00.30 mysqld             
 6116 mysql     21   1 5201m 4.1g  11m S 67.7 53.5  10:06.68 mysqld             
 6116 mysql     21   1 5257m 4.1g  11m S 49.7 53.9  10:13.04 mysqld             
 6116 mysql     21   1 5313m 4.2g  11m S 63.6 54.2  10:19.05 mysqld             
 6116 mysql     21   1 5377m 4.2g  11m S 71.6 54.7  10:25.58 mysqld             
 6116 mysql     21   1 5425m 4.2g  11m S 73.5 55.0  10:31.14 mysqld             
 6116 mysql     21   1 5481m 4.3g  11m S 55.7 55.4  10:37.19 mysqld             
 6116 mysql     21   1 5537m 4.3g  11m S 69.7 55.8  10:43.53 mysqld             
 6116 mysql     21   1 5593m 4.3g  11m S 73.6 56.2  10:50.58 mysqld             
 6116 mysql     21   1 5665m 4.4g  11m S 81.6 56.7  10:58.07 mysqld             
 6116 mysql     21   1 5729m 4.4g  11m S 37.8 57.2  11:05.47 mysqld             
 6116 mysql     21   1 5801m 4.4g  11m S 69.7 57.7  11:13.37 mysqld             
 6116 mysql     21   1 5857m 4.5g  11m S 77.5 58.1  11:20.10 mysqld             
 6116 mysql     21   1 5913m 4.5g  11m S 67.6 58.5  11:26.39 mysqld             
 6116 mysql     21   1 5977m 4.5g  11m S 81.5 59.0  11:33.20 mysqld             
 6116 mysql     21   1 6025m 4.6g  11m S 47.7 59.4  11:39.46 mysqld             
 6116 mysql     21   1 6089m 4.6g  11m S 77.5 59.9  11:46.72 mysqld             
 6116 mysql     21   1 6153m 4.6g  11m S 71.7 60.4  11:54.27 mysqld             
 6116 mysql     21   1 6217m 4.7g  11m S 57.6 60.8  12:01.51 mysqld             
 6116 mysql     21   1 6281m 4.7g  11m S 65.7 61.3  12:08.59 mysqld             
 6116 mysql     21   1 6337m 4.7g  11m S 75.2 61.8  12:15.96 mysqld             
 6116 mysql     21   1 6401m 4.8g  11m S 81.5 62.3  12:23.50 mysqld             
 6116 mysql     21   1 6465m 4.8g  11m S 65.6 62.7  12:30.81 mysqld             
 6116 mysql     21   1 6529m 4.9g  11m S 79.5 63.3  12:38.35 mysqld             
 6116 mysql     21   1 6593m 4.9g  11m S 85.5 63.8  12:45.67 mysqld             
 6116 mysql     21   1 6665m 4.9g  11m S 71.6 64.3  12:53.79 mysqld             
 6116 mysql     21   1 6737m 5.0g  11m S 85.5 64.9  13:02.06 mysqld             
 6116 mysql     21   1 6801m 5.0g  11m S 79.6 65.4  13:09.65 mysqld             
 6116 mysql     21   1 6873m 5.1g  11m S 69.7 66.0  13:17.27 mysqld             
 6116 mysql     21   1 6929m 5.1g  11m S 51.7 66.5  13:24.36 mysqld             
 6116 mysql     21   1 6993m 5.1g  11m S 77.5 67.0  13:31.92 mysqld             
 6116 mysql     21   1 7057m 5.2g  10m S 73.5 67.5  13:39.82 mysqld             
 6116 mysql     21   1 7121m 5.2g 9532 S 77.7 68.0  13:47.49 mysqld             
 6116 mysql     21   1 7193m 5.3g 8132 S 75.6 68.6  13:55.84 mysqld             
 6116 mysql     21   1 7265m 5.3g 7596 S 81.5 69.2  14:04.05 mysqld             
 6116 mysql     21   1 7329m 5.4g 7596 S 91.4 69.7  14:12.11 mysqld             
 6116 mysql     21   1 7393m 5.4g 7596 S 83.5 70.3  14:20.33 mysqld             
 6116 mysql     21   1 7465m 5.4g 7596 S 79.5 70.9  14:28.49 mysqld             
 6116 mysql     21   1 7521m 5.5g 7596 S 75.5 71.4  14:35.43 mysqld             
 6116 mysql     21   1 7593m 5.5g 7596 S 69.7 72.0  14:43.31 mysqld             
 6116 mysql     21   1 7641m 5.6g 4172 S 73.5 72.4  14:50.11 mysqld             
 6116 mysql     21   1 7697m 5.6g 3816 S 63.6 72.9  14:57.21 mysqld             
 6116 mysql     21   1 7761m 5.6g 3104 S 77.5 73.4  15:04.64 mysqld             
 6116 mysql     21   1 7809m 5.7g 2244 S 69.6 73.9  15:10.92 mysqld             
 6116 mysql     21   1 7865m 5.7g 3260 S 55.7 74.3  15:17.92 mysqld             
 6116 mysql     21   1 7921m 5.8g 3328 S 81.5 74.9  15:25.74 mysqld             
 6116 mysql     21   1 7985m 5.8g 3328 S 73.5 75.4  15:32.87 mysqld             
 6116 mysql     21   1 8049m 5.8g 2228 S 62.9 76.0  15:41.14 mysqld             
 6116 mysql     21   1 8105m 5.9g 2176 S 67.8 76.5  15:47.85 mysqld             
 6116 mysql     21   1 8145m 5.9g 1968 S 39.8 76.9  15:53.42 mysqld             
 6116 mysql     21   1 8193m 5.9g 1984 S 55.3 77.3  15:59.03 mysqld             
 6116 mysql     21   1 8233m 6.0g 1992 S 65.7 77.7  16:04.51 mysqld             
 6116 mysql     21   1 8281m 6.0g 1932 S 61.5 78.2  16:10.35 mysqld             
 6116 mysql     21   1 8329m 6.0g 1932 S 36.8 78.6  16:15.97 mysqld             
 6116 mysql     21   1 8377m 6.1g 1932 S 65.6 79.1  16:22.12 mysqld             
 6116 mysql     21   1 8425m 6.1g 1952 S 17.9 79.5  16:28.22 mysqld             
 6116 mysql     21   1 8481m 6.1g 1932 S 65.6 80.0  16:34.60 mysqld             
 6116 mysql     21   1 8529m 6.2g 1908 S 64.3 80.3  16:41.13 mysqld             
 6116 mysql     21   1 8585m 6.2g 1908 S 71.4 80.7  16:47.54 mysqld             
 6116 mysql     21   1 8641m 6.2g 1908 S 65.7 81.1  16:54.02 mysqld             
 6116 mysql     21   1 8689m 6.2g 1924 S 15.9 81.2  16:59.96 mysqld             
 6116 mysql     21   1 8737m 6.3g 1912 S 67.6 81.6  17:05.74 mysqld             
 6116 mysql     21   1 8785m 6.3g 1904 S 67.6 81.7  17:12.19 mysqld             
 6116 mysql     21   1 8841m 6.3g 1928 S 71.6 81.8  17:18.86 mysqld             
 6116 mysql     21   1 8905m 6.3g 1908 S 11.9 82.0  17:26.06 mysqld             
 6116 mysql     21   1 8961m 6.3g 1904 S 76.4 82.1  17:33.00 mysqld             
 6116 mysql     21   1 9017m 6.3g 1924 S 53.7 82.3  17:40.13 mysqld             
 6116 mysql     21   1 9073m 6.4g 1904 S 71.2 82.7  17:46.94 mysqld             
 6116 mysql     21   1 9129m 6.4g 1904 S 73.4 82.8  17:53.62 mysqld             
 6116 mysql     21   1 9185m 6.4g 1928 S 76.6 83.0  18:00.21 mysqld             
 6116 mysql     21   1 9249m 6.4g 1928 S 53.7 83.1  18:07.49 mysqld             
 6116 mysql     21   1 9313m 6.4g 1912 S 81.5 83.5  18:15.41 mysqld             
 6116 mysql     21   1 9377m 6.4g 1908 S 65.3 83.7  18:22.90 mysqld             
 6116 mysql     21   1 9433m 6.4g 1904 S 39.7 83.7  18:29.76 mysqld             
 6116 mysql     21   1 9497m 6.5g 1904 S 67.6 84.0  18:37.20 mysqld             
 6116 mysql     21   1 9553m 6.5g 1928 S 69.3 84.0  18:44.06 mysqld             
 6116 mysql     21   1 9617m 6.5g 1904 S 43.3 84.3  18:51.49 mysqld             
 6116 mysql     21   1 9673m 6.5g 1924 S 55.6 84.0  18:58.33 mysqld             
 6116 mysql     21   1 9721m 6.5g 1924 S 83.5 84.3  19:04.66 mysqld             
 6116 mysql     21   1 9793m 6.5g 1924 S 77.5 84.6  19:12.33 mysqld             
 6116 mysql     21   1 9857m 6.5g 1924 S 85.5 84.8  19:20.18 mysqld             
 6116 mysql     21   1 9921m 6.5g 1928 S 78.7 85.0  19:27.85 mysqld             
 6116 mysql     21   1 9993m 6.5g 1928 S 80.7 85.1  19:35.67 mysqld             
 6116 mysql     21   1  9.8g 6.5g 1924 S 75.5 85.2  19:43.45 mysqld             
 6116 mysql     21   1  9.9g 6.5g 1924 S 82.9 85.1  19:51.70 mysqld             
 6116 mysql     21   1 10.0g 6.6g 1924 S 89.4 85.4  20:00.19 mysqld             
 6116 mysql     21   1 10.0g 6.6g 1928 S 77.6 85.5  20:08.17 mysqld             
 6116 mysql     21   1 10.1g 6.6g 1928 S 81.5 85.7  20:16.03 mysqld             
 6116 mysql     21   1 10.2g 6.6g 1928 S 87.5 85.9  20:24.53 mysqld             
 6116 mysql     21   1 10.2g 6.6g 1928 S 83.5 85.7  20:32.62 mysqld             
 6116 mysql     21   1 10.3g 6.6g 1944 S 81.5 86.0  20:40.96 mysqld             
 6116 mysql     21   1 10.4g 6.6g 1940 S 43.0 86.3  20:49.33 mysqld             
 6116 mysql     21   1 10.4g 6.7g 2364 S 75.5 86.6  20:58.26 mysqld             
 6116 mysql     21   1 10.5g 6.7g 2260 S 81.8 87.0  21:07.18 mysqld             
 6116 mysql     21   1 10.6g 6.7g 2044 S 87.4 87.3  21:15.50 mysqld             
 6116 mysql     21   1 10.7g 6.7g 2020 S 91.4 87.1  21:24.49 mysqld             
 6116 mysql     21   1 10.8g 6.7g 1928 S 88.9 87.5  21:33.34 mysqld             
 6116 mysql     21   1 10.8g 6.7g 1928 S 89.4 87.7  21:42.36 mysqld             
 6116 mysql     21   1 10.9g 6.7g 1912 S 78.8 87.7  21:51.24 mysqld             
 6116 mysql     21   1 11.0g 6.7g 1908 S 91.9 87.6  21:59.99 mysqld             
 6116 mysql     21   1 11.1g 6.8g 1928 S 95.4 88.1  22:09.10 mysqld             
 6116 mysql     21   1 11.1g 6.8g 1916 S 94.2 88.0  22:18.68 mysqld             
 6116 mysql     21   1 11.2g 6.8g 1904 S 92.1 87.9  22:28.05 mysqld             
 6116 mysql     21   1 11.3g 6.8g 1924 S 91.3 88.3  22:37.65 mysqld             
 6116 mysql     21   1 11.4g 6.8g 1904 S 90.2 88.3  22:47.23 mysqld             
 6116 mysql     21   1 11.5g 6.8g 1980 S 83.3 88.4  22:56.20 mysqld             
 6116 mysql     21   1 11.5g 6.8g 1912 S 97.2 88.3  23:05.77 mysqld             
 6116 mysql     21   1 11.6g 6.8g 1916 S 93.3 88.7  23:15.30 mysqld             
 6116 mysql     21   1 11.7g 6.8g 1912 S 93.6 88.8  23:24.89 mysqld             
 6116 mysql     21   1 11.8g 6.8g 1932 S 97.2 88.8  23:35.06 mysqld             
 6116 mysql     21   1 11.9g 6.9g 1920 S 97.2 89.3  23:44.82 mysqld             
 6116 mysql     21   1 12.0g 6.8g 1928 S 97.4 89.1  23:54.82 mysqld             
 6116 mysql     21   1 12.1g 6.8g 1908 S 91.6 89.0  24:05.54 mysqld             
 6116 mysql     21   1 12.2g 6.8g 1900 S 94.8 88.5  24:15.43 mysqld             
 6116 mysql     21   1 12.3g 6.8g 1912 S 99.2 89.0  24:25.50 mysqld             
 6116 mysql     21   1 12.4g 6.8g 2024 S 97.8 88.8  24:35.74 mysqld             
 6116 mysql     21   1 12.5g 6.8g 1924 S 101.4 88.5  24:46.17 mysqld            
 6116 mysql     21   1 12.7g 6.8g 1948 S 99.4 88.8  24:56.48 mysqld             
 6116 mysql     21   1 12.9g 6.9g 1912 S 93.4 89.4  25:06.36 mysqld             
 6116 mysql     21   1     0    0    0 Z 100.2  0.0  25:13.31 mysqld <defunct>

I'm using RPM's.
After about 30 min of execution system is starting using swap.
After about 40 min no memory is left, and mysql process shuts down.

Comment by Elena Stepanova [ 2015-09-05 ]

I also tried to reproduce on CentOS 6 (with 10.1.6 RPM packages), result was the same as on my Wheezy.
It's quite possible that my data is too artificial and it does not trigger the problem.
Could you please run 

EXPLAIN EXTENDED
 
SELECT 
    M.date_interval, 
    SUM((SELECT TKRD.amount FROM log_table TKRD WHERE TKRD.related_id = KR.id AND TKRD.created_at <= M.end_date ORDER BY TKRD.created_at DESC LIMIT 1)) AS amount 
FROM 
    some_table KR 
    JOIN other_db.dates PID ON PID.related_entity_id = KR.related_entity_id 
    JOIN other_db.months M 
WHERE    
    M.end_date >= PID.purchase_date 
    AND M.end_date < PID.end_date 
    AND KR.simulated_date >= PID.purchase_date 
    AND ( 
        KR.actual_date = '0000-00-00' 
        OR KR.actual_date > M.end_date 
    ) 
GROUP BY 
    M.date_interval 
;
 
SHOW WARNINGS;

and 

SHOW INDEX IN <table name>;
 
SHOW TABLE STATUS LIKE '<table name>';

for all involved tables?
I'll see what's different on my side and will try to adjust data accordingly.

Comment by m.rygiel [ 2015-09-07 ] 

############################################################################################################
 
EXPLAIN:
 
############################################################################################################
 
    id  select_type         table   type    possible_keys        key                key_len  ref                            rows    Extra                                           
------  ------------------  ------  ------  -------------------  -----------------  -------  -----------------------------  ------  ------------------------------------------------
 
     1  PRIMARY             PID     ALL     PRIMARY              (NULL)             (NULL)   (NULL)                         60770   Using temporary; Using filesort                 
     1  PRIMARY             KR      ref     number               number             4        tempdb.PID.related_entity_id   10      Using where                                     
     1  PRIMARY             M       ALL     end_date             (NULL)             (NULL)   (NULL)                         28      Range checked for each record (index map: 0x4)  
     2  DEPENDENT SUBQUERY  TKRD    ref     related_id           related_id         4        db.KR.id                       3       Using where; Using filesort                     
 
 
 
 
############################################################################################################
 
SHOW INDEX IN some_table; (KR)
 
############################################################################################################
 
Table        Non_unique  Key_name        Seq_in_index  Column_name            Collation  Cardinality  Sub_part  Packed  Null    Index_type  Comment  Index_comment  
-----------  ----------  --------------  ------------  ---------------------  ---------  -----------  --------  ------  ------  ----------  -------  ---------------
 
some_table            0  PRIMARY                    1  id                     A              3730390    (NULL)  (NULL)          BTREE                               
some_table            1  status_id                  1  status_id              A                    8    (NULL)  (NULL)          BTREE                               
some_table            1  number                     1  related_entity_id      A               373039    (NULL)  (NULL)          BTREE                               
some_table            1  number                     2  number                 A              3730390    (NULL)  (NULL)          BTREE                               
some_table            1  percent                    1  percent                A                   20    (NULL)  (NULL)          BTREE  
 
 
 
 
############################################################################################################
 
SHOW INDEX IN other_db.dates; (PID)
 
############################################################################################################
 
Table        Non_unique  Key_name  Seq_in_index  Column_name        Collation  Cardinality  Sub_part  Packed  Null    Index_type  Comment  Index_comment  
-----------  ----------  --------  ------------  -----------------  ---------  -----------  --------  ------  ------  ----------  -------  ---------------
 
dates                 0  PRIMARY              1  related_entity_id  A                60770    (NULL)  (NULL)          BTREE                               
   
 
 
############################################################################################################
 
SHOW INDEX IN other_db.months; (M)
 
############################################################################################################
 
Table                Non_unique  Key_name             Seq_in_index  Column_name          Collation  Cardinality  Sub_part  Packed  Null    Index_type  Comment  Index_comment  
-------------------  ----------  -------------------  ------------  -------------------  ---------  -----------  --------  ------  ------  ----------  -------  ---------------
 
months                        1  date_interval                   1  date_interval        A                   28    (NULL)  (NULL)  YES     BTREE                               
months                        1  start_date                      1  start_date           A                   28    (NULL)  (NULL)  YES     BTREE                               
months                        1  end_date                        1  end_date             A                   28    (NULL)  (NULL)  YES     BTREE        
 
 
 
 
############################################################################################################
 
SHOW INDEX IN log_table; (TKRD)
 
############################################################################################################
 
Table                 Non_unique  Key_name           Seq_in_index  Column_name          Collation  Cardinality  Sub_part  Packed  Null    Index_type  Comment  Index_comment  
--------------------  ----------  -----------------  ------------  -------------------  ---------  -----------  --------  ------  ------  ----------  -------  ---------------
 
log_table                      0  PRIMARY                       1  id                   A             24319394    (NULL)  (NULL)          BTREE                               
log_table                      1  related_id                    1  related_id           A              8106464    (NULL)  (NULL)          BTREE                               
log_table                      1  status                        1  status               A                 6024    (NULL)  (NULL)          BTREE                               
log_table                      1  related_entity_id             1  related_entity_id    A               357638    (NULL)  (NULL)          BTREE                               
log_table                      1  last_id                       1  last_id              A             24319394    (NULL)  (NULL)          BTREE 
 
 
 
 
############################################################################################################
 
SHOW TABLE STATUS LIKE 'some_table';
 
############################################################################################################
 
Name         Engine  Version  Row_format     Rows  Avg_row_length  Data_length  Max_data_length  Index_length  Data_free  Auto_increment  Create_time          Update_time  Check_time  Collation       Checksum  Create_options  Comment  
-----------  ------  -------  ----------  -------  --------------  -----------  ---------------  ------------  ---------  --------------  -------------------  -----------  ----------  --------------  --------  --------------  ---------
 
some_table   InnoDB       10  Compact     3730390              69    260833280                0     161284096    5242880         4095757  2015-09-04 06:56:53  (NULL)       (NULL)      utf8_polish_ci    (NULL)                           
 
 
 
 
############################################################################################################
 
SHOW TABLE STATUS LIKE 'dates';
 
############################################################################################################
 
Name       Engine  Version  Row_format    Rows  Avg_row_length  Data_length  Max_data_length  Index_length  Data_free  Auto_increment  Create_time          Update_time  Check_time  Collation       Checksum  Create_options  Comment  
---------  ------  -------  ----------  ------  --------------  -----------  ---------------  ------------  ---------  --------------  -------------------  -----------  ----------  --------------  --------  --------------  ---------
 
dates      InnoDB       10  Compact      60770              43      2637824                0             0    4194304          (NULL)  2015-09-07 08:52:33  (NULL)       (NULL)      utf8_polish_ci    (NULL)                           
 
 
 
 
############################################################################################################
 
SHOW TABLE STATUS LIKE 'months';
 
############################################################################################################
 
Name     Engine  Version  Row_format    Rows  Avg_row_length  Data_length  Max_data_length  Index_length  Data_free  Auto_increment  Create_time          Update_time  Check_time  Collation       Checksum  Create_options  Comment  
-------  ------  -------  ----------  ------  --------------  -----------  ---------------  ------------  ---------  --------------  -------------------  -----------  ----------  --------------  --------  --------------  ---------
 
months   InnoDB       10  Compact         28             585        16384                0         49152          0          (NULL)  2015-09-07 08:52:33  (NULL)       (NULL)      utf8_polish_ci    (NULL)                           
 
 
 
 
############################################################################################################
 
SHOW TABLE STATUS LIKE 'log_table';
 
############################################################################################################
 
Name                  Engine  Version  Row_format      Rows  Avg_row_length  Data_length  Max_data_length  Index_length  Data_free  Auto_increment  Create_time          Update_time  Check_time  Collation       Checksum  Create_options  Comment  
--------------------  ------  -------  ----------  --------  --------------  -----------  ---------------  ------------  ---------  --------------  -------------------  -----------  ----------  --------------  --------  --------------  ---------
 
log_table             InnoDB       10  Compact     24319394              86   2100297728                0    2829926400    5242880        27640178  2015-09-04 06:56:53  (NULL)       (NULL)      utf8_polish_ci    (NULL)

Comment by m.rygiel [ 2015-09-07 ]

There might be some errors due to translation, sorry for that.

Comment by Elena Stepanova [ 2015-09-08 ]

Thank you.
I've amended my artificial data and eventually got the plan and statistics closed enough to yours.
Strangely enough, it only made the query much faster, now it completes in ~5 minutes and certainly does not cause insane memory consumption (your config, 10.1.6, CentOS 6).

Is it possible that while simplifying and obfuscating table structures and tables, you missed out something important?
E.g. other_db.months table in your report does not have any indexes, but your SHOW INDEX output shows that it actually does; only after adding these indexes I could produce the same execution plan for SELECT as you quoted. Could there be anything else?

Comment by Sergei Golubchik [ 2017-06-26 ]

No answer for ~20 months. Closing.

Generated at Thu Feb 08 07:28:03 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.