| Bug #100114 | bluk_insert may lead to memory leak | ||
|---|---|---|---|
| Submitted: | 5 Jul 2020 8:03 | Modified: | 11 Jul 2020 16:03 |
| Reporter: | tandon zhai | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.7.25 | OS: | CentOS (7.6) |
| Assigned to: | CPU Architecture: | Any | |
[6 Jul 2020 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 2020 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 2020 13:09]
MySQL Verification Team
Hi, Yes, I did ..... only changes that I made were to some paths ....
[7 Jul 2020 13:10]
MySQL Verification Team
Also, please, obligatory use our latest available release for either 5.7 or 8.0.
[7 Jul 2020 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 2020 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 2020 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 2020 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 2020 12:47]
MySQL Verification Team
Mr. zhai, We are glad that our conclusions were correct.
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