I found out that my MySQL server is configured with only 128MB for innodb_buffer_pool_size. That is not enough considering that I have a 8GB RAM server. It hosts MySQL server and an Apache/Catalina server. What should I change this seeting to?
asked Oct 25, 2014 at 22:03
-
how much data are in InnoDB ? select SUM(INDEX_LENGTH + DATA_LENGTH) from information_schema.tables WHERE Engine = 'Innodb';– akuzminskyCommented Oct 25, 2014 at 22:49
Add a comment
|
2 Answers
I have four(4) old posts to demonstrate how to measure and size up the InnoDB Buffer Pool based on the amount of RAM on the DB Server and how much space is currently being taken up by data/index pages:
Apr 14, 2011: What are the main differences between InnoDB and MyISAM?Oct 22, 2012: How much memory do I need for innodb buffer pool?Oct 22, 1012: How large should be mysql innodb_buffer_pool_size?Apr 08, 2013: MySQL performance impact of increasing innodb_buffer_pool_size
Please look over these posts, run the queries, and use good judgment. Why did I say to use good judgment ? If the ideal Buffer Pool Size is greater than 6GB (75% of your DB Server's RAM), the highest innodb_buffer_pool_size permissible would be 6G.
Give it a Try !!!
answered Oct 26, 2014 at 0:28
-
Since it is hosting other stuff, 6GB is dangerously high. Start with 5GB. Swapping is terrible for MySQL performance. Commented Dec 21, 2019 at 19:11
It is the default setting. You can modify it not higher than your memory can handle. Depends on the usage, set it to 70% of your memory might be good for a start.
