MariaDB won't start when making change to my.cnf

Naila

Member
Jun 20, 2017
15
0
1
Indonesia
cPanel Access Level
Root Administrator
I make optimizatin based from mysqltunner but when apply conf to my.cnf, mariadb can not starting sir. Maybe you can help optimizing mariadb (v-103) on my server (64GB ram, 20core/40th, nvme.


I try this

HTML:
## 
## Files
back_log            = 300
open-files-limit    = 8192
open-files          = 1024 
port                = 3306
socket              = /var/lib/mysql/mysql.sock
pid-file            = /var/lib/mysql/mysql.pid
skip-external-locking
skip-name-resolve

## Logging

relay_log               = mysql-relay-bin
relay_log_index         = mysql-relay-index
log_error               = mysql-error.err
log_warnings
log_bin                 = mysql-bin
log_slow_queries        = mysql-slow.log
long_query_time         = 10    #default: 10
max_binlog_size         = 256M  #max size for binlog before rolling
expire_logs_days        = 4     #binlog files older than this will be purged

## Per-Thread Buffers * (max_connections) = total per-thread mem usage
thread_stack          = 512K  #default: 32bit: 192K, 64bit: 256K
sort_buffer_size      = 2M    #default: 2M, larger may cause perf issues
read_buffer_size      = 2M    #default: 128K, change in increments of 4K
read_rnd_buffer_size  = 2M    #default: 256K               
join_buffer_size      = 2M    #default: 128K
binlog_cache_size     = 128K  #default: 32K, size of buffer to hold TX queries
## total per-thread buffer memory usage: 17664000K = 17.250GB

## Query Cache
query_cache_size   = 64M    #global buffer
query_cache_limit  = 512K   #max query result size to put in cache

## Connections
max_connections     = 4000  #multiplier for memory usage via per-thread buffers
max_connect_errors  = 100   #default: 10
concurrent_insert   = 2     #default: 1, 2: enable insert for all instances
connect_timeout     = 30    #default -5.1.22: 5, +5.1.22: 10
max_allowed_packet  = 32M   #max size of incoming data to allow

## Default Table Settings
sql_mode     = NO_AUTO_CREATE_USER

## Table and TMP settings
max_heap_table_size      = 1G    #recommend same size as tmp_table_size
bulk_insert_buffer_size  = 1G    #recommend same size as tmp_table_size
tmp_table_size           = 1G    #recommend 1G min
#tmpdir                  = /data/mysql-tmp0:/data/mysql-tmp1 #Recommend using RAMDISK for tmpdir


## Thread settings
thread_concurrency = 16  #recommend 2x CPU cores
thread_cache_size = 100  #recommend 5% of max_connections

## MyISAM Engine
key_buffer                = 1M      #global buffer
myisam_sort_buffer_size   = 128M    #index buffer size for creating/altering indexes
myisam_max_sort_file_size = 256M    #max file size for tmp table when creating/alering indexes
myisam_repair_threads     = 4       #thread quantity when running repairs
myisam_recover            = BACKUP  #repair mode, recommend BACKUP


## InnoDB Plugin Independent Settings
innodb_log_file_size            = 768M
innodb_log_files_in_group       = 4
innodb_buffer_pool_size         = 19G   #global buffer
innodb_additional_mem_pool_size = 4M    #global buffer
innodb_flush_log_at_trx_commit  = 2 #2/0 = perf, 1 = ACID
innodb_table_locks              = 0 #preserve table locks
innodb_log_buffer_size          = 128M  #global buffer
innodb_lock_wait_timeout        = 60   
innodb_thread_concurrency       = 16    #recommend 2x core quantity
innodb_commit_concurrency       = 16    #recommend 4x num disks
innodb_flush_method             = O_DIRECT     #O_DIRECT = local/DAS, O_DSYNC = SAN/iSCSI
innodb_support_xa               = 0        #recommend 0, disable xa to negate extra disk flush
skip-innodb-doublewrite
Event only add innodb_buffer_pool_size = 19G still can't start.
 

Jcats

Well-Known Member
PartnerNOC
May 25, 2011
797
152
168
New Jersey
cPanel Access Level
DataCenter Provider
You'd have to look at the MySQL error log. Try and start MySQL then:

Code:
tail -100 /var/lib/mysql/server.hostname.com.err
replace server.hostname.com with your actual server hostname, or run:

Code:
echo /var/lib/mysql/*.err
and it will show you the error log