钧言极客钧言极客

钧言极客

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 还可以进一步降低内存占用。

PS: 我当前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:如果你不修改datadirsocket数据目录为你设置的存放目录,可能会导致数据库不能正确启动。

未经允许不得转载:钧言极客 » MYSQL优化参数减少内存占用

评论 2

  1. AliSQL对应的哪个版本?

    Teacher Du    /  (2021-10-18) 回复
    • AliSQL5.6.32 不过阿里没有心思维护,转头就使用MariaDB

      ღ钧言ღ    /  (2021-10-18) 回复