Bug #95065 Strange memory management when using full-text indexes

Submitted: 19 Apr 2019 18:05 Modified: 30 Apr 2019 14:50
Reporter: Yura Sorokin (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: FULLTEXT search Severity:S3 (Non-critical)
Version:5.6.43, 5.7.25, 8.0.15, 8.0.16 OS:Any
Assigned to: CPU Architecture:Any

[19 Apr 2019 18:05] Yura Sorokin 
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;
[19 Apr 2019 18:07] Yura Sorokin 
MTR test case

Attachment: a001.test (application/octet-stream, text), 2.38 KiB.
[30 Apr 2019 14:50] MySQL Verification Team 
Hello Yura Sorokin,

Thank you for the report and test case.

Thanks,
Umesh
[30 Apr 2019 14:52] MySQL Verification Team 
8.0.16 - Debian9 with 1 CPU and 12GB Memory, 12 CPU and 12GB Memory  results

Attachment: 95065_8.0.16_Debian9.results (application/octet-stream, text), 451.07 KiB.
[30 Apr 2019 14:54] MySQL Verification Team 
8.0.16 - Ubuntu 19.04 with 1 CPU and 12GB Memory results

Attachment: 95065_8.0.16.Ubuntu.results (application/octet-stream, text), 206.24 KiB.
[2 May 2019 16:42] Frederic Descamps 
Sveta wrote a blog post about this: https://www.percona.com/blog/2019/05/02/mysql-memory-management-memory-allocators-and-oper...

And the jira page is also interesting: https://jira.percona.com/browse/PS-5312

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.055 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.