MySql crashes randomly Out of memory: Kill process 24978 (mysqld) score 99 or sacrifice child

Question

I am running a LEMP server. 2Core Processor, 4GB RAM on Hetzner Cloud. Ubuntu 18.04, PHP7.4, MySql 5.7, Nginx 1.16

But my MySql server randomly gets crashed even when traffic is negligible. It happens 2-3 times in a month.

How to prevent this issue in future?

Here is log from journalctl -xe

See the first line, it says: Nov 23 03:48:49 ubuntu-server-18 kernel: php-fpm7.4 invoked oom-killer: gfp_mask=0x14200ca(GFP_HIGHUSER_MOVABLE), nodemask=(null), order=0, oom_score_adj=0

Is the problem caused by php-fpm ?

=============

Nov 23 03:48:49 ubuntu-server-18 kernel: php-fpm7.4 invoked oom-killer: gfp_mask=0x14200ca(GFP_HIGHUSER_MOVABLE), nodemask=(null), order=0, oom_score_adj=0
Nov 23 03:48:50 ubuntu-server-18 kernel: php-fpm7.4 cpuset=/ mems_allowed=0
Nov 23 03:48:50 ubuntu-server-18 kernel: CPU: 0 PID: 24811 Comm: php-fpm7.4 Not tainted 4.15.0-64-generic #73-Ubuntu
Nov 23 03:48:50 ubuntu-server-18 kernel: Hardware name: Hetzner vServer, BIOS 20171111 11/11/2017
Nov 23 03:48:50 ubuntu-server-18 kernel: Call Trace:
Nov 23 03:48:50 ubuntu-server-18 kernel:  dump_stack+0x63/0x8b
Nov 23 03:48:50 ubuntu-server-18 kernel:  dump_header+0x71/0x285
Nov 23 03:48:50 ubuntu-server-18 kernel:  ? security_capable_noaudit+0x4b/0x70
Nov 23 03:48:50 ubuntu-server-18 kernel:  oom_kill_process+0x21f/0x420
Nov 23 03:48:50 ubuntu-server-18 kernel:  out_of_memory+0x2b6/0x4d0
Nov 23 03:48:50 ubuntu-server-18 kernel:  __alloc_pages_slowpath+0xa53/0xe00
Nov 23 03:48:50 ubuntu-server-18 kernel:  __alloc_pages_nodemask+0x29a/0x2c0
Nov 23 03:48:50 ubuntu-server-18 kernel:  alloc_pages_current+0x6a/0xe0
Nov 23 03:48:50 ubuntu-server-18 kernel:  __page_cache_alloc+0x81/0xa0
Nov 23 03:48:50 ubuntu-server-18 kernel:  filemap_fault+0x3ea/0x6f0
Nov 23 03:48:50 ubuntu-server-18 kernel:  ? page_add_file_rmap+0x134/0x180
Nov 23 03:48:50 ubuntu-server-18 kernel:  ? filemap_map_pages+0x22c/0x390
Nov 23 03:48:50 ubuntu-server-18 kernel:  ext4_filemap_fault+0x31/0x44
Nov 23 03:48:50 ubuntu-server-18 kernel:  __do_fault+0x5b/0x115
Nov 23 03:48:50 ubuntu-server-18 kernel:  __handle_mm_fault+0xdef/0x1290
Nov 23 03:48:50 ubuntu-server-18 kernel:  handle_mm_fault+0xb1/0x210
Nov 23 03:48:50 ubuntu-server-18 kernel:  __do_page_fault+0x281/0x4b0
Nov 23 03:48:50 ubuntu-server-18 kernel:  do_page_fault+0x2e/0xe0
Nov 23 03:48:50 ubuntu-server-18 kernel:  ? async_page_fault+0x2f/0x50
Nov 23 03:48:50 ubuntu-server-18 kernel:  do_async_page_fault+0x51/0x80
Nov 23 03:48:50 ubuntu-server-18 kernel:  async_page_fault+0x45/0x50
Nov 23 03:48:50 ubuntu-server-18 kernel: RIP: 0033:0x7f3be729d140
Nov 23 03:48:50 ubuntu-server-18 kernel: RSP: 002b:00007fff1d94b888 EFLAGS: 00010206
Nov 23 03:48:50 ubuntu-server-18 kernel: RAX: 0000000000000042 RBX: 0000000000000043 RCX: 0000000000000000
Nov 23 03:48:50 ubuntu-server-18 kernel: RDX: 00007f3bcdd5c400 RSI: 0000000036393034 RDI: 0000000000000043
Nov 23 03:48:50 ubuntu-server-18 kernel: RBP: 00007f3bcdd5c400 R08: 00007fff1d94b820 R09: 0000000000000036
Nov 23 03:48:50 ubuntu-server-18 kernel: R10: 0000000000000000 R11: 0000000000000000 R12: 000055f7b0be96a0
Nov 23 03:48:50 ubuntu-server-18 kernel: R13: 0000000000000200 R14: 0000000000000001 R15: 00007f3bcdd5c400
Nov 23 03:48:50 ubuntu-server-18 kernel: Mem-Info:
Nov 23 03:48:50 ubuntu-server-18 kernel: active_anon:915548 inactive_anon:1944 isolated_anon:0
                                          active_file:97 inactive_file:107 isolated_file:1
                                          unevictable:0 dirty:5 writeback:0 unstable:0
                                          slab_reclaimable:7073 slab_unreclaimable:18771
                                          mapped:37449 shmem:47203 pagetables:11749 bounce:0
                                          free:21461 free_pcp:471 free_cma:0
