MySql 8 crashing on loop without specific errors and with enough memory

Question

I'm running Ubuntu 20.04.03 with Mysql 8.0.27. I have reinstalled LAMP from scratch several times and at the moment I only have 3 WordPress sites but I have tested only 1 and only 2 sites as well. Increased the RAM to 2GB and 3GB Swap.

Nothing seems to work because Mysql 8.0.27 keeps crashing causing a database connection problem in every site every night even when this are totally new sites without any traffic. When i'm editing a post or even browsing through any of those websites MySql crash again. Sometimes it won't even start with systemctl restart mysql.

Apache Error log is not showing anything important:

/usr/sbin/mysqld (mysqld 8.0.27-0ubuntu0.20.04.1) starting as process 1524639

InnoDB initialization has started.

InnoDB initialization has ended.

Starting XA crash recovery... XA crash recovery finished.

A deprecated TLS version TLSv1 is enabled for channel mysql_main

A deprecated TLS version TLSv1.1 is enabled for channel mysql_main

CA certificate ca.pem is self signed.

Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.

X Plugin ready for connections. Bind-address: '127.0.0.1' port: 33060, socket: /var/run/mysqld/mysqlx.sock

I already checked that in fact the config is loading all the TLS versions as acceptable. So no real error there.

journalctl -u mysql Last journal log:

Jan 28 10:29:37 www.ignicion.org systemd[1]: mysql.service: Failed with result 'signal'.
Jan 28 10:29:37 www.ignicion.org systemd[1]: mysql.service: Scheduled restart job, restart counter is at 5.
Jan 28 10:29:37 www.ignicion.org systemd[1]: Stopped MySQL Community Server.
Jan 28 10:29:37 www.ignicion.org systemd[1]: Starting MySQL Community Server...
Jan 28 10:29:43 www.ignicion.org systemd[1]: mysql.service: Main process exited, code=killed, status=9/KILL
Jan 28 10:29:43 www.ignicion.org systemd[1]: mysql.service: Failed with result 'signal'.
Jan 28 10:29:43 www.ignicion.org systemd[1]: Failed to start MySQL Community Server.
Jan 28 10:29:43 www.ignicion.org systemd[1]: mysql.service: Scheduled restart job, restart counter is at 6.
Jan 28 10:29:43 www.ignicion.org systemd[1]: Stopped MySQL Community Server.
Jan 28 10:29:43 www.ignicion.org systemd[1]: Starting MySQL Community Server...
Jan 28 10:29:50 www.ignicion.org systemd[1]: Started MySQL Community Server.

I know it's a memory issue but why? Is not like the server is doing so much. I have been monitoring process and Mysql is the one consuming all memory. This same new databases was working fine on ubuntu 18 so I'm really out of ideas and can't find any solution to related issues that i have found on the forum. Some solutions i have found are talking about database/table corruption but this are brand new databases and hardware malfunction was discarded by the Digital Ocean Support.

I'll appreciate any insights

Update #1 There is no backup or any other task scheduled. It just a brand new installation and new databases. This is my config file in ubuntu 20: /etc/mysql/mysql.conf.d

[mysqld]
user            = mysql
bind-address            = 127.0.0.1
mysqlx-bind-address     = 127.0.0.1
key_buffer_size         = 16M
myisam-recover-options  = BACKUP
log_error = /var/log/mysql/error.log
max_binlog_size   = 100M
innodb_file_per_table = 1

And from my Kernel log file (tail -100 /var/log/kern.log) it seems like the SIGKILL 9 Term Kill signal signal is killing mysql because of too much memory usage

Jan 28 18:12:26 www kernel: [623011.971582] oom-kill:constraint=CONSTRAINT_NONE, 
nodemask= (null),cpuset=/,mems_allowed=0,global_oom,task_memcg=/system.slice/mysql.service,task=mysqld,pid=1669785,uid=113

Jan 28 18:12:26 www kernel: [623011.971617] Out of memory: Killed process 166978 
5 (mysqld) total-vm:720836kB, anon-rss:292028kB, file-rss:804kB, shmem-rss:0kB, 
UID:113 pgtables:816kB oom_score_adj:0

