Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-5312

Strange memory management when using full-text indexes

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: On Hold
    • Priority: High
    • Resolution: Unresolved
    • Affects Version/s: 5.6.43-84.3, 5.7.25-28, 8.0.15-5
    • Fix Version/s: None
    • Component/s: None
    • Labels:

      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='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';"; 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:

      root@test:~# 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

        Smart Checklist

          Attachments

          1. a000.test
            2 kB
          2. a001.test
            2 kB

            Activity

              People

              Assignee:
              yura.sorokin Yura Sorokin
              Reporter:
              iwo.panowicz Iwo Panowicz
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - Not Specified
                  Not Specified
                  Logged:
                  Time Spent - 1 week, 1 day, 5 hours, 30 minutes
                  1w 1d 5h 30m