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.

Need help optimize mysql setting

Discussion in 'Workarounds and Optimization' started by webhostnix, Jul 8, 2012.

  1. webhostnix

    webhostnix Registered

    Joined:
    Jul 8, 2012
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hi,

    I am new member in this forum. I hope I can learn a lot from this forum.

    I have server with RAM 64 GB with MySQL Version 5.1.63-cll x86_64. I need help how to optimize my.cnf

    Here is my.cnf
    Here is output mysql tuner :
    Here is output from tuning primer :
    Thanks
     
  2. srpurdy

    srpurdy Well-Known Member

    Joined:
    Jun 1, 2011
    Messages:
    101
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Basically Primer tells you whats wrong.
    These two.
    Code:
    QUERY CACHE
    Query cache is enabled
    Current query_cache_size = 32 K
    Current query_cache_used = 32 K
    Current query_cache_limit = 128 M
    Current Query cache Memory fill ratio = 100.00 %
    Current query_cache_min_res_unit = 4 K
    MySQL won't cache query results that are larger than query_cache_limit in size
    
    JOINS
    Current join_buffer_size = 128.00 M
    You have had 205 queries where a join could not use an index properly
    join_buffer_size >= 4 M
    This is not advised
    You should enable "log-queries-not-using-indexes"
    Then look for non indexed joins in the slow query log.
    
    query_cache_size 32K is really small and basically all your data is bigger than 32K so you'll never use the query cache. I would make it 4M to start.

    join_buffer_size of 128M is really crazy high. 256K is what you should set it to, and fix all your tables that don't have indexes. Having a higher join_buffer_size will actually be slower than fixing the tables. I understand it may be some work to fix all the tables, but it's the best option. I think some have also proven in very large sizes that's actually slower to have it higher than to force table scans.
     
  3. webhostnix

    webhostnix Registered

    Joined:
    Jul 8, 2012
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    I have adjust query_cache_size 4M and join_buffer_size 256K

    Btw, mean of fix all tables that don't have indexes are repair and optimize all tables server wide ?

    Thanks for advise
     
  4. srpurdy

    srpurdy Well-Known Member

    Joined:
    Jun 1, 2011
    Messages:
    101
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Sorry I made an error.

    what confused me is query_cache_limit = 128M which is also a strange value.

    Change these
    Code:
    Current query_cache_size = 32 K
    Current query_cache_limit = 128 M
    

    to these
    Code:
    query_cache_size = 128M
    query_cache_limit = 8M
    
    Those are more like it. :)

    And optimize is not what I mean.

    Tables that link together have indexes so mysql knows how to find things when you run queries.

    So if tables only have primary keys and no indexes joins without indexes will get created.

    best simple example is a simple category + entries.
    -> category_table
    cat_id
    category_name

    -> entry_table
    entry_id
    cat_id
    entry_name

    in this case cat_id needs an index. on both tables, since the category_table will likely have a primary key for cat_id it's not a problem there, but on entry_table cat_id needs an index. This also goes for any queries that are using order_by, group_by and where clauses. Basically this comes down to proper database design. If you know what queries are being performed you can fix this.
     
    #4 srpurdy, Jul 9, 2012
    Last edited: Jul 9, 2012
  5. webhostnix

    webhostnix Registered

    Joined:
    Jul 8, 2012
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    I have adjusted

    query_cache_size = 128M
    query_cache_limit = 8M

    and thanks for explanation about index join

     
  6. lbeachmike

    lbeachmike Well-Known Member

    Joined:
    Dec 27, 2001
    Messages:
    313
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Long Beach, NY
    cPanel Access Level:
    Root Administrator
    One thing that's important to note here is that you ran tuning-primer after only 35 minutes of uptime and just a handful of queries, so your data is not going to be very useful for optimizing unless you run it for a bit longer. They recommend 48 hours as in the output your posted, though 24 hours usually provides plenty of data for this purpose -

    That being said, data from a brief run can at least help get some of the initial bugs dealt with - you can then run for longer periods to fine-tune.
     
  7. webhostnix

    webhostnix Registered

    Joined:
    Jul 8, 2012
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Need new recommendation based on tuning-primer. Server has been running for over 48hrs.

    Thanks
     
Loading...

Share This Page