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.

optimizing mysql

Discussion in 'Workarounds and Optimization' started by bhanuprasad1981, Oct 12, 2010.

  1. bhanuprasad1981

    bhanuprasad1981 Well-Known Member

    Joined:
    Aug 5, 2008
    Messages:
    222
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    India
    i ran mysql tuner and got this result :-

    Code:
     >>  MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  Run with '--help' for additional options and output filtering
    
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.0.91-community
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 9G (Tables: 2965)
    [--] Data in MEMORY tables: 14M (Tables: 72)
    [!!] Total fragmented tables: 283
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 17h 34m 44s (33M q [220.842 qps], 2M conn, TX: 331B, RX: 9B)
    [--] Reads / Writes: 64% / 36%
    [--] Total buffers: 906.0M global + 6.2M per thread (4000 max threads)
    [!!] Maximum possible memory usage: 25.3G (260% of installed RAM)
    [OK] Slow queries: 0% (25K/33M)
    [OK] Highest usage of available connections: 13% (543/4000)
    [OK] Key buffer size / total MyISAM indexes: 512.0M/6.1G
    [OK] Key buffer hit rate: 99.9% (2B cached / 3M reads)
    [OK] Query cache efficiency: 63.4% (14M cached / 22M selects)
    [!!] Query cache prunes per day: 275647
    [!!] Sorts requiring temporary tables: 24% (314K temp sorts / 1M sorts)
    [!!] Joins performed without indexes: 2676
    [OK] Temporary tables created on disk: 21% (82K on disk / 381K total)
    [OK] Thread cache hit rate: 99% (543 created / 2M connections)
    [OK] Table cache hit rate: 77% (11K open / 14K opened)
    [OK] Open file limit used: 48% (13K/28K)
    [OK] Table locks acquired immediately: 97% (19M immediate / 19M locks)
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Reduce your overall MySQL memory footprint for system stability
        Enable the slow query log to troubleshoot bad queries
        Adjust your join queries to always utilize indexes
    Variables to adjust:
      *** MySQL's maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
        query_cache_size (> 128M)
        sort_buffer_size (> 2M)
        read_rnd_buffer_size (> 1M)
        join_buffer_size (> 2.0M, or always use indexes with joins)
    
    
    

    this is my.cnf:-

    Code:
    [mysqld]
    #log-slow-queries = /var/log/mysql-slow.log
    long_query_time = 3
    #skip-name-resolve
    safe-show-database
    #old_passwords
    back_log = 50
    skip-innodb
    max_connections = 4000
    max_user_connections= 1000
    key_buffer_size = 512M
    myisam_sort_buffer_size = 64M
    myisam_max_sort_file_size = 2048M
    join_buffer_size = 2M
    read_buffer_size = 1M
    sort_buffer_size = 2M
    table_cache = 12000
    thread_cache_size = 1024
    wait_timeout = 1000
    interactive_timeout = 500
    connect_timeout = 600
    tmp_table_size = 256M
    max_heap_table_size = 256M
    max_allowed_packet = 128M
    net_buffer_length = 16384
    max_connect_errors = 100000
    thread_concurrency = 16
    concurrent_insert = 2
    table_lock_wait_timeout = 30
    read_rnd_buffer_size = 1M
    bulk_insert_buffer_size = 8M
    query_cache_limit = 4M
    query_cache_size = 128M
    query_cache_type = 1
    query_prealloc_size = 262144
    query_alloc_block_size = 65536
    range_alloc_block_size = 4096
    transaction_alloc_block_size = 8192
    transaction_prealloc_size = 4096
    default-storage-engine = MyISAM
    max_write_lock_count = 8
    local-infile=0
    
    [mysqld_safe]
    nice = -10
    open_files_limit = 8192
    
    [mysqldump]
    quick
    max_allowed_packet = 128M
    
    [myisamchk]
    key_buffer_size = 512M
    sort_buffer_size = 16M
    read_buffer_size = 16M
    write_buffer_size = 16M
    
    [mysqlhotcopy]
    interactive-timeout
    
    
    
    
    what is worrying me is these words :-
    Variables to adjust:
    *** MySQL's maximum memory usage is dangerously high ***
    *** Add RAM before increasing MySQL buffer variables ***
     
  2. GaryT

    GaryT Well-Known Member

    Joined:
    May 19, 2010
    Messages:
    321
    Likes Received:
    3
    Trophy Points:
    16
    First of you will not get many replies as you have not stated what memory, what cores you got and such for your machine so nobody can give you help properly if they have no values to go from ?

    This is cause by:

    4000 ? Are you runnng ebay/Facebook or something :eek:

    even 500 is still high for mysql connections ! This is not the actual connections the server can handle, You need to read up more...

    Reduce those.

    Going of your my.cnf I can only assume your running something as big as 2 x Xeon Quad core machine with around 16GB of memory ?
     
    #2 GaryT, Oct 12, 2010
    Last edited: Oct 12, 2010
  3. johnburk

    johnburk Well-Known Member

    Joined:
    Jun 23, 2006
    Messages:
    241
    Likes Received:
    0
    Trophy Points:
    16
    My first impression is that you my.cnf is unbalanced. Who set it this way?


    If all connections are made, you will be using 260% of installed ram! That is going to cause huge problems. Try to stay <80% with a minimum of 256MB.

    To optimize you tables run a daily cron of
    mysqlcheck -o -A

    I day and 17 hours it too short for a accurate read, but you conecctions seem to be too high! Set it to 1200 and this will improve your max memory usage.

    You have 25k slow querys, you really need to look into that. View the log to see what they are

    Increase your query_cache_size to 256M and run some test to see it this does improve. In some cases more cache can slow down your system.

    Also run tuning-primer.sh before the above adjustment. This will give you more optimization data.
     
  4. GaryT

    GaryT Well-Known Member

    Joined:
    May 19, 2010
    Messages:
    321
    Likes Received:
    3
    Trophy Points:
    16
    Even 1200 is extremely high !

    Your talking about mysql not apache connections.
     
  5. bhanuprasad1981

    bhanuprasad1981 Well-Known Member

    Joined:
    Aug 5, 2008
    Messages:
    222
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    India
    thanks for replies, i am sorry for incomplete details :(,here is server spec:-

    dual nehlam 5520
    8 physical + 8 virtual cores
    12gb ddr3
    2*1 tb hdd
    No raid
    capnel / whm
    xcache installed
    nginx as proxy


    so some suggestion ?
     
    #5 bhanuprasad1981, Oct 12, 2010
    Last edited: Oct 12, 2010
  6. GaryT

    GaryT Well-Known Member

    Joined:
    May 19, 2010
    Messages:
    321
    Likes Received:
    3
    Trophy Points:
    16
    Ok thats a start !

    Right... First off this xCache module and nGinx will increase your server I/O temporarily creating cache files and changing it. I suggest using memcache server to increase the caching.

    Secondly, Try my suggested my.cnf ( Others may say different )

    Like I said my suggested configuration, Backup first, And always listen to others advice not just mine.
     
  7. bhanuprasad1981

    bhanuprasad1981 Well-Known Member

    Joined:
    Aug 5, 2008
    Messages:
    222
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    India
    thanks for suggestion, i have implemented your suggested settings, hope everything goes fine :)
     
  8. GaryT

    GaryT Well-Known Member

    Joined:
    May 19, 2010
    Messages:
    321
    Likes Received:
    3
    Trophy Points:
    16
    You probably know this but restart your mysql for changes to take affect :rolleyes:
     
  9. bhanuprasad1981

    bhanuprasad1981 Well-Known Member

    Joined:
    Aug 5, 2008
    Messages:
    222
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    India
    as mentioned in replies to use memcache instead of xcache, i tried installing it but failed to get it working, its not showing up in php.ini :(, also in past 12 hours /tmp (2GB) filled up twice which never happened before :(
     
  10. GaryT

    GaryT Well-Known Member

    Joined:
    May 19, 2010
    Messages:
    321
    Likes Received:
    3
    Trophy Points:
    16
    This is what xCache does - It takes all the resources from the CPU and uses it on the memory, Now alot of connections obviously would mean alot of ram usuage.

    You could try use your other partition to cache the files if you have more space, Just edit the xcache config and change:

    to

    you must make those folders though !
    Memcache can be installed via WHM > EasyApache, Now if you installed test this via:

    the www will tell it to run as the same server as your webroot runs, Also you could create a PHP file and open it within your website, Just create a php file then add the following:

    Once done upload to your website, And open the file:

    You should then see [MemCache Support] block :)
     
    #10 GaryT, Oct 13, 2010
    Last edited: Oct 13, 2010
  11. bhanuprasad1981

    bhanuprasad1981 Well-Known Member

    Joined:
    Aug 5, 2008
    Messages:
    222
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    India
    yes my dc said it maybe filling because both cache were installed and they removed memcache, ant how can some one guide me how to increase /tmp size to atleast 5 gb from present size of 2gb ?
     
  12. nightaddix

    nightaddix Active Member
    PartnerNOC

    Joined:
    Jul 7, 2008
    Messages:
    30
    Likes Received:
    0
    Trophy Points:
    6
  13. johnburk

    johnburk Well-Known Member

    Joined:
    Jun 23, 2006
    Messages:
    241
    Likes Received:
    0
    Trophy Points:
    16
    Could you please rerun mysqltuner and post the results? Is there any improvement?
     
  14. Deadly Silent

    Deadly Silent Registered

    Joined:
    Oct 4, 2011
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
Loading...

Share This Page