512M小内存VPS上MySQL优化

文章转自https://blog.kuoruan.com/72.html

做网站的时候选数据库，MySQL是首选，但是服务器配置不高，如果不优化的话，服务器内存完全不够。于是找了一些国外大神的优化实例，希望能给大家一些启发。

点这里进入MySQL优化设置

下面一张表格是我从国外的一个博客里边摘抄下来的MySQL的一些默认配置项和最低设置参数。

MySQL优化设置

依据以上参数，MySQL的最小配置为：

# /etc/my.cnf:
innodb_buffer_pool_size=5M
innodb_log_buffer_size=256K
query_cache_size=0
max_connections=10
key_buffer_size=8
thread_cache_size=0
host_cache_size=0
innodb_ft_cache_size=1600000
innodb_ft_total_cache_size=32000000
 
# per thread or per operation settings
thread_stack=131072
sort_buffer_size=32K
read_buffer_size=8200
read_rnd_buffer_size=8200
max_heap_table_size=16K
tmp_table_size=1K
bulk_insert_buffer_size=0
join_buffer_size=128
net_buffer_length=1K
innodb_sort_buffer_size=64K
 
#settings that relate to the binary log (if enabled)
binlog_cache_size=4K
binlog_stmt_cache_size=4K

FinetunedMySQL

但是并不推荐使用最小配置，可以适当修改一下。

在另外一个博客上找到了一个比较适合于512M内存的配置：

# Example MySQL config file for large systems.
#
# This is for a large system with memory = 512M where the system runs mainly
# MySQL.
#
# MySQL programs look for option files in a set of
# locations which depend on the deployment platform.
# You can copy this option file to one of those
# locations. For information about these locations, see:
# http://dev.mysql.com/doc/mysql/en/option-files.html
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.
 
# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /var/lib/mysql/mysql.sock
 
# Here follows entries for some specific programs
 
# The MySQL server
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
 
# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
# 
#skip-networking
 
# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin
 
# binary logging format - mixed recommended
binlog_format=mixed
 
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 1
 
# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
#    the syntax is:
#
#    CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
#    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#
#    where you replace <host>, <user>, <password> by quoted strings and
#    <port> by the master's port number (3306 by default).
#
#    Example:
#
#    CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
#    MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
#    start replication for the first time (even unsuccessfully, for example
#    if you mistyped the password in master-password and the slave fails to
#    connect), the slave will create a master.info file, and any later
#    change in this file to the variables' values below will be ignored and
#    overridden by the content of the master.info file, unless you shutdown
#    the slave server, delete master.info and restart the slaver server.
#    For that reason, you may want to leave the lines below untouched
#    (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id       = 2
#
# The replication master for this slave - required
#master-host     =   <hostname>
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user     =   <username>
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password =   <password>
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port     =  <port>
#
# binary logging - not required for slaves, but recommended
#log-bin=mysql-bin
 
# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 256M
#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 64M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50
 
[mysqldump]
quick
max_allowed_packet = 16M
 
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
 
[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
 
[mysqlhotcopy]
interactive-timeout

链接：

1.Configuring MySQL to use minimal memory

2.Mysql sample configuration with 512M ram

• 本文固定链接: https://blog.kuoruan.com/72.html
• 转载请注明: Index 2015年12月9日 于 扩软博客 发表

最后编辑：2015年12月9日 15:11作者：Index

百度ID：“度娘程序员”，博主。 站内专栏 站点 QQ交谈捐 赠如果您觉得这篇文章有用处，请支持作者！鼓励作者写出更好更多的文章！ MySQL
 给 Linux 服务器添加Swap空间以解决内存不足给博客加把小绿锁，Let’s Encrypt免费HTTPS SSL证书获取教程 

 您可能还会对这些文章感兴趣！

• CentOS编译安装MySQL 5.7.10
• CentOS7安装配置Nginx1.10 PHP5.6 MySQL5.7
• Windows下安装MySQL 5.7 ZIP解压版
• MySQL忘记root用户密码或者root权限丢失解决
• Linux下配置Golang环境
• 黑科技，效果逆天！使用FinalSpeed给搬瓦工(Bandwagonhost)加速！
• Nginx设置反向代理内网服务器
• 小内存福音，Kcptun Shadowsocks加速方案

《MySQL在512M以下小内存VPS上的优化设置》有 4 条评论

1. Pingback 引用通告： MySQL 在512M一下記憶體優化配置 | 程式前沿
2. Pingback 引用通告： MySQL 在512M一下内存优化配置 – x
3. 回复Google Chrome 50.0.2661.102Windows 8.1感谢博主分享，受教良多。
   我的感觉是，如果博客访问量不是特别大，小内存VPS就行，比如128MB或者256MB。
   比如，256MB这种小内存的VPS经过适当的优化，每天10000PV的流量应该是没有问题的。
   因为博主目前用的搬瓦工256MB的VPS，模拟5分钟内100个在线完全没问题。
   测试页面：http://www.seoimo.com/wordpress-vps/
   可以看到，页面有大量的图片和文字。倘若一般的博客页面只有很少图片的话，想必每天的流量再多点应该也没问题。
   所以说，小内存VPS不一定就不适合建大站，关键是要优化合理。
   P.S. 以上本人建站经历，供参考。SEOIMO 中国 辽宁大连 2017年3月17日 15:42
4. 回复Google Chrome 50.0.2661.102Windows 10 x64收藏一下，表示极限优化后 256内存 能跑LNMP