Nov 23 03:48:50 ubuntu-server-18 kernel: Node 0 active_anon:3662192kB inactive_anon:7776kB active_file:388kB inactive_file:428kB unevictable:0kB isolated(anon):0kB isolated(file):4kB mapped:149796kB dirty:20kB writeback:0kB shmem:188812kB
Nov 23 03:48:50 ubuntu-server-18 kernel: Node 0 DMA free:15472kB min:272kB low:340kB high:408kB active_anon:380kB inactive_anon:0kB active_file:4kB inactive_file:8kB unevictable:0kB writepending:0kB present:15992kB managed:15908kB mlocked
Nov 23 03:48:50 ubuntu-server-18 kernel: lowmem_reserve[]: 0 2968 3803 3803 3803
Nov 23 03:48:50 ubuntu-server-18 kernel: Node 0 DMA32 free:55640kB min:52528kB low:65660kB high:78792kB active_anon:2931328kB inactive_anon:448kB active_file:732kB inactive_file:696kB unevictable:0kB writepending:0kB present:3129204kB man
Nov 23 03:48:50 ubuntu-server-18 kernel: lowmem_reserve[]: 0 0 835 835 835
Nov 23 03:48:50 ubuntu-server-18 kernel: Node 0 Normal free:14732kB min:14776kB low:18468kB high:22160kB active_anon:729848kB inactive_anon:7328kB active_file:564kB inactive_file:28kB unevictable:0kB writepending:136kB present:950272kB ma
Nov 23 03:48:50 ubuntu-server-18 kernel: lowmem_reserve[]: 0 0 0 0 0
Nov 23 03:48:50 ubuntu-server-18 kernel: Node 0 DMA: 3*4kB (UM) 1*8kB (U) 2*16kB (UM) 0*32kB 3*64kB (UM) 1*128kB (U) 1*256kB (U) 1*512kB (M) 2*1024kB (UM) 0*2048kB 3*4096kB (ME) = 15476kB
Nov 23 03:48:50 ubuntu-server-18 kernel: Node 0 DMA32: 436*4kB (UEH) 565*8kB (EH) 582*16kB (UEH) 386*32kB (EH) 191*64kB (UME) 63*128kB (UMEH) 15*256kB (ME) 1*512kB (M) 1*1024kB (U) 1*2048kB (U) 0*4096kB = 55640kB
Nov 23 03:48:50 ubuntu-server-18 kernel: Node 0 Normal: 529*4kB (UMEH) 217*8kB (UMEH) 123*16kB (UEH) 96*32kB (UMEH) 23*64kB (UEH) 6*128kB (UMH) 6*256kB (MH) 1*512kB (M) 2*1024kB (MH) 0*2048kB 0*4096kB = 15228kB
Nov 23 03:48:50 ubuntu-server-18 kernel: Node 0 hugepages_total=0 hugepages_free=0 hugepages_surp=0 hugepages_size=1048576kB
Nov 23 03:48:50 ubuntu-server-18 kernel: Node 0 hugepages_total=0 hugepages_free=0 hugepages_surp=0 hugepages_size=2048kB
Nov 23 03:48:50 ubuntu-server-18 kernel: 47438 total pagecache pages
Nov 23 03:48:50 ubuntu-server-18 kernel: 0 pages in swap cache
Nov 23 03:48:50 ubuntu-server-18 kernel: Swap cache stats: add 0, delete 0, find 0/0
Nov 23 03:48:50 ubuntu-server-18 kernel: Free swap  = 0kB
Nov 23 03:48:50 ubuntu-server-18 kernel: Total swap = 0kB
Nov 23 03:48:50 ubuntu-server-18 kernel: 1023867 pages RAM
Nov 23 03:48:50 ubuntu-server-18 kernel: 0 pages HighMem/MovableOnly
Nov 23 03:48:50 ubuntu-server-18 kernel: 38546 pages reserved
Nov 23 03:48:50 ubuntu-server-18 kernel: 0 pages cma reserved
Nov 23 03:48:50 ubuntu-server-18 kernel: 0 pages hwpoisoned
Nov 23 03:48:50 ubuntu-server-18 kernel: [ pid ]   uid  tgid total_vm      rss pgtables_bytes swapents oom_score_adj name
Nov 23 03:48:50 ubuntu-server-18 kernel: [  448]     0   448    26475       46    94208        0             0 lvmetad
Nov 23 03:48:50 ubuntu-server-18 kernel: [  846]     0   846     6499      317    86016        0             0 dhclient
Nov 23 03:48:50 ubuntu-server-18 kernel: [ 1080]     0  1080   206132      332   163840        0             0 lxcfs
Nov 23 03:48:50 ubuntu-server-18 kernel: [ 1082]   102  1082    65759      366   167936        0             0 rsyslogd
Nov 23 03:48:50 ubuntu-server-18 kernel: [ 1084]     0  1084    17682      222   180224        0             0 systemd-logind
Nov 23 03:48:50 ubuntu-server-18 kernel: [ 1086]     0  1086     7506       72   106496        0             0 cron
Nov 23 03:48:50 ubuntu-server-18 kernel: [ 1087]     0  1087    27619       89   122880        0             0 irqbalance
Nov 23 03:48:50 ubuntu-server-18 kernel: [ 1088]     0  1088    42284     1945   221184        0             0 networkd-dispat
Nov 23 03:48:50 ubuntu-server-18 kernel: [ 1105]   103  1105    12574      230   139264        0          -900 dbus-daemon
Nov 23 03:48:50 ubuntu-server-18 kernel: [ 1126]     0  1126     7082       53    98304        0             0 atd
Nov 23 03:48:50 ubuntu-server-18 kernel: [ 1132]     0  1132    18075      187   188416        0         -1000 sshd
Nov 23 03:48:50 ubuntu-server-18 kernel: [ 1142]     0  1142    72220      230   200704        0             0 polkitd
Nov 23 03:48:50 ubuntu-server-18 kernel: [ 1143]     0  1143     3666       35    69632        0             0 agetty
Nov 23 03:48:50 ubuntu-server-18 kernel: [ 1145]     0  1145     3722       32    73728        0             0 agetty
Nov 23 03:48:50 ubuntu-server-18 kernel: [ 1158]     0  1158    46486     1977   262144        0             0 unattended-upgr
Nov 23 03:48:50 ubuntu-server-18 kernel: [ 1159]     0  1159     5764       56    86016        0             0 qemu-ga
Nov 23 03:48:50 ubuntu-server-18 kernel: [ 1228]     0  1228    43539     3614   237568        0             0 nginx
Nov 23 03:48:50 ubuntu-server-18 kernel: [25966]   101 25966    17730      228   180224        0             0 systemd-resolve
Nov 23 03:48:50 ubuntu-server-18 kernel: [25996] 62583 25996    35489      151   176128        0             0 systemd-timesyn
Nov 23 03:48:50 ubuntu-server-18 kernel: [26018]     0 26018    30318      708   249856        0             0 systemd-journal
Nov 23 03:48:50 ubuntu-server-18 kernel: [16675]     0 16675    10731      184   118784        0         -1000 systemd-udevd
Nov 23 03:48:50 ubuntu-server-18 kernel: [19523]     0 19523    71587      280   188416        0             0 accounts-daemon
Nov 23 03:48:50 ubuntu-server-18 kernel: [30852]     0 30852   235988     4258   274432        0          -900 snapd
Nov 23 03:48:50 ubuntu-server-18 kernel: [14252]     0 14252    72771     2616   425984        0             0 php-fpm5.6
Nov 23 03:48:50 ubuntu-server-18 kernel: [14272]    33 14272    73345     1286   405504        0             0 php-fpm5.6
Nov 23 03:48:50 ubuntu-server-18 kernel: [14274]    33 14274    73345     1286   405504        0             0 php-fpm5.6
Nov 23 03:48:50 ubuntu-server-18 kernel: [14319]     0 14319   112080     1758   581632        0             0 php-fpm7.2
Nov 23 03:48:50 ubuntu-server-18 kernel: [14341]    33 14341   112654     1367   552960        0             0 php-fpm7.2
Nov 23 03:48:50 ubuntu-server-18 kernel: [14345]    33 14345   112654     1367   552960        0             0 php-fpm7.2
Nov 23 03:48:50 ubuntu-server-18 kernel: [31607]     0 31607   112112     1912   577536        0             0 php-fpm7.4
Nov 23 03:48:50 ubuntu-server-18 kernel: [12292]    33 12292    60815    32497   466944        0             0 nginx
Nov 23 03:48:50 ubuntu-server-18 kernel: [12293]    33 12293    61214    32910   471040        0             0 nginx
Nov 23 03:48:50 ubuntu-server-18 kernel: [12294]    33 12294    44113     4358   233472        0             0 nginx
Nov 23 03:48:50 ubuntu-server-18 kernel: [23744]    33 23744   152297    42951   978944        0             0 php-fpm7.4
Nov 23 03:48:50 ubuntu-server-18 kernel: [24231]    33 24231   149576    40587   954368        0             0 php-fpm7.4
Nov 23 03:48:50 ubuntu-server-18 kernel: [24245]    33 24245   160604    50698  1024000        0             0 php-fpm7.4
Nov 23 03:48:50 ubuntu-server-18 kernel: [24247]    33 24247   150409    34720   888832        0             0 php-fpm7.4
Nov 23 03:48:50 ubuntu-server-18 kernel: [24251]    33 24251   149541    39766   929792        0             0 php-fpm7.4
Nov 23 03:48:50 ubuntu-server-18 kernel: [24253]    33 24253   149987    33909   880640        0             0 php-fpm7.4
Nov 23 03:48:50 ubuntu-server-18 kernel: [24255]    33 24255   149565    33360   872448        0             0 php-fpm7.4
Nov 23 03:48:50 ubuntu-server-18 kernel: [24261]    33 24261   149283    34006   892928        0             0 php-fpm7.4
Nov 23 03:48:50 ubuntu-server-18 kernel: [24270]    33 24270   151926    35619   905216        0             0 php-fpm7.4
Nov 23 03:48:50 ubuntu-server-18 kernel: [24272]    33 24272   151055    34804   901120        0             0 php-fpm7.4
Nov 23 03:48:50 ubuntu-server-18 kernel: [24280]    33 24280   149279    33033   872448        0             0 php-fpm7.4
Nov 23 03:48:50 ubuntu-server-18 kernel: [24474]    33 24474   149977    41005   942080        0             0 php-fpm7.4
Nov 23 03:48:50 ubuntu-server-18 kernel: [24480]    33 24480   160875    44150   958464        0             0 php-fpm7.4
Nov 23 03:48:50 ubuntu-server-18 kernel: [24481]    33 24481   155125    39199   929792        0             0 php-fpm7.4
Nov 23 03:48:50 ubuntu-server-18 kernel: [24494]    33 24494   130327    32679   851968        0             0 php-fpm7.4
Nov 23 03:48:50 ubuntu-server-18 kernel: [24501]    33 24501   149217    32395   872448        0             0 php-fpm7.4
Nov 23 03:48:50 ubuntu-server-18 kernel: [24711]    33 24711   167027    43212   954368        0             0 php-fpm7.4
Nov 23 03:48:50 ubuntu-server-18 kernel: [24716]    33 24716   130119    30374   839680        0             0 php-fpm7.4
Nov 23 03:48:50 ubuntu-server-18 kernel: [24728]    33 24728   129974    30180   839680        0             0 php-fpm7.4
Nov 23 03:48:50 ubuntu-server-18 kernel: [24729]    33 24729   129980    30295   835584        0             0 php-fpm7.4
Nov 23 03:48:50 ubuntu-server-18 kernel: [24730]    33 24730   130061    30360   835584        0             0 php-fpm7.4
Nov 23 03:48:50 ubuntu-server-18 kernel: [24731]    33 24731   129492    31333   839680        0             0 php-fpm7.4
Nov 23 03:48:50 ubuntu-server-18 kernel: [24732]    33 24732   129987    30500   839680        0             0 php-fpm7.4
Nov 23 03:48:50 ubuntu-server-18 kernel: [24733]    33 24733   129785    31073   839680        0             0 php-fpm7.4
Nov 23 03:48:50 ubuntu-server-18 kernel: [24735]    33 24735   114592    14326   716800        0             0 php-fpm7.4
Nov 23 03:48:50 ubuntu-server-18 kernel: [24736]    33 24736   148728    30660   851968        0             0 php-fpm7.4
Nov 23 03:48:50 ubuntu-server-18 kernel: [24737]    33 24737   130017    30366   839680        0             0 php-fpm7.4
Nov 23 03:48:50 ubuntu-server-18 kernel: [24739]    33 24739   119261    19469   741376        0             0 php-fpm7.4
Nov 23 03:48:50 ubuntu-server-18 kernel: [24740]    33 24740   140396    21720   778240        0             0 php-fpm7.4
Nov 23 03:48:50 ubuntu-server-18 kernel: [24742]    33 24742   151672    34753   892928        0             0 php-fpm7.4
Nov 23 03:48:50 ubuntu-server-18 kernel: [24777]    33 24777   129731    29546   811008        0             0 php-fpm7.4
Nov 23 03:48:50 ubuntu-server-18 kernel: [24779]    33 24779   129731    29562   811008        0             0 php-fpm7.4
Nov 23 03:48:50 ubuntu-server-18 kernel: [24780]    33 24780   129731    29537   811008        0             0 php-fpm7.4
Nov 23 03:48:50 ubuntu-server-18 kernel: [24783]    33 24783   129731    29554   811008        0             0 php-fpm7.4
Nov 23 03:48:50 ubuntu-server-18 kernel: [24784]    33 24784   129731    29554   811008        0             0 php-fpm7.4
Nov 23 03:48:50 ubuntu-server-18 kernel: [24785]    33 24785   114039    14515   704512        0             0 php-fpm7.4
Nov 23 03:48:50 ubuntu-server-18 kernel: [24786]    33 24786   115559    13569   679936        0             0 php-fpm7.4
Nov 23 03:48:50 ubuntu-server-18 kernel: [24791]    33 24791   120167    18244   716800        0             0 php-fpm7.4
Nov 23 03:48:50 ubuntu-server-18 kernel: [24792]    33 24792   120679    18849   720896        0             0 php-fpm7.4
Nov 23 03:48:50 ubuntu-server-18 kernel: [24793]    33 24793   120679    18729   720896        0             0 php-fpm7.4
Nov 23 03:48:50 ubuntu-server-18 kernel: [24794]    33 24794   120679    18793   720896        0             0 php-fpm7.4
Nov 23 03:48:50 ubuntu-server-18 kernel: [24795]    33 24795   119143    16921   708608        0             0 php-fpm7.4
Nov 23 03:48:50 ubuntu-server-18 kernel: [24797]    33 24797   120167    18172   716800        0             0 php-fpm7.4
Nov 23 03:48:50 ubuntu-server-18 kernel: [24798]    33 24798   119143    16921   708608        0             0 php-fpm7.4
Nov 23 03:48:50 ubuntu-server-18 kernel: [24807]    33 24807   116039    12175   667648        0             0 php-fpm7.4
Nov 23 03:48:50 ubuntu-server-18 kernel: [24808]    33 24808   116039    12945   671744        0             0 php-fpm7.4
Nov 23 03:48:50 ubuntu-server-18 kernel: [24809]    33 24809   117607    14883   696320        0             0 php-fpm7.4
Nov 23 03:48:50 ubuntu-server-18 kernel: [24810]    33 24810   117607    15484   696320        0             0 php-fpm7.4
Nov 23 03:48:50 ubuntu-server-18 kernel: [24811]    33 24811   117575    15597   696320        0             0 php-fpm7.4
Nov 23 03:48:50 ubuntu-server-18 kernel: [24942]    33 24942   113151     5722   630784        0             0 php-fpm7.4
Nov 23 03:48:50 ubuntu-server-18 kernel: [24978]   111 24978   415917    97861  1253376        0             0 mysqld
Nov 23 03:48:50 ubuntu-server-18 kernel: Out of memory: Kill process 24978 (mysqld) score 99 or sacrifice child
Nov 23 03:48:50 ubuntu-server-18 kernel: Killed process 24978 (mysqld) total-vm:1663668kB, anon-rss:391444kB, file-rss:0kB, shmem-rss:0kB
Nov 23 03:48:50 ubuntu-server-18 kernel: oom_reaper: reaped process 24978 (mysqld), now anon-rss:0kB, file-rss:0kB, shmem-rss:0kB
Nov 23 03:48:50 ubuntu-server-18 systemd[1]: mysql.service: Main process exited, code=killed, status=9/KILL
Nov 23 03:48:50 ubuntu-server-18 systemd[1]: mysql.service: Failed with result 'signal'.
Nov 23 03:48:50 ubuntu-server-18 systemd[1]: mysql.service: Service hold-off time over, scheduling restart.
Nov 23 03:48:50 ubuntu-server-18 systemd[1]: mysql.service: Scheduled restart job, restart counter is at 3.
-- Subject: Automatic restarting of a unit has been scheduled
-- Defined-By: systemd

