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 for mysql optimization...

Discussion in 'Workarounds and Optimization' started by shezi, Feb 2, 2011.

  1. shezi

    shezi Member

    Joined:
    Feb 2, 2011
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    Hello friends,

    I am here to have your great help about mysql optimization. I have VPS with 4 GB RAM. I request you to please let me know that how may i optimize mysql?

    Note: I am not a expert, please try to explain step by step and clearly.

    Regards,
    Shezi
     
  2. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Hello Shezi,

    You could try running the following script to see the recommendations it provides:

    Code:
    /usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl
    Next, I have a presentation that I did on MySQL Optimization at the cPanel conference last year that you may wish to review:

    MySQL Optimization | cPanel Video Site

    Thanks!
     
  3. shezi

    shezi Member

    Joined:
    Feb 2, 2011
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    Is there any other better way to optimize mysql queries?
     
  4. LinuxTechie

    LinuxTechie Well-Known Member

    Joined:
    Jan 22, 2011
    Messages:
    502
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Hello,

    I found the post by Tristan very helpful. May i know the reason why you are en-quiring about another method.
     
  5. shezi

    shezi Member

    Joined:
    Feb 2, 2011
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    Dear i already told you that i am not expert, That's why i am trying to say that please give me full coding for my VPS. I have 4GB of RAM on my VPS.

    Regards,
    Shahzaib
     
  6. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Hello Shahzaib,

    Unfortunately, there is no way to provide full coding for a machine without details being provided for the current MySQL usage. Do you have root SSH access? If so, please run the initial command I provided and provide the output here:

    Code:
    /usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl
    At that point, we would be happy to provide input on what to change in your /etc/my.cnf file. Also, if you could paste the contents of your /etc/my.cnf file:

    Code:
    cat /etc/my.cnf
    Again, we are happy to help, but we cannot provide the details on what is needed without more information.

    Finally, the video tutorial I linked is geared toward beginner level knowledge of MySQL.

    Thanks.
     
  7. shezi

    shezi Member

    Joined:
    Feb 2, 2011
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    Hello,

    Here is the result of running ./tuning-primer.sh

     
  8. shezi

    shezi Member

    Joined:
    Feb 2, 2011
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    /usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl

    cat /etc/my.cnf

     
  9. coderiser

    coderiser Member

    Joined:
    Jan 31, 2011
    Messages:
    18
    Likes Received:
    0
    Trophy Points:
    1
    yes i have used this method as well and found it to be successful
     
  10. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Hello,

    Since your server is running MySQL 5.1, please place the following into your /etc/my.cnf file so it looks like the following (basically adding those variables after the [mysqld] tag):

    Code:
    [mysqld]
    query_cache_size = 8M
    thread_cache_size = 4
    table_open_cache = 80
    innodb_buffer_pool_size = 27M
    slow_query_log
    Next, you can optimize all databases with this command:

    Code:
    mysqlcheck -u root -p --auto-repair --check --optimize --all-databases
    Restart MySQL after you've done the above:

    Code:
    /etc/init.d/mysql restart
    Please wait 24 hours and then re-run mysqltuner.pl again on the machine to see any other variable changes to make.
     
  11. shezi

    shezi Member

    Joined:
    Feb 2, 2011
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    Please let me know one thing more clearly that my VPS is shared. Should i run mysqltuner.pl after every 24 hours?
     
  12. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    You wouldn't need to run it every 24 hours, but should probably run it for 2-3 days every 24 hours to see the results. After you are satisfied with the changes, then you can simply re-run mysqltuner.pl again once or twice a month. I recommend consistently running it periodically just due to the fact that your environment can periodically change due to adding / removing accounts, so running that optimization script periodically would be good to do long-term.
     
  13. shezi

    shezi Member

    Joined:
    Feb 2, 2011
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    Here is the result of mysqltuner.pl

     
  14. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Hello,

    Please post the /etc/my.cnf file contents every time after running the script as well. It isn't possible to know if the values were changed from those I initially suggested. I cannot note what to change unless I know what is in there currently.

    Thanks!
     
  15. shezi

    shezi Member

    Joined:
    Feb 2, 2011
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    Hello Dear,

    etc/my.cnf After Running mysqltuner.pl today.
     
  16. shezi

    shezi Member

    Joined:
    Feb 2, 2011
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    Hello,

    I think that i need some more things to optimize mysql. I request u to please let me know that how may i optimize more?

    I have seen /etc/my.cnf in my friends VPS is using for 768MB of RAM.
    I have 4GB of Ram and going to upgrade it to 8GB and mine /etc/my.cnf
    A big differnece. I request you to please help me to optimize mysql.
     
  17. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Hello Shezi,

    Your friend is using one variable that don't even work in Linux for MySQL --> thread_concurrency doesn't exist on Linux, please see this location that shows it is only for Solaris-based systems.

    Next, safe-show-database is deprecated because it's already the default. Please see this location for details.

    I would suggest your friend remove both those variables and I would be extremely leery of taking a template from someone else who has a different server environment and using that template in any way, since as you can see with the above two options, people oftentimes use variables that they don't even know what they do or if they are valid for their MySQL version or OS type. More variables here does not = better optimization, so your friend having more variables is pretty unlikely to be good, since most times the default values are the preferred ones for many of the existing variables and changing the defaults will reduce performance not improve performance.

    Please provide a re-run of mysqltuner.pl again. What you need to provide each time is both the current /etc/my.cnf file and the results from mysqltuner.pl if possible. I don't know if the recommendations it has have changed since you last posted them.

    Thanks!
     
  18. shezi

    shezi Member

    Joined:
    Feb 2, 2011
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    Before
    After Running mysqltuner.pl
     
  19. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Hello Shezi,

    I would need the report from running mysqltuner.pl itself (what it recommends) to provide additional recommendations on what to change.

    Thanks!
     
  20. shezi

    shezi Member

    Joined:
    Feb 2, 2011
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    Please suggest some extra commands:

    et/my.cnf
     
Loading...

Share This Page