MYSQL CRASHING FREQUENTLY
Posted by: Sam Mathews
Date: June 22, 2017 01:31AM

Hi!
I am using mysql 5.6. on centos 7.3.

SERVER CONFIGURATIONS

Server configuration :
CPU xeon e5 2697A dual socket.
RAM 1.5 TB total 64 GB x24 DDR4 2133Mhz.
HDD 8 HDD 8TB each 12 Gbps NLSas 7.2 RPM.
NIC 10Gbps DA/SFP+ QLOGIC.
OS Centos 7 64 bit.
RAID 10 is being used.
CPU is multi-threaded


DATABASE Configuration File is

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
#innodb---------
innodb_buffer_pool_size=962072674304
innodb_buffer_pool_instances=16
innodb_log_file_size=2147483648
innodb_sort_buffer_size=67108864
innodb_change_buffering=all
innodb_change_buffer_max_size=35
innodb_log_buffer_size=134217728
#innodb Disk I/O-----
innodb_doublewrite=OFF
innodb_flush_log_at_trx_commit=1
innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_io_capacity=5000
innodb_file_per_table=ON
#Recovery----
innodb_force_recovery=0
slow_launch_time=2
#general_Variables
query_cache_size=53687091200
max_heap_table_size=2560000
thread_cache_size=16384
query_cache_type=1
max_allowed_packet=1073741824
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
#logs
expire_logs_days=7
log-bin=/var/lib/mysql/bin-log
log-bin-index=/var/lib/mysql/bin-log.index
max_binlog_size=1073741824
binlog_format=row
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[mysqld_safe]
log-error=/var/log/mysqlerr.log
pid-file=/var/run/mysqld/mysqld.pid





Everyday We are running bulk insert of approximate 2 Billion rows into 4 different tables.
Tables are partitioned (RANGE PARTITION) on daily basis with INNODB storage engine.

Everyday mysql is crashing. Error log


170622 04:31:33 mysqld_safe Number of processes running now: 0
170622 04:31:33 mysqld_safe mysqld restarted
2017-06-22 04:31:33 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-06-22 04:31:33 0 [Note] /usr/sbin/mysqld (mysqld 5.6.35-log) starting as process 126830 ...
2017-06-22 04:31:33 126830 [Warning] Buffered warning: Changed limits: max_open_files: 1024 (requested 5000)

2017-06-22 04:31:33 126830 [Warning] Buffered warning: Changed limits: table_open_cache: 431 (requested 2000)

2017-06-22 04:31:33 126830 [Note] Plugin 'FEDERATED' is disabled.
2017-06-22 04:31:33 126830 [Note] InnoDB: Using atomics to ref count buffer pool pages
2017-06-22 04:31:33 126830 [Note] InnoDB: The InnoDB memory heap is disabled
2017-06-22 04:31:33 126830 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017-06-22 04:31:33 126830 [Note] InnoDB: Memory barrier is not used
2017-06-22 04:31:33 126830 [Note] InnoDB: Compressed tables use zlib 1.2.3
2017-06-22 04:31:33 126830 [Note] InnoDB: Using Linux native AIO
2017-06-22 04:31:33 126830 [Note] InnoDB: Using CPU crc32 instructions
2017-06-22 04:31:33 126830 [Note] InnoDB: Initializing buffer pool, size = 896.0G
2017-06-22 04:32:12 126830 [Note] InnoDB: Completed initialization of buffer pool
2017-06-22 04:32:17 126830 [Note] InnoDB: Highest supported file format is Barracuda.
2017-06-22 04:32:17 126830 [Note] InnoDB: The log sequence numbers 7138674160333 and 7138674160333 in ibdata files do not match the log sequence number 7252262735448 in the ib_logfiles!
2017-06-22 04:32:17 126830 [Note] InnoDB: Database was not shutdown normally!
2017-06-22 04:32:17 126830 [Note] InnoDB: Starting crash recovery.
2017-06-22 04:32:17 126830 [Note] InnoDB: Reading tablespace information from the .ibd files...
2017-06-22 04:32:21 126830 [Note] InnoDB: Restoring possible half-written data pages
2017-06-22 04:32:21 126830 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Last MySQL binlog file position 0 731118539, file name bin-log.001003
2017-06-22 04:32:33 126830 [Note] InnoDB: 128 rollback segment(s) are active.
2017-06-22 04:32:33 126830 [Note] InnoDB: Waiting for purge to start
2017-06-22 04:32:33 126830 [Note] InnoDB: 5.6.35 started; log sequence number 7252262735448
2017-06-22 04:32:33 126830 [Note] Recovering after a crash using /var/lib/mysql/bin-log
2017-06-22 04:32:34 126830 [Note] Starting crash recovery...
2017-06-22 04:32:34 126830 [Note] Crash recovery finished.
2017-06-22 04:32:36 126830 [Note] Server hostname (bind-address): '*'; port: 3306
2017-06-22 04:32:36 126830 [Note] IPv6 is available.
2017-06-22 04:32:36 126830 [Note] - '::' resolves to '::';
2017-06-22 04:32:36 126830 [Note] Server socket created on IP: '::'.
2017-06-22 04:32:36 126830 [Note] Event Scheduler: Loaded 0 events
2017-06-22 04:32:36 126830 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.35-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)
2017-06-22 04:32:36 126830 [Warning] IP address '10.101.10.72' could not be resolved: Name or service not known
2017-06-22 04:32:36 126830 [Warning] IP address '10.101.124.87' could not be resolved: Name or service not known
2017-06-22 09:23:47 126830 [Warning] IP address '10.101.10.90' could not be resolved: Name or service not known
2017-06-22 09:31:39 126830 [Warning] IP address '10.101.124.58' could not be resolved: Name or service not known
2017-06-22 09:42:49 126830 [Warning] IP address '10.101.124.66' could not be resolved: Name or service not known
170622 09:46:15 mysqld_safe Number of processes running now: 0
170622 09:46:15 mysqld_safe mysqld restarted
2017-06-22 09:46:16 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-06-22 09:46:16 0 [Note] /usr/sbin/mysqld (mysqld 5.6.35-log) starting as process 130365 ...
2017-06-22 09:46:16 130365 [Warning] Buffered warning: Changed limits: max_open_files: 1024 (requested 5000)

2017-06-22 09:46:16 130365 [Warning] Buffered warning: Changed limits: table_open_cache: 431 (requested 2000)

2017-06-22 09:46:16 130365 [Note] Plugin 'FEDERATED' is disabled.
2017-06-22 09:46:16 130365 [Note] InnoDB: Using atomics to ref count buffer pool pages
2017-06-22 09:46:16 130365 [Note] InnoDB: The InnoDB memory heap is disabled
2017-06-22 09:46:16 130365 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017-06-22 09:46:16 130365 [Note] InnoDB: Memory barrier is not used
2017-06-22 09:46:16 130365 [Note] InnoDB: Compressed tables use zlib 1.2.3
2017-06-22 09:46:16 130365 [Note] InnoDB: Using Linux native AIO
2017-06-22 09:46:16 130365 [Note] InnoDB: Using CPU crc32 instructions
2017-06-22 09:46:16 130365 [Note] InnoDB: Initializing buffer pool, size = 896.0G
2017-06-22 09:46:51 130365 [Note] InnoDB: Completed initialization of buffer pool
2017-06-22 09:46:56 130365 [Note] InnoDB: Highest supported file format is Barracuda.
2017-06-22 09:46:56 130365 [Note] InnoDB: The log sequence numbers 7138674160333 and 7138674160333 in ibdata files do not match the log sequence number 7254914115945 in the ib_logfiles!
2017-06-22 09:46:56 130365 [Note] InnoDB: Database was not shutdown normally!
2017-06-22 09:46:56 130365 [Note] InnoDB: Starting crash recovery.
2017-06-22 09:46:56 130365 [Note] InnoDB: Reading tablespace information from the .ibd files...
2017-06-22 09:47:00 130365 [Note] InnoDB: Restoring possible half-written data pages
2017-06-22 09:47:00 130365 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Last MySQL binlog file position 0 732905726, file name bin-log.001004
2017-06-22 09:47:12 130365 [Note] InnoDB: 128 rollback segment(s) are active.
2017-06-22 09:47:12 130365 [Note] InnoDB: Waiting for purge to start
2017-06-22 09:47:12 130365 [Note] InnoDB: 5.6.35 started; log sequence number 7254914115945
2017-06-22 09:47:12 130365 [Note] Recovering after a crash using /var/lib/mysql/bin-log
2017-06-22 09:47:13 130365 [Note] Starting crash recovery...
2017-06-22 09:47:13 130365 [Note] Crash recovery finished.
2017-06-22 09:47:15 130365 [Note] Server hostname (bind-address): '*'; port: 3306
2017-06-22 09:47:15 130365 [Note] IPv6 is available.
2017-06-22 09:47:15 130365 [Note] - '::' resolves to '::';
2017-06-22 09:47:15 130365 [Note] Server socket created on IP: '::'.
2017-06-22 09:47:15 130365 [Note] Event Scheduler: Loaded 0 events
2017-06-22 09:47:15 130365 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.35-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)
2017-06-22 09:47:15 130365 [Warning] IP address '10.101.124.87' could not be resolved: Name or service not known
2017-06-22 09:47:15 130365 [Warning] IP address '10.101.10.72' could not be resolved: Name or service not known
2017-06-22 09:47:15 130365 [Warning] IP address '10.101.10.72' could not be resolved: Name or service not known
2017-06-22 09:50:20 130365 [Warning] IP address '10.101.124.66' could not be resolved: Name or service not known

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.