Score:1

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

cn flag

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

ua flag
Is a regular backup running at that time of night? Did you make changes to `my.cnf`? What are the settings in it?
gallo2000sv avatar
cn flag
No bakcups scheduled. Just updated my question with my.cnf file content and kernel log. Thanks
cn flag
The mysqld process was using about 286MB RSS when it was killed. However you have 2GB of RAM. Do you use a VM or a container? Can you add to your question the output of `cat /proc/meminfo` ? If you run `ps -aux --sort -rss|head -5` you can see the top 5 most memory consuming processes. Do you use huge pages?
ua flag
`my.cnf` looks OK, but please add `innodb_buffer_pool_size = 150M` -- This is in case it is somehow defaulting to too big a value.
gallo2000sv avatar
cn flag
Updated cat /proc/meminfo and ps -aux --sort -rss|head -5 and will increase innodb_buffer_pool_size It's a Digital Ocean Droplet i think is a Virtual Machine
gallo2000sv avatar
cn flag
still crashing after adding innodb_buffer_pool_size = 512M to my config file. Updated my question
Wilson Hauck avatar
jp flag
gallo2000sv, 512M on my calculator indicated you would expect 536870912 for your requested innodb_buffer_pool_size which would be 32768 pages at 16384 bytes per page. Display reported within SHOW ENGINE INNNDB STATUS; reported you do have 'Buffer pool size 32765' the report does not indicate this is PAGES of data. Not a significant difference considering the scope of complexity.
Wilson Hauck avatar
jp flag
gallo2000sv, You also asked what is - innodb_change_buffer_max_size | 25 - . This is the percentage of innodb_buffer_pool_size SET ASIDE for change management to accomplish all the details of getting your data stored to your table(s) when required for a row. When you ask for 50% SET ASIDE, expect 1/2 of your declared space to be reserved for change management (and this does improve performance when inserts are active). Consider Google search for 'MySQL innodb_change_buffer_max_size tutorial' for another explanation.
Wilson Hauck avatar
jp flag
@gallo2000sv If you are still crashing, consider opening a new Question. And post to pastebin.com TEXT results of SHOW GLOBAL VARIABLES; and SHOW GLOBAL STATUS; after minimum of 24 hours of UPTIME.
Wilson Hauck avatar
jp flag
@gallo2000sv How are you doing at this time? If you could post the data requested on March 3, 2022, a workload analysis for your instance would be performed to provide performance tuning suggestions for your current workload.
Score:0
cn flag

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

ua flag
There about 1000 variables and status values. See http://mysql.rjweb.org/doc.php/mysql_analysis#tuning for a more thorough analysis.
Wilson Hauck avatar
jp flag
@gallo2000sv A good start. With experience you will find that many more variables are important depending on workload. Keep an open mind and watch as usage grows consider how your RATE PER SECOND is influenced with more connections.
Wilson Hauck avatar
jp flag
@gallo2000sv You have more than one line used in your configuration for the same variable name. The last one through wins, usually. Keep it clean and do not have duplicates to improve our perception of your skills. Tip, occasionally (every 90 days) sort your configuration and look for dups and clean them up.
gallo2000sv avatar
cn flag
Thanks guys. Will keep an eye on this :)
mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.