mysql 5.6内存参数调优

mysql 5.6默认启动占用内存400多M,如果是vps等,明显偏高。
修改performance_schema_max_table_instances,table_definition_cache,table_open_cache这个三个参数,调低值能明显减小。
默认值如下:
performance_schema_max_table_instances 12500
table_definition_cache 1400
table_open_cache 2000
参数含义:
performance_schema_max_table_instances :The maximum number of instrumented table objects.
table_definition_cache:The number of table definitions (from .frm files) that can be stored in the definition cache
table_open_cache:The number of open tables for all threads.
调低如下:
performance_schema_max_table_instances = 200
table_definition_cache = 100
table_open_cache = 100

可降低到60多M左右。
进一步调整参数:
innodb_buffer_pool_size,innodb_additional_mem_pool_size,innodb_log_buffer_size这个三个参数可以调小。
再进一步调整: mysql 5.6默认启用performance_schema,占用很多内存,可以禁用。

完整配置文件如下,内存占用到22M:
cat /etc/my.cnf “`

[mysqld]
port = 3306
server_id = 1
socket = /tmp/mysql.sock

performance_schema = OFF

innodb_buffer_pool_size = 8M
innodb_additional_mem_pool_size = 1M
innodb_log_buffer_size = 1M

key_buffer_size = 0
query_cache_size = 0

log_bin = mysql-bin
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES “`