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