Jan 28 18:12:26 www kernel: [623012.005506] oom_reaper: reaped process 1669785 (mysqld), now anon-rss:0kB, file-rss:0kB, shmem-rss:0kB

So I was reading that i should check Mysql buffers configuration and that's where I'm at the moment.

UPDATE #2 cat /proc/meminfo

MemTotal:        2030808 kB
MemFree:           54016 kB
MemAvailable:       3424 kB
Buffers:             240 kB
Cached:           285620 kB
SwapCached:        44532 kB
Active:          1188660 kB
Inactive:         495868 kB
Active(anon):    1187984 kB
Inactive(anon):   495088 kB
Active(file):        676 kB
Inactive(file):      780 kB
Unevictable:       19120 kB
Mlocked:           19120 kB
SwapTotal:       3145724 kB
SwapFree:              0 kB
Dirty:                 0 kB
Writeback:             0 kB
AnonPages:       1383352 kB
Mapped:           284872 kB
Shmem:            276064 kB
KReclaimable:      47968 kB
Slab:             171388 kB
SReclaimable:      47968 kB
SUnreclaim:       123420 kB
KernelStack:        7744 kB
PageTables:        59832 kB
NFS_Unstable:          0 kB
Bounce:                0 kB
WritebackTmp:          0 kB
CommitLimit:     4161128 kB
Committed_AS:    9186644 kB
VmallocTotal:   34359738367 kB
VmallocUsed:       16512 kB
VmallocChunk:          0 kB
Percpu:             1808 kB
HardwareCorrupted:     0 kB
AnonHugePages:         0 kB
ShmemHugePages:        0 kB
ShmemPmdMapped:        0 kB
FileHugePages:         0 kB
FilePmdMapped:         0 kB
CmaTotal:              0 kB
CmaFree:               0 kB
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB
Hugetlb:               0 kB
DirectMap4k:     1335276 kB
DirectMap2M:      761856 kB

ps  -aux --sort -rss|head -5

USER         PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
mysql    1715614  6.7 18.8 1763392 383344 ?      Ssl  22:21   0:01 /usr/sbin/mysqld
aceitep+ 1686006  0.0  2.0 342620 41076 ?        S    19:44   0:02 /bin/php-cgi7.4
aceitep+ 1686009  0.0  1.9 265576 39268 ?        S    19:44   0:02 /bin/php-cgi7.4
aceitep+ 1686001  0.0  1.8 342152 38348 ?        S    19:44   0:04 /bin/php-cgi7.4

And since i thought it was related to some mysql variables i ran MySql Tunner and this was the recomendation:

Variables to adjust:
    innodb_buffer_pool_size (>= 391.3M) if possible.
    innodb_log_file_size should be (=16M) if possible, so InnoDB total log files                                                                              size equals to 25% of buffer pool size.

So i'll test to increase this and will post the results.

UPDATE #3 Added innodb_buffer_pool_size = 512M to my /etc/mysql/mysql.conf.d/mysqld.cnf file and still crashing. Logs still the same. :(

Is it normal that my Total Large memory allocated to be zero when i run Show Engine InnoDB status;

----------------------
BUFFER POOL AND MEMORY
----------------------
**Total large memory allocated 0**
Dictionary memory allocated 1009720
Buffer pool size   32765
Free buffers       29298
Database pages     3405
Old database pages 1276
Modified db pages  0

And what about when i run show variables like 'innodb_%';

innodb_buffer_pool_size           | 536870912
innodb_change_buffer_max_size    | 25

Why the buffer pool size does not match and what this innodb_change_buffer_max_size does means? What other variables should i check? Thanks again guys

Answer 1

ok. Solved now. There is not an specific solution for this kind of behaviour since the kernel it's killing mysql because of using too much RAM and when you try to find why, there are no specific reasons. So the only thing you can do is try to play with the variables and test a lot. What I learnt:

