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.

Best optimization for my.cnf

Discussion in 'Workarounds and Optimization' started by mustafamsy, Apr 12, 2012.

  1. mustafamsy

    mustafamsy Registered

    Joined:
    Apr 11, 2012
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hi there

    I really need the best optimization/configuration for "my.cnf" - MySQL 5.5 - CentOS 5.8

    Currently for my.cnf I have the following:
    Code:
    [mysqld]
    max_connections=500
    join_buffer_size = 2M
    tmp_table_size = 24M
    max_heap_table_size = 24M
    query_cache_size = 256M
    key_buffer=256M
    key_buffer_size = 1332M
    thread_cache_size = 4
    table_cache = 500
    table_open_cache = 96
    innodb_buffer_pool_size = 27M
    slow_query_log
    local-infile=0
    Processor Information
    Code:
    Total processors: 4
    Processor #1
    Vendor
    GenuineIntel
    Name
    Intel(R) Core(TM)2 Quad CPU Q8300 @ 2.50GHz
    Speed
    1998.000 MHz
    Cache
    2048 KB
    Processor #2
    Vendor
    GenuineIntel
    Name
    Intel(R) Core(TM)2 Quad CPU Q8300 @ 2.50GHz
    Speed
    1998.000 MHz
    Cache
    2048 KB
    Processor #3
    Vendor
    GenuineIntel
    Name
    Intel(R) Core(TM)2 Quad CPU Q8300 @ 2.50GHz
    Speed
    1998.000 MHz
    Cache
    2048 KB
    Processor #4
    Vendor
    GenuineIntel
    Name
    Intel(R) Core(TM)2 Quad CPU Q8300 @ 2.50GHz
    Speed
    1998.000 MHz
    Cache
    2048 KB
    Memory Information
    Code:
    Memory for crash kernel (0x0 to 0x0) notwithin permissible range
    Memory: 8165712k/9175040k available (2574k kernel code, 212064k reserved, 1304k data, 212k init)

    MySQLTuner
    Code:
    root@marr [~]# /usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl
    
     >>  MySQLTuner 1.1.2 - 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.5.21-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 3G (Tables: 7422)
    [--] Data in InnoDB tables: 1M (Tables: 21)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 994K (Tables: 59)
    [!!] Total fragmented tables: 482
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 2h 30m 14s (1M q [127.577 qps], 18K conn, TX: 30B, RX: 131M)
    [--] Reads / Writes: 75% / 25%
    [--] Total buffers: 1.6G global + 4.6M per thread (500 max threads)
    [OK] Maximum possible memory usage: 3.9G (49% of installed RAM)
    [OK] Slow queries: 0% (9/1M)
    [OK] Highest usage of available connections: 5% (29/500)
    [OK] Key buffer size / total MyISAM indexes: 1.3G/1.3G
    [OK] Key buffer hit rate: 96.5% (7M cached / 280K reads)
    [OK] Query cache efficiency: 80.9% (782K cached / 967K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (27 temp sorts / 49K sorts)
    [!!] Joins performed without indexes: 2248
    [!!] Temporary tables created on disk: 26% (17K on disk / 65K total)
    [OK] Thread cache hit rate: 96% (570 created / 18K connections)
    [!!] Table cache hit rate: 0% (96 open / 101K opened)
    [OK] Open file limit used: 4% (181/4K)
    [OK] Table locks acquired immediately: 99% (329K immediate / 330K locks)
    [OK] InnoDB data size / buffer pool: 1.3M/27.0M
    
    -------- 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_cache gradually to avoid file descriptor limits
    Variables to adjust:
        join_buffer_size (> 2.0M, or always use indexes with joins)
        tmp_table_size (> 24M)
        max_heap_table_size (> 24M)
        table_cache (> 96)
    Thank you for your help!!

    ~Mustafa
     
  2. mustafamsy

    mustafamsy Registered

    Joined:
    Apr 11, 2012
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Code:
    root@alhussain [~]# /usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl
    
     >>  MySQLTuner 1.1.2 - 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.5.21-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 3G (Tables: 7425)
    [--] Data in InnoDB tables: 34M (Tables: 247)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 2M (Tables: 61)
    [!!] Total fragmented tables: 732
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 8d 19h 20m 20s (217M q [285.805 qps], 2M conn, TX: 3499B, RX: 19B)
    [--] Reads / Writes: 44% / 56%
    [--] Total buffers: 1.6G global + 4.6M per thread (500 max threads)
    [OK] Maximum possible memory usage: 3.9G (49% of installed RAM)
    [OK] Slow queries: 0% (274/217M)
    [OK] Highest usage of available connections: 51% (255/500)
    [OK] Key buffer size / total MyISAM indexes: 1.3G/1.3G
    [OK] Key buffer hit rate: 95.3% (1B cached / 71M reads)
    [OK] Query cache efficiency: 70.5% (107M cached / 152M selects)
    [!!] Query cache prunes per day: 210621
    [OK] Sorts requiring temporary tables: 0% (2K temp sorts / 5M sorts)
    [!!] Joins performed without indexes: 247029
    [OK] Temporary tables created on disk: 20% (895K on disk / 4M total)
    [OK] Thread cache hit rate: 94% (130K created / 2M connections)
    [!!] Table cache hit rate: 0% (96 open / 11M opened)
    [OK] Open file limit used: 7% (181/2K)
    [!!] Table locks acquired immediately: 77%
    [!!] InnoDB data size / buffer pool: 34.9M/27.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
        Increase table_cache gradually to avoid file descriptor limits
        Optimize queries and/or use InnoDB to reduce lock wait
    Variables to adjust:
        query_cache_size (> 256M) [see warning above]
        join_buffer_size (> 2.0M, or always use indexes with joins)
        table_cache (> 96)
        innodb_buffer_pool_size (>= 34M)
    

    Up
     
  3. NetMantis

    NetMantis BANNED

    Joined:
    Apr 22, 2012
    Messages:
    117
    Likes Received:
    0
    Trophy Points:
    0
    Location:
    Utah
    cPanel Access Level:
    DataCenter Provider
    The correct configuration for my.cnf is going to vary greatly from server machine to server machine.

    The values you want to put in there really depends heavily on both your usage and the resources that are available on your server machine so it would be unwise to use just any generic my.cnf posted somewhere built for some other server.

    Looking at your my.cnf posted in your first post above and knowing you are MySQL 5.5, I do see a few obvious items:

    You put this:
    Code:
    thread_cache_size = 4
    Instead, you should try this (16K is the maximum for this item):
    Code:
    thread_cache_size = 16K
    You are not making any real use of the multi-core processors above so add this:
    Code:
    low_priority_updates=1
    concurrent_insert=ALWAYS
    You have this in your my.cnf configuration which is a bit too low:
    Code:
    tmp_table_size = 24M
    max_heap_table_size = 24M
    
    For a good starting point with your memory, change both of those items above to 64M and then you can adjust those two upward as needed up to around 128M would be good if you have a lot of database activity going on particular the type that deals with creating tmp tables in memory.

    You tried to set your query_cache_size to "256M" but failed to setup query_cache_limit so your actual functional query cache is only 1 MB instead of 256 MB like you had intended plus 256 is usually a bit too large even if you had set it up correctly.

    Replace your query_cache_size line to the following:
    Code:
    query_cache_size = 128M
    query_cache_limit = 148M
    
    The above may even be a bit large or could be small depending on how your server is used and what sort of databases you keep on your server but is a decent starting point for a server with your memory and processor resources. I generally make the query_cache_limit just slightly larger than the base query_cache_size and MySQL scales the cache up to those limits as needed.

    You wrote this in your my.cnf file which is really the same thing for both lines though the first line is a typographical error and would likely keep your MySQL server from properly starting up:
    Code:
    table_cache = 500
    table_open_cache = 96
    
    So you are either trying to set your table_cache to 500 or 96. Both of those are extremely small and would indicate that you do not plan on having very many database tables on your server.

    What you need to do is find out how many tables you have on your server and then set a table_cache number about 20% roughly higher than that number and then table_definition_cache will be 4x that number and your open_files_limit would be 3x the table_cache number.

    For example, lets say you had 862 tables on your server, then you might use a table_cache of 1K (shorthand for 1,024) which is just a bit larger than 862 and then the table_definition cache would then be 4K and the open_files_limit would be 3K.

    Example of the lines needed for your table cache setup:
    Code:
    table_cache = 1K
    table_definition_cache = 4K
    open_files_limit = 3K
    
    In your my.cnf, you wrote essentially the same thing two different ways in another section:
    Code:
    key_buffer=256M
    key_buffer_size = 1332M
    
    Both of these are quite large and could actually slow down your server. We'll ignore the 1332M you setup and go with the 256M. Even at the 256M, it is still quite large unless you have some very large MyISAM based databases; then in that case it might be warranted to go that high with the key_buffer size.

    As a general decent starting point, I would typically start around 64M for the key_buffer and then move up or down as needed depending on the amount of MyISAM indexing you have on your server.

    Should look more like this:
    Code:
    key_buffer = 64M
    I notice you put no limits on query execution and that could actually impact performance a bit.

    You might want to do something like this:
    Code:
    long_query_time = 5
    Setting a 5 second limit which is generally more than sufficient for most queries on most databases

    You also have a join_buffer_size of 2 MB. For your server size, using 3 MB might be better but go no higher than that!

    The MySQL tuner you downloaded is generally good as a performance analyzer for tweaking on fine tuning an existing MySQL configuration at a later date but not really that good at setting up your initial configuration.

    For initial tuning especially with finding the correct table_cache value, I would recommend running Matthew Montgomery's tuning-primer.sh script after you make the initial setup changes that I told you about above and that will help you get more exact values for your specific server.

    The link to where to find the tuning primer script is here --> https://launchpad.net/mysql-tuning-primer

    All of this should make a very noticeable difference in the performance of your MySQL server!
     
  4. mustafamsy

    mustafamsy Registered

    Joined:
    Apr 11, 2012
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    @NetMantis Thank you so much for helping out !!!! :D

    I made the necessary changes and saw that the server load is now low. Thanks again
     
  5. XenomediaBV

    XenomediaBV Well-Known Member

    Joined:
    Sep 3, 2009
    Messages:
    60
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    The Netherlands
    cPanel Access Level:
    Root Administrator
    This is actually not correct:
    Code:
    query_cache_size = 128M
    query_cache_limit = 148M
    MySQL :: MySQL 5.5 Reference Manual :: 5.1.4 Server System Variables
    The query cache is used to store queries with their results that are the same each time they are requested. So the results will always be the same until the table structure or its data changes. Those query/result-sets are candidates for caching.

    This means that with the above settings MySQL will try to push query/result-sets up to 148M into the available query_cache_size. Everything that is above 128M will be dropped directly. Each query/result-set below 128M will be pushed into the cache. So, when you have two query/result-sets of 64M each your cache is stuffed and the "pruning" starts with a third query/result-set that is a cache candidate. Pruning means that the oldest cached query/result-set is purged out of the cache to make space for the newest candidate.

    Something like below is more efficient:
    Code:
    query_cache_size = 128M
    query_cache_limit = 4M
    With a query_cache_size of 128M the cache can hold up to 32 query/result-sets and most likely a lot more because not all query/result-sets require 4M. You can tune the query_cache_limit a little higher if you have a lot of larger query/result-sets, but not too high since the query_cache_size is advised to have a maximum of 128M. Or of course lower since the default is 1M to cache more smaller queries/result-sets.

    http://www.mysqlperformanceblog.com/2006/07/27/mysql-query-cache/
     
    #5 XenomediaBV, Mar 17, 2013
    Last edited: Mar 17, 2013
Loading...

Share This Page