Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
Which buffer pool? I'm not clear on what you mean. 90% of RAM assigned to the buffer pool. –  user2732180 May 12 at 8:48
    
OK, I guess you mean the InnoDB buffer pool (and not the (MyISAM) buffers). Can you explain what you mean with "crash"? Is the service restarted? Do you see errors in the error log? What is the setting of innodb_buffer_pool_size? How much RAM does the machine have? How much memory does the OS show that MySQL is using? –  ypercube May 12 at 8:55

2 Answers 2

First, take a look at the InnoDB Architecture

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.

share|improve this answer
    
Thanks Rolando for the detailed response. You mentioned no one you know of has crashed MySQL this way. Could having too much RAM become an issue? We're playing with .5TB. I know that's counter intuitive, but it seems strange almost like clockwork it crashes when it approaches 90% full. - In the meantime, I'll try your suggestions. –  user2732180 May 16 at 23:51

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.

share|improve this answer
    
Thanks for the formatting love! –  Morgan Tocker May 12 at 21:06
    
Sorry for the late response! - My understanding is 90% is fine as long as you have enough memory. On this sandbox we have .5TB of RAM. –  user2732180 May 16 at 23:45
    
It might be OK, but it still seems high. Check /var/log/messages etc to see OOM killer messages. –  Morgan Tocker May 18 at 14:40

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.