How to increase innodb_buffer_pool_size?

1

How to increase innodb_buffer_pool_size step by step procedure? im not familiar with this.

2

This link maybe helpful

3

I’m providing you the steps but first please read the link as mentioned in Pawan’s reply. It will help you understand what buffer_pool will mean for ERPNext performance.

  1. Find your mysql cnf file - Most probably located in /etc/mysql/

  2. Find innodb_buffer_pool_size and change it to the required size.

  3. Also, set innodb_buffer_pool_instances such that each pool is of equal size. e.g. to set pool size to 2GB write innodb_buffer_pool_size=2G, then we divide pool into equal size parts by adding innodb_buffer_pool_instances=2

  4. Save the file.

  5. Restart MySQL service (without restart settings won’t take effect)

For more information on what size to choose, please read answers on Stack Exchange because it differs for everybody. You don’t want to allocate too much or too less. There are many such answers which will educate you on that.

1 Like
4

hi @Pawan and @root13F thank you for your response but the innodb_buffer_pool_size is not editable ? how can i edit this?

image
image724×442 49.9 KB

5

Simply replace 256M by 2G and save it. Look at the shortcut help in the bottom of the editor.

FIrst open this file as root in editor, then edit and save. Also, restart mysql.

6

thanks! i already edited the buffer pool @root13F last question how can improve my settings to be more faster my system?

7

Hey very good.

Now, this is something I cannot exactly answer because I don’t know much about architecture. One thing is for sure, that this will help because most of your tables and indexes will stay in cache. One basic point to understand is, most of the times when disk will be in usage CPU will get hogged. Hence to reduce disk usage this cache will help. Normally, this cache can be 1.5 or 2 times the size of your database and it should do good. Also, I think this cache size will have to adjusted periodically depending on how fast the database grows.

I recommend you to read more about innodb cache on Stack Overflow, lots of experienced people have given their views about it.

8

Thanks a lot @root13F you may closed this topic.