Description:
In the attached MTR test case memory is not freed immediately neither after executing "SELECT COUNT(*) FROM t1 WHERE MATCH (col2, col3, col8, col6, col7, col5, col4, col13) AGAINST ('"ipsum"');" nor after closing the connection.
In some cases this may cause process termination because of OOM.
Here is the output from the 'pidstat' utility. Notice how %MEM value is growing during the execution.
inserting: 4589
inserting: 4590
inserting: 4591
Linux 4.9.0-8-amd64 (stretch) 03/15/19 _x86_64_ (1 CPU)
17:04:12 UID PID minflt/s majflt/s VSZ RSS %MEM Command
17:04:12 1000 3421 22.00 0.00 782484 106312 10.62 mysqld-debug
SET GLOBAL innodb_optimize_fulltext_only = ON;
OPTIMIZE TABLE t1;
Table Op Msg_type Msg_text
test.t1 optimize status OK
SET GLOBAL innodb_optimize_fulltext_only = OFF;
Linux 4.9.0-8-amd64 (stretch) 03/15/19 _x86_64_ (1 CPU)
17:04:12 UID PID minflt/s majflt/s VSZ RSS %MEM Command
17:04:12 1000 3421 22.00 0.00 782484 106160 10.61 mysqld-debug
SELECT COUNT(*) FROM t1 WHERE MATCH (col2, col3, col8, col6, col7, col5, col4, col13) AGAINST ('"ipsum"');
Linux 4.9.0-8-amd64 (stretch) 03/15/19 _x86_64_ (1 CPU)
17:04:12 UID PID minflt/s majflt/s VSZ RSS %MEM Command
17:04:12 1000 3421 39.88 0.01 848284 177152 17.70 mysqld-debug
querying: 0
SELECT COUNT(*) FROM t1 WHERE MATCH (col2, col3, col8, col6, col7, col5, col4, col13) AGAINST ('"ipsum"');
Linux 4.9.0-8-amd64 (stretch) 03/15/19 _x86_64_ (1 CPU)
17:04:12 UID PID minflt/s majflt/s VSZ RSS %MEM Command
17:04:12 1000 3421 57.74 0.02 914084 252656 25.24 mysqld-debug
querying: 1
SELECT COUNT(*) FROM t1 WHERE MATCH (col2, col3, col8, col6, col7, col5, col4, col13) AGAINST ('"ipsum"');
Linux 4.9.0-8-amd64 (stretch) 03/15/19 _x86_64_ (1 CPU)
17:04:12 UID PID minflt/s majflt/s VSZ RSS %MEM Command
17:04:12 1000 3421 75.59 0.02 979620 320068 31.98 mysqld-debug
querying: 2
SELECT COUNT(*) FROM t1 WHERE MATCH (col2, col3, col8, col6, col7, col5, col4, col13) AGAINST ('"ipsum"');
Linux 4.9.0-8-amd64 (stretch) 03/15/19 _x86_64_ (1 CPU)
17:04:12 UID PID minflt/s majflt/s VSZ RSS %MEM Command
17:04:12 1000 3421 92.86 0.08 1051852 390672 39.03 mysqld-debug
querying: 3
SELECT COUNT(*) FROM t1 WHERE MATCH (col2, col3, col8, col6, col7, col5, col4, col13) AGAINST ('"ipsum"');
Linux 4.9.0-8-amd64 (stretch) 03/15/19 _x86_64_ (1 CPU)
17:04:13 UID PID minflt/s majflt/s VSZ RSS %MEM Command
17:04:13 1000 3421 110.76 0.11 1117388 468412 46.80 mysqld-debug
querying: 4
SELECT COUNT(*) FROM t1 WHERE MATCH (col2, col3, col8, col6, col7, col5, col4, col13) AGAINST ('"ipsum"');
Linux 4.9.0-8-amd64 (stretch) 03/15/19 _x86_64_ (1 CPU)
17:04:13 UID PID minflt/s majflt/s VSZ RSS %MEM Command
17:04:13 1000 3421 128.59 0.12 1182924 537508 53.70 mysqld-debug
querying: 5
SELECT COUNT(*) FROM t1 WHERE MATCH (col2, col3, col8, col6, col7, col5, col4, col13) AGAINST ('"ipsum"');
Linux 4.9.0-8-amd64 (stretch) 03/15/19 _x86_64_ (1 CPU)
17:04:13 UID PID minflt/s majflt/s VSZ RSS %MEM Command
17:04:13 1000 3421 146.45 0.15 1248460 613776 61.32 mysqld-debug
querying: 6
SELECT COUNT(*) FROM t1 WHERE MATCH (col2, col3, col8, col6, col7, col5, col4, col13) AGAINST ('"ipsum"');
Linux 4.9.0-8-amd64 (stretch) 03/15/19 _x86_64_ (1 CPU)
17:04:13 UID PID minflt/s majflt/s VSZ RSS %MEM Command
17:04:13 1000 3421 164.26 0.15 1313996 688180 68.75 mysqld-debug
querying: 7
SELECT COUNT(*) FROM t1 WHERE MATCH (col2, col3, col8, col6, col7, col5, col4, col13) AGAINST ('"ipsum"');
Linux 4.9.0-8-amd64 (stretch) 03/15/19 _x86_64_ (1 CPU)
17:04:13 UID PID minflt/s majflt/s VSZ RSS %MEM Command
17:04:13 1000 3421 181.76 0.15 1379532 734692 73.40 mysqld-debug
querying: 8
SELECT COUNT(*) FROM t1 WHERE MATCH (col2, col3, col8, col6, col7, col5, col4, col13) AGAINST ('"ipsum"');
Linux 4.9.0-8-amd64 (stretch) 03/15/19 _x86_64_ (1 CPU)
17:04:13 UID PID minflt/s majflt/s VSZ RSS %MEM Command
17:04:13 1000 3421 199.55 0.15 1379532 734616 73.39 mysqld-debug
How to repeat:
Run the following MTR test case on a virtual machine with 1 logical CPU and 1 GB RAM.
Originally reproduced on a Debian 9.8 VM, but most probably is not platform/OS-dependent.
Surprisingly, having more than 1 logical CPU dramatically improves memory utilization in this test.
--source include/have_innodb.inc
--let $pid_file = `SELECT @@global.pid_file`
--echo pid_file: $pid_file
SET @saved_thread_cache_size = @@global.thread_cache_size;
SET GLOBAL thread_cache_size = 0;
CREATE TABLE t1 (
col1 INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
col2 TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
col3 TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
col4 TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
col5 LONGTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
col6 TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
col7 TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
col8 TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
col9 VARCHAR(255) DEFAULT NULL,
col10 TEXT,
col11 TEXT,
col12 ENUM('low', 'normal', 'high') NOT NULL,
col13 TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
PRIMARY KEY (col1),
UNIQUE KEY col9_UNIQUE (col9),
FULLTEXT KEY col10_idx (col10),
FULLTEXT KEY col11_idx (col11),
FULLTEXT KEY col4_idx (col4),
FULLTEXT KEY col5_idx (col5),
FULLTEXT KEY col_composite (col2, col3, col8, col6, col7, col5, col4, col13),
FULLTEXT KEY col23_composite (col2, col3),
FULLTEXT KEY col867_composite (col8, col6, col7),
FULLTEXT KEY col2_idx (col2),
FULLTEXT KEY col3_idx (col3),
FULLTEXT KEY col8_idx (col8)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--disable_query_log
--let $i = 1
--let $n = 4591
while($i <= $n)
{
eval INSERT INTO t1 SET
col2 = 'test@test.test$i',
col3 = 'Percona $i',
col4 = 'Lorem Ipsum',
col5 = REPEAT('Lorem Ipsum $i ', 1000),
col6 = 'Lorem Ipsum $i',
col7 = 'Ipsum Lorem $i',
col8 = 'test@test.test$i',
col9 = 'asdkjsadkjsad;jadkjadlk $i',
col10 = REPEAT('Lorem Ipsum $i ', 1000),
col11 = 'lorem ipsum',
col12 = 'normal',
col13 = 'something'
;
--echo inserting: $i
--inc $i
}
--enable_query_log
--let $i = 0
--let $n = 100
while($i < $n)
{
--connect(con1,localhost,root,,test)
--connection con1
--disable_result_log
SELECT COUNT(*) FROM t1 WHERE MATCH (col2, col3, col8, col6, col7, col5, col4, col13) AGAINST ('"ipsum"');
--enable_result_log
--exec pidstat -p `cat $pid_file` -r
--echo querying: $i
--disconnect con1
--inc $i
}
--connection default
DROP TABLE t1;
SET GLOBAL thread_cache_size = @saved_thread_cache_size;
Description: In the attached MTR test case memory is not freed immediately neither after executing "SELECT COUNT(*) FROM t1 WHERE MATCH (col2, col3, col8, col6, col7, col5, col4, col13) AGAINST ('"ipsum"');" nor after closing the connection. In some cases this may cause process termination because of OOM. Here is the output from the 'pidstat' utility. Notice how %MEM value is growing during the execution. inserting: 4589 inserting: 4590 inserting: 4591 Linux 4.9.0-8-amd64 (stretch) 03/15/19 _x86_64_ (1 CPU) 17:04:12 UID PID minflt/s majflt/s VSZ RSS %MEM Command 17:04:12 1000 3421 22.00 0.00 782484 106312 10.62 mysqld-debug SET GLOBAL innodb_optimize_fulltext_only = ON; OPTIMIZE TABLE t1; Table Op Msg_type Msg_text test.t1 optimize status OK SET GLOBAL innodb_optimize_fulltext_only = OFF; Linux 4.9.0-8-amd64 (stretch) 03/15/19 _x86_64_ (1 CPU) 17:04:12 UID PID minflt/s majflt/s VSZ RSS %MEM Command 17:04:12 1000 3421 22.00 0.00 782484 106160 10.61 mysqld-debug SELECT COUNT(*) FROM t1 WHERE MATCH (col2, col3, col8, col6, col7, col5, col4, col13) AGAINST ('"ipsum"'); Linux 4.9.0-8-amd64 (stretch) 03/15/19 _x86_64_ (1 CPU) 17:04:12 UID PID minflt/s majflt/s VSZ RSS %MEM Command 17:04:12 1000 3421 39.88 0.01 848284 177152 17.70 mysqld-debug querying: 0 SELECT COUNT(*) FROM t1 WHERE MATCH (col2, col3, col8, col6, col7, col5, col4, col13) AGAINST ('"ipsum"'); Linux 4.9.0-8-amd64 (stretch) 03/15/19 _x86_64_ (1 CPU) 17:04:12 UID PID minflt/s majflt/s VSZ RSS %MEM Command 17:04:12 1000 3421 57.74 0.02 914084 252656 25.24 mysqld-debug querying: 1 SELECT COUNT(*) FROM t1 WHERE MATCH (col2, col3, col8, col6, col7, col5, col4, col13) AGAINST ('"ipsum"'); Linux 4.9.0-8-amd64 (stretch) 03/15/19 _x86_64_ (1 CPU) 17:04:12 UID PID minflt/s majflt/s VSZ RSS %MEM Command 17:04:12 1000 3421 75.59 0.02 979620 320068 31.98 mysqld-debug querying: 2 SELECT COUNT(*) FROM t1 WHERE MATCH (col2, col3, col8, col6, col7, col5, col4, col13) AGAINST ('"ipsum"'); Linux 4.9.0-8-amd64 (stretch) 03/15/19 _x86_64_ (1 CPU) 17:04:12 UID PID minflt/s majflt/s VSZ RSS %MEM Command 17:04:12 1000 3421 92.86 0.08 1051852 390672 39.03 mysqld-debug querying: 3 SELECT COUNT(*) FROM t1 WHERE MATCH (col2, col3, col8, col6, col7, col5, col4, col13) AGAINST ('"ipsum"'); Linux 4.9.0-8-amd64 (stretch) 03/15/19 _x86_64_ (1 CPU) 17:04:13 UID PID minflt/s majflt/s VSZ RSS %MEM Command 17:04:13 1000 3421 110.76 0.11 1117388 468412 46.80 mysqld-debug querying: 4 SELECT COUNT(*) FROM t1 WHERE MATCH (col2, col3, col8, col6, col7, col5, col4, col13) AGAINST ('"ipsum"'); Linux 4.9.0-8-amd64 (stretch) 03/15/19 _x86_64_ (1 CPU) 17:04:13 UID PID minflt/s majflt/s VSZ RSS %MEM Command 17:04:13 1000 3421 128.59 0.12 1182924 537508 53.70 mysqld-debug querying: 5 SELECT COUNT(*) FROM t1 WHERE MATCH (col2, col3, col8, col6, col7, col5, col4, col13) AGAINST ('"ipsum"'); Linux 4.9.0-8-amd64 (stretch) 03/15/19 _x86_64_ (1 CPU) 17:04:13 UID PID minflt/s majflt/s VSZ RSS %MEM Command 17:04:13 1000 3421 146.45 0.15 1248460 613776 61.32 mysqld-debug querying: 6 SELECT COUNT(*) FROM t1 WHERE MATCH (col2, col3, col8, col6, col7, col5, col4, col13) AGAINST ('"ipsum"'); Linux 4.9.0-8-amd64 (stretch) 03/15/19 _x86_64_ (1 CPU) 17:04:13 UID PID minflt/s majflt/s VSZ RSS %MEM Command 17:04:13 1000 3421 164.26 0.15 1313996 688180 68.75 mysqld-debug querying: 7 SELECT COUNT(*) FROM t1 WHERE MATCH (col2, col3, col8, col6, col7, col5, col4, col13) AGAINST ('"ipsum"'); Linux 4.9.0-8-amd64 (stretch) 03/15/19 _x86_64_ (1 CPU) 17:04:13 UID PID minflt/s majflt/s VSZ RSS %MEM Command 17:04:13 1000 3421 181.76 0.15 1379532 734692 73.40 mysqld-debug querying: 8 SELECT COUNT(*) FROM t1 WHERE MATCH (col2, col3, col8, col6, col7, col5, col4, col13) AGAINST ('"ipsum"'); Linux 4.9.0-8-amd64 (stretch) 03/15/19 _x86_64_ (1 CPU) 17:04:13 UID PID minflt/s majflt/s VSZ RSS %MEM Command 17:04:13 1000 3421 199.55 0.15 1379532 734616 73.39 mysqld-debug How to repeat: Run the following MTR test case on a virtual machine with 1 logical CPU and 1 GB RAM. Originally reproduced on a Debian 9.8 VM, but most probably is not platform/OS-dependent. Surprisingly, having more than 1 logical CPU dramatically improves memory utilization in this test. --source include/have_innodb.inc --let $pid_file = `SELECT @@global.pid_file` --echo pid_file: $pid_file SET @saved_thread_cache_size = @@global.thread_cache_size; SET GLOBAL thread_cache_size = 0; CREATE TABLE t1 ( col1 INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, col2 TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, col3 TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, col4 TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, col5 LONGTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, col6 TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, col7 TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, col8 TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, col9 VARCHAR(255) DEFAULT NULL, col10 TEXT, col11 TEXT, col12 ENUM('low', 'normal', 'high') NOT NULL, col13 TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, PRIMARY KEY (col1), UNIQUE KEY col9_UNIQUE (col9), FULLTEXT KEY col10_idx (col10), FULLTEXT KEY col11_idx (col11), FULLTEXT KEY col4_idx (col4), FULLTEXT KEY col5_idx (col5), FULLTEXT KEY col_composite (col2, col3, col8, col6, col7, col5, col4, col13), FULLTEXT KEY col23_composite (col2, col3), FULLTEXT KEY col867_composite (col8, col6, col7), FULLTEXT KEY col2_idx (col2), FULLTEXT KEY col3_idx (col3), FULLTEXT KEY col8_idx (col8) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; --disable_query_log --let $i = 1 --let $n = 4591 while($i <= $n) { eval INSERT INTO t1 SET col2 = 'test@test.test$i', col3 = 'Percona $i', col4 = 'Lorem Ipsum', col5 = REPEAT('Lorem Ipsum $i ', 1000), col6 = 'Lorem Ipsum $i', col7 = 'Ipsum Lorem $i', col8 = 'test@test.test$i', col9 = 'asdkjsadkjsad;jadkjadlk $i', col10 = REPEAT('Lorem Ipsum $i ', 1000), col11 = 'lorem ipsum', col12 = 'normal', col13 = 'something' ; --echo inserting: $i --inc $i } --enable_query_log --let $i = 0 --let $n = 100 while($i < $n) { --connect(con1,localhost,root,,test) --connection con1 --disable_result_log SELECT COUNT(*) FROM t1 WHERE MATCH (col2, col3, col8, col6, col7, col5, col4, col13) AGAINST ('"ipsum"'); --enable_result_log --exec pidstat -p `cat $pid_file` -r --echo querying: $i --disconnect con1 --inc $i } --connection default DROP TABLE t1; SET GLOBAL thread_cache_size = @saved_thread_cache_size;