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.

High load (mysql)

Discussion in 'Workarounds and Optimization' started by iulian_2007, Jul 12, 2010.

  1. iulian_2007

    iulian_2007 Registered

    Joined:
    Feb 3, 2010
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    1
    Hello,

    I have a cpanel server with 4GB ram, P4 3.0 Ghz, Raid 1 with 2 sata hdd.
    My load is high: 12:33:14 up 1 day, 3:11, 1 user, load average: 9.85, 8.97, 7.24

    /etc/my.cnf
    [mysqld]
    set-variable = max_connections=200
    safe-show-database
    max_allowed_packet=8M


    log-slow-queries = /var/log/mysql-slow.log
    log-queries-not-using-indexes
    long_query_time = 1
    query_cache_limit=1M
    query_cache_size=64M ## 32MB for every 1GB of RAM
    query_cache_type=1
    thread_cache_size=24

    query_cache_size=64M
    join_buffer_size=2M
    sort_buffer_size=2M
    read_buffer_size=2M
    read_rnd_buffer_size=2M
    table_cache=2048

    key_buffer_size=512M
    open_files_limit=10240


    innodb_buffer_pool_size=512M

    results of mysqltunner:

    >> MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net>
    >> Bug reports, feature requests, and downloads at MySQLTuner
    >> Run with '--help' for additional options and output filtering

    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.0.91-community-log
    [!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 455M (Tables: 1884)
    [--] Data in InnoDB tables: 2M (Tables: 22)
    [--] Data in MEMORY tables: 1M (Tables: 1)
    [!!] Total fragmented tables: 39

    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 29m 41s (416K q [233.758 qps], 13K conn, TX: 358M, RX: 38M)
    [--] Reads / Writes: 86% / 14%
    [--] Total buffers: 1.1G global + 8.2M per thread (200 max threads)
    [!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
    [!!] Maximum possible memory usage: 2.7G (72% of installed RAM)
    [!!] Slow queries: 6% (29K/416K)
    [OK] Highest usage of available connections: 12% (24/200)
    [OK] Key buffer size / total MyISAM indexes: 512.0M/299.4M
    [OK] Key buffer hit rate: 99.9% (101M cached / 82K reads)
    [OK] Query cache efficiency: 67.3% (245K cached / 365K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (70 temp sorts / 10K sorts)
    [!!] Joins performed without indexes: 12
    [OK] Temporary tables created on disk: 3% (135 on disk / 3K total)
    [OK] Thread cache hit rate: 99% (24 created / 13K connections)
    [OK] Table cache hit rate: 99% (2K open / 2K opened)
    [OK] Open file limit used: 38% (3K/10K)
    [OK] Table locks acquired immediately: 98% (175K immediate / 178K locks)
    [OK] InnoDB data size / buffer pool: 2.8M/512.0M

    -------- Recommendations -----------------------------------------------------
    General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Adjust your join queries to always utilize indexes
    Variables to adjust:
    join_buffer_size (> 2.0M, or always use indexes with joins)


    Can someone please help?
     
  2. Miraenda

    Miraenda Well-Known Member

    Joined:
    Jul 28, 2004
    Messages:
    242
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Coralville, Iowa USA
    You'd probably want to increase the join_buffer_size setting to be higher, since it's recommending that to be done.

    Next, you can run an optimize for all databases with this command:

    Code:
    mysqlcheck -u root -p --auto-repair --check --optimize --all-databases
    For the slow queries, you should grab a tool to go through the slow query log:

    Code:
    wget http://hackmysql.com/scripts/mysqlsla
    chmod 755 mysqlsla*
    mv mysqlsla* mysqlsla
    ./mysqlsla -lt slow /var/log/mysql-slow.log
    Hopefully, /var/log/mysql-slow.log has been created with 660 file permissions and mysql:mysql ownership already. The above program will look for the top 10 database users for slow queries (those taking 10 or more seconds to run) and provide a percentage output for each database user, which can help to track down the most inefficient databases.
     
  3. zestylemon

    zestylemon Member

    Joined:
    Jul 19, 2010
    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    1
    Is there anyway to put this into cron so that it runs on a regular basis? I'm not a Unix guru, just someone who knows what cron is.
     
  4. Miraenda

    Miraenda Well-Known Member

    Joined:
    Jul 28, 2004
    Messages:
    242
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Coralville, Iowa USA
    You could cron it, although I'm not certain if that's advisable to cron too frequently, since it could take awhile to run and push the load during the optimization and repair if you have a large number of databases or a lot of disk usage for MySQL.

    To do a cron once a week for it, you could do the following in crontab -e location:

    Code:
    0 1 * * 6 mysqlcheck -u root -p --auto-repair --check --optimize --all-databases
    This would run a cron at 1:00am server time on Saturdays.

    To understand the cron, here is how it works for 0 1 * * 6

    0 = 0 minute of the hour (0-60 minutes in an hour, so 0 is 00 after the hour)
    1 = 1st hour (0-23 hours, so 1 is 1am server time)
    * = day of the month (1-31 days in the month) where * means every
    * = month (1-12 for the months) where * means every
    6 = 6th day of the week (0-6 being the days of the week starting 0 for Sunday, so 6 is Saturday)

    Thanks.
     
  5. zestylemon

    zestylemon Member

    Joined:
    Jul 19, 2010
    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    1
    Am I correct to assume that when this cronjob runs it will just run succesfully; ie: it won't pause waiting for a password because of the -p switch?
     
  6. PenguinInternet

    PenguinInternet Well-Known Member
    PartnerNOC

    Joined:
    Jun 20, 2007
    Messages:
    149
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Cardiff, UK
    cPanel Access Level:
    DataCenter Provider
    Twitter:
    It won't run via cron without passing the password after the -p switch, which personally I would not do due to the security implications of this
     
  7. onel0ve

    onel0ve Member

    Joined:
    Jul 14, 2010
    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    1
    Code:
    [mysqld]
    max_connections = 300
    key_buffer = 32M
    myisam_sort_buffer_size = 32M
    join_buffer_size = 1M
    read_buffer_size = 1M
    sort_buffer_size = 2M
    table_cache = 4000
    thread_cache_size = 286
    interactive_timeout = 25
    wait_timeout = 7000
    connect_timeout = 10
    max_allowed_packet = 16M
    max_connect_errors = 10
    query_cache_limit = 2M
    query_cache_size = 12M
    query_cache_type = 1
    tmp_table_size = 16M
    skip-innodb
    [mysqld_safe]
    open_files_limit = 8192
    [mysqldump]
    quick
    max_allowed_packet = 16M
    [myisamchk]
    key_buffer = 64M
    sort_buffer = 64M
    read_buffer = 16M
    write_buffer = 16M
    [mysqlhotcopy]
    interactive-timeout
    use this config for mysql
     
  8. alex_hgt

    alex_hgt Registered

    Joined:
    Feb 27, 2011
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    i use this command for all my users

    Code:
    mysqlcheck -Aor
    i think is the best
     
  9. LaceHost-Ishan

    LaceHost-Ishan Active Member

    Joined:
    Dec 6, 2008
    Messages:
    32
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Pune, India
    cPanel Access Level:
    DataCenter Provider
    +1 . No password needed.
     
  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
    If you take out the -p switch in the command, you don't have to pass the password when running the cron as the root user (in root's crontab at crontab -e or /var/spool/cron/root):

    Code:
    mysqlcheck -u root --auto-repair --check --optimize --all-databases
    Using -Aor is simply shorthand for almost the exact same command previously provided. Sure it is shorter, but how will people know what they are running who don't normally use mysqlcheck when you shorten it all? For those unfamilar with the flags, here is a key:

    Key
    --all-databases = -A flag
    --optimize = -o flag
    --repair = -r flag

    I prefer auto-repair to repair. Here is the difference between the two:

    The --check option certainly doesn't need to be used since it is the default option, so really a better command would be:

    Code:
    mysqlcheck -u root --auto-repair --optimize --all-databases
    The -u root portion could also be omitted. Again, I do not believe it is in the best interest in understanding the command for newer users by shortening these to flags, but if you already understand the flags, then go for it.
     
  11. onel0ve

    onel0ve Member

    Joined:
    Jul 14, 2010
    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    1
    one problem

    Code:
    [root@srv ~]# mysqlcheck -Aor
    -bash: mysqlcheck: command not found
    
    not working for me
     
  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
    Then try:

    Code:
    whereis mysqlcheck
    If you don't find it, then something is seriously wrong with your MySQL installation. mysqlcheck has been part of MySQL since at least MySQL 3.23
     
Loading...

Share This Page