MySQL memory not released _ MySQL

A MySQL database memory growth exception occurred a few days before MySQL memory was released. The symptoms are as follows:
Database version: Percona MySQL 5.0.67
Data Volume: 670 M +
Memory size: 16 GB
Innodb Buffer Pool Size: 4G
Other session memory allocation is not large, basically within MB, and the number of connections is very small
If the server load is low and Binlog is enabled, the database and program have been migrated, and the problem persists. Run the top command to check that the memory usage of MySQL has been increasing and Swap is used.

Analysis exclusion process:
1. I suspect InnoDB memory usage. I used show engine innodb status to check and found that Free Buffer accounts for a large part. Therefore, I can determine that the memory allocated to the Innodb engine is sufficient.
2. check the number of calls. the number of calls is basically 3 ~ 4, and the memory size of the session allocation differs greatly from the total number of calls occupied by MySQL, so it is not a problem of session
3. program and database software problems. The version is the same as that of the Internet. the problem persists.
4. After MySQL is restarted, the memory is released. Therefore, the problem is still caused by MySQL. according to the Flush Tables practice on the Internet, it is not caused by the large number of opened Tables.

One hour before temporary maintenance, some people found the case online and said that improper Memory table configuration may also cause Memory exceptions. However, Tianlong didn't use the Memory engine, so he didn't care too much, however, if you do not know what to do, you can check tables other than Innodb in the database, mainly in the mysql database and information_schema database:
+ ------- + ------------- + --- +
| TABLE_SCHEMA | TABLE_NAME | ENGINE |
+ ------- + ------------- + --- +
| Information_schema | CHARACTER_SETS | MEMORY |
| Information_schema | CLIENT_STATISTICS | MEMORY |
| Information_schema | COLLATIONS | MEMORY |
| Information_schema | COLLATION_CHARACTER_SET_APPLICABILITY | MEMORY |
| Information_schema | COLUMNS | MyISAM |
......
| Mysql | help_topic | MyISAM |
| Mysql | time_zone | MyISAM |
| Mysql | time_zone_leap_second | MyISAM |
| Mysql | time_zone_name | MyISAM |
| Mysql | time_zone_transition | MyISAM |
| Mysql | time_zone_transition_type | MyISAM |
| Mysql | user | MyISAM |
+ ------- + ------------- + --- +
Because there are not many MyISAM tables, and the tables are not big, there is no problem with MEMORY allocation, so I probably went through it again, and the rest is the MEMORY table.
View these tables separately and find that the CLIENT_STATISTICS table is very abnormal because the query speed of the memory table is very slow and an error is reported. a temporary file under/tmp/needs to be repaired, therefore, you can find the problem in the/tmp Directory: the export temporary table is about 7 ~ 8G!
It is basically impossible to view the table data. Therefore, during maintenance, the database is restarted and the data in the table is viewed. it is found that the data growth is very fast and the client connection records are recorded, in particular, the host name is not displayed completely. Therefore, we chose to change the host name to an IP address to locate the problematic server (it was still suspected that the program connection was interrupted, resulting in an increase in the number of records ). After the modification, the problem disappears. This server is different from other servers, and the data volume of the problem table is also normal. Therefore, we locate the problem on the host name. I conducted an experiment:

There are two machines: 245 and 246:
246 is the client and 245 is the server
(Root: 245: Thu Apr 24 16:37:28 2014) [information_schema]> select count (*) from CLIENT_STATISTICS;
+ ---- +
| Count (*) |
+ ---- +
| 2 |
+ ---- +
1 row in set (0.00 sec)
The data in the table is two.

[Dbatlbb @ ~] $ Mysql-h 245-uwuwl_test-p
Mysql: Can't create/write to file '/home/mysql/query. log' (Errcode: 13)
Error logging to file '/home/mysql/query. log'
Logging to file '/home/dbatlbb/mysql/query. log'
Enter password:
Welcome to the MySQL monitor. Commands end with; or/g.
Your MySQL connection id is 556
Server version: 5.0.67-percona-highperf-log Source distribution

Type 'help; 'or'/h' for help. type'/C' to clear the buffer.

(Wuwl_test :$ {HOSTNAME}: Thu Apr 24 16:39:19 2014) [(none)]> show processlist;
+ -- + ---- + ----------- + -- + --- + ------ +
| Id | User | Host | db | Command | Time | State | Info |
+ -- + ---- + ----------- + -- + --- + ------ +
| 556 | wuwl_test | gs_438_friuha_fgesihs_fsd: 50768 | NULL | Query | 0 | NULL | show processlist |
+ -- + ---- + ----------- + -- + --- + ------ +
1 row in set (0.00 sec)
Connect and perform a query operation
(Root: NJ-245: Thu Apr 24 16:38:38 2014) [information_schema]> select count (*) from CLIENT_STATISTICS;
+ ---- +
| Count (*) |
+ ---- +
| 5 |
+ ---- +
1 row in set (0.01 sec)
If we find that the data has increased, we can perform several queries again and find that each query will increase. Is this normal? I modified the host name to perform another query and found that the table data does not grow any more.
Modification method: the host name must be smaller than 16 bytes.
Currently, tests are conducted on versions 5.5 and 5.6. we found that the table data is empty. Therefore, this problem may be caused by a BUG in version 5.0.

Summary:
This problem occurs mainly because the memory usage of MySQL is abnormal and continues to rise. The change of programs and database servers still has no effect, the server data volume is small, and Innodb Buffer is idle.
Cause: because the database's statistical data table information_schema.CLIENT_STATISTICS occupies a large amount of memory, CLIENT_STATISTICS is a memory table.
Solution: because the Host column in The CLIENT_STATISTICS table is 16 bytes, if the Host name is too long, each client operation will add a new data in the table, whether the operation is successful or not. Therefore, you only need to modify the connection host name to no more than 16 bits, so that the memory usage will not be released.
Thinking: if you modify the default settings, you need to test them on a small number of servers and record the modified exceptions in detail to compare the differences between different servers.

Extension:
CLIENT_STATISTICS is used to collect statistics on client connections. Currently, 5.5, 5.6, and Maria have upgraded the client field to 64 bytes, and this statistical function is disabled by default, be sure to remember that the host name cannot exceed 64 bytes!