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 best settings

Discussion in 'Workarounds and Optimization' started by nickfou, Jan 5, 2015.

  1. nickfou

    nickfou Registered

    Joined:
    Jan 5, 2015
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hello, i need your help for my.cnf. My server information:

    Code:
    # cat /proc/cpuinfo
    processor       : 0
    vendor_id       : GenuineIntel
    cpu family      : 6
    model           : 26
    model name      : Intel(R) Core(TM) i7 CPU         950  @ 3.07GHz
    stepping        : 5
    microcode       : 17
    cpu MHz         : 3068.000
    cache size      : 8192 KB
    physical id     : 0
    siblings        : 8
    core id         : 0
    cpu cores       : 4
    apicid          : 0
    initial apicid  : 0
    fpu             : yes
    fpu_exception   : yes
    cpuid level     : 11
    wp              : yes
    flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse s                                                                                                                    se2 ss ht tm pbe syscall nx rdtscp lm constant_tsc arch_perfmon pebs bts rep_good xtopology nonstop_tsc aperfmperf pni dt                                                                                                                    es64 monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr pdcm sse4_1 sse4_2 popcnt lahf_lm ida dts tpr_shadow vnmi flexpriority ep                                                                                                                    t vpid
    bogomips        : 6148.07
    clflush size    : 64
    cache_alignment : 64
    address sizes   : 36 bits physical, 48 bits virtual
    power management:
    
    processor       : 1
    vendor_id       : GenuineIntel
    cpu family      : 6
    model           : 26
    model name      : Intel(R) Core(TM) i7 CPU         950  @ 3.07GHz
    stepping        : 5
    microcode       : 17
    cpu MHz         : 1600.000
    cache size      : 8192 KB
    physical id     : 0
    siblings        : 8
    core id         : 1
    cpu cores       : 4
    apicid          : 2
    initial apicid  : 2
    fpu             : yes
    fpu_exception   : yes
    cpuid level     : 11
    wp              : yes
    flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse s                                                                                                                    se2 ss ht tm pbe syscall nx rdtscp lm constant_tsc arch_perfmon pebs bts rep_good xtopology nonstop_tsc aperfmperf pni dt                                                                                                                    es64 monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr pdcm sse4_1 sse4_2 popcnt lahf_lm ida dts tpr_shadow vnmi flexpriority ep                                                                                                                    t vpid
    bogomips        : 6148.07
    clflush size    : 64
    cache_alignment : 64
    address sizes   : 36 bits physical, 48 bits virtual
    power management:
    
    processor       : 2
    vendor_id       : GenuineIntel
    cpu family      : 6
    model           : 26
    model name      : Intel(R) Core(TM) i7 CPU         950  @ 3.07GHz
    stepping        : 5
    microcode       : 17
    cpu MHz         : 1600.000
    cache size      : 8192 KB
    physical id     : 0
    siblings        : 8
    core id         : 2
    cpu cores       : 4
    apicid          : 4
    initial apicid  : 4
    fpu             : yes
    fpu_exception   : yes
    cpuid level     : 11
    wp              : yes
    flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse s                                                                                                                    se2 ss ht tm pbe syscall nx rdtscp lm constant_tsc arch_perfmon pebs bts rep_good xtopology nonstop_tsc aperfmperf pni dt                                                                                                                    es64 monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr pdcm sse4_1 sse4_2 popcnt lahf_lm ida dts tpr_shadow vnmi flexpriority ep                                                                                                                    t vpid
    bogomips        : 6148.07
    clflush size    : 64
    cache_alignment : 64
    address sizes   : 36 bits physical, 48 bits virtual
    power management:
    
    processor       : 3
    vendor_id       : GenuineIntel
    cpu family      : 6
    model           : 26
    model name      : Intel(R) Core(TM) i7 CPU         950  @ 3.07GHz
    stepping        : 5
    microcode       : 17
    cpu MHz         : 1600.000
    cache size      : 8192 KB
    physical id     : 0
    siblings        : 8
    core id         : 3
    cpu cores       : 4
    apicid          : 6
    initial apicid  : 6
    fpu             : yes
    fpu_exception   : yes
    cpuid level     : 11
    wp              : yes
    flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse s                                                                                                                    se2 ss ht tm pbe syscall nx rdtscp lm constant_tsc arch_perfmon pebs bts rep_good xtopology nonstop_tsc aperfmperf pni dt                                                                                                                    es64 monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr pdcm sse4_1 sse4_2 popcnt lahf_lm ida dts tpr_shadow vnmi flexpriority ep                                                                                                                    t vpid
    bogomips        : 6148.07
    clflush size    : 64
    cache_alignment : 64
    address sizes   : 36 bits physical, 48 bits virtual
    power management:
    
    processor       : 4
    vendor_id       : GenuineIntel
    cpu family      : 6
    model           : 26
    model name      : Intel(R) Core(TM) i7 CPU         950  @ 3.07GHz
    stepping        : 5
    microcode       : 17
    cpu MHz         : 1600.000
    cache size      : 8192 KB
    physical id     : 0
    siblings        : 8
    core id         : 0
    cpu cores       : 4
    apicid          : 1
    initial apicid  : 1
    fpu             : yes
    fpu_exception   : yes
    cpuid level     : 11
    wp              : yes
    flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse s                                                                                                                    se2 ss ht tm pbe syscall nx rdtscp lm constant_tsc arch_perfmon pebs bts rep_good xtopology nonstop_tsc aperfmperf pni dt                                                                                                                    es64 monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr pdcm sse4_1 sse4_2 popcnt lahf_lm ida dts tpr_shadow vnmi flexpriority ep                                                                                                                    t vpid
    bogomips        : 6148.07
    clflush size    : 64
    cache_alignment : 64
    address sizes   : 36 bits physical, 48 bits virtual
    power management:
    
    processor       : 5
    vendor_id       : GenuineIntel
    cpu family      : 6
    model           : 26
    model name      : Intel(R) Core(TM) i7 CPU         950  @ 3.07GHz
    stepping        : 5
    microcode       : 17
    cpu MHz         : 3068.000
    cache size      : 8192 KB
    physical id     : 0
    siblings        : 8
    core id         : 1
    cpu cores       : 4
    apicid          : 3
    initial apicid  : 3
    fpu             : yes
    fpu_exception   : yes
    cpuid level     : 11
    wp              : yes
    flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse s                                                                                                                    se2 ss ht tm pbe syscall nx rdtscp lm constant_tsc arch_perfmon pebs bts rep_good xtopology nonstop_tsc aperfmperf pni dt                                                                                                                    es64 monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr pdcm sse4_1 sse4_2 popcnt lahf_lm ida dts tpr_shadow vnmi flexpriority ep                                                                                                                    t vpid
    bogomips        : 6148.07
    clflush size    : 64
    cache_alignment : 64
    address sizes   : 36 bits physical, 48 bits virtual
    power management:
    
    processor       : 6
    vendor_id       : GenuineIntel
    cpu family      : 6
    model           : 26
    model name      : Intel(R) Core(TM) i7 CPU         950  @ 3.07GHz
    stepping        : 5
    microcode       : 17
    cpu MHz         : 1600.000
    cache size      : 8192 KB
    physical id     : 0
    siblings        : 8
    core id         : 2
    cpu cores       : 4
    apicid          : 5
    initial apicid  : 5
    fpu             : yes
    fpu_exception   : yes
    cpuid level     : 11
    wp              : yes
    flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse s                                                                                                                    se2 ss ht tm pbe syscall nx rdtscp lm constant_tsc arch_perfmon pebs bts rep_good xtopology nonstop_tsc aperfmperf pni dt                                                                                                                    es64 monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr pdcm sse4_1 sse4_2 popcnt lahf_lm ida dts tpr_shadow vnmi flexpriority ep                                                                                                                    t vpid
    bogomips        : 6148.07
    clflush size    : 64
    cache_alignment : 64
    address sizes   : 36 bits physical, 48 bits virtual
    power management:
    
    processor       : 7
    vendor_id       : GenuineIntel
    cpu family      : 6
    model           : 26
    model name      : Intel(R) Core(TM) i7 CPU         950  @ 3.07GHz
    stepping        : 5
    microcode       : 17
    cpu MHz         : 1600.000
    cache size      : 8192 KB
    physical id     : 0
    siblings        : 8
    core id         : 3
    cpu cores       : 4
    apicid          : 7
    initial apicid  : 7
    fpu             : yes
    fpu_exception   : yes
    cpuid level     : 11
    wp              : yes
    flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse s                                                                                                                    se2 ss ht tm pbe syscall nx rdtscp lm constant_tsc arch_perfmon pebs bts rep_good xtopology nonstop_tsc aperfmperf pni dt                                                                                                                    es64 monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr pdcm sse4_1 sse4_2 popcnt lahf_lm ida dts tpr_shadow vnmi flexpriority ep                                                                                                                    t vpid
    bogomips        : 6148.07
    clflush size    : 64
    cache_alignment : 64
    address sizes   : 36 bits physical, 48 bits virtual
    power management:
    
    Memory:

    Code:
    MemTotal: 24598412 kB
    Mysqltuner.pl:

    Code:
    # ./mysqltuner.pl
    
     >>  MySQLTuner 1.4.0 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  Run with '--help' for additional options and output filtering
    [OK] Currently running supported MySQL version 5.5.40-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
    [--] Data in MyISAM tables: 902M (Tables: 110)
    [--] Data in InnoDB tables: 33M (Tables: 156)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [!!] Total fragmented tables: 156
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 52d 20h 49m 13s (266M q [58.301 qps], 4M conn, TX: 12947B, RX: 38B)
    [--] Reads / Writes: 83% / 17%
    [--] Total buffers: 168.0M global + 2.8M per thread (151 max threads)
    [OK] Maximum possible memory usage: 583.2M (2% of installed RAM)
    [OK] Slow queries: 0% (7/266M)
    [OK] Highest usage of available connections: 56% (86/151)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/489.9M
    [OK] Key buffer hit rate: 99.9% (67B cached / 41M reads)
    [!!] Query cache is disabled
    [OK] Sorts requiring temporary tables: 0% (565K temp sorts / 95M sorts)
    [!!] Temporary tables created on disk: 75% (26M on disk / 34M total)
    [!!] Thread cache is disabled
    [!!] Table cache hit rate: 0% (398 open / 4M opened)
    [OK] Open file limit used: 3% (319/10K)
    [OK] Table locks acquired immediately: 99% (276M immediate / 276M locks)
    [OK] InnoDB buffer pool / data size: 128.0M/33.6M
    [OK] InnoDB log waits: 0
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Enable the slow query log to troubleshoot bad queries
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
        Set thread_cache_size to 4 as a starting value
        Increase table_open_cache gradually to avoid file descriptor limits
        Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
    Variables to adjust:
        query_cache_size (>= 8M)
        tmp_table_size (> 16M)
        max_heap_table_size (> 16M)
        thread_cache_size (start at 4)
        table_open_cache (> 400)
    
    my.cnf now:

    Code:
    [mysqld]
    innodb_file_per_table=0
    max_allowed_packet=268435456
    innodb_buffer_pool_size=134217728
    open_files_limit=10000
    
    I running only 4 wordpress sites in this server. Only one have high traffic 4.000 - 6.000 visitors per day. Sometimes exceeds 10.000 visitors per day. So what are the best settings for my.cnf?
     
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    675
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Hello :)

    Could you elaborate further on why you decided to modify these settings? Were you experiencing a specific issue or some type of reduced performance?

    Thank you.
     
  3. nickfou

    nickfou Registered

    Joined:
    Jan 5, 2015
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    There is no serious problem, i want to know if can increase performance. Problems exist only when i have multiple users online (300-350 per minute, source google analytics).
     
  4. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    675
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Note that you may want to consult with a qualified system administrator for assistance with tuning your MySQL configuration if you do not receive sufficient user-feedback on this thread.

    Thank you.
     
Loading...

Share This Page