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.

Help optimizing mysql, please.

Discussion in 'Workarounds and Optimization' started by Secmas, Mar 12, 2012.

  1. Secmas

    Secmas Well-Known Member

    Joined:
    Feb 18, 2005
    Messages:
    321
    Likes Received:
    0
    Trophy Points:
    16
    Hi all,
    I am having a very high load in my server, I have manage to tweak mysql configuration but want to know if there is something else that I should improve and hope you can help me.

    My server has REHL 6 / 64 bits with 12GB RAM and WHM says it has 16 processors (physically two Intel(R) Xeon(R) CPU E5520 @ 2.27GHz), what it is weird is that WHM shows the legend "Total processors: 10" but it shows 16, so, go figure.

    Any way, here is what I have in my.cnf:
    I have run mysqltuner.pl and this is what is showing for more than 48 hours:
    Thanks in advance.

    Sergio
     
  2. Secmas

    Secmas Well-Known Member

    Joined:
    Feb 18, 2005
    Messages:
    321
    Likes Received:
    0
    Trophy Points:
    16
    BUMP :D
    no help at all?
     
  3. storminternet

    storminternet Well-Known Member

    Joined:
    Nov 2, 2011
    Messages:
    462
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Have you adjusted the variables as suggested in output

     
  4. Eric

    Eric Administrator
    Staff Member

    Joined:
    Nov 25, 2007
    Messages:
    746
    Likes Received:
    11
    Trophy Points:
    18
    Location:
    Texas
    cPanel Access Level:
    Root Administrator
    Howdy,

    This might be an issue you should look into too.

    Code:
    [!!] Total fragmented tables: 2879
    Thanks!
     
  5. Secmas

    Secmas Well-Known Member

    Joined:
    Feb 18, 2005
    Messages:
    321
    Likes Received:
    0
    Trophy Points:
    16
    Sorry for the delay in answering back, but as you know we have to wait 24 hours for any changes to see how it works.

    Ok, here is the last my.cfn configuration that I did, using all your inputs, thank you for that:

    I used to have a maximum 400 connections but after reviewing all the tests that I did and that no more of the 10% (40 connections) were made, I drop the connections to 250, and that helped me a lot, the load in my server has dropped considerably.

    This is the last info that mysqltuner shows now:

    For the Total fragmented tables: 2887, I have ran so many times the optimaztion and repair command and everything shows OK, only some tables that can't be optimized or repaired because are olders. So, what I have to do there?

    What do you do with the query_cache_size? it says it needs more than 150MB but at the same time is says that the size over 128M may reduce performance.

    Why tmp_table_size and max_heap_table_size always requires more? is there a size that I could set in order for this not to be adjusted every 24 hours?

    Once again, thanks.


    Sergio
     
  6. Secmas

    Secmas Well-Known Member

    Joined:
    Feb 18, 2005
    Messages:
    321
    Likes Received:
    0
    Trophy Points:
    16
    Well, finally I manage to set my.cnf at a really nice configuration, here is my last mysqltuner:

    It seems that the "join_buffer_size" will never be fixed; so, I will left it as it is now, "tmp_table_size and max_head_table_size" will be gradually adjust as needed.

    Sergio
     
  7. tank

    tank Well-Known Member

    Joined:
    Apr 12, 2011
    Messages:
    236
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Chicago, IL
    cPanel Access Level:
    Root Administrator
  8. Secmas

    Secmas Well-Known Member

    Joined:
    Feb 18, 2005
    Messages:
    321
    Likes Received:
    0
    Trophy Points:
    16
    Thank you, Tank.

    I will watch the video.

    Sergio
     
  9. tank

    tank Well-Known Member

    Joined:
    Apr 12, 2011
    Messages:
    236
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Chicago, IL
    cPanel Access Level:
    Root Administrator
    #9 tank, Mar 18, 2012
    Last edited: Mar 18, 2012
  10. Secmas

    Secmas Well-Known Member

    Joined:
    Feb 18, 2005
    Messages:
    321
    Likes Received:
    0
    Trophy Points:
    16
    I have seen the tutorial but it is kind of old, it is from 2010, I assume a lot of thing has changed since then. The only thing that I have take from that video is the variable "thread_concurrency" that was set in my.cnf, so i deleted it and I will be checking until tomorrow how it goes.

    About what storminternet said, yes, I did that. But, since the first post that I wrote to this day, I have learned a few things and made a lot of changes in my.cnf and I really liked the performance that my server has now. I have been using another tool to check my.cnf and used a mysql memory calculator to see if the variables and sizes were right.
     
  11. tank

    tank Well-Known Member

    Joined:
    Apr 12, 2011
    Messages:
    236
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Chicago, IL
    cPanel Access Level:
    Root Administrator
    That video is only a year and half old not too bad. Do you mind sharing your changes so that all of us may learn what worked in your instance? What kind of calculator are you referring too?
     
  12. Secmas

    Secmas Well-Known Member

    Joined:
    Feb 18, 2005
    Messages:
    321
    Likes Received:
    0
    Trophy Points:
    16
    Well, I have learned that there are some variables that could take all your memory if you don't tweak it right. All of this variables will be multiplied for each connection made to your MySQL, so, be careful on how you set them:

    max_allowed_packet
    sort_buffer_size
    read_rnd_buffer_size
    join_buffer_size <<<<<<== be carefull with this variable, just use as it says 128KB if you set this to high you can get rid of all your memory.

    The following variables can bet set accordingly to your MySQL usage and are not multiplied by all the accounts, they are global variables:

    query_cache_size
    innodb_buffer_pool_size
    key_buffer_size

    tmp_table_size
    max_heap_table_size
    table_open_cache
    open_files_limit

    read_buffer_size
    myisam_sort_buffer_size
    thread_cache_size

    To check how mucho memory have you set to your mysql, use the following online calculator MySQL &mdash; How much memory do I need?

    Sergio
     
  13. tank

    tank Well-Known Member

    Joined:
    Apr 12, 2011
    Messages:
    236
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Chicago, IL
    cPanel Access Level:
    Root Administrator
    Thank you very much on this. I will have to look into some of those variables myself.
     
Loading...

Share This Page