RAM recommedation for Mysql DB server

Question

We are dealing with large dataset and currently database size is 400 GB. Lately logs are showing error "Out of memory" and we are trying to fix the same.

Largest table size exists in database approx 25 GB size.

Database server details

DB Version: 10.3.27-MariaDB
RAM: 90 GB
Disk space: 600 GB
Tmp space: 110 GB
Architecture: x86_64
CPU(s): 6
Thread(s) per core: 1
CORE(s) per socket: 1
Socket(s): 6
CPU family: 6
Model: 63
Model name: Intel Xeon Gold 6140 CPU 2.3 GHz
L1d cache: 32k
L1i cache: 32k
L2 cache: 1024K
L3 cache: 25344K

my.cnf

[mysqld]
ignore-db-dir=lost+found
max_allowed_packet=1G
innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=74G
tmp_table_size=28G
max_heap_table_size=28G
max_connections=1000
interactive_timeout=3600
wait_timeout=3600
sql_mode='ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

After some research , found below SQL and ran it. Looks like atleast 112 GB RAM required as per of current my.cnf configuration. Reference https://www.singlestore.com/blog/mysql-error-out-of-memory/

SELECT @@innodb_buffer_pool_size + @@innodb_log_buffer_size + @@tmp_table_size + (1000 * 
(@@sort_buffer_size + @@read_buffer_size + @@join_buffer_size +@@binlog_cache_size)); 
//Output 111 GB

Erorr in log file

Out of memory: Kill process 44553 (mysqld) score 971 or sacrifice child
Killed process 44553 (mysqld), UID 27, total-vm:97221704kB, anon-rss:90273608kB, file-rss:0kB,     shmem-rss:0kB
mariadb.service: main process exited, code=killed, status=9/KILL
Unit mariadb.service entered failed state.
mariadb.service failed.
mariadb.service holdoff time over, scheduling restart.
Stopped MariaDB 10.3.27 database server.
Starting MariaDB 10.3.27 database server...

We are ok to increase the RAM but didn't get any reference point for RAM recommendation for similar details.

Question: What would be recommendation RAM size for above configuration ?

Answer 1

You are mixing two things; first, let me separate them:

Disk space:

• database size is 400 GB
• Disk space: 600 GB & Tmp space: 110 GB
• Largest table size exists in database approx 25 GB size.

So...

• 600 - 400 - 110 = 90GB for OS, etc. It is low, but not deadly.
• If you need to ALTER the largest table, 25GB will be temporarily chewed up.

Memory (RAM):

• error "Out of memory"
• RAM: 90 GB
• innodb_buffer_pool_size=74G (good)
• tmp_table_size and max_heap_table_size=28G -- Much too large; keep them under 1% of RAM.
• SELECT @@... = 111GB -- This formula (and all similar formulas) is flawed. Ignore it.

So...

• Change that pair of settings to avoid "Out of Memory".