What is InnoDB Buffer Usage and how to clear it?

Question

In Mysql Workbench when I go in Server > Server Status there is a field called "InnoDB Buffer Usage" It is "95%" even if there is no query running. Why? It affects my performance in my new queries? There is a way to clear it?

Answer 1

Assuming that your dataset is larger than your buffer pool, having it at 95% usage is not only normal, but a desired state. You want as much information as possible on memory- hardware and resources are there to be used- so that both next reads and writes can be done faster than having to access disk.

A different thing is if that memory usage is being used effectively or not: if your read hit ratio is high and if you can buffer writes effectively, which you can check by observing your number of disk IOPS (by querying InnoDB and OS statistics) and buffer pool content (available on the information_schema tables).

If you server is idle, do not worry about memory usage (again, assuming your dataset is larger than your buffer pool and that your buffer pool is smaller than your physical memory)- the buffer pool will fill naturally as you read and write data; and it will be use more or less as a more complex version of an LRU policy (data used less will be evicted first). Unless you want to use that memory for something else, because it is not a dedicated server (so, lower your buffer pool usage), most people will envy you as having the buffer pool fully utilized is a desired state.

Please note that most operating systems will cache (files) in the same way- unless memory is used for other things, they will try to fit as much data as possible in memory.

Restarting the server will usually clear most of the buffer pool unless configured to be preloaded.