Bug #106089 memory summary for event `memory/sql/String::value` is incorrect
Submitted: 7 Jan 2022 2:27 Modified: 19 Feb 2022 6:47
Reporter: Brian Yue (OCA) Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Performance Schema Severity:S3 (Non-critical)
Version:8.0.25 OS:Any (rhel-7.4)
Assigned to: CPU Architecture:Any (x86-64)

[7 Jan 2022 2:27] Brian Yue 
Description:
Hello,
  Recently we test a dml sql repeatly, and find that the memory summary is increased to a high value which is event bigger than total virtual memory of mysqld process. Like this:

```
mysql>
mysql> select EVENT_NAME, CURRENT_NUMBER_OF_BYTES_USED USED_BYTES, CURRENT_NUMBER_OF_BYTES_USED/1024/1024/1024 USED_GB from memory_summary_global_by_event_name where EVENT_NAME = 'memory/sql/String::value';
+--------------------------+------------+----------------+
| EVENT_NAME               | USED_BYTES | USED_GB        |
+--------------------------+------------+----------------+
| memory/sql/String::value | 2983640400 | 2.778731659055 |
+--------------------------+------------+----------------+
1 row in set (0.00 sec)

mysql> ^DBye
[yxxdb1@rhel7 etc]$
[yxxdb1@rhel7 etc]$
[yxxdb1@rhel7 etc]$ ps x | grep mysqld
18545 pts/2    Sl    12:18 /home/yxxdb1/bin/mysqld --defaults-file=/home/yxxdb1/etc/my.cnf --datadir=/home/data_pcie/yxx3_data/data --pid-file=/home/yxxdb1/bin/mysqld1.pid
19886 pts/2    S+     0:00 grep --color=auto mysqld
[yxxdb1@rhel7 etc]$ top -p 18545
top - 10:32:11 up 34 days, 20:11,  7 users,  load average: 0.01, 0.25, 0.49
Tasks:   1 total,   0 running,   1 sleeping,   0 stopped,   0 zombie
%Cpu(s):  0.0 us,  0.0 sy,  0.0 ni,100.0 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
KiB Mem : 19749246+total, 16067840+free, 13386652 used, 23427412 buff/cache
KiB Swap:  4194300 total,   314440 free,  3879860 used. 18303046+avail Mem

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
18545 yxxdb1    20   0 2506324 1.736g  23644 S   0.0  0.9  12:18.88 mysqld
```

  So I guess there is something wrong for the pfs static for event `memory/sql/String::value`.

  

