Details
-
Type:
Bug
-
Status: Open
-
Priority:
Major
-
Resolution: Unresolved
-
Affects Version/s: 10.1.5, 10.1.6
-
Fix Version/s: None
-
Component/s: None
-
Labels:None
-
Environment:CentOS 6
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?
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
# 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;
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.
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.
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.
############################################################################################################
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)
There might be some errors due to translation, sorry for that.
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?
Hi,
Could you please also give us a hint on what log_table (used in the subquery) might look like?
Thanks.