Bug #100114 bluk_insert may lead to memory leak
Submitted: 5 Jul 8:03 Modified: 11 Jul 16:03
Reporter: tandon zhai Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.7.25 OS:CentOS (7.6)
Assigned to: CPU Architecture:Any

[5 Jul 8:03] tandon zhai
Description:
bluk_insert may lead to memery leak

How to repeat:
1.run mysql use /etc/init.d/mysql with /etc/my.cnf
2.run these command in shell: 
source command in mysql should run in 10 concurrently´╝î
then you can see the rss of mysqld3306 become big just before all the source cmd are successful.

echo 'INSERT INTO ztd.ztd VALUES ' >   ztd.sql
num=1
while [[ "$num" -le "9999999" ]]
do
echo '(1,"This is a ztd value"),' >> ztd.sql
let num+=1
done
echo '(1,"This is a ztd value");' >> ztd.sql

#repeat for 10 times
mysql -uhotdb_root -photdb_root -h127.0.0.1 -P3306 -e 'create database if not exists ztd;use ztd;create table  if not exists ztd.ztd (acol bigint, anything varchar(256)) engine=innodb;source ztd.sql;' &

3.[root@co160 ~ 15:42:52]# cat /etc/init.d/mysql 
#!/bin/sh
#
# A simple startup script for mysqld_multi by Tim Smith and Jani Tolonen.
# This script assumes that my.cnf file exists either in /etc/my.cnf or
# /root/.my.cnf and has groups [mysqld_multi] and [mysqldN]. See the
# mysqld_multi documentation for detailed instructions.
#
# This script can be used as /etc/init.d/mysql.server
#
# Comments to support chkconfig on RedHat Linux
# chkconfig: 2345 64 36
# description: A very fast and reliable SQL database engine.
#
# Version 1.0
#

basedir=/usr/local/mysql
bindir=/usr/local/mysql/bin
export PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin
. /etc/profile.d/mysql.sh

if test -x $bindir/mysqld_multi
then
  mysqld_multi="$bindir/mysqld_multi";
else
  echo "Can't execute $bindir/mysqld_multi from dir $basedir";
  exit;
fi

case "$1" in
    'start' )
        numactl --interleave=all "$mysqld_multi" start $2
        ;;
    'stop' )
        "$mysqld_multi" --user=root stop $2
        ;;
    'report' )
        "$mysqld_multi" report $2
        ;;
    'restart' )
        "$mysqld_multi" --user=root stop $2
        numactl --interleave=all "$mysqld_multi" start $2
        ;;
    *)
        echo "Usage: $0 {start|stop|report|restart}" >&2
        ;;
esac

[root@co160 ~ 15:49:05]# cat /etc/my.cnf
#*************************
#Created By:   
#*************************
[mysqld_multi]
mysqld=/usr/local/mysql/bin/mysqld_safe
#mysqladmin=
#user=root
#password=
log=/data/mysql/multi.log

[mysqld_safe]
user=mysql
open-files-limit=65535
malloc-lib=/usr/lib64/libtcmalloc.so.4
[mysql]
no-auto-rehash
prompt='\\u@\\h:\\d \\v \\r:\\m:\\s> '
default-character-set=utf8mb4
show-warnings
#socket=/data/mysql/mysqldata3306/sock/mysql.sock

[mysqldump]
default-character-set=utf8mb4
max-allowed-packet=1G
net-buffer-length=32K

[mysqld]
#***********************************common parameters******************************
basedir=/usr/local/mysql

skip-federated
skip-blackhole
skip-name-resolve
loose-validate-password=off
loose-mysqlx=off

performance-schema=1
event-scheduler=ON
default-storage-engine=InnoDB

lower-case-table-names=1

explicit-defaults-for-timestamp
sql-mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION'

optimizer-switch='index_merge_intersection=off,loosescan=off,batched_key_access=on'
max-seeks-for-key=10000

user=mysql

group-concat-max-len=1048576

back-log=1000
max-connections=8400
max-user-connections=8200
thread-cache-size=128
max-connect-errors=99999
wait-timeout=172800
interactive-timeout=172800
net-read-timeout=30
net-write-timeout=60
net-buffer-length=32K
max-allowed-packet=64M

