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.

my.cnf and optimization HELP DOWNTIME

Discussion in 'Workarounds and Optimization' started by maikon, Jul 18, 2010.

  1. maikon

    maikon Registered

    Joined:
    Jul 18, 2010
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    as i ran mysqltuner i got the following recommendation but i dont know how to adjust to the recommended ones need help, i have optimized apache and other services the only last missing part is mycnf


    heres how mycnf looks like FROM /etc/my.cnf

    using top
     
    #1 maikon, Jul 18, 2010
    Last edited: Jul 22, 2010
  2. jerrybell

    jerrybell Well-Known Member

    Joined:
    Nov 27, 2006
    Messages:
    90
    Likes Received:
    0
    Trophy Points:
    6
    To implement the mysqltuner suggestions:
    query_cache_size (>= 8M)
    tmp_table_size (> 32M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)
    table_cache (> 64)

    use the settings is my.cnf, under the [mysqld] section:

    query_cache_size = 32M
    tmp_table_size = 256M
    max_heap_table_size = 256M
    thread_cache_size = 64
    table_cache = 2048
     
  3. Miraenda

    Miraenda Well-Known Member

    Joined:
    Jul 28, 2004
    Messages:
    242
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Coralville, Iowa USA
    These values are far higher than the mysqltuner.pl recommendations:

    query_cache_size = 32M
    tmp_table_size = 256M
    max_heap_table_size = 256M
    thread_cache_size = 64
    table_cache = 2048

    Here again was what mysqltuner.pl recommended:

    Code:
    query_cache_size (>= 8M)
    tmp_table_size (> 32M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)
    table_cache (> 64)
    It's better to go up slowly rather than using values far above the mysqltuner.pl suggestions, since you could end up actually degrading rather than improving performance. These would be better to start out with:

    Code:
    query_cache_type=1
    query_cache_limit=1M
    query_cache_size=8M
    tmp_table_size=40M
    max_heap_table_size=40M
    thread_cache_size=4
    table_cache=72
    Also, please provide your MySQL version via running:

    Code:
    mysqladmin --version
    You'd want to enable the slow query log, but your MySQL version will determine how to do that in /etc/my.cnf file.

    I did want to mention that your load isn't actually what I'd call high, so if it's a load concern, then 2-3 isn't a high load really. If MySQL itself keeps crashing or causing a crash, you may want to check the error log, since there will be details there on why MySQL stopped or crashed normally:

    Code:
    cd /var/lib/mysql
    tail -500 hostname.err
    If you aren't sure the error log name, when in /var/lib/mysql, simply do this command to find it:

    Code:
    ls -lah *.err
    You should then see a file with .err listed and that would be the error log file.
     
    #3 Miraenda, Jul 18, 2010
    Last edited: Jul 18, 2010
  4. maikon

    maikon Registered

    Joined:
    Jul 18, 2010
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    thanjns miraenda i changed the mycnf and restarded mysql but the load is till heavy

    mysql version is 5.0.91-community

    i tried going withthe error log but it says no such file or directory
    note using vps and with 3 cpu cores
    top - 09:07:10 up 6 days, 17:32, 1 user, load average: 3.77, 3.27, 3.47
    Tasks: 153 total, 1 running, 140 sleeping, 11 stopped, 1 zombie
    Cpu(s): 24.4%us, 8.3%sy, 0.0%ni, 67.0%id, 0.1%wa, 0.0%hi, 0.0%si, 0.1%st
    Mem: 1925120k total, 1632460k used, 292660k free, 46728k buffers
    Swap: 1048568k total, 60k used, 1048508k free, 1082460k cached

    PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
    24942 nobody 15 0 72524 3520 1668 S 0.7 0.2 0:00.02 httpd
    19173 mysql 15 0 219m 36m 3420 S 0.3 1.9 19:59.57 mysqld
    24135 nobody 15 0 72932 4040 1736 S 0.3 0.2 0:00.05 httpd
    24579 nobody 15 0 72564 3740 1736 S 0.3 0.2 0:00.01 httpd
    24592 nobody 15 0 72548 3716 1736 S 0.3 0.2 0:00.03 httpd
    24596 nobody 15 0 72564 3748 1736 S 0.3 0.2 0:00.06 httpd
    24920 nobody 16 0 72812 3756 1728 S 0.3 0.2 0:00.01 httpd
    24937 nobody 15 0 72932 3944 1732 S 0.3 0.2 0:00.01 httpd
    1 root 15 0 10348 632 532 S 0.0 0.0 0:00.72 init
    2 root RT -5 0 0 0 S 0.0 0.0 0:41.21 migration/0
    3 root 34 19 0 0 0 S 0.0 0.0 0:05.95 ksoftirqd/0
    4 root RT -5 0 0 0 S 0.0 0.0 0:00.00 watchdog/0
    5 root 10 -5 0 0 0 S 0.0 0.0 0:00.12 events/0
    6 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 khelper
    7 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 kthread
    9 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 xenwatch
     
    #4 maikon, Jul 19, 2010
    Last edited: Jul 19, 2010
  5. Miraenda

    Miraenda Well-Known Member

    Joined:
    Jul 28, 2004
    Messages:
    242
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Coralville, Iowa USA
    Since the machine is on MySQL 5.0, you could put the following into /etc/my.cnf file to enable the slow query log:

    Code:
    log-slow-queries=/var/lib/mysql/slow.log
    After adding that line into /etc/my.cnf, then do the following to create the log file:

    Code:
    cd /var/lib/mysql
    touch slow.log
    chown mysql:mysql slow.log
    chmod 660 slow.log
    Then restart MySQL. Also, please provide the output from running this command:

    Code:
    ls -l /var/lib/mysql/*.err
    There should be an error log, since that's always created by default. There must be something returning for it.

    Finally, the load is not high for what you are showing and MySQL is only showing 0.3% CPU. Why do you believe that 3.77 is a high load in this instance? Is MySQL actually crashing on the machine?
     
  6. keddie

    keddie Well-Known Member

    Joined:
    Nov 17, 2007
    Messages:
    50
    Likes Received:
    0
    Trophy Points:
    6
    Just wanted to say thanks to the thread contributors, have been using these suggestions today with great success :)
     
  7. Miraenda

    Miraenda Well-Known Member

    Joined:
    Jul 28, 2004
    Messages:
    242
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Coralville, Iowa USA
    I'm glad the discussion here has been helpful for you :)
     
  8. maikon

    maikon Registered

    Joined:
    Jul 18, 2010
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    wel the site crashed and was down for 15 minutes and i still cant find anything on the log
     
  9. Miraenda

    Miraenda Well-Known Member

    Joined:
    Jul 28, 2004
    Messages:
    242
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Coralville, Iowa USA
    I was replying to the prior user who stated it was useful what had been discussed.

    Your prior reply to this one indicated you couldn't find any error logs. Did you find error logs? If so, what is in those logs? If not, what is the exact return on the command I asked about previously:

    Code:
    ls -l /var/lib/mysql/*.err
     
  10. maikon

    maikon Registered

    Joined:
    Jul 18, 2010
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    says no file or directory
    but i do notice php soetimes takes up 90% of the cpu resourcces
    any idea how to optimiz php
    0only strictly using blog script


    Tasks: 119 total, 2 running, 116 sleeping, 0 stopped, 1 zombie
    Cpu0 : 63.2%us, 27.1%sy, 0.0%ni, 9.4%id, 0.0%wa, 0.0%hi, 0.0%si, 0.3%st
    Cpu1 : 66.3%us, 26.3%sy, 0.0%ni, 7.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.3%st
    Cpu2 : 74.7%us, 22.7%sy, 0.0%ni, 2.3%id, 0.0%wa, 0.0%hi, 0.0%si, 0.3%st
    Mem: 2695168k total, 2445816k used, 249352k free, 41020k buffers
    Swap: 1048568k total, 0k used, 1048568k free, 1905892k cached

    PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
    15001 yako 17 0 0 0 0 Z 13.7 0.0 0:00.41 php <defunct>
    15014 yako 17 0 125m 36m 5788 R 12.0 1.4 0:00.36 php
    27689 mysql 15 0 153m 29m 3196 S 1.7 1.1 1:16.19 mysqld
    12927 nobody 15 0 72596 3652 1736 S 1.3 0.1 0:00.16 httpd
    14877 nobody 15 0 72668 3708 1724 S 1.3 0.1 0:00.04 httpd
    13384 nobody 15 0 72548 3600 1736 S 1.0 0.1 0:00.08 httpd
    14284 nobody 15 0 72556 3604 1732 S 1.0 0.1 0:00.06 httpd
    14350 nobody 15 0 72548 3600 1736 S 1.0 0.1 0:00.04 httpd
    2070 named 21 0 171m 4608 1980 S 0.7 0.2 0:00.70 named
    13687 nobody 15 0 72716 3756 1736 S 0.7 0.1 0:00.07 httpd
    13688 nobody 15 0 72716 3752 1736 S 0.3 0.1 0:00.07 httpd
    13690 nobody 16 0 72716 3768 1736 S 0.3 0.1 0:00.07 httpd
    14141 nobody 15 0 72548 3600 1736 S 0.3 0.1 0:00.02 httpd
    14142 nobody 15 0 72548 3596 1732 S 0.3 0.1 0:00.01 httpd
    14325 nobody 16 0 72540 3492 1736 S 0.3 0.1 0:00.01 httpd
     
    #10 maikon, Jul 22, 2010
    Last edited: Jul 22, 2010
Loading...

Share This Page