MySQL Bugs: #69391: MySQL Server consuming excesive swap space

Bug #69391	MySQL Server consuming excesive swap space
Submitted:	3 Jun 2013 18:45	Modified:	4 Jun 2013 14:21
Reporter:	Van Stokes	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.6.11-community	OS:	Linux (Ubuntu 12.04 x86_64)
Assigned to:		CPU Architecture:	Any
Tags:	Memory, server, swap

Description:
MySQL 5.6.11 consumes RAM and never releases it.MySQL crashes when all SWAP is gone (expected).

Server Configuration:
Virtual Machine under VMware
Dedicated MySQL server (no other processes run - other than OS processes).
RAM: 8GB
SWAP: 8GB
CPUs: 2
OS: Ubuntu 12.04.2 LTS amd64

$ uname -a
Linux atl-mysql01 3.5.0-32-generic #53~precise1-Ubuntu SMP Wed May 29 20:33:37 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux

# Fresh startup of mysql
# (note swap)
$ top -u mysql

top - 13:32:14 up 3 days,  5:15,  2 users,  load average: 0.19, 1.03, 1.12
Tasks: 101 total,   1 running, 100 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
Mem:   8178236k total,  8003888k used,   174348k free,     3136k buffers
Swap:  8386556k total,  2719760k used,  5666796k free,    38956k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND            
27320 mysql     20   0 17.0g 7.5g 7836 S    0 95.6   0:26.39 mysqld             

############################
# Start importing
# (note swap io)
$ vmstat 2

procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa
 1  2 2804536 124220   1672  25700    1  126    65   274   54   51  1  0 98  0
 2  1 2805052 106912    568  31684   16  274   156 29496 2963 1935 57  4 12 27
 0  0 2805804 119044    432  27484    0  376    28 14248 1712 1811 63  2 15 20
 1  1 2806112 109364    396  30036    0  154    34 16320 1718 1128 60  3 20 18
 2  1 2806788 104312    308  25300    0  338    20 14238 1371 1190 58  1 21 20
 1  2 2810316 103752    772  29644   16 1780   164 22052 1738 1277 54  2 22 21
 2  1 2811620 116728   1152  27540   32  684   288 22280 1635 1563 45  3 31 22
 0  2 2822844 119976    692  28448   80 5708   602 20268 2996 1745 55  5 13 27
 1  1 2832236 103768    972  26172   96 4814   688 17378 3383 1943 48  6 29 18
 2  2 2842824 106112    380  24760    0 5294    18 20340 3266 1194 55  6 15 24
 2  0 2905216 122096    384  29068    0 31196    72 40806 6728 1034 48  9 30 14
 2  1 2905216 103748    612  39228    0    0   118 18612 1657 1320 57  4 24 15
 2  2 2913576 109400    756  28320    0 4180    24 22732 3411 1478 56  6 19 19
 3  1 2927040 108256    804  31176  112 6834   586 24198 3167 1478 60  5 10 26
 1  1 2950684 122532    804  28380  192 12002   318 27978 5306 1550 58  6 12 24
 1  1 2950316 118064    660  28020  176    0   274 17326 1981 1564 59  5 10 27
 1  4 2949836 108612   1648  27824  260    0  1374 23148 2565 2038 39  4 18 40
 0  1 3004572 108688   1928  27952  144 27518   220 45754 7939 1362 50 10 14 27
 2  1 3004224 103960    980  29976  160    0   556 14046 1995 1215 63  3 11 23
 1  2 3003856 104664    864  27440  192    0   280 14040 1746 1264 66  3 12 20
 3  2 3003472 103852   1264  26720  272    0  2838 17066 2077 1712 65  3 10 22

###########################################
# After running about an hour
# (note swap)
#
$ top -u mysql

top - 14:24:05 up 3 days,  6:07,  2 users,  load average: 3.17, 3.12, 2.69
Tasks: 101 total,   1 running, 100 sleeping,   0 stopped,   0 zombie
Cpu(s): 18.2%us, 12.6%sy,  0.0%ni, 18.5%id, 49.2%wa,  0.2%hi,  1.2%si,  0.0%st
Mem:   8178236k total,  7935756k used,   242480k free,     1008k buffers
Swap:  8386556k total,  8386556k used,        0k free,   113896k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND            
27320 mysql     20   0 17.3g 7.3g 4152 S   41 93.7  52:58.96 mysqld   

How to repeat:
1. Dump large databases from another server (100GB each)
2. Start MySQL fresh on target server.
3. Begin three parallel imports of step 1 dumps to target MySQL server
4. Watch swap

Mix of ISAM and InnoDB databases.

Suggested fix:
Check to ensure

1) My configuration is correct.
2) That application is release RAM (doesn't appear to be).

Oh, BTW, this is repeatable.

We don't seem to have this issue with 5.5.x server (we are running 5.5.31-ubuntu).

Curious what "SHOW ENGINE PERFORMANCE_SCHEMA STATUS" shows as the memory consumption with the high table_open_cache you have?

SHOW ENGINE PERFORMANCE_SCHEMA STATUS

Attachment: 201306031537-show_engine_performance_schema_status.csv (text/csv), 9.92 KiB.

$ top -u mysql 

top - 15:39:43 up 3 days,  7:22,  1 user,  load average: 0.00, 0.01, 0.12
Tasks:  96 total,   1 running,  95 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
Mem:   8178236k total,  7963296k used,   214940k free,     4760k buffers
Swap:  8386556k total,  2772732k used,  5613824k free,    40776k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  SWAP COMMAND       
30707 mysql     20   0 17.0g 7.4g 7972 S    0 94.9   0:27.85 9.6g mysqld        

see attached file for the SHOW ENGINE PERFORMANCE_SCHEMA STATUS

P_S is using 10GB of ram due to the large table cache specific.

Please, read about providing sizing data for performance_schema.
http://marcalff.blogspot.com/2013/04/on-configuring-performance-schema.html

Or if you don't care much to use performance_schema, simply set in my.cnf:
performance_schema=0

tables-open-cache = 512K 

is a remnant setting from our 5.5 servers.

I reviewed the article you provided and I also found this article
http://dimitrik.free.fr/blog/archives/2012/09/mysql-performance-table-open-cache-in-56.htm...

I modified the my.cnf to be:

performance-schema = off
table-open-cache = 2K
table-open-cache-instances = 2

That did it. Thank you.