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.

Optimization for my.cnf

Discussion in 'Workarounds and Optimization' started by Bidi, Dec 14, 2014.

  1. Bidi

    Bidi Well-Known Member

    Joined:
    Oct 3, 2012
    Messages:
    51
    Likes Received:
    1
    Trophy Points:
    8
    Location:
    Romania, Transilvania
    cPanel Access Level:
    DataCenter Provider
    Hy guys, I really need the best optimization/configuration for "my.cnf"

    Currently for my.cnf I have the following:

    Code:
    [mysqld]
    slow-query-log=1
    long_query_time=1
    max_connections=100
    max_user_connections=50
    
    key_buffer_size=325M
    myisam_sort_buffer_size=64M
    
    join_buffer_size=10M
    sort_buffer_size=2M
    
    table_open_cache = 1000
    thread_cache_size = 25
    
    
    interactive_timeout=16
    wait_timeout=600
    connect_timeout=8
    
    max_allowed_packet=268435456
    max_connect_errors=10
    
    query_cache_limit=2M
    query_cache_size=128M
    query_cache_type=1
    
    open_files_limit=30000
    
    max_heap_table_size = 256M
    tmp_table_size = 256M
    
    innodb_buffer_pool_size = 256M
    
    tmpdir = "/home/mysqltmp"
    
    
    
    local-infile=0
    innodb_file_per_table=1
    default-storage-engine=MyISAM
    
    Processor Information
    Code:
    root@d1 [~]# cat /proc/cpuinfo
    processor       : 0
    vendor_id       : GenuineIntel
    cpu family      : 6
    model           : 23
    model name      : Intel(R) Xeon(R) CPU           L5420  @ 2.50GHz
    stepping        : 10
    cpu MHz         : 2499.983
    cache size      : 6144 KB
    physical id     : 0
    siblings        : 4
    core id         : 0
    cpu cores       : 4
    apicid          : 0
    initial apicid  : 0
    fpu             : yes
    fpu_exception   : yes
    cpuid level     : 13
    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 sse2 ss ht tm pbe syscall nx lm constant_tsc arch_perfmon pebs bts rep_good aperfmperf pni dtes64 monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr pdcm dca sse4_1 xsave lahf_lm dts tpr_shadow vnmi flexpriority
    bogomips        : 4999.96
    clflush size    : 64
    cache_alignment : 64
    address sizes   : 38 bits physical, 48 bits virtual
    power management:
    
    processor       : 1
    vendor_id       : GenuineIntel
    cpu family      : 6
    model           : 23
    model name      : Intel(R) Xeon(R) CPU           L5420  @ 2.50GHz
    stepping        : 10
    cpu MHz         : 2499.983
    cache size      : 6144 KB
    physical id     : 1
    siblings        : 4
    core id         : 0
    cpu cores       : 4
    apicid          : 4
    initial apicid  : 4
    fpu             : yes
    fpu_exception   : yes
    cpuid level     : 13
    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 sse2 ss ht tm pbe syscall nx lm constant_tsc arch_perfmon pebs bts rep_good aperfmperf pni dtes64 monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr pdcm dca sse4_1 xsave lahf_lm dts tpr_shadow vnmi flexpriority
    bogomips        : 5000.02
    clflush size    : 64
    cache_alignment : 64
    address sizes   : 38 bits physical, 48 bits virtual
    power management:
    
    processor       : 2
    vendor_id       : GenuineIntel
    cpu family      : 6
    model           : 23
    model name      : Intel(R) Xeon(R) CPU           L5420  @ 2.50GHz
    stepping        : 10
    cpu MHz         : 2499.983
    cache size      : 6144 KB
    physical id     : 0
    siblings        : 4
    core id         : 1
    cpu cores       : 4
    apicid          : 1
    initial apicid  : 1
    fpu             : yes
    fpu_exception   : yes
    cpuid level     : 13
    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 sse2 ss ht tm pbe syscall nx lm constant_tsc arch_perfmon pebs bts rep_good aperfmperf pni dtes64 monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr pdcm dca sse4_1 xsave lahf_lm dts tpr_shadow vnmi flexpriority
    bogomips        : 4999.96
    clflush size    : 64
    cache_alignment : 64
    address sizes   : 38 bits physical, 48 bits virtual
    power management:
    
    processor       : 3
    vendor_id       : GenuineIntel
    cpu family      : 6
    model           : 23
    model name      : Intel(R) Xeon(R) CPU           L5420  @ 2.50GHz
    stepping        : 10
    cpu MHz         : 2499.983
    cache size      : 6144 KB
    physical id     : 1
    siblings        : 4
    core id         : 1
    cpu cores       : 4
    apicid          : 5
    initial apicid  : 5
    fpu             : yes
    fpu_exception   : yes
    cpuid level     : 13
    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 sse2 ss ht tm pbe syscall nx lm constant_tsc arch_perfmon pebs bts rep_good aperfmperf pni dtes64 monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr pdcm dca sse4_1 xsave lahf_lm dts tpr_shadow vnmi flexpriority
    bogomips        : 5000.02
    clflush size    : 64
    cache_alignment : 64
    address sizes   : 38 bits physical, 48 bits virtual
    power management:
    
    processor       : 4
    vendor_id       : GenuineIntel
    cpu family      : 6
    model           : 23
    model name      : Intel(R) Xeon(R) CPU           L5420  @ 2.50GHz
    stepping        : 10
    cpu MHz         : 2499.983
    cache size      : 6144 KB
    physical id     : 0
    siblings        : 4
    core id         : 2
    cpu cores       : 4
    apicid          : 2
    initial apicid  : 2
    fpu             : yes
    fpu_exception   : yes
    cpuid level     : 13
    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 sse2 ss ht tm pbe syscall nx lm constant_tsc arch_perfmon pebs bts rep_good aperfmperf pni dtes64 monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr pdcm dca sse4_1 xsave lahf_lm dts tpr_shadow vnmi flexpriority
    bogomips        : 4999.96
    clflush size    : 64
    cache_alignment : 64
    address sizes   : 38 bits physical, 48 bits virtual
    power management:
    
    processor       : 5
    vendor_id       : GenuineIntel
    cpu family      : 6
    model           : 23
    model name      : Intel(R) Xeon(R) CPU           L5420  @ 2.50GHz
    stepping        : 10
    cpu MHz         : 2499.983
    cache size      : 6144 KB
    physical id     : 1
    siblings        : 4
    core id         : 2
    cpu cores       : 4
    apicid          : 6
    initial apicid  : 6
    fpu             : yes
    fpu_exception   : yes
    cpuid level     : 13
    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 sse2 ss ht tm pbe syscall nx lm constant_tsc arch_perfmon pebs bts rep_good aperfmperf pni dtes64 monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr pdcm dca sse4_1 xsave lahf_lm dts tpr_shadow vnmi flexpriority
    bogomips        : 5000.02
    clflush size    : 64
    cache_alignment : 64
    address sizes   : 38 bits physical, 48 bits virtual
    power management:
    
    processor       : 6
    vendor_id       : GenuineIntel
    cpu family      : 6
    model           : 23
    model name      : Intel(R) Xeon(R) CPU           L5420  @ 2.50GHz
    stepping        : 10
    cpu MHz         : 2499.983
    cache size      : 6144 KB
    physical id     : 0
    siblings        : 4
    core id         : 3
    cpu cores       : 4
    apicid          : 3
    initial apicid  : 3
    fpu             : yes
    fpu_exception   : yes
    cpuid level     : 13
    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 sse2 ss ht tm pbe syscall nx lm constant_tsc arch_perfmon pebs bts rep_good aperfmperf pni dtes64 monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr pdcm dca sse4_1 xsave lahf_lm dts tpr_shadow vnmi flexpriority
    bogomips        : 4999.96
    clflush size    : 64
    cache_alignment : 64
    address sizes   : 38 bits physical, 48 bits virtual
    power management:
    
    processor       : 7
    vendor_id       : GenuineIntel
    cpu family      : 6
    model           : 23
    model name      : Intel(R) Xeon(R) CPU           L5420  @ 2.50GHz
    stepping        : 10
    cpu MHz         : 2499.983
    cache size      : 6144 KB
    physical id     : 1
    siblings        : 4
    core id         : 3
    cpu cores       : 4
    apicid          : 7
    initial apicid  : 7
    fpu             : yes
    fpu_exception   : yes
    cpuid level     : 13
    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 sse2 ss ht tm pbe syscall nx lm constant_tsc arch_perfmon pebs bts rep_good aperfmperf pni dtes64 monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr pdcm dca sse4_1 xsave lahf_lm dts tpr_shadow vnmi flexpriority
    bogomips        : 5000.02
    clflush size    : 64
    cache_alignment : 64
    address sizes   : 38 bits physical, 48 bits virtual
    power management:
    
    Code:
    root@d1 [~]# cat /proc/meminfo
    MemTotal:       16295916 kB
    
    MySQLTuner
    Code:
    >>  MySQLTuner 1.3.0 mod - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  Modified by George Liu (eva2000) at http://vbtechsupport.com/
     >>  Run with '--help' for additional options and output filtering
    [OK] Currently running supported MySQL version 5.6.21-log
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
    [--] Data in MyISAM tables: 14G (Tables: 12876)
    [--] Data in InnoDB tables: 547M (Tables: 1679)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
    [--] Data in MEMORY tables: 0B (Tables: 20)
    [!!] Total fragmented tables: 335
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 12m 52s (82K q [107.259 qps], 1K conn, TX: 556M, RX: 17M)
    [--] Reads / Writes: 78% / 22%
    [--] Total buffers: 981.0M global + 12.6M per thread (100 max threads)
    [OK] Maximum possible memory usage: 2.2G (14% of installed RAM)
    [OK] Slow queries: 1% (1K/82K)
    [!!] Highest connection usage: 100%  (101/100)
    [OK] Key buffer size / total MyISAM indexes: 325.0M/9.2G
    [OK] Key buffer hit rate: 99.8% (12M cached / 19K reads)
    [OK] Query cache efficiency: 60.4% (41K cached / 68K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 7K sorts)
    [!!] Joins performed without indexes: 38 (see join_buffer_size note below)
    [!!] Temporary tables created on disk: 36% (1K on disk / 4K total)
    [OK] Thread cache hit rate: 92% (152 created / 1K connections)
    [OK] Table cache hit rate: 61% (1K open / 1K opened)
    [OK] Open file limit used: 4% (1K/30K)
    [OK] Table locks acquired immediately: 98% (39K immediate / 39K locks)
    [!!] InnoDB  buffer pool / data size: 256.0M/547.8M
    [OK] InnoDB log waits: 0
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Reduce or eliminate persistent connections to reduce connection usage
        Adjust your join queries to always utilize indexes. Please note this
        calculation is made by adding Select_full_join + Select_range_check
        status values and triggered when the total >250
        Temporary table size is already large - reduce result set size
        Reduce your SELECT DISTINCT queries without LIMIT clauses
    
    Variables to adjust:
        max_connections (> 100)
        wait_timeout (< 600)
        interactive_timeout (< 16)
        join_buffer_size (> 10.0M, or always use indexes with joins)
        innodb_buffer_pool_size (>= 547M)
    
    before running mysqtunner i had to restart my mysql because of the high memory usage
    Thank you for your help!!
     
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,678
    Likes Received:
    654
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
  3. Bidi

    Bidi Well-Known Member

    Joined:
    Oct 3, 2012
    Messages:
    51
    Likes Received:
    1
    Trophy Points:
    8
    Location:
    Romania, Transilvania
    cPanel Access Level:
    DataCenter Provider
    OKa no problem i will whait shoud i edit this prost or just post directly here ?
     
  4. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,678
    Likes Received:
    654
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
  5. Bidi

    Bidi Well-Known Member

    Joined:
    Oct 3, 2012
    Messages:
    51
    Likes Received:
    1
    Trophy Points:
    8
    Location:
    Romania, Transilvania
    cPanel Access Level:
    DataCenter Provider
    Code:
    >>  MySQLTuner 1.4.0 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at [url=http://mysqltuner.com/]MySQLTuner-perl by major[/url]
     >>  Run with '--help' for additional options and output filtering
    [OK] Currently running supported MySQL version 5.6.21-log
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
    [--] Data in MyISAM tables: 12G (Tables: 12854)
    [--] Data in InnoDB tables: 157M (Tables: 1656)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
    [--] Data in MEMORY tables: 0B (Tables: 20)
    [!!] Total fragmented tables: 321
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 2d 7h 2m 38s (32M q [164.523 qps], 852K conn, TX: 263B, RX: 6B)
    [--] Reads / Writes: 80% / 20%
    [--] Total buffers: 1.0G global + 14.6M per thread (120 max threads)
    [OK] Maximum possible memory usage: 2.7G (17% of installed RAM)
    [OK] Slow queries: 0% (18/32M)
    [OK] Highest usage of available connections: 45% (55/120)
    [OK] Key buffer size / total MyISAM indexes: 325.0M/9.0G
    [OK] Key buffer hit rate: 99.7% (917M cached / 2M reads)
    [OK] Query cache efficiency: 70.3% (19M cached / 27M selects)
    [!!] Query cache prunes per day: 1163679
    [OK] Sorts requiring temporary tables: 0% (7 temp sorts / 2M sorts)
    [!!] Joins performed without indexes: 41344
    [!!] Temporary tables created on disk: 50% (478K on disk / 949K total)
    [OK] Thread cache hit rate: 99% (444 created / 852K connections)
    [!!] Table cache hit rate: 0% (500 open / 443K opened)
    [OK] Open file limit used: 2% (810/31K)
    [OK] Table locks acquired immediately: 99% (11M immediate / 11M locks)
    [OK] InnoDB buffer pool / data size: 512.0M/157.1M
    [OK] InnoDB log waits: 0
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Adjust your join queries to always utilize indexes
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
        Increase table_open_cache gradually to avoid file descriptor limits
        Read this before increasing table_open_cache over 64: [url=http://bit.ly/1mi7c4C]table_cache negative scalability - MySQL Performance Blog[/url]
    Variables to adjust:
        query_cache_size (> 64M)
        join_buffer_size (> 12.0M, or always use indexes with joins)
        tmp_table_size (> 128M)
        max_heap_table_size (> 128M)
        table_open_cache (> 500)
    
    
    Report Complete:
    Wed Dec 17 11:59:20 EET 2014
    ----------------------------
    
    and this is my.

    Code:
    [mysqld]
    slow-query-log=1
    long_query_time=5
    max_connections=120
    max_user_connections=50
    
    key_buffer_size=325M
    myisam_sort_buffer_size=64M
    
    join_buffer_size=12M
    sort_buffer_size=2M
    
    
    table_open_cache = 500
    thread_cache_size = 25
    
    
    interactive_timeout=5
    wait_timeout=300
    connect_timeout=8
    
    max_allowed_packet=268435456
    max_connect_errors=10
    
    
    query_cache_size=64M
    query_cache_limit=2M
    query_cache_type=1
    
    open_files_limit=31000
    
    tmp_table_size = 128M
    max_heap_table_size = 128M
    
    
    innodb_buffer_pool_size = 512M
    
    tmpdir = "/home/mysqltmp"
    
    
    
    local-infile=0
    innodb_file_per_table=1
    default-storage-engine=MyISAM
    
    the top one was another one witch i was using and i mixed un

    Tnx :D
     
    #5 Bidi, Dec 17, 2014
    Last edited: Dec 17, 2014
  6. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,678
    Likes Received:
    654
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Note that you may need to post the results on another forum such as WHT if you don't receive enough user-feedback on our forums here.

    Thank you.
     
Loading...

Share This Page