How to repeat:
(1) Initialize the database and table on a MySQL8.0.25 instance unpon redhat7.4 system.
create database zx;
use zx;
CREATE TABLE `PORTFOLIO_TECH` (
  `ID` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT 'id',
  `RISK_CODE` varchar(10) COLLATE utf8_bin DEFAULT NULL,
  `ASSET_TYPE` varchar(2) COLLATE utf8_bin DEFAULT NULL,
  `STAT_CODE` varchar(10) COLLATE utf8_bin DEFAULT NULL,
  `VALUE` decimal(24,6) DEFAULT NULL,
  `TENANT_CODE` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  KEY `IDX_PORTFOLIO_TECH_ID_STAT` (`ID`,`STAT_CODE`,`TENANT_CODE`),
  KEY `index_portfolio_tech` (`ASSET_TYPE`,`ID`,`TENANT_CODE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

(2) copy the following content to a file named "zx_memory_test.sql":

```
INSERT INTO PORTFOLIO_TECH(
        ID,
        RISK_CODE,
        ASSET_TYPE,
        STAT_CODE,
        VALUE,
        TENANT_CODE
        )
                    SELECT '272010238258204672',
                    'R3','BM','MGR2Y',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','STD',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','DDR3Y',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','AAVR5Y',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','STN',-0.9874208829065747,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','DDR3M',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','AAVR6M',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','DDRCY',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','DDRCW',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','DDRCQ',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','MGR1Y',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','MGR3Y',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','MGR3M',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','MGRCY',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','MGRCW',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','PCT1D',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','MGRCQ',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','PCT1M',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','MGRCM',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','ASTD',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','PCT1W',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','DDR1Y',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','DDR1W',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','ASTD1Y',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','ASTD1W',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','PCT1Y',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','DDR1M',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','MGR1W',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','DDRCM',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','DDR2Y',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','PCTCQ',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','MGR1M',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','PCTCM',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','PCT2Y',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','PCTCY',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','PCTCW',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','PCT3M',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','ASTD1M',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','AAVR1Y',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','PCTLM',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','AAVR1W',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','PCTLY',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','PCTLW',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','MGR6M',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','STD6M',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','STD5Y',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','AAVR1M',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','PCT',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','AAVR',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','AAVR3Y',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','AAVRCM',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','MGR5Y',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','AAVR3M',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','AAVRCY',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','AAVRCW',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','APCT',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','STDCY',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','STD3M',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','STDCW',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','STD2Y',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','STDCM',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','STN6M',-0.9874208829065747,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','STN5Y',-0.9874208829065747,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','STD1W',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','STD1Y',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','DDRA30R',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','STD3Y',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','PCT3Y',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','ASTD3Y',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','STN2Y',-0.9874208829065747,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','STNCM',-0.7071067811865475,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','ASTD2Y',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','STN1W',-0.8660254037844386,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','STN1Y',-0.9874208829065747,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','ASTDCM',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','STN1M',-0.9770084209183942,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','ASTDCW',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','ASTDCY',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','ASTD3M',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','PCT5Y',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','ASTD5Y',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','STD1M',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','DDR5Y',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','DDR',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','ASTD6M',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','PCT6M',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','STN3Y',-0.9874208829065747,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','DDR6M',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','MGR',0.0,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','STNCW',-0.7071067811865475,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','STNCY',-0.7071067811865475,'CITICB'
                    FROM dual
                 UNION ALL 
                    SELECT '272010238258204672',
                    'R3','BM','STN3M',-0.9874208829065747,'CITICB'
                    FROM dual;
```

(3) copy the following content to a file named "zx_memory_test.sh" (user and password to connect MySQL8.0.25 should be replaced):

```
#!/bin/bash
i=1
sum=0
while [ $i -le 10000000 ]
do
  let sum=sum+$i
  let i++
  mysql -uroot -p'xxxxx' -e"source ~/dbtest/zx_memory_test.sql"

done
```

(4) run the zx_memory_test.sh script:
date && sh zx_memory_test.sh > /dev/null 2>&1 && date

(5) While running the script, we watch the memory summary of `memory/sql/String::value` in memory_summary_global_by_event_name pfs table, and watch the VIRTUAL memory occupation, and we can find that memory summary of `memory/sql/String::value` is always increasing.
[7 Jan 2022 2:37] Brian Yue 
By the way, in order not to be interfered by bufferpool, the innodb_buffer_pool_size is suggested to be configured as small as possible. For instance, it's configured as 600MB in my MySQL instance.
[7 Jan 2022 3:21] Brian Yue 
After running the script for over 1 hour:

mysql> select EVENT_NAME, CURRENT_NUMBER_OF_BYTES_USED USED_BYTES, CURRENT_NUMBER_OF_BYTES_USED/1024/1024/1024 USED_GB from memory_summary_global_by_event_name where EVENT_NAME = 'memory/sql/String::value';
+--------------------------+------------+----------------+
| EVENT_NAME               | USED_BYTES | USED_GB        |
+--------------------------+------------+----------------+
| memory/sql/String::value | 7515421680 | 6.999281868339 |
+--------------------------+------------+----------------+
1 row in set (0.00 sec)

mysql> ^DBye
[yxxdb1@rhel7 etc]$
[yxxdb1@rhel7 etc]$ ps x | grep mysqld
18545 pts/2    Sl    30:49 /home/yxxdb1/bin/mysqld --defaults-file=/home/yxxdb1/etc/my.cnf --datadir=/home/data_pcie/yxx3_data/data --pid-file=/home/yxxdb1/bin/mysqld1.pid
33537 pts/2    S+     0:00 grep --color=auto mysqld
[yxxdb1@rhel7 etc]$ top -p 18545
top - 11:25:12 up 34 days, 21:04,  8 users,  load average: 1.05, 1.11, 1.06
Tasks:   1 total,   0 running,   1 sleeping,   0 stopped,   0 zombie
%Cpu(s):  1.7 us,  0.7 sy,  0.0 ni, 97.7 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
KiB Mem : 19749246+total, 16065926+free, 13405684 used, 23427504 buff/cache
KiB Swap:  4194300 total,   314440 free,  3879860 used. 18301152+avail Mem

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
18545 yxxdb1    20   0 2508420 1.744g  23644 S  33.3  0.9  30:50.89 mysqld
[19 Jan 2022 6:47] MySQL Verification Team 
Hello Brian Yue,

Thank you for the report and feedback.
I tried reproduce using provided steps on 8.0.27(with default setting) but not seeing any issues. Could you please confirm if the issue is reproducible on latest GA version 8.0.28? If you are able to reproduce then please report us back with the details along with exact configuration file(my.cnf) in use so that we can reproduce at our end. Thank you.

regards,
Umesh
[20 Feb 2022 1:00] Bugs System 
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".