max-heap-table-size=1G
tmp-table-size=2M

sort-buffer-size=8M
max-length-for-sort-data=16k
join-buffer-size=4M   #bka
read-rnd-buffer-size=8M  #mrr
open-files-limit=65535
table-open-cache=10240
table-open-cache-instances=16

loose-query-cache-type=0
loose-query-cache-size=0
#query-cache-limit=1M

#lock-wait-timeout=300

#*******************************  Logs related settings ***************************
#general-log
loose-log-warnings=2
loose-log-error-verbosity=3
loose-log-timestamps=system
long-query-time=0.3
slow-query-log
#log-queries-not-using-indexes
log-slow-slave-statements
log-slow-admin-statements

binlog-cache-size=256K
max-binlog-size=512M

binlog-format=MIXED
loose-binlog-rows-query-log-events
#binlog-checksum=CRC32
expire-logs-days=10

#*******************************  Replication related settings **********************

#skip-slave-start
#slave-skip-errors=
slave-type-conversions='ALL_NON_LOSSY'
loose-rpl-semi-sync-master-enabled=1
loose-rpl-semi-sync-slave-enabled=1
loose-rpl-semi-sync-master-wait-for-slave-count=1
loose-rpl-semi-sync-master-wait-no-slave=1
loose-rpl-semi-sync-master-timeout=10000

master-info-repository=TABLE
relay-log-info-repository=TABLE
#slave-parallel-workers=4
#loose-slave-parallel-type=LOGICAL_CLOCK
#loose-binlog-group-commit-sync-delay=1   #5.7
#loose-binlog-group-commit-sync-no-delay-count=10   #5.7
#loose-binlog-max-flush-queue-time=1  #5.6
log-slave-updates=1
log-bin-trust-function-creators=1
auto-increment-increment=1
auto-increment-offset=1
allow-suspicious-udfs
loose-innodb-support-xa=1
sysdate-is-now

#******************************* MyISAM Specific options ****************************
key-buffer-size=8M
bulk-insert-buffer-size=16M
myisam-sort-buffer-size=64M
myisam-max-sort-file-size=10G
myisam-repair-threads=1
myisam-recover-options=default
read-buffer-size=1M

#***************************** INNODB Specific options ****************************
loose-innodb-use-sys-malloc=1

innodb-buffer-pool-instances=8
innodb-max-dirty-pages-pct=40
innodb-sort-buffer-size=16M
#large-pages

innodb-autoextend-increment=128
innodb-file-per-table
innodb-open-files=7168
loose-innodb-file-format=Barracuda
loose-innodb-file-format-check=1
#innodb-checksum-algorithm=CRC32

innodb-fast-shutdown=1

innodb-log-buffer-size=64M
innodb-log-files-in-group=2
innodb-adaptive-flushing-lwm=30

innodb-read-io-threads=16
innodb-write-io-threads=8
innodb-flush-method=O_DIRECT
innodb-flush-neighbors=2
innodb-lru-scan-depth=1024
innodb-change-buffering=all
innodb-purge-threads=1

innodb-commit-concurrency=0
innodb-thread-concurrency=64
innodb-concurrency-tickets=1024
innodb-autoinc-lock-mode=1

innodb-stats-on-metadata=0
innodb-lock-wait-timeout=300
innodb-rollback-on-timeout=1
innodb-print-all-deadlocks=1

[mysqld3306]
pid-file=/data/mysql/mysqldata3306/sock/mysql.pid
port=3306
socket=/data/mysql/mysqldata3306/sock/mysql.sock
datadir=/data/mysql/mysqldata3306/mydata
tmpdir=/data/mysql/mysqldata3306/tmpdir
slave-load-tmpdir=/data/mysql/mysqldata3306/tmpdir

general-log-file=/data/mysql/mysqldata3306/log/general.log
log-error=/data/mysql/mysqldata3306/log/error.log
slow-query-log-file=/data/mysql/mysqldata3306/log/slow-query.log

server-id=01603306
log-bin-index=/data/mysql/mysqldata3306/binlog/mysql-bin.index
log-bin=/data/mysql/mysqldata3306/binlog/mysql-bin
sync-binlog=10

