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 :)