----- UPDATE (Server Configs) -----

Here is my PHP settings

pm.max_children = 50 
pm.start_servers = 22 
pm.min_spare_servers = 15 
pm.max_spare_servers = 30  
pm.max_requests = 300

MySql Settings /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
#
# * Basic Settings
#
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address        = 127.0.0.1
#
# * Fine Tuning
#
key_buffer_size     = 16M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP
#max_connections        = 100
#table_open_cache       = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit   = 1M
query_cache_size        = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#slow_query_log     = 1
#slow_query_log_file    = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id      = 1
#log_bin            = /var/log/mysql/mysql-bin.log
expire_logs_days    = 10
max_binlog_size   = 100M
#binlog_do_db       = include_database_name
#binlog_ignore_db   = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

Where should I define innodb_buffer_pool and max_connections ?

See the Hetzner Graph. I see something strange when MySql was killed.

Find out what is using the memory and allocate it better. Probably less to MySQL. — tater, Nov 23 '20 at 05:22
You have max_connections in your config - disabled with the leading #. Remove the leading # and your max_connections will drop from 151 to 100. innodb_buffer_pool_size=100M - to be added before * Security in your config will reduce size from default of 128M to 100M. Stop/Start instance for changes to take effect. — Wilson Hauck, Nov 25 '20 at 09:40
Additional information request. Any SSD or NVME devices on MySQL Host server? Post on pastebin.com and share the links. From your SSH login root, Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) complete MySQLTuner report AND Optional very helpful information, if available includes - htop OR top for most active apps, ulimit -a for a Linux/Unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, for server workload tuning analysis to provide suggestions. — Wilson Hauck, Nov 25 '20 at 09:41
@Future-King You may have to ask Hetzner to post this information for you. We can assist after Hetzner posts the links so we can see your data. — Wilson Hauck, Dec 05 '20 at 14:29

