Mysql – Lowering MySQL Memory Usage without Restart

cacheinnodbmemoryMySQL

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

we tried RESET QUERY CACHE but it didn't help

We came across this post https://serverfault.com/questions/238…ear-the-buffer
where it says we can use

innodb_max_dirty_pages_pct=0;

to clear the buffer. We would like to know whether its safe to use in Prodution enviorment( no data loss) ?

Does it clear the clear memory ?

does it require a mysql restart ?

Please advise

thanks,
Santhosh

Best Answer

Have a look at the InnoDB Architecture from Vadim Tkachenko

InnoDB Architecture

When you set innodb_max_dirty_pages_pct to 0, it just expedites dirty pages out of the buffer pool. The changed pages also exist in the Double Write Buffer inside the System Tablespace (ibdata1).

Even if mysqld crashes, the crash recovery cycle is initiated during mysqld's start up. Thus, no need to worry about data loss.

Does it clear the clear memory ?

No, it does not clear memory. It simply sends the pending changes (data pages and index pages) from the Buffer Pool into the Double Write Buffer and then to the .ibd files.

does it require a mysql restart ?

No. Login to MySQL and run

mysql> SET GLOBAL innodb_max_dirty_pages_pct = 0;

Please note that this clearing of the buffer pool happens on MySQL shutdown anyway. Setting innodb_max_dirty_pages_pct to 0 will keep the buffer pool as clean as possible. Doing this about 5 minutes before doing an actual shutdown will allow for a faster shutdown without changing anything else.

If you want the setting to be permanent, add this to my.cnf

[mysqld]
innodb_max_dirty_pages_pct = 0

You can do this in a production environment and it would be safe if your have really good disks. If the Disks are slow, you will see increased I/O. If you have a high-write application, you should leave it set to the default value of 75. (If you use MySQL 5.1, the default is 90).

OTHER THINGS TO TRY

Drop the Query Cache and Disable Query Cache Mutexing

mysql> SET GLOBAL query_cache_size = 0;
mysql> SET GLOBAL query_cache_type = 0;

From my post https://dba.stackexchange.com/questions/65888/recommendation-for-mysql-please-mysqltuner/65928#65928

GIVE IT A TRY !!!

CAVEAT : If you change any of these settings and you want them permanent, add them to my.cnf so that future restarts of mysqld will have the setting in place.

Related Question