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 help required

Discussion in 'Workarounds and Optimization' started by webhosting-k999, Nov 10, 2014.

  1. webhosting-k999

    Joined:
    Mar 29, 2014
    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hi,
    I have suffering a slow speed on my server running mostly wordpress and apps. Server is Xeon 8 processors 16 GB RAM.
    My.cnf file details----
    Code:
    [mysqld]
        local-infile=0
    
        connect_timeout=360
        wait_timeout=2400
        interactive_timeout=2400
        default-storage-engine=InnoDB
    
        max_connections = 400
        max_user_connections = 100
    
        key_buffer_size=1400M
        join_buffer_size=10M
        sort_buffer_size=256K
        read_buffer_size = 256K
        read_rnd_buffer_size = 256K
    
        slow_query_log=1
        slow_query_log_file=mysql-slow.log
        long_query_time=0.1
        log-queries-not-using-index = 1
        low_priority_updates=1
        concurrent_insert=ALWAYS
    
        query_cache_type = 1
        query_cache_size =128M
        query_cache_limit = 4M
    
        max_allowed_packet=268435456
        tmp_table_size=64M
        max_heap_table_size=64M
    
    
        table_definition_cache=38000
        table_open_cache=40000
    
        thread_cache_size=64
    
        innodb_buffer_pool_size=1624M
        innodb_file_per_table=1
    
    open_files_limit=655690
    
    
    Mysqltuner result output as follows-

    Code:
    >>  MySQLTuner 1.3.0 - Major Hayden 
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  Run with '--help' for additional options and output filtering
    [OK] Logged in using credentials passed on the command line
    [OK] Currently running supported MySQL version 5.6.17-log
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM 
    [--] Data in MyISAM tables: 11G (Tables: 8537)
    [--] Data in InnoDB tables: 1G (Tables: 7114)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
    [--] Data in MEMORY tables: 0B (Tables: 73)
    [!!] Total fragmented tables: 3525
    
    -------- Security Recommendations  -------------------------------------------
    [!!] User 'mmbagsin_wrdp1@204.45.126.18' has no password set.
    [!!] User 'mmbagsin_wrdp1@76.73.118.178' has no password set.
    [!!] User 'mmbagsin_wrdp1@localhost' has no password set.
    [!!] User 'mmbagsin_wrdp1@server1.kakinfotech.com' has no password set.
    [!!] User 'mmbagsin_wrdp1@server1.watchmenindia.com' has no password set.
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 3d 23h 51m 28s (21M q [61.043 qps], 297K conn, TX: 106B, RX: 5B)
    [--] Reads / Writes: 76% / 24%
    [--] Total buffers: 3.2G global + 11.0M per thread (400 max threads)
    [OK] Maximum possible memory usage: 7.5G (47% of installed RAM)
    [OK] Slow queries: 3% (633K/21M)
    [OK] Highest usage of available connections: 14% (58/400)
    [OK] Key buffer size / total MyISAM indexes: 1.4G/4.4G
    [OK] Key buffer hit rate: 100.0% (578M cached / 226K reads)
    [OK] Query cache efficiency: 61.4% (10M cached / 17M selects)
    [!!] Query cache prunes per day: 75532
    [OK] Sorts requiring temporary tables: 0% (148 temp sorts / 607K sorts)
    [!!] Joins performed without indexes: 5563
    [!!] Temporary tables created on disk: 32% (269K on disk / 815K total)
    [OK] Thread cache hit rate: 99% (58 created / 297K connections)
    [OK] Table cache hit rate: 46% (32K open / 69K opened)
    [OK] Open file limit used: 3% (25K/655K)
    [OK] Table locks acquired immediately: 99% (9M immediate / 9M locks)
    [OK] InnoDB buffer pool / data size: 1.6G/1.5G
    [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
    Variables to adjust:
        query_cache_size (> 128M)
        join_buffer_size (> 10.0M, or always use indexes with joins)
        tmp_table_size (> 64M)
        max_heap_table_size (> 64M)
    
    Waiting for an update on this thread.
    Thank you,
     
  2. Infopro

    Infopro cPanel Sr. Product Evangelist
    Staff Member

    Joined:
    May 20, 2003
    Messages:
    14,478
    Likes Received:
    203
    Trophy Points:
    63
    Location:
    Pennsylvania
    cPanel Access Level:
    Root Administrator
    Twitter:
  3. webhosting-k999

    Joined:
    Mar 29, 2014
    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hi,
    After a long while, we have added many sites. System takes too much time to load a website...
    Can you suggest some tweaks in the variables...???
     
  4. Infopro

    Infopro cPanel Sr. Product Evangelist
    Staff Member

    Joined:
    May 20, 2003
    Messages:
    14,478
    Likes Received:
    203
    Trophy Points:
    63
    Location:
    Pennsylvania
    cPanel Access Level:
    Root Administrator
    Twitter:
    I can't. Well I could, but not the tweaks you're hoping for, the same tweaks many come by these forums and others, hoping for. That magic configuration that solves everything. There isn't one.

    I will say this, no matter how well tweaked your system is, how perfect your configurations are, one poorly maintained website can cause you grief. Add "many sites" similar to that first problematic site, and you can tweak till the cows come home, it won't help. Starting multiple threads on the same topic, won't help either.

    Managing the sites on the server better, properly, is the best advice I could give you. Details on how to do that, you should already know. Only you know what those "many sites" are doing, what they have installed as far as addons and customizations that can crush a server in minutes if done incorrectly, how much actual traffic they get and so on. If you don't, start there.

    As for me, if a website starts pounding one of my servers resources, I don't go look for ways to meet the demand, I figure out if the demand is real or some poorly coded chat room script for wordpress, for example.

    Please don't start additional threads on the same topic.
     
Loading...

Share This Page