mysql占用内存高的一种解决方法

top命令看到，5000和6000 mysql实例 分别占用内存54G：

75361 mysql     20   0  239.5g  54.6g  11120 S 365.6 21.7 307944:06 /usr/local/mysql/bin/mysqld --server-id=5000 --user=mysql --port=5000 
62045 mysql     20   0  253.0g  54.2g  11076 S 183.0 21.6  82442:08 /usr/local/mysql/bin/mysqld --server-id=6000 --user=mysql --port=6000

查看到是sql_acl_memex（22G）、sql_acl_mem（9.6G）、HP_PTRS（6.4G）引起：

 mysql> select event_name,current_alloc  from sys.memory_global_by_current_bytes limit 10 ;
+-------------------------------------------------------------+---------------+
| event_name                                                  | current_alloc |
+-------------------------------------------------------------+---------------+
| memory/sql/sql_acl_memex                                    | 22.21 GiB     |
| memory/innodb/buf_buf_pool                                  | 16.38 GiB     |
| memory/sql/sql_acl_mem                                      | 9.64 GiB      |
| memory/memory/HP_PTRS                                       | 6.49 GiB      |
| memory/innodb/hash0hash                                     | 493.92 MiB    |
| memory/innodb/os0event                                      | 413.78 MiB    |
| memory/mysys/IO_CACHE                                       | 212.96 MiB    |
| memory/innodb/log0log                                       | 128.01 MiB    |
| memory/performance_schema/table_handles                     | 81.56 MiB     |
| memory/performance_schema/events_statements_current.sqltext | 70.00 MiB     |
+-------------------------------------------------------------+---------------+
10 rows in set (0.02 sec)

查看setup_instruments表，发现memory监控开启着呢：

mysql> select * from performance_schema.setup_instruments  where name like '%acl%';
+--------------------------+---------+-------+
| NAME                     | ENABLED | TIMED |
+--------------------------+---------+-------+
| memory/sql/sql_acl_mem   | YES     | NO    |
| memory/sql/sql_acl_memex | YES     | NO    |
| memory/sql/acl_cache     | YES     | NO    |
+--------------------------+---------+-------+
3 rows in set (0.00 sec)

再看一下配置文件，确实将memory监控设置成了开机启动，这个监控比较占用物理内存，建议关闭。默认就是关闭的

grep -A 10  performance-schema  /etc/mysql/5000.cnf 
performance-schema-instrument='memory/%=COUNTED'
performance_schema_digests_size = 40000
performance_schema_max_table_instances = 40000
performance_schema_max_sql_text_length = 4096

也可以用命令动态关闭内存监控：

update performance_schema.setup_instruments set ENABLED='NO' where name like 'memory/%';
select *from performance_schema.setup_instruments  where name like 'memory/%';