Please whitelist cPanel in your adblocker so that you’re able to see our version release promotions, thanks!

The Community Forums

Interact with an entire community of cPanel & WHM users!

My.cnf - Performance tweak, outperformed by slower server

Discussion in 'Workarounds and Optimization' started by TrebleMebbel, Sep 13, 2013.

  1. TrebleMebbel

    TrebleMebbel Registered

    Joined:
    Sep 13, 2013
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Good Morning Folks (morning in my timezone),

    I am looking for some advice on my current setup, we recently upgraded our hardware from a low budget R420 server to an R910 with the following specs:

    4 x CPU Intel(R) Xeon(R) CPU E7- 4870 @ 2.40GHz - 10 Core
    64 x 16GB DDR3 Memory
    1 x ioDrive2 1.2 TB

    Now, the R420 is outperforming the R910 by approx 30%. I have been asked to review this, however I have to admit I am new to MySQL on CentOS. Every bit of research that I have done on this, has brought me back to this forum, hence my post in the hope that someone is able to assist.

    My apologies in advance if I am doing something incorrect or if I am asking questions I should not be asking.

    If someone could review the cnf file to see whether anything is/ could obviously be causing the performance issues.

    Code:
    
    datadir		= /opt/mysql
    tmpdir          = /opt/tmp
    #general_log	= /var/log/mysqld.log
    user		= mysql
    port		= 3306
    socket		= /opt/mysql/mysql.sock
    # default-storage-engine=MyISAM
    sql_mode	= NO_AUTO_CREATE_USER
    sql-mode	= NO_AUTO_CREATE_USER
    init_connect='SET collation_connection = utf8_general_ci'
    init_connect='SET NAMES utf8'
    #init_file=/opt/mysql/buffer_pool_heatup.sql
    secure-auth	= 0
    #default-character-set=utf8
    collation-server=utf8_unicode_ci
    character-set-server=utf8
    #default-collation=utf8_general_ci
    #explicit_defaults_for_timestamp	= 0
    #skip-locking
    skip-external-locking
    # skip-bdb
    # skip-innodb
    myisam-recover
    #memlock
    skip-name-resolve
    symbolic-links=0
    skip-host-cache
    # performance_schema=0
    large-pages=1
    key_buffer = 2048M # 1024M
    max_allowed_packet = 32M
    table_cache = 2048
    table_definition_cache = 2048
    myisam_sort_buffer_size = 16M #only for create tables
    join_buffer_size = 3M #256K #4M
    read_buffer_size = 8M #16M
    read_rnd_buffer_size = 32M
    sort_buffer_size = 32M #256K #1G
    open_files_limit = 4096 #16384
    max_heap_table_size = 512M #maxsize before dumping to disk
    tmp_table_size = 512M
    
    thread_stack = 256K
    thread_cache_size = 16384 #128 
    query_cache_size = 0 #32M   #query_cache_size = 64M
    #query_cache_limit = 1M    #query_cache_limit = 64M
    query_cache_type = 0
    #query_prealloc_size = 64M
    #thread_concurrency = 24
    
    ;extension=mysql.so
    log-output = TABLE
    slow_query_log 
    log-queries-not-using-indexes
    log-slow-admin-statements
    #log-long-format
    #set-variable = long_query_time=5
    #set-variable = max_connections=200
    #set-variable = max_connect_errors=64
    max_connections = 255 
    #old_passwords = 1
    expire_logs_days = 7
    sync_binlog = 0 #1 is safer, but slower
    # max_binlog_size         = 104857600
    # tuning script adaptations
    concurrent_insert=2
    low_priority_updates=1
    max_write_lock_count=1
    
    # skip-networking
    # Replication Master Server (default)
    log-bin = /binlog/mysqlbin/dbase.log
    server-id	= 2
    binlog-format = MIXED
    
    # Point the following paths to different dedicated disks
    #log-update 	= /path-to-dedicated-directory/hostname
    
    # Uncomment the following if you are using BDB tables
    #bdb_cache_size = 384M
    #bdb_max_lock = 100000
    
    # Uncomment the following if you are using InnoDB tables
    innodb_data_home_dir = /opt/mysql/
    innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
    innodb_log_group_home_dir = /opt/mysql/
    #innodb_log_arch_dir = /var/lib/mysql/
    # You can set .._buffer_pool_size up to 50 - 80 %
    # of RAM but beware of setting memory usage too high
    innodb_checksums=0
    innodb_flush_method=O_DIRECT
    innodb_buffer_pool_size = 128G #40960M # 384G
    innodb_buffer_pool_restore_at_startup = 600 # periodically dump the buffer pool 
    innodb_blocking_buffer_pool_restore = 1
    #innodb_buffer_pool_instances = 64
    innodb_additional_mem_pool_size = 8M
    innodb_log_file_size = 1024M
    innodb_log_buffer_size = 8M
    #innodb_log_block_size=4096
    innodb_flush_log_at_trx_commit = 2 #1 for ACID 2 for speed
    innodb_lock_wait_timeout = 50
    innodb_file_per_table = true
    innodb_file_format=barracuda
    #innodb_use_sys_malloc = 0
    innodb_thread_concurrency=0
    innodb_io_capacity=30000
    innodb_doublewrite=0
    #innodb_support_xa=0
    #innodb_use_native_aio=1
    #hash index makes mysql an in-memory-database !! needs a lot of ram
    innodb_spin_wait_delay = 96 # default is 6 max 96 and 0 is disabled
    innodb_adaptive_hash_index=1 #try 0 if no speedup
    #innodb_lru_scan_depth=8192
    #innodb_adaptive_checkpoint=keep_average
    #innodb_read_ahead=0
    innodb_read_io_threads=16
    innodb_write_io_threads=16
    innodb_purge_threads=1
    innodb_flush_neighbor_pages=none
    innodb_max_dirty_pages_pct=60
    innodb_adaptive_flushing=0
    #innodb_use_sys_malloc=1
    innodb_old_blocks_pct=50
    innodb_adaptive_flushing_method = keep_average
    #NUMA
    innodb_buffer_pool_populate=1
    
    [mysqldump]
    quick
    max_allowed_packet = 16M
    
    [isamchk]
    key_buffer = 256M
    sort_buffer_size = 256M
    read_buffer = 2M
    write_buffer = 2M
    
    [myisamchk]
    key_buffer = 256M
    sort_buffer_size = 256M
    read_buffer = 2M
    write_buffer = 2M
    
    [mysqlhotcopy]
    interactive-timeout
    
    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
    #NUMA
    flush_caches=1
    numa_interleave=1
    open_files_limit=25000
    ulimit -l unlimited
    
    
    I hope someone can see the obvious things that I am unable to.

    Thank you in advance
     
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    43,660
    Likes Received:
    1,787
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  3. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Pretty amazing setup, but config very weird, inconsistent, like a copy/paste from many different configs

    Before optimizing it you need to mention at least :
    - mysql version (and if you have possiblity to upgrade)
    - used database engine (InnoDB or MyISAM)
    - mysqltuner / mysqlreport results


    All of those 3 are very important to help you
     
  4. TrebleMebbel

    TrebleMebbel Registered

    Joined:
    Sep 13, 2013
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    It seems the output that I have is different from what I have seen in previous posts. So, although I am almost certain this is not (attachement) what is needed to identify the root cause of the problem.

    I am just back from Holiday and see the issue persists, as stated previously I most certainly am not an expert on the matter, just looking for some enlightenment.

    Thanks in advance and my apologies for any inconvenience caused.
     

    Attached Files:

  5. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    OK,

    1. It seems like Hyper Threading on R910 is disabled, please enable it, you should have 80 cores
    each E7 - 4870 got 10 physical cores + 10 virtual (HT) = 20 per CPU, you got 4 CPUs = 80 cores
    btw. very nice setup

    2. You are using Percona XtraDB, thats great, but do you have an option to upgrade to 5.6 ?


    Can you run mysqltuner.pl
    https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl
    and mysqlreport
    http://hackmysql.com/scripts/mysqlreport
    tools

    since they provide info in better form,
    From your current data it is also possible to read it, but going thru mysql extended variables and counting the ratios/sums etc would take quite a while

    Once you send mysqltuner.pl and mysqlreport info, I will help you with the optimization :)

    btw. may I ask what application you run on such powerful machine ?
    It got 80 cores, how many mysql threads does your application usually use through the day ?

    mysql shows Max_used_connections | 1 |
    queries 651114
    uptime 15072
    gives 43 queries per second

    very few for such powerful server

    Best Regards
     
Loading...

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice