Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I am currently optimizing our mysql production server and I noticed the ram usage on this server only increases. It never decreases. I read on several website sites that this could be caused by to many open connections. However our server has very little connections on any given time (max 15 sleep/query). After some time queries become very slow, and i think this is because the server's swap file is used because the ram memory is full.

Is this normal behavour or is something leaking memory and how can i best identify the problem?

Just to be complete below our server configuration and the my.ini configuration file.

Server configuration:

  • Windows 2008 SBS Server
  • Mysql community server 5.1
  • 4 quad core processors
  • 32 gb ram
  • 142 GB Memory (Raid 1)

my.ini

[client]
port=3306
[mysql]
default-character-set=utf8


[mysqld]
port=3306
basedir="E:/MySQL/MySQL Server 5.1/"
datadir="E:/MySQL/Data/"
default-character-set=utf8
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=100
table_cache=256
tmp_table_size = 2G
thread_cache_size=8
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=410M
key_buffer_size=354M
read_buffer_size = 64 
read_rnd_buffer_size=256K
sort_buffer_size = 10M
innodb_additional_mem_pool_size=15M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=7M
innodb_buffer_pool_size = 7G
innodb_log_file_size=343M
innodb_thread_concurrency=18
max_allowed_packet=16M
wait_timeout = 4800
ft_min_word_len = 2
ft_stopword_file = ""
max_heap_table_size = 2G
tmpdir = "E:/mysql/MySqlTmp/"
log-slow-queries = "F:/log/slow.log"
long_query_time = 2
init-file=E:\mysql\MySQL Server 5.1\my_OnStartup.txt
share|improve this question
    
What precisely do you mean by "ram usage on this server"? Do you mean the actual amount of physical RAM used? –  David Schwartz Jan 24 at 12:58
    
Yes, actual amount of physical RAM used. –  Nebu Jan 24 at 14:12

2 Answers 2

up vote 0 down vote accepted

As caches are built up, memory usage goes up. Simply put, it's like remembering bits and pieces of what queries were made and what their results were so that similar or same queries can be fetched faster. That is why you have settings like tmp_table_size, etc.

This is normal behavior, however, it is not possible to say you don't have a memory leak or some sort of issue as the above is a general answer.

If you believe that you have an issue, you may try updating if you have not already (of 5.1 branch, 5.1.73 is now newest stable. Or you can go much further to 5.5/5.6 branches).

You can also adjust your configuration so that it's more capable of handling error prone situations. These include, but not limited to:

  • Lowering your wait_timeout. Frankly, by a lot... Do you really have a case where you need to wait for 80 minutes?!
  • myisam_max_sort_file_size=100G - Do you really need it this big? You don't even have memory to handle this.
  • Configuring your configuration so that your memory usage can't be bigger than your available memory (which you stated to be 32GB). This is to prevent swap usage which will result in slow performance.
share|improve this answer
    
Preventing the total memory usage to be less then 12GB (which is reserved for mysql) would do the trick i think. Could you please explain which alternations must be made in the config file to accomplish this (if this is not to difficult). –  Nebu Jan 24 at 14:11

Reduction in physical RAM usage is rare on a modern operating system. Free RAM is entirely wasted. If there's anything the OS could possibly put in that RAM that might have any chance of being useful, that's better than making it free. So it's perfectly normal to see physical RAM use often increase and rarely decrease.

Think about data read in from disk. When that data is no longer needed, the system has two choices. It can make the memory free or not make it free. Let's consider first if it makes it free:

  1. This takes effort, the memory must be placed on the free list.

  2. If the data is needed later, it has to be read from disk again.

  3. If the RAM needs to be used for some other purpose, it has to be made no longer free.

Now, let's consider if it keeps it used:

  1. This takes no effort. The RAM is already use and just stays that way.

  2. If the data is needed later, it doesn't have to be read from disk.

  3. If the RAM needs to be used for some other purpose later, it does not need to be removed from the free list.

Wins all around.

If you're thinking, "I want the RAM to be free now so I can use it later", that's silly. You can use it now and use it later. There's no painful tradeoff to make.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.