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.

MYSQL optimization

Discussion in 'Database Discussions' started by skysel, Dec 26, 2010.

  1. skysel

    skysel Member

    Joined:
    Nov 30, 2010
    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    1
    I would like some guidlines on further optimizing mysql on my cPanel server. Below are the results from mysqltuner + current configuration. Any good advice or pointers would be greatly appreciated. Please note, that I'm not an experienced mysql administrator, I know basics + some advanced stuff, but do not know how to for example resolve slow queries.

    Server configuration:

    Code:
    Memory: 
    
    MemTotal:     12290784 kB
    MemFree:       2015168 kB
    Buffers:        345652 kB
    Cached:        7575200 kB
    SwapCached:       1364 kB
    Active:        4445528 kB
    Inactive:      5112664 kB
    Code:
    CPU (Quadcore):
    
    processor       : 0
    vendor_id       : GenuineIntel
    cpu family      : 6
    model           : 26
    model name      : Intel(R) Xeon(R) CPU           E5504  @ 2.00GHz
    stepping        : 5
    cpu MHz         : 1600.000
    cache size      : 4096 KB
    physical id     : 1
    siblings        : 4
    core id         : 0
    cpu cores       : 4
    apicid          : 16
    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 sse2 ss ht tm syscall nx rdtscp lm constant_tsc nonstop_tsc pni monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr sse4_1 sse4_2 popcnt lahf_lm
    bogomips        : 4000.23
    clflush size    : 64
    cache_alignment : 64
    address sizes   : 40 bits physical, 48 bits virtual
    power management: [8]
    Code:
    System:
    
    Linux 2.6.18-164.11.1.el5 #1 SMP Wed Jan 20 07:32:21 EST 2010 x86_64 x86_64 x86_64 GNU/Linux
    
    mysqltuner.pl:

    Code:
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.0.91-community-log
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 2G (Tables: 16321)
    [--] Data in InnoDB tables: 17M (Tables: 252)
    [--] Data in MEMORY tables: 0B (Tables: 5)
    [!!] Total fragmented tables: 712
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 2d 2h 29m 18s (101M q [559.499 qps], 1M conn, TX: 116B, RX: 11B)
    [--] Reads / Writes: 83% / 17%
    [--] Total buffers: 412.0M global + 18.6M per thread (300 max threads)
    [OK] Maximum possible memory usage: 5.9G (49% of installed RAM)
    [OK] Slow queries: 2% (2M/101M)
    [OK] Highest usage of available connections: 44% (133/300)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/895.8M
    [OK] Key buffer hit rate: 97.8% (1B cached / 22M reads)
    [OK] Query cache efficiency: 87.0% (78M cached / 89M selects)
    [!!] Query cache prunes per day: 391920
    [OK] Sorts requiring temporary tables: 0% (9K temp sorts / 986K sorts)
    [!!] Joins performed without indexes: 96786
    [!!] Temporary tables created on disk: 28% (241K on disk / 855K total)
    [OK] Thread cache hit rate: 99% (6K created / 1M connections)
    [OK] Table cache hit rate: 63% (32K open / 51K opened)
    [OK] Open file limit used: 73% (48K/65K)
    [OK] Table locks acquired immediately: 99% (16M immediate / 16M locks)
    [OK] InnoDB data size / buffer pool: 17.7M/18.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Increasing the query_cache size over 128M may reduce 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
    Variables to adjust:
        query_cache_size (> 256M) [see warning above]
        join_buffer_size (> 16.0M, or always use indexes with joins)
        tmp_table_size (> 128M)
        max_heap_table_size (> 128M)
    my.cnf

    Code:
    [mysqld]
    set-variable = max_connections=300
    safe-show-database
    query_cache_size=256M
    join_buffer_size=16M
    thread_cache_size=12
    table_cache=170000
    interactive_timeout=14400
    innodb_buffer_pool_size=18M
    max_heap_table_size=128M
    low_priority_updates=1
    concurrent_insert=2
    wait_timeout=5
    connect_timeout=10
    tmp_table_size=128M
    
    long_query_time         = 1
    log-slow-queries
    log-queries-not-using-indexes
    #basedir=/var/lib/mysql
    #datadir=/var/lib/mysql
     
  2. cPanelDavidG

    cPanelDavidG Technical Product Specialist

    Joined:
    Nov 29, 2006
    Messages:
    11,279
    Likes Received:
    8
    Trophy Points:
    38
    Location:
    Houston, TX
    cPanel Access Level:
    Root Administrator
Loading...

Share This Page