Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Incomplete
-
10.1.5, 10.1.6
-
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?
