2

Is it normal for MySQL to increase memory usage over time? See image below of my server's memory usage over the last two weeks. After "service mysql restart" it drops to 40%. The database is used by a node.js app.

Server has 4GB of RAM with below additions to mysqld.cnf:

innodb_ft_min_token_size = 1   
ft_min_word_len = 1  
innodb_buffer_pool_size = 3G  
innodb_buffer_pool_instances = 5  
innodb_read_io_threads = 8  
innodb_write_io_threads = 8  
innodb_log_file_size = 128M  
innodb_flush_method = O_DIRECT  
max_connections = 300  
long_query_time = 1  
innodb_ft_enable_stopword = 0  

Thank you!

3 Answers 3

1

I can't address the MySQL specifics, but in general, yes, memory usage of database engines will tend to increase over time. As queries are processed, if the data isn't in memory, the engine will load the data from disk into memory. Then, as long as there are no other pressing needs to use that memory, the data will be left there in case it is queried again.

So as a database engine starts up, it'll use a moderate amount of memory. (Some will allocate a "minimum memory" just to have it ready for when they need it.) Then, as it runs, the memory usage will gradually increase, until it reaches either:

  • an amount configured as the "maximum memory" within the DBMS, or
  • the maximum amount the operating system will allow it to take

Only then will it start ejecting data from memory to make room for more data coming in from disk.

The specifics vary from DBMS to DBMS, and I've used some terms here that are inspired by SQL Server's terminology. But the general concepts should hold for most engines.

I suspect you'll get other answers with better MySQL details, or a better explanation of memory usage and caching and pools. But I hope this summary is still useful.

1

innodb_buffer_pool_size = 3G is dangerously high. Drop it to 2G. Swapping is terrible for performance.

Notice that you start with 1.6GB (40%) in used. Add 3G to that, and you get 4.6G, which will overflow RAM into swap.

Back to your question. The buffer_pool is gradually filled until the limit is reached, hence the increase you are seeing. It will not shrink until you restart.

0

3G buffer pool would equate to 75% of ram, and a 80% is 3.2G meaning 200M of other stuff so this looks about right.

You are probably measuring paged-in memory, rather than memory that is allocated but not used yet. A large portion of the innodb buffer pool will fit into this category. As new data is read the innodb buffer pool will fill up slowly as new data not in its existing cache is used.

This clearly responds to restarting ending up as a lower amount of ram paged-in.

If it was to eventually reach up into the 95% (3.8G) then that would be worryingly close to hitting out of memory.

In addition to the buffer pool there is a amount of memory per whatever your default settings are that are per thread. Each of your 300 max_connections will use an amount of RAM here. To examine you potential maximum memory use needs to account this limit. Tools like mysqltuner provide some measure of this in a pessimistic worst case scenario that each thread can use its maximum amount.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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