Question increasing innodb_buffer_pool_size & memory bottleneck

[stas styler]

Dear pleskers!

Love your forum and platform. Plesk is my main server manager and I totally love it.
Unfortunately there are some things that are different when considering plesk installed.

I got a new server with couple of websites on it (im providing hosting).
It hosts big woo commerce websites with alot of traffic & a alot of queries probably because they import & export products.

I'm afraid the server is bottlenecked because of the mysql (maria db).
These are the specs of one of our servers (it contains a webserver + bind + reversed NGINX + mariadb on one server):
AMD EPYC 7401P
24-Core "Naples" (Zen)
Simultaneous Multithreading
128GB Ram DDR4 ECC RAM
1TB NVME SSD

MariaDB

• Server: Localhost via UNIX socket
• Server type: MariaDB
• Server version: 10.1.30-MariaDB - MariaDB Server
• Protocol version: 10
• User: admin@localhost
• Server charset: UTF-8 Unicode (utf8)

web server

• nginx/1.11.10
• Database client version: libmysql - mysqlnd 5.0.11-dev
  
• PHP extension: mysqli curl mbstring
• PHP version: 5.6.30
• centos 7.4

I want to increase the
innodb_buffer_pool_size because it is set to 128 MiB.
I'm afraid that maybe it bottlenecks it.
last time i did it was in my.cnf file and it broke the engine... (IDK why).
Is there the right way to do this? I hope you guys can help me out, there is no way with this kind of server one of my websites runs pretty slow.

 

[IgorG]

What is the output of commands:

# plesk db
mysql> show variables like 'innodb_buffer_pool%';

You will see current value of innodb_buffer_pool_size with this command.
Also, it is possible to count Recommended InnoDB Buffer Pool Size based on all InnoDB Data and Indexes with an additional 60% using the following MySQL query:

mysl> SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM (SELECT SUM(data_length+index_length) Total_InnoDB_Bytes FROM information_schema.tables WHERE engine='InnoDB') A;

+-------+| RIBPS |
+-------+
| 1 |
+-------+
1 row in set (0.31 sec)

For example, with this output, you would set innodb_buffer_pool_size to 1G in my.cnf configuration file.

 

[stas styler]

What is the output of commands:

# plesk db
mysql> show variables like 'innodb_buffer_pool%';

You will see current value of innodb_buffer_pool_size with this command.
Also, it is possible to count Recommended InnoDB Buffer Pool Size based on all InnoDB Data and Indexes with an additional 60% using the following MySQL query:

mysl> SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM (SELECT SUM(data_length+index_length) Total_InnoDB_Bytes FROM information_schema.tables WHERE engine='InnoDB') A;

+-------+| RIBPS |
+-------+
| 1 |
+-------+
1 row in set (0.31 sec)

For example, with this output, you would set innodb_buffer_pool_size to 1G in my.cnf configuration file.

Wow this is awesome! Thanks.

Do you know by any chance where is it located? (im using mariadb)
If i recall its path is a bit different from the usual path?

After I set it do I need systemctl restart mariadb?

 

[IgorG]

All setting should be located in my.cnf
Common MariaDB recommendations about innodb_buffer_pool_size adjusting you can find here MariaDB Memory Allocation

 

[Gabor H]

You can fine tune your database with mysqltuner perl script. Google for that.
Sometime it's a big help, and you can sort out the bottlenecks very easy.