Maintenance for ERPNext Performance

ERPNext
14

Okay the default value isn’t enough. I tried like this from https://dba.stackexchange.com/a/27341:

Start mysql as root

mysql -u root -p

Then execute this query:

SELECT CONCAT(CEILING(RIBPS/POWER(1024,pw)),SUBSTR(' KMGT',pw+1,1))
Recommended_InnoDB_Buffer_Pool_Size FROM
(
    SELECT RIBPS,FLOOR(LOG(RIBPS)/LOG(1024)) pw
    FROM
    (
        SELECT SUM(data_length+index_length)*1.1*growth RIBPS
        FROM information_schema.tables AAA,
        (SELECT 1.25 growth) BBB
        WHERE ENGINE='InnoDB'
    ) AA
) A;

This will show you recommended size for innodb_buffer_pool_size. G for Gigabytes and M for Megabytes

Now, before making any changes please take a backup of your my.cnf file.

Then copy and paste this at the bottom of your my.cnf or include these in the existing mysqld section that is mentioned at the bottom:

[mysqld]
innodb_buffer_pool_size=SIZE
innodb_buffer_pool_instances=NUMBER

Replace the pool size value by the recommended value from the query. If the recommended pool size is higher than or equal to 2G, select an adequate value for innodb_buffer_pool_instances such that each pool size is atleast 1G.

Then restart your mariadb service.

The above pool memory will be reserved from RAM as far as i know. Hence please take care that you don’t allow too much of RAM to be used for database.

Again, there surely are other ways to improve performance, but I know this one. Please remember this value will need to be updated when your database grows. Once set, this should reduce the Disk I/O.

10 Likes