Help me for my.cnf config for Dedicated Server

welcome @Talia Olga

Centmin Mod is provided as is so there is no optimisation support provided by me for free. Community members here are however free to help each other out if they want For Nginx and PHP-FPM and MariaDB questions, you can post in relevant forum in future at Nginx, PHP-FPM & MariaDB MySQL and there's forums for web apps like forums and cms like wordpress here Centmin Mod Web App Usage | Centmin Mod Community Again, I do not provide any free optimisation support other than provide Centmin Mod as is.

If you have problems after initial install or need to find where all the config files and log files are, you can read info at How to troubleshoot Centmin Mod initial install issues | Centmin Mod Community as well as official FAQ at Centmin Mod FAQ - Menu based Nginx installer

I realise that Centmin Mod's MariaDB /etc/my.cnf format might be confusing so what I have done is take all your relevant values from your old my.cnf and transposed them into Centmin Mod's default MariaDB 5.5 /etc/my.cnf format which should work. There is no optimisation done, just transposing of your existing values to the new format - with exception of read_rnd_buffer_size value which was way too high at 524M, I set that at 1M and ensured you properly set ft_min_word_len = 4 in [myisamchk] too if you set it in [mysqld].

FYI, to post long code/text like config files use CODE bbcode tags.

This is the transposed /etc/my.cnf you can use.

Code:

[mysqld]
#character-set-server=utf8
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log_bin = mysql-bin
binlog_format = mixed
expire_logs_days = 30
binlog_cache_size = 1M

bind-address = 0.0.0.0
server-id = 1
# optimized my.cnf for MariaDB 5.5.x
# by eva2000
# vbtechsupport.com

tmpdir=/home/mysqltmp

skip-innodb
skip-federated
skip-archive
skip-name-resolve
skip-external-locking
back_log = 75
max_connections = 400
key_buffer_size = 1400M
myisam_sort_buffer_size = 64M
myisam_max_sort_file_size = 10G
join_buffer_size = 2M
read_buffer_size =1M
sort_buffer_size = 256K
table_definition_cache = 8192
table_open_cache = 6000
thread_cache_size = 256
interactive_timeout = 2400
wait_timeout = 2400
connect_timeout = 10
tmp_table_size = 256M
max_heap_table_size = 256M
max_allowed_packet = 64M
max_seeks_for_key = 1000
group_concat_max_len = 1024
max_length_for_sort_data = 1024
net_buffer_length = 16384
max_connect_errors = 1000
concurrent_insert = 2
read_rnd_buffer_size = 1M
bulk_insert_buffer_size = 8M
query_cache_limit = 1M
query_cache_size = 150M
query_cache_type = 1
query_cache_min_res_unit = 2K
query_prealloc_size = 262144
query_alloc_block_size = 65536
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
default-storage-engine = InnoDB
ft_min_word_len = 4

log_warnings=1
slow_query_log=1
long_query_time=0.1
slow_query_log_file=/var/lib/mysql/slowq.log
log-error=/var/log/mysqld.log

# innodb settings
performance_schema = 0
innodb_purge_threads=1
innodb_doublewrite = 1

innodb_file_per_table = 1
innodb_open_files = 4096
innodb_data_file_path= ibdata1:10M:autoextend
innodb_buffer_pool_size = 12G

innodb_log_files_in_group = 3
innodb_log_file_size = 2047M
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency = 0
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout=120
innodb_support_xa=1

# 200 * # DISKS
innodb_io_capacity = 100
innodb_read_io_threads = 64
innodb_write_io_threads = 64

# mariadb settings
[mariadb]
#thread-handling = pool-of-threads
#thread-pool-size= 20
#mysql --port=3307 --protocol=tcp
#extra-port=3307
#extra-max-connections=1

userstat = 0
key_cache_segments = 1
aria_group_commit = none
aria_group_commit_interval = 0
aria_log_file_size = 64M
aria_log_purge_type = immediate
aria_pagecache_buffer_size = 16M
aria_sort_buffer_size = 16M

[mariadb-5.5]
#ignore_db_dirs=
query_cache_strip_comments=0

innodb_read_ahead = linear
innodb_adaptive_flushing_method = estimate
innodb_flush_neighbor_pages = 1
innodb_stats_update_need_lock = 0
innodb_log_block_size = 512

log_slow_filter =admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk

[mysqld_safe]
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
#nice = -5
open-files-limit = 65535

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
ft_min_word_len = 4
key_buffer = 64M
sort_buffer = 64K
read_buffer = 16M
write_buffer = 16M

[mysqlhotcopy]
interactive-timeout

For dedicated mysql database server, be aware of Centmin Mod's default is to install CSF Firewall so would need to whitelist allow on Centmin Mod server's your web server's IP address, see these 4 links

• CSF - CSF Firewall info | Centmin Mod Community
• CSF Firewall - Centmin Mod - Menu based Nginx installer for CentOS servers
• Installing MySQL on a separate server | Centmin Mod Community
• MariaDB - Separate DB Server from Web | Centmin Mod Community

Again, for Nginx and PHP-FPM and MariaDB questions, you can post in relevant forum in future at Nginx, PHP-FPM & MariaDB MySQL and there's forums for web apps like forums and cms like wordpress here Centmin Mod Web App Usage | Centmin Mod Community

