1

MySQL seems to crash suddenly but recovered in around 2minutes. I am running on Ubuntu 18.04 (16vcpu, 32GB RAM). I dont know what caused it.

Here is my.ini

[mysql]
default-character-set=utf8mb4

[mysqld]
lower_case_table_names = 1
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /data/mysql
log-error       = /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address   = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
max_allowed_packet=1G
max_connect_errors=1000
max_connections=2500
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
innodb_buffer_pool_size=27G
server-id=2
log-bin=env1
expire_logs_days=2
sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
net_read_timeout=300
net_write_timeout=300
log_bin_trust_function_creators=1

Here is my /var/log/mysql/error.log enter image description here

The crash is around 8:11am as seen on the image above

Im trying to find the root cause for this. Is there any logs in Ubuntu that I can check to find more info on what caused MySQL to crash?

EDIT: I saw the OOM (Out of Memory) error on the /var/log/syslog. Any idea what caused this?

3
  • 1
    given the lack of messages like a shutdown, it looks like it was hard killed like OOM. Check dmesg output. – danblack Feb 1 at 10:57
  • @danblack It crashed again a while ago. Any idea how to fix this? or what config should I adjust – JRA Feb 2 at 2:03
  • 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) STATUS; 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 Feb 2 at 18:48
1

innodb_buffer_pool_size=27G is too close to the limit especially with a large amount of (max-)connections.

Lower this down and examine utilization. show global status like 'innodb_buffer_pool_pages% should be full after a reasonable amount of warmup (24hrs+)

show global status like 'innodb_buffer_pool_read%. read_requests vs reads should be quite large. (reads% < 1%).

Once you have this balance hopefully there is sufficient ram available to avoid OOM, but still has sufficient performance.

3
  • Thanks for this. I will try to lower my buffer pool size to 24GB. Right now read requests is around 2gb while reads is below 1mb – JRA Feb 2 at 3:22
  • With those numbers I assume that innodb_buffer_pool_reads hasn't changed since the warmup of your data and you still have innodb_buffer_pool_pages_free. Also look at how many innodb_buffer_pool_reads occur over a minute or 2 interval. This is effectively disk reads to get data. Should be low but no point tuning to 0. – danblack Feb 2 at 3:26
  • the innodb_buffer_pool_reads over 2 minute interval is around 0.9KB – JRA Feb 2 at 3:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.