欢迎您光临亿恩科技官网！
域名常见问题
虚拟主机使用指南
云主机使用指南
服务器租用/托管指南
服务器租用/托管指南
智能建站使用指南
> >文章内容
mysql占用内存过大的解决办法
发布时间:&& 14:40:03
进入mysql安装目录用记事本打开my.ini对应如下参数进行修改。
参数参照如下：
table_cache=1024 物理内存越大,设置就越大.默认为2402,调到512-1024最佳
innodb_additional_mem_pool_size=4M& &默认为2M
innodb_flush_log_at_trx_commit=1
(设置为0就是等到innodb_log_buffer_size列队满后再统一储存,默认为1)
innodb_log_buffer_size=2M& && && && & 默认为1M
innodb_thread_concurrency=8& && & 你的服务器CPU有几个就设置为几,建议用默认一般为8
key_buffer_size=256M& && && && && && && &&&默认为218& && & 调到128最佳
tmp_table_size=64M& && && && && && && & 默认为16M& && &&&调到64-256最挂
read_buffer_size=4M& && && && && && && &&&默认为64K
read_rnd_buffer_size=16M& && && && &&&默认为256K
sort_buffer_size=32M& && && && && && & 默认为256K
max_connections=1024& && && && && &&&默认为1210
thread_cache_size=120& && && && & 默认为60
query_cache_size=64M本文出自：
您可能在找
扫扫关注-微信公众号
ENKJ All Rights Reserved 亿恩科技 版权所有 　 亿恩科技法律顾问：河南三融律师事务所周跃、裴松龄律师 　
　 经营性ICP/ISP证：京B2-mysql&5.6内存占用过高解决方法
重启mysql服务后，top一下发现这一个mysql服务就占用了500多MB内存。。
Google一番，贴一下记录：
mysql5.6的默认配置是不适合小型站点的，win的话在my.ini里修改、linux则在/f里修改performance_schema_max_table_instances参数，有就修改，没有就追加：
performance_schema_max_table_instances=400
table_definition_cache=400
table_open_cache=256
修改完毕后，重启mysql服务，service mysql
restart，然后top后发现内存占用少了一半，应该还有很大优化空间，鉴于内存基本够用了，就不继续折腾了。
已投稿到：
以上网友发言只代表其个人观点，不代表新浪网的观点或立场。简单解决mysql占用资源过大问题
&&& 在 linux 下，经常出现的情况就是使用 VBB 这种时，出现缓慢， 假死状态，sleep进程过多等等现像。导致几十个用户，就把系统搞定了。
&&& 其实这是 mysql 配置上的问题， 默认的 linux 中， mysql 的配置是 f 配置，该配置适合大型服务器。有高内存，比如2G，4G内存的，适合使用，而一般512M内存 的就不行了。它会占用512M内存来保存系统 mysql 的进程，这些进程长期堆质，并不释放，导致系统缓慢。所以，把配置改变成为 f ，小型配置就可以了。它的配置如下：
&&& # Example mysql config file for small systems.
&&& # This is for a system with little memory (&= 64M) where MySQL is only used
&&& # from time to time and its important that the mysqld deamon
&&& # doesnt use much resources.
&&& # You can copy this file to
&&& # /f to set global options,
&&& # mysql-data-f to set server-specific options (in this
&&& # installation this directory is /var/lib/mysql) or
&&& # ~/.my.cnf to set user-specific options.
&&& # One can in this file use all long options that the program supports.
&&& # If you want to know which options a program support, run the program
&&& # with --help option.
&&& # The following options will be passed to all MySQL clients
&&& [client]
&&& #password = 你的密码
&&& port = 3306
&&& socket = /tmp/mysql.sock
&&& # Here follows entries for some specific programs
&&& # The MySQL server
&&& [mysqld]
&&& port = 3306
&&& socket = /tmp/mysql.sock
&&& skip-locking
&&& set-variable = key_buffer=16K
&&& set-variable = max_allowed_packet=1M
&&& set-variable = thread_stack=64K
&&& set-variable = table_cache=4
&&& set-variable = sort_buffer=64K
&&& set-variable = net_buffer_length=2K
&&& server-id = 1
&&& # Uncomment the following if you want to log updates
&&& #log-bin
&&& # Uncomment the following if you are NOT using BDB tables
&&& #skip-bdb
&&& # 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/
&&& #innodb_log_arch_dir = /var/lib/mysql/
&&& # You can set .._buffer_pool_size up to 50 - 80 %
&&& # of RAM but beware of setting memory usage too high
&&& #set-variable = innodb_buffer_pool_size=16M
&&& #set-variable = innodb_additional_mem_pool_size=2M
&&& # Set .._log_file_size to 25 % of buffer pool size
&&& #set-variable = innodb_log_file_size=5M
&&& #set-variable = innodb_log_buffer_size=8M
&&& #innodb_flush_log_at_trx_commit=1
&&& #set-variable = innodb_lock_wait_timeout=50
&&& [mysqldump]
&&& set-variable = max_allowed_packet=16M
&&& [mysql]
&&& no-auto-rehash
&&& # Remove the next comment character if you are not familiar with SQL
&&& #safe-updates
&&& [isamchk]
&&& set-variable = key_buffer=8M
&&& set-variable = sort_buffer=8M
&&& [myisamchk]
&&& set-variable = key_buffer=8M
&&& set-variable = sort_buffer=8M
&&& [mysqlhotcopy]
&&& interactive-timeout
&&& 以上配置足够 你的系统高速运行 mysql 了，sleep的进程，很快就被回收，不占资源，这非常适合小型服务器。一般个人托管的服务器也很适合。
(window.slotbydup=window.slotbydup || []).push({
id: '2467140',
container: s,
size: '1000,90',
display: 'inlay-fix'
(window.slotbydup=window.slotbydup || []).push({
id: '2467141',
container: s,
size: '1000,90',
display: 'inlay-fix'
(window.slotbydup=window.slotbydup || []).push({
id: '2467142',
container: s,
size: '1000,90',
display: 'inlay-fix'
(window.slotbydup=window.slotbydup || []).push({
id: '2467143',
container: s,
size: '1000,90',
display: 'inlay-fix'
(window.slotbydup=window.slotbydup || []).push({
id: '2467148',
container: s,
size: '1000,90',
display: 'inlay-fix'mysql耗内存吗？应该怎么处理？
mysql耗内存吗？应该怎么处理？
学习啦【内存知识】 编辑：本达
　　mysql耗内存吗?很多人都说MySQL占用了很大的虚拟内存，那么这个问题应该怎么解决呢?下面是学习啦小编收集整理的一些方法，现在分享给大家!
　　解决mysql耗内存的具体方法一：
　　在分析的过程中发现最耗内存的是MySQL，其中近1GB的内存被它吞了，而且不在任务管理器体现出来。这个数据库软件是EMS要用到了，所以必须要运行。这个软件在安装的时候会根据机器的实际内存自动进行配置，PC机物理内存越多，它默认占有的内存就越多，难怪3GB的内存被它给吞了近1GB。
　　优化方法：
　　1. 退出EMS client&server
　　2. 在CMD里运行：net stop mysql
　　3. 找到MySQL\MySQL Server的安装目录，里面有个my.ini文件，参考附件的配置对参数query_cache_size tmp_table_size myisam_sort_buffer_size key_buffer_size innodb_buffer_pool_size进行修改，注意不要改动innodb_log_file_size，修改前备份my.ini
　　4. 在CMD里运行：net start mysql，如果提示成功，则说明修改的参数没有什么问题，如果失败，重新调整一下上面的参数
　　5. 找到EMS 安装目录runGUI.bat runServer.bat脚本，找到-Xmx700m，改为-Xmx256m，注意修改前备份这两个文件，感谢Liping Sun提供帮助
　　6. 重新运行EMS
　　前后对比，对于3GB的PC，发现可以节省近1GB的内存。对于2GB的PC，也可以节省600-800MB。优化后发现EMS启动稍微慢一些，但是其它的软件运行速度提高了很多，不在经常出现卡机现象了。如果在运行过程中发现EMS特别慢的话，自己也可以适当放大上面提到的一些参数。
　　my.ini
　　# MySQL Server Instance Configuration File
　　# ----------------------------------------------------------------------
　　# Generated by the MySQL Server Instance Configuration Wizard
　　# Installation Instructions
　　# ----------------------------------------------------------------------
　　# On Linux you can copy this file to /f to set global options,
　　# mysql-data-f to set server-specific options
　　# (@localstatedir@ for this installation) or to
　　# ~/.my.cnf to set user-specific options.
　　# On Windows you should keep this file in the installation directory
　　# of your server (e.g. C:\Program Files\MySQL\MySQL Server X.Y). To
　　# make sure the server reads the config file use the startup option
　　# &--defaults-file&.
　　# To run run the server from the command line, execute this in a
　　# command line shell, e.g.
　　# mysqld --defaults-file=&C:\Program Files\MySQL\MySQL Server X.Y\my.ini&
　　# To install the server as a Windows service manually, execute this in a
　　# command line shell, e.g.
　　# mysqld --install MySQLXY --defaults-file=&C:\Program Files\MySQL\MySQL Server X.Y\my.ini&
　　# And then execute this in a command line shell to start the server, e.g.
　　# net start MySQLXY
　　# Guildlines for editing this file
　　# ----------------------------------------------------------------------
　　# In this file, you can use all long options that the program supports.
　　# If you want to know the options a program supports, start the program
　　# with the &--help& option.
　　# More detailed information about the individual options can also be
　　# found in the manual.
　　# CLIENT SECTION
　　# ----------------------------------------------------------------------
　　# The following options will be read by MySQL client applications.
　　# Note that only client applications shipped by MySQL are guaranteed
　　# to read this section. If you want your own MySQL client program to
　　# honor these values, you need to specify it as an option during the
　　# MySQL client library initialization.
　　[client]
　　port=3306
　　[mysql]
　　default-character-set=utf8
　　# SERVER SECTION
　　# ----------------------------------------------------------------------
　　# The following options will be read by the MySQL Server. Make sure that
　　# you have installed the server correctly (see above) so it reads this
　　# file.
　　[mysqld]
　　# The TCP/IP Port the MySQL Server will listen on
　　port=3306
　　#Path to installation directory. All paths are usually resolved relative to this.
　　basedir=&D:/Program Files/MySQL/MySQL Server 5.1/&
　　#Path to the database root
　　datadir=&C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.1/Data/&
　　# The default character set that will be used when a new schema or table is
　　# created and no character set is defined
　　character-set-server=utf8
　　# The default storage engine that will be used when create new tables when
　　default-storage-engine=INNODB
　　# Set the SQL mode to strict
　　sql-mode=&STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION&
　　# The maximum amount of concurrent sessions the MySQL server will
　　# allow. One of these connections will be reserved for a user with
　　# SUPER privileges to allow the administrator to login even if the
　　# connection limit has been reached.
　　max_connections=1510
　　# Query cache is used to cache SELECT results and later return them
　　# without actual executing the same query once again. Having the query
　　# cache enabled may result in significant speed improvements, if your
　　# have a lot of identical queries and rarely changing tables. See the
　　# &Qcache_lowmem_prunes& status variable to check if the current value
　　# is high enough for your load.
　　# Note: In case your tables change very often or if your queries are
　　# textually different every time, the query cache may result in a
　　# slowdown instead of a performance improvement.
　　query_cache_size=16M
　　# The number of open tables for all threads. Increasing this value
　　# increases the number of file descriptors that mysqld requires.
　　# Therefore you have to make sure to set the amount of open files
　　# allowed to at least 4096 in the variable &open-files-limit& in
　　# section [mysqld_safe]
　　table_cache=3020
　　# Maximum size for internal (in-memory) temporary tables. If a table
　　# grows larger than this value, it is automatically converted to disk
　　# based table This limitation is for a single table. There can be many
　　# of them.
　　tmp_table_size=4M
　　# How many threads we should keep in a cache for reuse. When a client
　　# disconnects, the client's threads are put in the cache if there aren't
　　# more than thread_cache_size threads from before. This greatly reduces
　　# the amount of thread creations needed if you have a lot of new
　　# connections. (Normally this doesn't give a notable performance
　　# improvement if you have a good thread implementation.)
　　thread_cache_size=64
　　#*** MyISAM Specific options
　　# The maximum size of the temporary file MySQL is allowed to use while
　　# recreating the index (during REPAIR, ALTER TABLE or LOAD DATA INFILE.
　　# If the file-size would be bigger than this, the index will be created
　　# through the key cache (which is slower).
　　myisam_max_sort_file_size=100G
　　# If the temporary file used for fast index creation would be bigger
　　# than using the key cache by the amount specified here, then prefer the
　　# key cache method. This is mainly used to force long character keys in
　　# large tables to use the slower key cache method to create the index.
　　myisam_sort_buffer_size=4M
　　# Size of the Key Buffer, used to cache index blocks for MyISAM tables.
　　# Do not set it larger than 30% of your available memory, as some memory
　　# is also required by the OS to cache rows. Even if you're not using
　　# MyISAM tables, you should still set it to 8-64M as it will also be
　　# used for internal temporary disk tables.
　　key_buffer_size=16M
　　# Size of the buffer used for doing full table scans of MyISAM tables.
　　# Allocated per thread, if a full scan is needed.
　　read_buffer_size=64K
　　read_rnd_buffer_size=256K
　　# This buffer is allocated when MySQL needs to rebuild the index in
　　# REPAIR, OPTIMZE, ALTER table statements as well as in LOAD DATA INFILE
　　# into an empty table. It is allocated per thread so be careful with
　　# large settings.
　　sort_buffer_size=256K
　　#*** INNODB Specific options ***
　　# Use this option if you have a MySQL server with InnoDB support enabled
　　# but you do not plan to use it. This will save memory and disk space
　　# and speed up some things.
　　#skip-innodb
　　# Additional memory pool that is used by InnoDB to store metadata
　　# information. If InnoDB requires more memory for this purpose it will
　　# start to allocate it from the OS. As this is fast enough on most
　　# recent operating systems, you normally do not need to change this
　　# value. SHOW INNODB STATUS will display the current amount used.
　　innodb_additional_mem_pool_size=9M
　　# If set to 1, InnoDB will flush (fsync) the transaction logs to the
　　# disk at each commit, which offers full ACID behavior. If you are
　　# willing to compromise this safety, and you are running small
　　# transactions, you may set this to 0 or 2 to reduce disk I/O to the
　　# logs. Value 0 means that the log is only written to the log file and
　　# the log file flushed to disk approximately once per second. Value 2
　　# means the log is written to the log file at each commit, but the log
　　# file is only flushed to disk approximately once per second.
　　innodb_flush_log_at_trx_commit=1
　　# The size of the buffer InnoDB uses for buffering log data. As soon as
　　# it is full, InnoDB will have to flush it to disk. As it is flushed
　　# once per second anyway, it does not make sense to have it very large
　　# (even with long transactions).
　　innodb_log_buffer_size=5M
　　# InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and
　　# row data. The bigger you set this the less disk I/O is needed to
　　# access data in tables. On a dedicated database server you may set this
　　# parameter up to 80% of the machine physical memory size. Do not set it
　　# too large, though, because competition of the physical memory may
　　# cause paging in the operating system. Note that on 32bit systems you
　　# might be limited to 2-3.5G of user level memory per process, so do not
　　# set it too high.
　　innodb_buffer_pool_size=32M
　　# Size of each log file in a log group. You should set the combined size
　　# of log files to about 25%-100% of your buffer pool size to avoid
　　# unneeded buffer pool flush activity on log file overwrite. However,
　　# note that a larger logfile size will increase the time needed for the
　　# recovery process.
　　innodb_log_file_size=88M
　　# Number of threads allowed inside the InnoDB kernel. The optimal value
　　# depends highly on the application, hardware as well as the OS
　　# scheduler properties. A too high value may lead to thread thrashing.
　　innodb_thread_concurrency=8
　　解决mysql耗内存的具体方法二：
　　更改后如下：
　　innodb_buffer_pool_size=576M -&256M InnoDB引擎缓冲区占了大头，首要就是拿它开刀
　　query_cache_size=100M -&16M 查询缓存
　　tmp_table_size=102M -&64M 临时表大小
　　key_buffer_size=256m -&32M
　　重启mysql服务后，虚拟内存降到200以下.
　　另外mysql安装目录下有几个文件：my-huge.ini 、my-large.ini、my-medium.ini...这几个是根据内存大小作的建议配置，新手在设置的时候也可以参考一下。
　　2G内存的MYSQL数据库服务器 my.ini优化 (my.ini)
　　2G内存,针对站少,优质型的设置,试验特：
　　table_cache=1024 物理内存越大,设置就越大.默认为2402,调到512-1024最佳
　　innodb_additional_mem_pool_size=8M 默认为2M
　　innodb_flush_log_at_trx_commit=0 等到innodb_log_buffer_size列队满后再统一储存,默认为1
　　innodb_log_buffer_size=4M 默认为1M
　　innodb_thread_concurrency=8 你的服务器CPU有几个就设置为几,默认为8
　　key_buffer_size=256M 默认为218 调到128最佳
　　tmp_table_size=64M 默认为16M 调到64-256最挂
　　read_buffer_size=4M 默认为64K
　　read_rnd_buffer_size=16M 默认为256K
　　sort_buffer_size=32M 默认为256K
　　max_connections=1024 默认为1210
　　试验一：
　　table_cache=512或1024
　　innodb_additional_mem_pool_size=2M
　　innodb_flush_log_at_trx_commit=0
　　innodb_log_buffer_size=1M
　　innodb_thread_concurrency=8 你的服务器CPU有几个就设置为几,默认为8
　　key_buffer_size=128M
　　tmp_table_size=128M
　　read_buffer_size=64K或128K
　　read_rnd_buffer_size=256K
　　sort_buffer_size=512K
　　max_connections=1024
　　试验二：
　　table_cache=512或1024
　　innodb_additional_mem_pool_size=8M
　　innodb_flush_log_at_trx_commit=0
　　innodb_log_buffer_size=4M
　　innodb_thread_concurrency=8
　　key_buffer_size=128M
　　tmp_table_size=128M
　　read_buffer_size=4M
　　read_rnd_buffer_size=16M
　　sort_buffer_size=32M
　　max_connections=1024
　　一般：
　　table_cache=512
　　innodb_additional_mem_pool_size=8M
　　innodb_flush_log_at_trx_commit=0
　　innodb_log_buffer_size=4M
　　innodb_thread_concurrency=8
　　key_buffer_size=128M
　　tmp_table_size=128M
　　read_buffer_size=4M
　　read_rnd_buffer_size=16M
　　sort_buffer_size=32M
　　max_connections=1024
　　经过测试.没有特殊情况,最好还是用默认的.
　　2G内存,针对站多,抗压型的设置,最佳：
　　table_cache=1024 物理内存越大,设置就越大.默认为2402,调到512-1024最佳
　　innodb_additional_mem_pool_size=4M 默认为2M
　　innodb_flush_log_at_trx_commit=1
　　(设置为0就是等到innodb_log_buffer_size列队满后再统一储存,默认为1)
　　innodb_log_buffer_size=2M 默认为1M
　　innodb_thread_concurrency=8 你的服务器CPU有几个就设置为几,建议用默认一般为8
　　key_buffer_size=256M 默认为218 调到128最佳
　　tmp_table_size=64M 默认为16M 调到64-256最挂
　　read_buffer_size=4M 默认为64K
　　read_rnd_buffer_size=16M 默认为256K
　　sort_buffer_size=32M 默认为256K
　　max_connections=1024 默认为1210
　　thread_cache_size=120 默认为60
　　query_cache_size=64M
　　优化mysql数据库性能的十个参数
　　(1)、max_connections：
　　允许的同时客户的数量。增加该值增加 mysqld 要求的文件描述符的数量。这个数字应该增加，否则，你将经常看到 too many connections 错误。 默认数值是100，我把它改为1024 。
　　(2)、record_buffer：
　　每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描，你可能想要增加该值。默认数值是k)，我把它改为m)
　　(3)、key_buffer_size：
　　索引块是缓冲的并且被所有的线程共享。key_buffer_size是用于索引块的缓冲区大小，增加它可得到更好处理的索引(对所有读和多重写)，到你能负担得起那样多。如果你使它太大，系统将开始换页并且真的变慢了。默认数值是m)，我的mysql主机有2gb内存，所以我把它改为 0mb)。
　　4)、back_log：
　　要求 mysql 能有的连接数量。当主要mysql线程在一个很短时间内得到非常多的连接请求，这就起作用，然后主线程花些时间(尽管很短)检查连接并且启动一个新线程。
　　back_log 值指出在mysql暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接，你需要增加它，换句话说，这值对到来的tcp/ip连接的侦听队列的大小。你的操作系统在这个队列大小上有它自己的限制。试图设定back_log高于你的操作系统的限制将是无效的。
　　当你观察你的主机进程列表，发现大量 264084 | unauthenticated user | xxx.xxx.xxx.xxx | null | connect | null | login | null 的待连接进程时，就要加大 back_log 的值了。默认数值是50，我把它改为500。
　　(5)、interactive_timeout：
　　服务器在关闭它前在一个交互连接上等待行动的秒数。一个交互的客户被定义为对 mysql_real_connect()使用 client_interactive 选项的客户。 默认数值是28800，我把它改为7200。
　　(6)、sort_buffer：
　　每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速order by或group by操作。默认数值是m)，我把它改为 m)。
　　(7)、table_cache：
　　为所有线程打开表的数量。增加该值能增加mysqld要求的文件描述符的数量。mysql对每个唯一打开的表需要2个文件描述符。默认数值是64，我把它改为512。
　　(8)、thread_cache_size：
　　可以复用的保存在中的线程的数量。如果有，新的线程从缓存中取得，当断开连接的时候如果有空间，客户的线置在缓存中。如果有很多新的线程，为了提高性能可以这个变量值。通过比较 connections 和 threads_created 状态的变量，可以看到这个变量的作用。我把它设置为 80。
　　(9)mysql的搜索功能
　　用mysql进行搜索，目的是能不分大小写，又能用中文进行搜索
　　只需起动mysqld时指定 --default-character-set=gb2312
　　(10)、wait_timeout：
　　服务器在关闭它之前在一个连接上等待行动的秒数。 默认数值是28800，我把它改为7200。
　　注：参数的调整可以通过修改 /f 文件并重启 mysql 实现。这是一个比较谨慎的工作，上面的结果也仅仅是我的一些看法，你可以根据你自己主机的硬件情况(特别是内存大小)进一步修改。
本文已影响 人
[mysql耗内存吗？应该怎么处理？]相关的文章
看过本文的人还看了
5224人看了觉得好
839人看了觉得好
6682人看了觉得好
【内存知识】图文推荐