3

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?
zx485
  • 2,170
  • 11
  • 17
  • 24
MohanBabu
  • 143
  • 6
  • Don't forget about http://dba.stackexchange.com/ – Vomit IT - Chunky Mess Style Jan 21 '17 at 04:04
  • This is a frequently-encountered problem, and isn't actually a problem with MySQL. MySQL is the victim of Apache being a memory hog. http://dba.stackexchange.com/a/25171/11651 – Michael - sqlbot Jan 21 '17 at 14:47
  • * Im using nginx. Are you facing the same problem in your setup with apache? @Michael-sqlbot – MohanBabu Jan 21 '17 at 14:53
  • 1
    No, I'm not experiencing it. I always run MySQL on a machine by itself and stay as far away from MyISAM as possible. If you have found a solution, please post that as an answer, explaining what you found, and how future visitors might use it to solve the same issue. – Michael - sqlbot Jan 21 '17 at 20:33

1 Answers1

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

Community
  • 1
RolandoMySQLDBA
  • 3,065
  • 1
  • 20
  • 25