score 1 · Answer 1 · answered Nov 23 '20 at 06:20

1

4GB is a rather small machine.

Decrease the number of PHP children nginx is allowed to spawn -- perhaps down to only 20.
Decrease MySQL's innodb_buffer_pool some.
Decrease MySQL's max_connections some.

answered Nov 23 '20 at 06:20

Rick James

2,463
1
6
13

I have increased the RAM to 8GB. Will it prevent the issue in future? – Future King Nov 23 '20 at 09:48
Can you suggest me some program which can store history of CPU and RAM usage over period of time and display the data in graph? – Future King Nov 23 '20 at 09:52
@FutureKing - I can't promise that the issue is now solved -- there was no clear evidence that you were running out of RAM. Usually, Cloud operators provide such; I don't know about Hetzner. Is there any swap space? – Rick James Nov 23 '20 at 16:14
Hetzner does not provides data about RAM usage. SWAP space is not present. During high traffic hours RAM usages goes to maximum 2.5GB It never reaches to 3GB. Mysql was killed during minimum traffic hour. Thats why it looks very strange. – Future King Nov 23 '20 at 19:34
Where should I enter innodb_buffer_pool and max_connections ??? – Future King Nov 23 '20 at 19:34
I have added a Graph report from Hetzner. It shows something unusual. – Future King Nov 23 '20 at 19:41
Can you tell whether the spikes are just before the crash? Or just after? I would expect an I/O spike afterward while it reloads lots of stuff. – Rick James Nov 27 '20 at 21:27
Does Hertzner have a way of modifying the configuration? (They probably don't allow you access to the file system, where `my.cnf` is.) – Rick James Nov 27 '20 at 21:28

MySql crashes randomly Out of memory: Kill process 24978 (mysqld) score 99 or sacrifice child

1 Answers1