Finally, re-read the Getting Started guide to become more familiar with initial setup

Hope that helps

George

 

you can check PHPINFO page for PHP-FPM modules/extensions that are supported or via SSH command

Code:

php -m

default Centmin Mod PHP-FPM modules are the following and it has what you need for mbstring and SimpleXML support already.

Code:

[PHP Modules]
apc
bcmath
bz2
calendar
Core
ctype
curl
date
dom
enchant
ereg
exif
filter
ftp
gd
gettext
gmp
hash
iconv
igbinary
imagick
imap
intl
json
libxml
mbstring
mcrypt
memcache
memcached
mhash
mysql
mysqli
mysqlnd
openssl
pcntl
pcre
PDO
pdo_mysql
pdo_sqlite
Phar
posix
pspell
readline
Reflection
session
shmop
SimpleXML
soap
sockets
SPL
sqlite3
standard
sysvmsg
sysvsem
sysvshm
tidy
tokenizer
xml
xmlreader
xmlrpc
xmlwriter
zip
zlib

and specific extension info via SSH command line

Code:

php --ri phpextensionname

mbstring info

Code:

php --ri mbstring

mbstring

Multibyte Support => enabled
Multibyte string engine => libmbfl
HTTP input encoding translation => disabled
libmbfl version => 1.3.2

mbstring extension makes use of "streamable kanji code filter and converter", which is distributed under the GNU Lesser General Public License version 2.1.

Multibyte (japanese) regex support => enabled
Multibyte regex (oniguruma) backtrack check => On
Multibyte regex (oniguruma) version => 4.7.1

Directive => Local Value => Master Value
mbstring.language => neutral => neutral
mbstring.detect_order => no value => no value
mbstring.http_input => pass => pass
mbstring.http_output => pass => pass
mbstring.internal_encoding => no value => no value
mbstring.substitute_character => no value => no value
mbstring.func_overload => 0 => 0
mbstring.encoding_translation => Off => Off
mbstring.http_output_conv_mimetypes => ^(text/|application/xhtml\+xml) => ^(text/|application/xhtml\+xml)
mbstring.strict_detection => Off => Off

SimpleXML info

Code:

 php --ri SimpleXML

SimpleXML

Simplexml support => enabled
Revision => $Id: 16070fc92ad6f69cebb2d52ad3f02794f833ce39 $
Schema support => enabled

If you need to compile you own PHP extensions, here in Centmin Mod Insights forum there's a few examples showing you how.

Examples of adding additional PHP extensions include:

• PHP-FPM - Install PHP extension ssh2 | Centmin Mod Community
  
• PHP-FPM - Experimenting with APCu data only caching with PHP 5.5/Zend OpCache | Centmin Mod Community
• PHP-FPM - Ioncube on Centminmod ? | Centmin Mod Community
• PHP-FPM - Enabling PHP Fileinfo extension module | Centmin Mod Community
• PHP-FPM - How to enable php fileinfo? | Centmin Mod Community

To edit php.ini find the relevant config files from FAQ 16 and PHP-FPM page for command shortcuts which also shows default PHP-FPM compile options and setting a custom.ini for php.ini customisatons

 

For security reasons, only uninstall CSF Firewall if you have another alternate firewall in place. If you do, you can run CSF uninstall script

Code:

/etc/csf/uninstalll.sh

As to mysql error check your mysql error log which is listed along with all other log file locations as per How to troubleshoot Centmin Mod initial install issues (/var/log/mysqld.log) for clues which probably related to different sized innodb_log_file_size compared to the initialised innodb instance on initial Centmin Mod install which defaults to 48M size while the one I transposed is 2047M

you can confirm by changing /etc/my.cnf

back to

Code:

innodb_log_file_size = 48M

restart mysql server and see

if so, then to change from 48M to 2047M, read official MySQL documentation for Changing Size of InnoDB Log Files. Only move/delete the log files ib_logfile0, ib_logfile1 and in your case ib_logfile2 as you have 3.

 

As there's no optimisation advice provided by me, it's usually whatever amount your web app requires. As you have an existing forum/site, just look at your own servers memcached statistics page to see how much memory you usually use. For Centmin Mod, you can find the memcache.php statistics page to see how much memory you use and adjust accordingly. Posted details for the statistics pages on official site's Getting Started Guide and at PHP Opcode and Memcached statistics pages

To uninstall Nginx Pagespeed Nginx PageSpeed - How can I stop pagespeed? (If you want to remove ngx_pagespeed completely from Nginx see ngx_pagespeed install on Centmin Mod - Menu based Nginx installer for CentOS servers)

moved reply for varnish to Installing Varnish Cache ? | Centmin Mod Community

As to mysql error check your mysql error log which is listed along with all other log file locations as per How to troubleshoot Centmin Mod initial install issues (/var/log/mysqld.log) for clues it will tell you why MySQL can't start.

 

Code:

service mysql stop
rm -rf /var/lib/mysql/ib_logfile*
service mysql start

but only applies to MySQL 5.5 and below. MySQL 5.6 and MariaDB 10 can do it with just restarting mysql with new values without needing to remove ib_logfile*

 

MariaDB 10 has come along way