Details
-
Bug
-
Status: On Hold
-
High
-
Resolution: Unresolved
-
5.6.43-84.3, 5.7.25-28, 8.0.15-5
-
None
-
None
Description
Percona Server does not release memory after executing query that required full-text index.
Tested against 5.7.24-26, this issue is not reproducible with upstream or PS 8. This is reproducible with thread_cache_size=0.
Steps to reproduce:
1. Create a test table.
mysql> CREATE TABLE `tbl` ( `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;
2. Populate it with data:
# for i in $(seq 1 4591); do echo "INSERT INTO test.tbl SET col2='[email protected]${i}', col3='Percona $i', col4='Lorem Ipsum', col5=repeat('Lorem Ipsum $i ', 1000), col6='Lorem Ipsum $i', col7='Ipsum Lorem $i', col8='[email protected]${i}', col9='asdkjsadkjsad;jadkjadlk $i', col10=repeat('Lorem Ipsum $i ', 1000), col11='lorem ipsum', col12='normal', col13='something';"; done | mysql
3. Start monitoring memory usage (pidstat -p pid -r 1 1000, for example)
4. Start select query in a loop:
# for i in $(seq 1 10); do echo $i; mysql -e "select * from test.tbl WHERE MATCH (col2, col3, col8, col6, col7, col5, col4, col13) AGAINST ('\"ipsum\"');" > /dev/null; done;
5. Check current MySQL memory usage
- It starts growing:
... UID PID minflt/s majflt/s VSZ RSS %MEM Command 17:27:41 108 1331 0,00 0,00 1254224 117164 11,48 mysqld 17:27:42 108 1331 8046,99 1,20 1254224 143816 14,10 mysqld 17:27:43 108 1331 20254,76 0,00 1254224 177872 17,43 mysqld 17:27:44 108 1331 25323,81 4,76 1319760 220656 21,63 mysqld 17:27:45 108 1331 21345,24 0,00 1319760 256560 25,14 mysqld
and finally, it reaches a maximum point after it is not released back even if queries from point 3 are finished already:
17:28:55 108 1331 0,00 0,00 1778512 706948 69,29 mysqld 17:28:56 108 1331 0,00 0,00 1778512 706948 69,29 mysqld 17:28:57 108 1331 0,00 0,00 1778512 706948 69,29 mysqld
What's interesting, executing the same amount of 'dummy' queries:
[email protected]:~# mysql -e "select 1;" +---+ | 1 | +---+ | 1 | +---+
caused MySQL to release back its memory:
17:30:13 108 1331 0,00 0,00 1778512 704868 69,08 mysqld 17:30:14 108 1331 34,34 42,42 1712976 631708 61,91 mysqld 17:30:15 108 1331 0,00 0,00 1712976 631708 61,91 mysqld 17:30:16 108 1331 0,00 0,00 1712976 631708 61,91 mysqld 17:30:17 108 1331 0,00 0,00 1712976 631708 61,91 mysqld 17:30:18 108 1331 34,00 8,00 1581904 481176 47,16 mysqld 17:30:19 108 1331 52,53 10,10 1450832 330696 32,41 mysqld 17:30:20 108 1331 19,19 3,03 1385296 255448 25,04 mysqld 17:30:21 108 1331 17,17 2,02 1319760 180168 17,66 mysqld 17:30:22 108 1331 21,00 3,00 1254224 104912 10,28 mysqld 17:30:23 108 1331 14,71 5,88 1254224 104992 10,29 mysqld 17:30:24 108 1331 14,29 6,12 1254224 105068 10,30 mysqld 17:30:25 108 1331 15,84 1,98 1254224 105136 10,30 mysqld 17:30:26 108 1331 19,19 4,04 1254224 105224 10,31 mysqld
