How to calculate innodb_buffer_pool_size value

Run to the following command to start calculation:

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;

Example:

mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 60295069
Server version: 10.3.34-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Read the rest

How to enable Slow Query Log

Append into /etc/my.cnf the following:

slow_query_log = 1
slow-query_log_file = /var/log/mysql-slow.log
long_query_time = 2

Create /var/log/mysql-slow.log log file and set it as mysql user:

touch /var/log/mysql-slow.log
chown mysql:mysql /var/log/mysql-slow.log

Restart MySQL server:

/scripts/restartsrv_mysql

Monitor the logs using mysqldumpslow command:

mysqldumpslow -a /var/log/mysql-slow.log
Read the rest