Optimize MySQL Configuration - MySQL crash and after restart works

Question

My WAMP MySQL has crashed 2 times the last week and I really wonder why! Once I restart the server everything works fine! Below I attach the error log of MySQL and the my.ini file! Can anyone suggest anything??

MySQL Error LOG:

2018-02-23 21:45:52 2000 [Note] Plugin 'FEDERATED' is disabled.
2018-02-23 21:45:52 2000 [Note] InnoDB: The InnoDB memory heap is disabled
2018-02-23 21:45:52 2000 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2018-02-23 21:45:52 2000 [Note] InnoDB: Compressed tables use zlib 1.2.3
2018-02-23 21:45:52 2000 [Note] InnoDB: Not using CPU crc32 instructions
2018-02-23 21:45:53 2000 [Note] InnoDB: Initializing buffer pool, size = 67.0G
2018-02-23 21:45:55 2000 [Note] InnoDB: Completed initialization of buffer pool
2018-02-23 21:45:55 2000 [Note] InnoDB: Highest supported file format is Barracuda.
2018-02-23 21:47:37 2000 [Note] InnoDB: 128 rollback segment(s) are active.
2018-02-23 21:47:37 2000 [Note] InnoDB: Waiting for purge to start
2018-02-23 21:47:38 2000 [Note] InnoDB: 5.6.12 started; log sequence number 321531472808
2018-02-23 21:48:16 2000 [Note] Server hostname (bind-address): '*'; port: 3306
2018-02-23 21:48:16 2000 [Note] IPv6 is available.
2018-02-23 21:48:16 2000 [Note]   - '::' resolves to '::';
2018-02-23 21:48:16 2000 [Note] Server socket created on IP: '::'.
2018-02-23 21:48:18 2000 [Note] Event Scheduler: Loaded 0 events
2018-02-23 21:48:18 2000 [Note] wampmysqld: ready for connections.
Version: '5.6.12-log'  socket: ''  port: 3306  MySQL Community Server (GPL)

My.ini Configuration File:

[wampmysqld]  
port        = 3306 
socket      = /tmp/mysql.sock
key_buffer = 64M
max_allowed_packet = 1000M

sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
basedir=c:/wamp/bin/mysql/mysql5.6.12
log-error=c:/wamp/logs/mysql.log
datadir=c:/wamp/bin/mysql/mysql5.6.12/data
#innodb_thread_concurrency = 8
innodb_read_io_threads = 12
innodb_buffer_pool_size = 67G
max_connections = 4000
max_user_connections = 0

 # Disable Federated by default
skip-federated

[mysqldump]
 quick
max_allowed_packet = 1000M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 4M
write_buffer = 4M

[myisamchk] 
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 4M
write_buffer = 4M

[mysqlhotcopy]
interactive-timeout

 [mysqld]
 port=3306
 table_definition_cache = 800

My Server Details:

RAM: 128GB RAM CPU: Intel XEON 3,5GHZ HD: 2TB HDD

Any recommendation??

score 0 · Accepted Answer · answered Feb 24 '18 at 11:40

Suggestions for my.cnf/ini [wampmysqld] section

key_buffer=64M       SHOULD BE
key_buffer_size=64M  # from key_buffer  to be recognized
expire_logs_days=10  # from default 0, to provide limited historical logs
innodb_buffer_pool_instances=8  # to minimize mutex contention
innodb_buffer_pool_dump_at_shutdown=ON  # prepare for WARM on restart
innodb_buffer_pool_load_at_startup=ON  # pool will be warmed up
innodb_buffer_pool_dump_pct=75  # from 25 you will likely need the data
innodb_page_cleaners=64  # 4 to auto-limit = innodb_buffer_pool_instances
innodb_print_all_deadlocks=ON  # chk error log-be proactive to prevent 
innodb_purge_threads=4  # to keep up with activity
log_warnings=2  # to have record of attempted connections in error log
#max_connections=4000  # lead with # to have default of 151 until stable
#sort_buffer_size=512K  # lead with # for default of 2M
#max_allowed_packet=1000M  # lead with # for default of 1M

if you need MAP greater than 1M, it is likely for data loading infile in your SESSION, SET @max_allowed_packet=nnnnnnnnn bytes max in input file 1G is THE LIMIT

this technique will HELP avoid OOM due to a new connection.

Optimize MySQL Configuration - MySQL crash and after restart works

1 Answers1