relay-log-index=/data/mysql/mysqldata3306/relaylog/mysql-relay-bin.index
relay-log=/data/mysql/mysqldata3306/relaylog/mysql-relay-bin

character-set-server=utf8mb4
collation-server=utf8mb4_general_ci

innodb-buffer-pool-size=128M
innodb-data-home-dir=/data/mysql/mysqldata3306/innodb_ts
innodb-data-file-path=ibdata1:128M:autoextend
innodb-log-group-home-dir=/data/mysql/mysqldata3306/innodb_log
innodb-log-file-size=128M
innodb-flush-log-at-trx-commit=2
innodb-io-capacity=100

innodb-force-recovery=0

gtid-mode=ON
enforce-gtid-consistency
[6 Jul 15:23] MySQL Verification Team
Hi Mr. zhai,

Thank you for your bug report.

We can not verify it, based  on the info that you have provided us.

I could not reproduce it, bug it might be because I do not use GNU malloc. What you describe is a typical behaviour of GNU malloc, or of the further usage of the InnoDB buffer pool. In first case, OS is keeping memory with the process and in the second one, memory is actually allocated only when it is accessed.

To prove memory leak you should use ASAN or some other professional tool.
[7 Jul 12:47] tandon zhai
Hi, did you use the /etc/my.cnf and /etc/init.d/mysql i provide previously?

I think the malloc lib doesn't matter, i tryed the je and tc, but the result both shown that the rss become 5.6G and the buffer pool was only 128M.

and i will try ASAN to see if there is any memory problem.

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND                                                                                                                  
22426 mysql     20   0 6237504   5.6g  12204 S   0.3 35.5   1:29.86 mysqld

[mysqld_safe]
malloc-lib=/usr/lib64/libjemalloc.so.1
malloc-lib=/usr/lib64/libtcmalloc.so.4

[root@co160 ~ 20:25:13]# lsof -p 22426 | grep malloc
mysqld  22426 mysql  mem    REG              253,0     301136   4197394 /usr/lib64/libtcmalloc.so.4.4.5

echo 'INSERT INTO ztd.ztd VALUES ' >   ztd.sql
num=1
while [[ "$num" -le "999999" ]]
do
echo '(1,"This is a ztd value"),' >> ztd.sql
let num+=1
done
echo '(1,"This is a ztd value");' >> ztd.sql
[root@co160 ~ 20:40:22]# wc -l ztd.sql 
1000001 ztd.sql
[7 Jul 13:09] MySQL Verification Team
Hi,

Yes, I did ..... only changes that I made were to some paths ....
[7 Jul 13:10] MySQL Verification Team
Also, please, obligatory use our latest available release for either 5.7 or 8.0.
[7 Jul 13:13] MySQL Verification Team
5.7.30 memory heap showing the functions who consume memory.

Attachment: mybin.hprof.2031.heap.pdf (application/pdf, text), 13.77 KiB.

[7 Jul 13:16] MySQL Verification Team
I repeated a 6G memory usage on 5.7.30. Ran under profiler.
Simple solution is to restrict the parser's memory (unlimited by default!)

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_parser_max_mem...

Then you get:

ERROR 3170 (HY000) at line 1 in file: 'ztd.sql': Memory capacity of 104857600 bytes for 'parser_max_mem_size' exceeded. Parser bailed out for this query.
[11 Jul 16:03] tandon zhai
sorry, i made a mistake.

actually, 
for 5.7.30,
if i use /usr/lib64/libjemalloc.so.1, the rss will decrease after the session disconnection´╝î
and if i use /usr/lib64/libtcmalloc.so.4 or /usr/lib64/libc-2.17.so, the rss will still be quite big.

for 8.0.20,
the malloc lib doesn't matter. the rss decrease to normal standard after disconnection

so, just as what you said, we can use jemalloc to avoid big rss kept by mysqld.
thank you for you time.
[12 Jul 7:14] MySQL Verification Team
This particular report does not represent a bug.
I did anyway create an internal feature request to prevent the situation happening:
Enh 31590648 - MAKE A SMALLER DEFAULT VALUE FOR PARSER_MAX_MEM_SIZE
[13 Jul 12:47] MySQL Verification Team
Mr. zhai,

We are glad that our conclusions were correct.