How to find whether mysql database my key_buffer and innodb_buffer_pool contending

Question

Server infrastructure:
I'm having a centos server running nginx + wordpress + mysql.

Problem:
mysql frequently getting shutodown and wordpress showing the "cannot connect to database" alerts.

Problem found:
Seems the problem was due to innodb_buffer_pool size.

Analysis:
After checking logs and reading about the buffer pool, i understood that if i'm using mixed storage engines ( myISAM and innodb). Indeed, its true that i have been using the both engines.

Questions:

1. How to find if mysql buffer's contending each other during working?
2. If possible to find using some methodology, can it be automated by sending email/sms?

Answer 1

Short Answer

• MyISAM only caches indexes from .MYI files.
• InnoDB caches data and index pages (InnoDB Page is 16K)

Its not possible for the key buffer and the InnoDB Buffer Pool to compete unless there are memory allocation issues outside of mysqld (usually due to having a lot of DB Connections where each DB Connection allocates a lot of memory for its own session)

Longer Answer

See my old posts

• Apr 14, 2011 : What are the main differences between InnoDB and MyISAM?
• Apr 24, 2012 : How costly is opening and closing of a DB connection?