Buffer pool above 90% utilized causes MySQL to crash

Question

Without getting deep into the architecture and setup, is it common behavior for MySQL to act in this manner?

EDIT

I'm using Rolando's script to identify how full the innodb_buffer_pool is.

SELECT FORMAT(A.num * 100.0 / B.num,2) BufferPoolFullPct FROM
(SELECT variable_value num FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_data') A,
(SELECT variable_value num FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_total') B;

Thanks

Answer 1

First, take a look at the InnoDB Architecture

Look closely at the Buffer Pool in the upper left corner.

There is an Insert Buffer inside it. It is responsible for migrating changes to nonunique indexes from the Buffer Pool to the Insert Buffer inside the system tablespace (better known to us commoners as ibdata1).

When InnoDB tables have lots of secondary indexes, heavy INSERTs, UPDATEs, and DELETEs to those tables will trigger growth and congestion in the Buffer Pool's Insert Buffer.

The setting innodb_change_buffer_max_size governs what percentage of the Buffer Pool is to be use for the Insert Buffer. The default is 25. That means if you allocate 24G for innodb_buffer_pool_size, don't be shocked if the Insert Buffer uses 6G of it.

When a Buffer Pool is 90% full of data, it's only good for doing heavy SELECTs (when doing reports and analytics). In your particular case, since you ran that query and determined that 90% of the Buffer Pool had data, that leaves only 10% for the Insert Buffer and other InnoDB incidentals. It is entirely possible that a loading of a mysqldump could push the limits of a Buffer Pool that has an insufficient amount of room for its Insert Buffer. Personally, I have never heard anyone complain about InnoDB crashing MySQL because of this.

For the sake of erring on the side of caution, perhaps you may want to try setting innodb_change_buffer_max_size to 50 when doing heavy INSERTs with

SET GLOBAL innodb_change_buffer_max_size = 50;

and scaling it back to 25 with

SET GLOBAL innodb_change_buffer_max_size = 25;

afterwards.

Incidentally, you could set it with

SET GLOBAL innodb_change_buffer_max_size = 10;

If you are not doing any heavy INSERTs for days or weeks. Just remember to set it back to 25.

Answer 2

90% of memory available is too high for the buffer pool. The MySQL manual even says up to 80% + please allow for 10% additional overhead:

The larger you set this value, the less disk I/O is needed to access the same data in tables more than once. On a dedicated database server, you might set this to up to 80% of the machine physical memory size. Be prepared to scale back this value if these other issues occur:

• Competition for physical memory might cause paging in the operating system.

• InnoDB reserves additional memory for buffers and control structures, so that the total allocated space is approximately 10% greater than the specified size.