mysql leading_MYSQL优化参数减少内存占用

最近安装了AliSQL，跑起来感觉没有怎么。但是top查看内存占用的时候，MySQL占用高达800M，但是服务器的配置是1H2G不经操，一旦高并发测试数据库直接占满内存就挂了需要手动重启。经过一番了解后，数据库配置文件默认是没有写的，需要自己去修改my.ini，可能习惯了宝塔环境一件自动优化，切到命令行感觉有点陌生了。

在宝塔环境可以了解到相关的参数，直接套用。performance_schema_max_table_instances = 200

table_definition_cache = 100

table_open_cache = 100

这三个参数调小后，可以感觉到内存明显降下来。

还可以关闭 performance_schema 数据库性能数据采集，调小innobd缓存池 innodb_buffer_pool_size=4M 还可以进一步降低内存占用。我当前AliSQL使用的配置，可以大幅度降低内存到100M# For advice on how to change settings please see

# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]

#

# Remove leading # and set to the amount of RAM for the most important data

# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

# innodb_buffer_pool_size = 128M

#

# Remove leading # to turn on a very important data integrity option: logging

# changes to the binary log between backups.

# log_bin

#

# Remove leading # to set options mainly useful for reporting servers.

# The server defaults are faster for transactions and fast SELECTs.

# Adjust sizes as needed, experiment to find the optimal values.

# join_buffer_size = 128M

# sort_buffer_size = 2M

# read_rnd_buffer_size = 2M

#

# 数据库数据存放目录

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

#

#symbolic-links=0

skip-external-locking

key_buffer_size = 8M

max_allowed_packet = 1M

table_open_cache = 4

sort_buffer_size = 64K

read_buffer_size = 256K

read_rnd_buffer_size = 256K

net_buffer_length = 2K

thread_stack = 240K

#innodb_use_native_aio = 0

innodb_buffer_pool_size=2M

performance_schema_max_table_instances=50

table_definition_cache=50

table_open_cache=32

max_connections=50

max_user_connections=35

wait_timeout=10

interactive_timeout=15

long_query_time=5

performance_schema = off

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

# Recommended in standard MySQL setup

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

PS：如果你不修改datadir、socket数据目录为你设置的存放目录，可能会导致数据库不能正确启动。