• I my short experience i didn't know that Myslq config file (/etc/mysql/mysql.conf.d/mysqlconf.d on Ubuntu 20.04) doesn't show much variables on it since these variables are set by default so if we want to set a different value we have to manually add each variable on the config file.

• Install and run mysql tuner (google it) so it could point where to start for your specific settings. In my case it suggested to increase innodb_buffer_pool_size. And that the innodb_log_file_size should be equal to 25% of the innodb_buffer_pool_size value for optimal performance. eg innodb_buffer_pool_size = 1Gb innodb_log_file_size = 0.25Gb

• I set innodb_buffer_pool_size = 512M since i have 2GB RAM. This just made the server to last a bit longer before crashing again. So that's where you have to start to learn more about each Mysql variables and make calculations depending on your databases size.

Here is my final Mysql config file so you're free to try this configuration. Just know that my databases size where 394MB at the moment:

[mysqld]
skip-log-bin
user            = mysql
bind-address            = 127.0.0.1
mysqlx-bind-address     = 127.0.0.1
myisam-recover-options  = BACKUP
log_error = /var/log/mysql/error.log
general_log = on
general_log_file=/var/log/mysql/general.log

key_buffer_size                 = 1M
max_allowed_packet              = 1M
thread_stack                    = 200K
thread_cache_size               = 8
max_connect_errors              = 100
max_connections                 = 100
#table_cache                    = 64
#thread_concurrency             = 30
binlog_cache_size               = 1M
net_buffer_length               = 1M

default_storage_engine          = InnoDB
innodb_buffer_pool_instances    = 1 # Use 1 instance per 1GB of InnoDB pool size
innodb_buffer_pool_size         = 400M   # Use up to 70-80% of RAM
innodb_file_per_table           = 1
innodb_flush_log_at_trx_commit  = 0
innodb_flush_method             = O_DIRECT
innodb_log_buffer_size          = 16M
innodb_log_file_size            = 16M
innodb_stats_on_metadata        = 0
#innodb_thread_concurrency      = 0
innodb_read_io_threads          = 40
innodb_write_io_threads         = 40
innodb_buffer_pool_chunk_size   = 10
innodb_lock_wait_timeout        = 600
innodb_flush_log_at_trx_commit  = 1
innodb_io_capacity              = 3000
innodb_io_capacity_max          = 4000
innodb_buffer_pool_dump_pct     = 80
innodb_flush_neighbors          = 0
innodb_doublewrite              = 0
innodb_change_buffer_max_size   = 10
innodb_old_blocks_pct           = 70
innodb_old_blocks_time          = 5000
innodb_use_native_aio           = ON

# The number of seconds the server waits for activity on an interactive connection before closing it.
interactive_timeout             = 600

# The number of seconds the server waits for activity on a noninteractive connection before closing it.
wait_timeout                    = 600
net_read_timeout                = 300
net_write_timeout               = 300
connect_timeout                 = 1800

# Table Settings
table_definition_cache          = 1K
table_open_cache                = 2K
table_open_cache                = 2K
open_files_limit                = 4000

max_heap_table_size             = 100M
tmp_table_size                  = 100M

#
# * Query Cache Configuration
#
#query_cache_limit               = 1M
#query_cache_size                = 100M
#query_cache_size                = 0
#query_cache_type                = 0



# Buffer Settings
join_buffer_size                = 256K
read_buffer_size                = 256K
read_rnd_buffer_size            = 256K
sort_buffer_size                = 128K
performance_schema              = ON

If my databases size increase with time (they surely will) I'll have to increase this values:

key_buffer_size = 1M
max_connections = 100
innodb_buffer_pool_size = 400M
join_buffer_size = 256K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
sort_buffer_size = 128K

I could increase innodb_buffer_pool_size to 1Gb but that will mean I'll have to decrease other variables and test again in order to test the best performance settings. (how i said, if you don't know, a lot of research is needed about these variables) In other hand I could just let this same values even if my databases increases with time but Mysql will start to take more Swamp memory so querys will be a bit slower with time.

And then again, you could just increase RAM on your server if you can afford it.

Hope this helps anybody. I'm not experienced so i'm posting this answers for my futures reference as well :)