Recently, we faced severe & unrecoverable corruption of database (a bug from MySQL 8.0.27, but only hit us 2 years in).
So we upgraded to MySQL 8.0.37, and everything's good so far. But there's a change that we did not notice it happen during 8.0.27.
Occasionally, MySQL restarts due to OOM (Signal 9 killed MySQL). Does anyone know if we're missing anything or misusing any flags?
Little info on my server, LAMP stack, Ubuntu 20.04 with 128GB RAM (was 64GB last time during 8.0.27, we doubled it now) and below is my config (it's very old, passed down since 10 years ago (since MySQL 5 days) and we always maintain/replicate the same config since it just "works".
[mysqld]
innodb_print_all_deadlocks = 1 #added on 1 sep 2022, want to get all deadlocks
disable_log_bin #added on 26 jul 2022, not tested. To not create bin.log files
bind-address = 0.0.0.0
mysqlx-bind-address = 127.0.0.1
symbolic-links=0
sql-mode = "NO_ENGINE_SUBSTITUTION"
key_buffer_size = 16M
myisam-recover-options = BACKUP
innodb_buffer_pool_size = 48G #it was 64G, but causing OOM, changed to 48G.
innodb_log_file_size = 6G
innodb_buffer_pool_instances = 32
innodb_autoinc_lock_mode = 2
interactive_timeout = 120 #to prevent too many sleep in processes
wait_timeout = 120
max_execution_time = 60000 #60s, its in ms
innodb_flush_log_at_trx_commit = 0 #commit once/per sec instead of each line, faster performance might lose last 1 sec data. but no diff in test
sync_binlog = 0
max_allowed_packet = 64M
max_connections = 512
innodb_io_capacity = 1000
innodb_io_capacity_max = 2000
optimizer_switch = derived_merge=off #dont know what is this, but it was here since forever.
skip-name-resolve #something to do with connection, when connection comes from particular host, SQL does reverse DNS lookup, we don't need
Notes to mention, the only thing changed after upgrade was changing innodb_buffer_pool_size from 32G (original) to 64G (new), since we doubled the RAM. But now we tweaked to 48GB in hope to solve the OOM issue, but still seeking for help on why did it happen now, but not previously.
Thanks in advance.
Below attached is my HTOP on normal usage period as well as SQL Tuner's response (which states I have sufficient RAM for current configurations)
