Hello,
We are using Percona Server 5.5.35-rel33.0-611.precise with InnoDB in our production server. Recently Mysql memory went high and it did’t drop from there. We are afraid whether it would result in OOM sooner. We would like know whether mysql memory can be freed with out doing a restart
When MySQL grabs memory, it tends to not give it back. That does not mean MySQL is actively “using” all of the memory; it is simply holding onto the memory so that it can use the memory later if needed. If MySQL is the only application running on the server, then you should be okay as MySQL will manage the memory and do with it as it sees fit. However if MySQL ends up using even more memory that you have free, then you could run into problems, so you need to see what your “high water mark” is and lower memory usage as needed to prevent the system from being overran.
If you are running another memory hungry application on the same server, then you could get into trouble. If that is the case, then you should ideally put MySQL on it’s own server, otherwise you’ll need to lower the memory footprint of MySQL (most likely decrease the InnoDB buffer pool size) to prevent memory contention between applications.
As far as innodb_max_dirty_pages_pct, I do not believe that will lower the currently used memory footprint of MySQL. All that does is write changes from the InnoDB buffer pool to disk. Setting this variable to zero is typically only done when you are planning to shutdown the server and want there to be fewer changes to write to disk once you actually perform the shutdown so that it happens quicker. This is “safe” to do in production, as long as you understand the performance implications of it since you are writing more to disk at that point. If your system is not very busy, and you have free I/O time, then it likely will not cause an issue. However if you run a busy system, you could notice a performance impact when setting that variable to zero.
So to specifically answer your question, I do not know of any way to release a significant amount of memory from MySQL without restarting it. The only mostly insignificant thing I’ve seen is resetting the query cache, but most people do not even use the query cache these days.
I have never used drop_caches myself, so I could not say, but from what I understand about it I would not recommend it.
What is the real issue at hand? Is your system actually running out of memory and killing MySQL / other applications? Or are you just worried about seeing the high % of memory usage by MySQL?
If your server is actually running out of memory and applications are being killed, then you need to lower the potential memory footprint of MySQL by modifying settings like innodb_buffer_pool size and tmp_table_size / max_heap_table_size (among many other settings you can tweak if needed).
If your server is not actually running out of memory, then you likely do not have a problem. If you are still uncomfortable at that point seeing MySQL use so much memory, then simply do the same as above and lower the possible memory footprint of MySQL by modifying the various settings to limit memory usage.
MySQL (and most database servers) are memory hungry animals that will grab up and hold onto as much memory as you let them. So the key is to give MySQL as much memory as safely possible without getting to the point where the system is swapping and/or killing MySQL or other applications due to low memory.
Hi Scott,
The real problem is that the mysql memory usage is getting high everyday. It was 95% when I reported and its now 98% (courtesy Nagios). This started to happen all of a sudden(It has never reached 90% before) . We are running with the same configuration for a long time without any issues. We are afraid that the memory will reach 100% and eventually end up impacting the application . We want to avoid the situation by trying out the ways to lower the memory usage without a restart.
The server has 122 GB of RAM and its dedicated only to MySQL (no other application runs in this server) . The settings that you mentioned have
You are focusing on the symptom (increased memory usage) instead of the problem. Since MySQL all of a sudden started using more memory, and it sounds like it is increasing still, then you need to determine what caused the change and either fix it if it is broken or adjust your MySQL config to cope with it (assuming the usage rises to the point where you actually start having problems).
Did you do a code release for your application that uses MySQL as the backend? Did you upgrade any software that hits MySQL? Did another application start using MySQL (i.e. an ETL process for a data warehouse)? Did you increase the user base of your application a lot? Once you determine what caused the change, it should be easier to figure out what you need to do to stabilize the situation.
I would start by running a slow query log and see what queries are showing up. One likely cause is you have queries working with large result sets that are sucking up memory. Along with that I’d monitor your temporary table usage, as you will also likely see a lot of tmp tables being created if the problem is big/bad queries. If that ends up being the case, optimizing the queries could help you out a lot, or lowering the tmp_table_size / max_heap_table_size variables if needed to lower the memory used.
Do you have 2GB MEMORY tables? Do you have lots of SELECTs that need tmp tables? Those two table_sizes you mentioned are quite high – Each SELECT that needs a tmp table will allocate 2GB. If you happened to run a bunch of such queries simultaneously, they could have eaten up the memory. (No, I can’t say for sure that this is the cause.) Recommend lowering GLOBAL tmp_table_size.
If you could post SHOW VARIABLES and SHOW GLOBAL STATUS, we may be able to find more clues.
I agree with Scott that the slowlog is a good place to look – a query that is a memory hog would also be a slow. Please provide EXPLAIN SELECT… and SHOW CREATE TABLE when posting the queries for critique.