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.

I need to optimize mysql

Discussion in 'Workarounds and Optimization' started by zontrakulla, Jan 19, 2015.

  1. zontrakulla

    zontrakulla Active Member

    Joined:
    Jan 16, 2008
    Messages:
    39
    Likes Received:
    0
    Trophy Points:
    6
    Hello, i need your help for some optimization.

    My server information:

    Code:
    processor       : 0
    vendor_id       : GenuineIntel
    cpu family      : 6
    model           : 58
    model name      : Intel(R) Xeon(R) CPU E3-1245 V2 @ 3.40GHz
    stepping        : 9
    cpu MHz         : 3400.068
    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     : 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 rdtscp lm constant_tsc arch_perfmon pebs bts rep_good xtopology nonstop_tsc aperfmperf pni pclmulqdq dtes64 monitor ds_cpl vmx smx est tm2 ssse3 cx16 xtpr pdcm pcid sse4_1 sse4_2 x2apic popcnt tsc_deadline_timer aes xsave avx f16c rdrand lahf_lm ida arat epb xsaveopt pln pts dts tpr_shadow vnmi flexpriority ept vpid fsgsbase smep erms
    bogomips        : 6800.13
    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           : 58
    model name      : Intel(R) Xeon(R) CPU E3-1245 V2 @ 3.40GHz
    stepping        : 9
    cpu MHz         : 3400.068
    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     : 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 rdtscp lm constant_tsc arch_perfmon pebs bts rep_good xtopology nonstop_tsc aperfmperf pni pclmulqdq dtes64 monitor ds_cpl vmx smx est tm2 ssse3 cx16 xtpr pdcm pcid sse4_1 sse4_2 x2apic popcnt tsc_deadline_timer aes xsave avx f16c rdrand lahf_lm ida arat epb xsaveopt pln pts dts tpr_shadow vnmi flexpriority ept vpid fsgsbase smep erms
    bogomips        : 6800.13
    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           : 58
    model name      : Intel(R) Xeon(R) CPU E3-1245 V2 @ 3.40GHz
    stepping        : 9
    cpu MHz         : 3400.068
    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     : 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 rdtscp lm constant_tsc arch_perfmon pebs bts rep_good xtopology nonstop_tsc aperfmperf pni pclmulqdq dtes64 monitor ds_cpl vmx smx est tm2 ssse3 cx16 xtpr pdcm pcid sse4_1 sse4_2 x2apic popcnt tsc_deadline_timer aes xsave avx f16c rdrand lahf_lm ida arat epb xsaveopt pln pts dts tpr_shadow vnmi flexpriority ept vpid fsgsbase smep erms
    bogomips        : 6800.13
    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           : 58
    model name      : Intel(R) Xeon(R) CPU E3-1245 V2 @ 3.40GHz
    stepping        : 9
    cpu MHz         : 3400.068
    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     : 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 rdtscp lm constant_tsc arch_perfmon pebs bts rep_good xtopology nonstop_tsc aperfmperf pni pclmulqdq dtes64 monitor ds_cpl vmx smx est tm2 ssse3 cx16 xtpr pdcm pcid sse4_1 sse4_2 x2apic popcnt tsc_deadline_timer aes xsave avx f16c rdrand lahf_lm ida arat epb xsaveopt pln pts dts tpr_shadow vnmi flexpriority ept vpid fsgsbase smep erms
    bogomips        : 6800.13
    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           : 58
    model name      : Intel(R) Xeon(R) CPU E3-1245 V2 @ 3.40GHz
    stepping        : 9
    cpu MHz         : 3400.068
    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     : 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 rdtscp lm constant_tsc arch_perfmon pebs bts rep_good xtopology nonstop_tsc aperfmperf pni pclmulqdq dtes64 monitor ds_cpl vmx smx est tm2 ssse3 cx16 xtpr pdcm pcid sse4_1 sse4_2 x2apic popcnt tsc_deadline_timer aes xsave avx f16c rdrand lahf_lm ida arat epb xsaveopt pln pts dts tpr_shadow vnmi flexpriority ept vpid fsgsbase smep erms
    bogomips        : 6800.13
    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           : 58
    model name      : Intel(R) Xeon(R) CPU E3-1245 V2 @ 3.40GHz
    stepping        : 9
    cpu MHz         : 3400.068
    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     : 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 rdtscp lm constant_tsc arch_perfmon pebs bts rep_good xtopology nonstop_tsc aperfmperf pni pclmulqdq dtes64 monitor ds_cpl vmx smx est tm2 ssse3 cx16 xtpr pdcm pcid sse4_1 sse4_2 x2apic popcnt tsc_deadline_timer aes xsave avx f16c rdrand lahf_lm ida arat epb xsaveopt pln pts dts tpr_shadow vnmi flexpriority ept vpid fsgsbase smep erms
    bogomips        : 6800.13
    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           : 58
    model name      : Intel(R) Xeon(R) CPU E3-1245 V2 @ 3.40GHz
    stepping        : 9
    cpu MHz         : 3400.068
    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     : 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 rdtscp lm constant_tsc arch_perfmon pebs bts rep_good xtopology nonstop_tsc aperfmperf pni pclmulqdq dtes64 monitor ds_cpl vmx smx est tm2 ssse3 cx16 xtpr pdcm pcid sse4_1 sse4_2 x2apic popcnt tsc_deadline_timer aes xsave avx f16c rdrand lahf_lm ida arat epb xsaveopt pln pts dts tpr_shadow vnmi flexpriority ept vpid fsgsbase smep erms
    bogomips        : 6800.13
    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           : 58
    model name      : Intel(R) Xeon(R) CPU E3-1245 V2 @ 3.40GHz
    stepping        : 9
    cpu MHz         : 3400.068
    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     : 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 rdtscp lm constant_tsc arch_perfmon pebs bts rep_good xtopology nonstop_tsc aperfmperf pni pclmulqdq dtes64 monitor ds_cpl vmx smx est tm2 ssse3 cx16 xtpr pdcm pcid sse4_1 sse4_2 x2apic popcnt tsc_deadline_timer aes xsave avx f16c rdrand lahf_lm ida arat epb xsaveopt pln pts dts tpr_shadow vnmi flexpriority ept vpid fsgsbase smep erms
    bogomips        : 6800.13
    clflush size    : 64
    cache_alignment : 64
    address sizes   : 36 bits physical, 48 bits virtual
    power management:
    
    Memory
    Code:
    MemTotal:       32722692 kB
    MemFree:          564204 kB
    Buffers:         2755888 kB
    Cached:         17155512 kB
    SwapCached:        92500 kB
    MemCommitted:   196075520 kB
    VirtualSwap:           0 kB
    Active:         15915056 kB
    Inactive:       10825628 kB
    Active(anon):    5431520 kB
    Inactive(anon):  1509092 kB
    Active(file):   10483536 kB
    Inactive(file):  9316536 kB
    Unevictable:           0 kB
    Mlocked:               0 kB
    SwapTotal:       5118968 kB
    SwapFree:        4829916 kB
    Dirty:              7868 kB
    Writeback:             0 kB
    AnonPages:       6781480 kB
    Mapped:           210316 kB
    Shmem:            115104 kB
    Slab:            5198140 kB
    SReclaimable:    4972980 kB
    SUnreclaim:       225160 kB
    KernelStack:        4728 kB
    PageTables:        87608 kB
    NFS_Unstable:          0 kB
    Bounce:                0 kB
    WritebackTmp:          0 kB
    CommitLimit:    21480312 kB
    Committed_AS:   15594164 kB
    VmallocTotal:   34359738367 kB
    VmallocUsed:      437408 kB
    VmallocChunk:   34359164108 kB
    HardwareCorrupted:     0 kB
    AnonHugePages:         0 kB
    HugePages_Total:       0
    HugePages_Free:        0
    HugePages_Rsvd:        0
    HugePages_Surp:        0
    Hugepagesize:       2048 kB
    DirectMap4k:        8192 kB
    DirectMap2M:    33478656 kB
    
    mysqltuner
    Code:
     >>  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.32-cll-lve
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
    [--] Data in MyISAM tables: 11G (Tables: 17011)
    [--] Data in InnoDB tables: 754M (Tables: 9103)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 124K (Tables: 173)
    [!!] Total fragmented tables: 443
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 3h 27m 26s (726K q [58.380 qps], 48K conn, TX: 3B, RX: 104M)
    [--] Reads / Writes: 57% / 43%
    [--] Total buffers: 6.2G global + 10.6M per thread (1024 max threads)
    [OK] Maximum possible memory usage: 16.8G (53% of installed RAM)
    [OK] Slow queries: 0% (782/726K)
    [OK] Highest usage of available connections: 4% (43/1024)
    [OK] Key buffer size / total MyISAM indexes: 4.0G/1.3G
    [!!] Key buffer hit rate: 86.5% (5M cached / 675K reads)
    [OK] Query cache efficiency: 73.0% (347K cached / 475K selects)
    [!!] Query cache prunes per day: 71051
    [OK] Sorts requiring temporary tables: 0% (6 temp sorts / 16K sorts)
    [!!] Joins performed without indexes: 1384
    [!!] Temporary tables created on disk: 30% (6K on disk / 19K total)
    [OK] Thread cache hit rate: 99% (49 created / 48K connections)
    [!!] Table cache hit rate: 1% (2K open / 155K opened)
    [OK] Open file limit used: 3% (1K/50K)
    [OK] Table locks acquired immediately: 99% (228K immediate / 228K locks)
    [OK] InnoDB buffer pool / data size: 2.0G/754.1M
    [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
        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: http://bit.ly/1mi7c4C
    Variables to adjust:
        query_cache_size (> 32M)
        join_buffer_size (> 8.0M, or always use indexes with joins)
        tmp_table_size (> 128M)
        max_heap_table_size (> 128M)
        table_open_cache (> 2000)
    my.cnf
    Code:
    [mysqld]
    innodb_file_per_table=1
    open_files_limit=50000
    max_allowed_packet=268435456
    max_connections = 1024
    local-infile=0
    
    large-pages
    innodb_use_sys_malloc = 0
    innodb_buffer_pool_size=2G
    large_pages = true
    
    query_cache_size=32M
    join_buffer_size=8M
    tmp_table_size=96M
    max_heap_table_size=96M
    key_buffer=64M
    thread_cache_size=32
    table_cache=2000
    table_definition_cache = 1024
    
    key_buffer_size=8G
    log-slow-queries = /var/log/slowqueries
    long_query_time = 3
    log-slow-queries = /var/log/slowqueries
    long_query_time = 3
    
    Thank you
     
  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
    Hello :)

    Are you experiencing any particular issues with slowness or are you simply seeking to optimize your MySQL configuration as much as possible?

    Thank you.
     
  3. zontrakulla

    zontrakulla Active Member

    Joined:
    Jan 16, 2008
    Messages:
    39
    Likes Received:
    0
    Trophy Points:
    6

    Hello Michael,
    I don't experience problems. However i haven't touched these settings more than a year. Just trying to find the best my.cnf setting to avoid any future problems as much as i can.

    Thank you

    Edit: Only error i get is:

    On-disk temporary table rate value 57.8947368421 % should be less than 20.0


    I think i may need help about this issue.
    thanks
     
    #3 zontrakulla, Jan 20, 2015
    Last edited: Jan 20, 2015
  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
    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