Discover Centmin Mod today
Register Now

Help me for my.cnf config for Dedicated Server

Discussion in 'Introductions' started by Talia Olga, Sep 28, 2014.

  1. Talia Olga

    Talia Olga Member

    40
    0
    6
    Sep 28, 2014
    Ratings:
    +0
    Local Time:
    5:11 AM
    1.7.5
    5.x.x
    I have several dedicated server.
    no i don't have any hosting business
    i have a site with 1M+ million pageview every day
    and 20000 unique visitor per day. (stat provided by cloudflare and google ana)
    in my life i never ever use any cp for server.
    before i use my hand written shall script for installing the stack
    currently am using nginx with varnish, mariadb with redis, php-fpm with zend optimizer plus,
    firewall is APF. keep it simple for best performence
    my all servers are softlayer baremetal.
    just yesterday i installed centminmod for testing pupose.
    i wanna use a dedi for dedi database server.
    so my example my cnf is like that
    PS: Coreā„¢ i5-3570S @ 3.1 GHz+, 16 GB Ram

    [client]
    port = 3306
    socket = /tmp/mysql.sock

    [mysqld]
    port = 3306
    socket = /tmp/mysql.sock

    basedir = /usr/local/mariadb
    datadir = /data/mariadb
    pid-file = /data/mariadb/mysql.pid
    user = mysql
    bind-address = 0.0.0.0
    server-id = 1

    skip-name-resolve
    #skip-networking
    back_log = 75

    max_connections = 400
    max_connect_errors = 1000
    open_files_limit = 65535
    table_open_cache = 6000
    max_allowed_packet = 32M
    binlog_cache_size = 1M
    max_heap_table_size = 256M
    tmp_table_size = 256M

    read_buffer_size = 1M
    read_rnd_buffer_size = 524M
    sort_buffer_size = 256k
    join_buffer_size = 2M
    key_buffer_size = 1400M

    thread_cache_size = 64

    query_cache_type = 1
    query_cache_size = 150M
    query_cache_limit = 1M

    ft_min_word_len = 4

    log_bin = mysql-bin
    binlog_format = mixed
    expire_logs_days = 30

    log_error = /data/mariadb/mysql-error.log
    slow_query_log = 1
    long_query_time = 0.1
    slow_query_log_file = /data/mariadb/mysql-slow.log

    performance_schema = 0

    #lower_case_table_names = 1

    skip-external-locking

    default_storage_engine = InnoDB
    #default-storage-engine = MyISAM
    innodb_file_per_table = 1
    innodb_open_files = 4096
    innodb_buffer_pool_size = 12G
    innodb_write_io_threads = 64
    innodb_read_io_threads = 64
    innodb_thread_concurrency = 0
    innodb_purge_threads = 1
    innodb_flush_log_at_trx_commit = 2
    innodb_log_buffer_size = 64M
    innodb_log_file_size = 2047M
    innodb_log_files_in_group = 3
    innodb_max_dirty_pages_pct = 90
    innodb_lock_wait_timeout = 120

    bulk_insert_buffer_size = 8M
    myisam_sort_buffer_size = 64M
    myisam_max_sort_file_size = 10G
    myisam_repair_threads = 1

    interactive_timeout = 2400
    wait_timeout = 2400

    [mysqldump]
    quick
    max_allowed_packet = 16M

    [myisamchk]
    key_buffer_size = 64M
    sort_buffer_size = 64M
    read_buffer = 16M
    write_buffer = 16M

    currently i have 10M+ post and daily added 7 thousand +

    PS: i make this conf reading this articles:
    mysql - InnoDB performance tweaks - Stack Overflow
    MySQL optimization for a 16GB ram server. - cPanel Forums
    Mysql Performance tuning and Optimize Mysql response time
    Ubuntu MySQL 5.1 Configuration - 4GB RAM, Heavy InnoDB

    NGINX conf i follow:
    High performance web server with Nginx and PHP-FPM | Devlog | Lukasz Cepowski | DevOp | Software Developer | System Administrator | High Performance Web Applications | Server Side Software | Consulting

    php-fpm:
    PHP-FPM Configuration 101 (php-fpm.conf, www.conf) | myjeeva blog
    Nginx and PHP-FPM for heavy load wordpress web server with high traffic 2000+ concurrent connections
    Optimizing NGINX and PHP-fpm for high traffic sites - Software Projects Inc.

    Linux Core:
    ulimit -n
    was 1024
    now increased : 65532

    OS: centos 6.5 x64


    i am using CMS as WORDPRESS
    I convert all db table from myIsam to Innodb
    so in centminmod what should be my innodb conf anf my.cnf for that dedi config????

    and if some one provide me the config of:
    php.ini optimize for dedi
    php-fpm.conf optimize for dedi
    nginx.conf for thr dedi
    memcache and memcached conf
    wp-config.php (memory allocator)

    that should be really good for me
    and am expecting the main author of centminmod will reply for this thread.
    thank you
     
    Last edited: Sep 28, 2014
  2. eva2000

    eva2000 Administrator Staff Member

    29,023
    6,586
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,776
    Local Time:
    10:11 PM
    Nginx 1.13.x
    MariaDB 5.5
    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
    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
     
  3. Talia Olga

    Talia Olga Member

    40
    0
    6
    Sep 28, 2014
    Ratings:
    +0
    Local Time:
    5:11 AM
    1.7.5
    5.x.x
    @eva2000
    thank you so much for quick reply.
    i have another quick question:
    i need to enable "mb_detect_encoding", "SimpleXML"
    how do i check that this 2 function is working properly??
     
  4. eva2000

    eva2000 Administrator Staff Member

    29,023
    6,586
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,776
    Local Time:
    10:11 PM
    Nginx 1.13.x
    MariaDB 5.5
    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:
    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
     
    • Like Like x 1
  5. Talia Olga

    Talia Olga Member

    40
    0
    6
    Sep 28, 2014
    Ratings:
    +0
    Local Time:
    5:11 AM
    1.7.5
    5.x.x
    oh, thanks
    the problem was for csf firewall. it blocks one of my soft.
    oh, i already installed APF firewall in centos 6.6 x64
    i never use this with centos
    i install like that:
    Code:
    wget http://apt.sw.be/redhat/el6/en/x86_64/rpmforge/RPMS/apf-9.7_1-1.el6.rf.noarch.rpm
    sudo rpm --nodeps -i apf-9.7_1-1.el6.rf.noarch.rpm
    vi /etc/apf/conf.apf
    
    DEVEL_MODE="0"
    IG_TCP_CPORTS="21,22,25,53,80,110,143,443,3306"
    IG_UDP_CPORTS="53,111"
    
    /etc/init.d/apf restart
    and stop csf with this cmd
    csf -x
    how can i uninstall CSF??
    how much memcache allocate memory is best foe 16 gb ram??
    i never ever use memcache, always use REDIS
     
    Last edited: Sep 29, 2014
  6. Talia Olga

    Talia Olga Member

    40
    0
    6
    Sep 28, 2014
    Ratings:
    +0
    Local Time:
    5:11 AM
    1.7.5
    5.x.x
    after copy paste your my.cnf. i got this error
    >> mysqlrestart
    ERROR! MySQL server PID file could not be found!
    Starting MySQL. ERROR!
    WHy??
     
  7. eva2000

    eva2000 Administrator Staff Member

    29,023
    6,586
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,776
    Local Time:
    10:11 PM
    Nginx 1.13.x
    MariaDB 5.5
    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.
     
    • Like Like x 1
  8. Talia Olga

    Talia Olga Member

    40
    0
    6
    Sep 28, 2014
    Ratings:
    +0
    Local Time:
    5:11 AM
    1.7.5
    5.x.x
    how much memory is best for memcache for 16 gb??
     
  9. Talia Olga

    Talia Olga Member

    40
    0
    6
    Sep 28, 2014
    Ratings:
    +0
    Local Time:
    5:11 AM
    1.7.5
    5.x.x
    how i will uninstall pagespeed and install varnish??
     
  10. Talia Olga

    Talia Olga Member

    40
    0
    6
    Sep 28, 2014
    Ratings:
    +0
    Local Time:
    5:11 AM
    1.7.5
    5.x.x
    NO, I AM GETTING THE SAME ERROR
     
  11. eva2000

    eva2000 Administrator Staff Member

    29,023
    6,586
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,776
    Local Time:
    10:11 PM
    Nginx 1.13.x
    MariaDB 5.5
    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.
     
    Last edited: Sep 29, 2014
  12. RoldanLT

    RoldanLT Well-Known Member

    3,830
    929
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,258
    Local Time:
    8:11 PM
    1.11
    10.2
    This command will do it right?
     
  13. eva2000

    eva2000 Administrator Staff Member

    29,023
    6,586
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,776
    Local Time:
    10:11 PM
    Nginx 1.13.x
    MariaDB 5.5
    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*
     
    • Informative Informative x 1
  14. RoldanLT

    RoldanLT Well-Known Member

    3,830
    929
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,258
    Local Time:
    8:11 PM
    1.11
    10.2
    Aw :D
    Then much better :).
    Didn't know that :).
     
  15. eva2000

    eva2000 Administrator Staff Member

    29,023
    6,586
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,776
    Local Time:
    10:11 PM
    Nginx 1.13.x
    MariaDB 5.5
    MariaDB 10 has come along way :D