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.

MySQL optimize help for newbe

Discussion in 'Workarounds and Optimization' started by saamxvr, Oct 31, 2010.

  1. saamxvr

    saamxvr Well-Known Member

    Joined:
    Oct 30, 2010
    Messages:
    90
    Likes Received:
    0
    Trophy Points:
    6
    IM using Ngnix +Apache + Wordpress , im daily get Cpu load Mail from WHM , Plz help me to Confige it Step by step Thank .!!


     
  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
    Please provide your MySQL version. The slow query log differs on MySQL 5.0 and 5.1. I'm not certain why people always cut off the beginning of the report where it shows the MySQL version.

    To get the version outside of running the report, run this command:

    Code:
    mysql --version
    Next, to optimize all tables that are fragmented, you could run this command:

    Code:
    mysqlcheck -u root -p --auto-repair --check --optimize --all-databases
    To enable the slow query log if you are running MySQL 5.0, it would be these steps:

    1. Add the following to /etc/my.cnf file

    Code:
    log-slow-queries=/var/lib/mysql/slow.log
    2. Create the slow query log file with the right ownership and permissions

    Code:
    cd /var/lib/mysql
    touch slow.log
    chown mysql:mysql slow.log
    chmod 660 slow.log
    3. Restart MySQL

    Code:
    /etc/init.d/mysql restart
    To enable the slow query log if you are running MySQL 5.1, it would be these steps:

    1. Add the following to /etc/my.cnf file

    Code:
    slow-query-log
    2. Restart MySQL

    Code:
    /etc/init.d/mysql restart
    On MySQL 5.1, the slow-query-log line in /etc/my.cnf will auto create the file in /var/lib/mysql/hostname-slow.log location.

    Next for the variables recommended by mysqltuner.pl, please use the following (and please paste what you have in /etc/my.cnf as well):

    query_cache_type=1
    query_cache_size=8M
    query_cache_limit=1M
    sort_buffer_size=2M
    read_rnd_buffer_size=512K
    tmp_table_size=48M
    max_heap_table_size=48M
    thread_cache_size=24
    key_buffer_size=16M (if you don't already have this variable set, if you do, keep it at whatever it's set at)
    table_cache=80 (if on MySQL 5.1, use table_open_cache=80 instead)

    I added table_cache / table_open_cache and key_buffer_size as those should almost always be placed into any /etc/my.cnf file.

    After you've revised /etc/my.cnf file, restart MySQL. Please give the changes 24 hours to see the results. After you do, re-run mysqltuner.pl again and see what recommendations it has. You'll need to keep doing this for several days to optimize. Of note, don't expect it will necessarily be a huge improvement. You have a 32-bit system and can only make use of 2GB of RAM. MySQL performs better on 64-bit systems and with 4GB or more RAM.

    Also, it's entirely possible your issues aren't even MySQL related, since I have no idea of the actual issues your machine is having. Sometimes, people presume MySQL is the cause due to the high CPU usage MySQL always displays, but high CPU isn't always an indicator of legitimate load causing issues. I've seen MySQL use 80-100% CPU on a server with low load, and I've seen MySQL using over 100% CPU on a server with high load. It's the MySQL processes actually running at the set time that were causing the load and CPU was worthless as any type of indicator for load in any testing that I performed for MySQL. Even on loaded systems (load over 35), MySQL never caused any high iowait times on the machines I tested, so if you find your server has high iowait times, it probably is something else other than MySQL causing it.
     
  3. saamxvr

    saamxvr Well-Known Member

    Joined:
    Oct 30, 2010
    Messages:
    90
    Likes Received:
    0
    Trophy Points:
    6
    this is my MSQL vertion

    this is my old my.cof

    now is like this

    i just modify it will see after next 24hours Thank your gide
     
  4. saamxvr

    saamxvr Well-Known Member

    Joined:
    Oct 30, 2010
    Messages:
    90
    Likes Received:
    0
    Trophy Points:
    6
    this is my MSQL after 48 hours:) have any problem ?

     
  5. saamxvr

    saamxvr Well-Known Member

    Joined:
    Oct 30, 2010
    Messages:
    90
    Likes Received:
    0
    Trophy Points:
    6
    This my MYSQL PERFORMANCE TUNING PRIMER report :)

     
  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
    Both reports seem to indicate the same for what needs increased:

    query_cache_size (> 8M)
    tmp_table_size (> 48M)
    max_heap_table_size (> 48M)
    table_cache (> 80)

    Just do what it says, increase the values for those 4 settings to be higher, so something like the following would work:

    query_cache_size=16M
    tmp_table_size=60M
    max_heap_table_size=60M
    table_cache=160
     
  7. saamxvr

    saamxvr Well-Known Member

    Joined:
    Oct 30, 2010
    Messages:
    90
    Likes Received:
    0
    Trophy Points:
    6
    yes i did, i will post next 24hour result thank.!!! :):)
     
  8. saamxvr

    saamxvr Well-Known Member

    Joined:
    Oct 30, 2010
    Messages:
    90
    Likes Received:
    0
    Trophy Points:
    6
    This is my /etc/my.cnf let me know best teune variables?





     
  9. saamxvr

    saamxvr Well-Known Member

    Joined:
    Oct 30, 2010
    Messages:
    90
    Likes Received:
    0
    Trophy Points:
    6
    PLz loo my aother result



     
  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
    At this point, you've added variables that you weren't told to add from the original posting. Originally, you only had these and have added others that were never indicated:

    [mysqld]
    set-variable = max_connections=500
    query_cache_type=1
    query_cache_size=8M
    query_cache_limit=1M
    sort_buffer_size=2M
    read_rnd_buffer_size=512K
    tmp_table_size=48M
    max_heap_table_size=48M
    thread_cache_size=24
    key_buffer_size=16M
    table_cache=80
    log-slow-queries=/var/lib/mysql/slow.log

    Versus what you are showing now:

    [mysqld]
    local-infile=0
    log-slow-queries=/var/lib/mysql/slow.log
    max_connections = 100
    skip-locking (this is already the default, it has no purpose in /etc/my.cnf)
    key_buffer_size= 128M
    max_allowed_packet = 16M
    table_cache = 256
    sort_buffer_size = 1M
    join_buffer_size=1M
    read_buffer_size = 2M
    read_rnd_buffer_size = 32M (normally have no need for this and it's set too high)
    myisam_sort_buffer_size = 48M (normally have no need for this)
    thread_cache_size = 96
    query_cache_size= 64M
    query_cache_type=1
    query_cache_limit=1M
    skip-innodb (you only want to skip InnoDB if you aren't using it)
    wait_timeout = 30

    [mysqldump]
    quick (this is the default, no purpose to have in /etc/my.cnf file)
    max_allowed_packet = 16M

    [mysql]
    no-auto-rehash

    [isamchk]
    key_buffer = 64M
    sort_buffer_size = 64M
    read_buffer = 16M
    write_buffer = 16M

    Please go back to what we were originally working with and take the recommendations that were given on changes (for example, you changed query_cache_size= 64M from the value I had said and put it far higher, you removed tmp_table_size and max_heap_table_size entirely). I'm unsure why you removed the values we were working with and put in different variables, but the values you removed and replaced them with have now caused you to be utilizing far more memory.

    Once you've gone back to what we were working with originally and incorporate the previous suggestions for those values, we can go from there. We cannot work with your existing my.cnf as you've entirely changed it. The point of optimization is to keep increasing the existing variables until they are the right settings and it is never good policy to take someone else's template and use it, especially if they are going to suggest you add in variables that either are bad values for your environment or worthless ones such as skip-locking, which is included by default since MySQL 4.0 and has no purpose in /etc/my.cnf
     
  11. saamxvr

    saamxvr Well-Known Member

    Joined:
    Oct 30, 2010
    Messages:
    90
    Likes Received:
    0
    Trophy Points:
    6
    im sorry i did some modification but its will not help for me , ok i will update my old my.cnf and let you know
     
  12. saamxvr

    saamxvr Well-Known Member

    Joined:
    Oct 30, 2010
    Messages:
    90
    Likes Received:
    0
    Trophy Points:
    6
    last 2 today's im tuning mysql but at the moment show like this




    [mysqld]
    log-slow-queries=/var/lib/mysql/slow.log
    local-infile=0
    set-variable = max_connections=500
    query_cache_type=1
    query_cache_size=64M
    query_cache_limit=1M
    sort_buffer_size=2M
    read_rnd_buffer_size=512K
    tmp_table_size=256M
    max_heap_table_size=256M
    thread_cache_size=24
    key_buffer_size=16M
    table_cache=512

    :confused: plz chack what i do ?
     
  13. GaryT

    GaryT Well-Known Member

    Joined:
    May 19, 2010
    Messages:
    321
    Likes Received:
    3
    Trophy Points:
    16
    This is caused by the amount of connetions your allowing, Also increase the other values as suggested.

    What got changed ?

    table_cache - I added an extra 128 to the value, which the tuner suggested to increase.

    query_cache_size I added 32M to the value, This does not have to be a big amount anyway so only adjust this slightly.

    set-variable = max_connections= I halfed the connections. The reason I done this is on your previous post's within apache and connection problems you only had 25 at one given point at peak.

    500 is quite a big amount for mysql and considering you only use 4 wordpress websites.

    Change the config as I suggested, Then restart your MySql server.

    You can do this by running:

    Hope this helps.
     
  14. saamxvr

    saamxvr Well-Known Member

    Joined:
    Oct 30, 2010
    Messages:
    90
    Likes Received:
    0
    Trophy Points:
    6
    Thanks mr.UK , i already update will see next 24hors :rolleyes:
     
  15. saamxvr

    saamxvr Well-Known Member

    Joined:
    Oct 30, 2010
    Messages:
    90
    Likes Received:
    0
    Trophy Points:
    6
    this is result at the moment :) what is do for now ?





     
  16. GaryT

    GaryT Well-Known Member

    Joined:
    May 19, 2010
    Messages:
    321
    Likes Received:
    3
    Trophy Points:
    16
    restart mysql:

     
Loading...

Share This Page