2

i'm using 5.6.21-70.0 for performance test.

when i run

mysqlslap -a --concurrency=40 --number-of-queries 1000 --iterations=500 --engine=innodb --debug-info -utest -p

do some performance test and the ram growth up over the maximum memory usage and never release

when finish mysqlslap the memory show use 78%

i have a 1G physical memory and and i do NOT use swap

KiB Mem: 1016656 total, 953808 used, 62848 free, 30324 buffers

KiB Swap: 0 total, 0 used, 0 free. 41384 cached Mem

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
26049 mysql 20 0 1544156 778316 3992 S 16.3 76.6 6:24.01 mysqld

**it seems like mysqld use about 700M memory? why it over the max memory usage? and why mysql never release the memory? **

my mysqlturning.pl shows:

[OK] Highest usage of available connections: 70% (42/60)

[OK] Key buffer size / total MyISAM indexes: 32.0M/98.0K

[--] Reads / Writes: 40% / 60%

[--] Total buffers: 336.0M global + 1.1M per thread (60 max threads)

[OK] Maximum possible memory usage: 403.5M (40% of installed RAM)

----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 274726912; in additional pool allocated 0
Total memory allocated by read views 96
Internal hash tables (constant factor + variable factor)
    Adaptive hash index 4446416     (4425832 + 20584)
    Page hash           277432 (buffer pool 0 only)
    Dictionary cache    1170261     (1107952 + 62309)
    File system         815920  (812272 + 3648)
    Lock system         665656  (664936 + 720)
    Recovery system     0   (0 + 0)
Dictionary memory allocated 62309
Buffer pool size        16383
Buffer pool size, bytes 268419072
Free buffers            1024
Database pages          15358
Old database pages      5649
Modified db pages       0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 407, not young 484
0.00 youngs/s, 0.00 non-youngs/s
Pages read 614, created 17947, written 20737
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 15358, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

my my.cnf----

 # Generated by Percona Configuration Wizard (http://tools.percona.com/) version REL5-20120208

 [mysql]

 # CLIENT #
 port                           = 3306
 socket                         = /data/data/mysql.sock

 [mysqld]

 # GENERAL #
 user                           = mysql
 default-storage-engine         = InnoDB
 socket                         = /data/data/mysql.sock
 pid-file                       = /data/data/mysql.pid
 character-set-server=utf8
 collation-server=utf8_general_ci

 # MyISAM #
 key-buffer-size                = 32M
 myisam-recover                 = FORCE,BACKUP

 # SAFETY #
 max-allowed-packet             = 16M
 max-connect-errors             = 1000000
 skip-name-resolve
 sql-mode                       = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
 sysdate-is-now                 = 1
 innodb                         = FORCE

 # DATA STORAGE #
 datadir                        = /data/data/

 # BINARY LOGGING #
 log-bin                        = /data/data/mysql-bin
 expire-logs-days               = 14
 sync-binlog                    = 1

 # CACHES AND LIMITS #
 tmp-table-size                 = 32M
 max-heap-table-size            = 32M
 query-cache-type               = 0
 query-cache-size               = 0
 max-connections                = 60
 thread-cache-size              = 50
 open-files-limit               = 65535
 table-definition-cache         = 1024
 table-open-cache               = 2048

 # INNODB #
 innodb-flush-method            = O_DIRECT
 innodb-log-files-in-group      = 2
 innodb-log-file-size           = 64M
 innodb-flush-log-at-trx-commit = 1
 innodb-file-per-table          = 1
 innodb-buffer-pool-size        = 64M

 # LOGGING #
 log-error                      = /data/data/mysql-error.log
 log-queries-not-using-indexes  = 0 # slow will not log the query which do not use index
 long-query-time                = 1
 slow-query-log                 = 1
 slow-query-log-file            = /data/data/mysql-slow.log
  • 1
    Try setting thread_cache_size to 0 or at least a much smaller value, such as 8. There is no such thing as a maximum memory usage in MySQL. The server daemon will use as much memory as the workload requires. The mysqltuner script uses an old formula that is essentially meaningless unless it shows an extremely large number, which implies that you've set one or more parameters to an unreasonable value. – Michael - sqlbot Jan 10 '15 at 22:06
  • i've tried your suggestion, and it still keep ram increasing and never release. so i have a problem. if i have some other application, like web server or memcached.Does the mysqld will keep the ram that other application willnot can have the memory or mysql OOM by kernel if i run mysql for 1 month. or i need increase the physical ram size or use swap like 1G? – Henry Jan 10 '15 at 23:58
  • Either your server is not using the actual my.cnf file that you think it is, or your post represents at least two different states of the server's operating environment over time. innodb-buffer-pool-size = 64M does not match Buffer pool size, bytes 268419072 ... it's off by a factor of about 4, and I see no explanation for this. Any ideas? You should perhaps SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size'; (or thread_cache_size, or anything else you change) and be certain the changes you believe you are making... are actually happening. – Michael - sqlbot Jan 11 '15 at 0:59
  • i have found out why it happened.. please see my answer below..actually it's performance_schema memory usage. thanks for you post! @Michael - sqlbot – Henry Jan 11 '15 at 6:24
2

i have found out why memory leak happeneds. now i have only 1GB memory and no swap.

the performance_schema cause the allocate the memory for about 400M at startup. and innodb will take up to 400M memory usage

so,because of my.cnf use

table-definition-cache= 1024
table-open-cache= 2048 
max-connections= 60

it will cause high performance_schema allocated, more information pls see

ref1

ref2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.