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

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

1
2
3
performance_schema_max_table_instances = 200
table_definition_cache = 100
table_open_cache = 100

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

还可以关闭 performance_schema 数据库性能数据采集，调小innobd缓存池 innodb_buffer_pool_size=4M 还可以进一步降低内存占用。

PS： 我当前AliSQL使用的配置，可以大幅度降低内存到100M

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
# 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数据目录为你设置的存放目录，可能会导致数据库不能正确启动。