Have a look at the InnoDB Architecture from Vadim Tkachenko
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
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
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
- Reduce max_connections in half
- Reduce the sizes of your per-connection buffers
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.