0

Currently, I have MySQL server installed in the AWS RHEL EC2 instance.

The team was running heavy data processing in the DB and we found out that every now and then the DB restarted itself. After troubleshooting, we realized that the DB was actually killed by the OS! Because the MySQL is so hungry that it consumes all the memory in the OS.

The OS has 124GB RAM and 68GB is allocated to innodb_buffer_pool_size. Attached my.cnf file

Question:

How do we prevent the OS from killing the mysqld?

    [mysqld]
# Server configuration
user=mysql
port=3306
socket=/home/mysql/server/run/mysql.sock

# File locations
datadir=/mysql/data
basedir=/home/mysql/server
lc_messages_dir=/usr/share/mysql-8.0
plugin-dir=/lib64/mysql/plugin
innodb_log_group_home_dir=/redolog/redologfiles
innodb_data_file_path=ibdata1:12M:autoextend
log-error=/home/mysql/server/logs/mysqld.log
innodb_log_file_size=2147483648
innodb_log_files_in_group=8
innodb_page_size=16384
innodb_checksum_algorithm=crc32
innodb_buffer_pool_filename=ib_buffer_pool
innodb_undo_tablespaces=2
innodb_buffer_pool_filename=ib_buffer_pool
skip-log-bin

# New server config
character-set-server=latin1
collation-server=latin1_swedish_ci
connect_timeout=31530000
net_read_timeout=31530000
net_write_timeout=31530000
innodb_buffer_pool_size=68719476736
innodb_buffer_pool_instances=64
innodb_temp_data_file_path=ibtmp1:512M:autoextend:max:512000M
innodb_max_undo_log_size=4294967296
innodb_read_io_threads=16
binlog_expire_logs_seconds=259200
#expire_logs_days=10
max_binlog_size=1073741824
sql_mode = ''
max_connections=300
log_timestamps=SYSTEM
local_infile = 1
max_digest_length = 3072
performance_schema_max_digest_length=10240 
max_digest_length=10240 
performance_schema_max_sql_text_length=10240
innodb_adaptive_hash_index = 0

interactive_timeout  = 86400
mysqlx_interactive_timeout  = 86400
mysqlx_wait_timeout  = 86400
wait_timeout  = 86400

general_log = OFF
general_log_file = /home/mysql/server/logs/mysqld_db.log
[client]
port=3306
socket=/home/mysql/server/run/mysql.sock

#slow_query_log_file=/home/mysql/server/logs/mysql-slow.log
#slow_query_log=ON
#general_log_file=/home/mysql/server/logs/mysql_gen_db.log
#general_log=ON
  • Turn off the Performance_Schema to see if that is the cause. – Rick James Jan 24 at 23:49
  • What else is running on the server? – Rick James Jan 24 at 23:49
  • Decreasing innodb_buffer_pool_size further is a quick-and-dirty solution. – Rick James Jan 24 at 23:50
  • Turn off the Performance_schema does not help. I tried to reduce the innodb_buffer_pool_size as well. – Jupyterous Jan 29 at 5:51
  • When I was testing, there is nothing running in the DB at the same time – Jupyterous Jan 29 at 5:52
0

9 times out of 10 this is due to a hungry query. Set long-query-time to something < 2 and once you have a good sample size analyse the log file using the Percona tool pt-query-digest. This should give you a good place to start.

Try splitting your transactional and analytical workloads. Running them on different hardware is common place.

  • Agree. I am trying the replicate the instance in another environment and reproduce the issue for MOS to debug – Jupyterous Jan 29 at 5:52

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.