The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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:
    30,765
    Likes Received:
    662
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
  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