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.

Database usage creating high CPU load and crashing

Discussion in 'General Discussion' started by supportech, Mar 10, 2011.

  1. supportech

    supportech Member

    Joined:
    Dec 8, 2010
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    1
    Hi guys,

    The database usage in our shared server is always going up and the server crashes too often . Could you please advise out a solution to optimize the server , like doing something in the tweak settings, installing any software to limit the usage of database for any particular user, or editing the my.cnf file. Due to some of the users, the server load is going up every time . This has been a headache for us and our main job is to always monitor this server because of the load and try our best to keep the load low .

    Please check the settings in the my.cnf file
    -------------------------------------------------------------------------------------
    [mysqld]
    #set-variable = max_connections=500
    safe-show-database
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    local-infile = 0
    skip-locking
    skip-bdb
    #key_buffer = 128M
    key_buffer = 384M
    myisam_sort_buffer_size = 64M
    join_buffer_size = 1M
    read_buffer_size = 2M
    sort_buffer_size = 2M
    read_rnd_buffer_size = 2M
    table_cache = 1024
    record_buffer = 1M
    thread_cache_size = 32
    tmp_table_size = 64M
    max_heap_table_size = 64M
    max_allowed_packet = 16M
    max_connect_errors = 10
    query_cache_limit = 1M
    query_cache_size = 32M
    query_cache_type = 1
    thread_concurrency = 8
    max_connections = 500
    max_user_connections = 25
    interactive_timeout = 30
    wait_timeout = 30
    connect_timeout = 10

    #newly added
    [myisamchk]
    key_buffer=64M
    sort_buffer=64M
    read_buffer=16M
    write_buffer=16M

    #[safe_mysqld]
    log_slow_queries = ON
    #log=/var/log/mysql/mysqld.log
    log_error=/var/log/mysql/mysqld.log
    long_query_time=2

    innodb_buffer_pool_size=2G
    innodb_additional_mem_pool_size=20M
    #innodb_log_file_size = 32M
    innodb_log_buffer_size = 32M
    ========================================================================
    Please check the usage also.
    --------------------------------------------------------------------------------------------------------------
    | Id | User | Host | db | Command | Time | State | Info |
    +--------+----------------+-----------+-------------------+---------+------+-------+------------------+
    | 198272 | user | localhost | user_whmcs | Sleep | 351 | |
    =========================================================================

    Hoping for a solution soon from you.
    Thank you.
     
  2. LinuxTechie

    LinuxTechie Well-Known Member

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

    supportech Member

    Joined:
    Dec 8, 2010
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    1
    Hello

    Thank you very much for your reply.

    Please have a look at the mysqltuner.pl script output that. Could you please provide some suggestions about the changes that we have to do in my.cnf.

    >> MySQLTuner 1.1.2 - Major Hayden <major@mhtx.net>
    >> Bug reports, feature requests, and downloads at http://mysqltuner.com/
    >> 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.92-community
    [OK] Operating on 64-bit architecture

    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 3G (Tables: 17677)
    [--] Data in InnoDB tables: 2G (Tables: 1715)
    [--] Data in MEMORY tables: 1M (Tables: 57)
    [!!] Total fragmented tables: 1270

    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 3h 35m 10s (8M q [87.094 qps], 217K conn, TX: 29B, RX: 1B)
    [--] Reads / Writes: 78% / 22%
    [--] Total buffers: 490.0M global + 6.2M per thread (500 max threads)
    [OK] Maximum possible memory usage: 3.5G (60% of installed RAM)
    [OK] Slow queries: 0% (546/8M)
    [OK] Highest usage of available connections: 23% (118/500)
    [OK] Key buffer size / total MyISAM indexes: 384.0M/686.5M
    [OK] Key buffer hit rate: 99.3% (202M cached / 1M reads)
    [OK] Query cache efficiency: 75.2% (5M cached / 7M selects)
    [!!] Query cache prunes per day: 792869
    [OK] Sorts requiring temporary tables: 0% (2K temp sorts / 595K sorts)
    [!!] Joins performed without indexes: 4794
    [!!] Temporary tables created on disk: 34% (151K on disk / 435K total)
    [OK] Thread cache hit rate: 99% (264 created / 217K connections)
    [!!] Table cache hit rate: 0% (1K open / 451K opened)
    [OK] Open file limit used: 76% (1K/2K)
    [OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)
    [!!] InnoDB data size / buffer pool: 2.3G/8.0M

    -------- Recommendations -----------------------------------------------------
    General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
    query_cache_size (> 32M)
    join_buffer_size (> 1.0M, or always use indexes with joins)
    tmp_table_size (> 64M)
    max_heap_table_size (> 64M)
    table_cache (> 1024)
    innodb_buffer_pool_size (>= 2G)


    Thanks again!!
     
  4. 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,

    Here is the revised /etc/my.cnf file with the settings removed that were commented out, those that don't exist or are the defaults removed and those that were under the entirely wrong section moved up to the right section. Since you are using MySQL 5.0, you also need to create the slow query log to get it logging, which I've added the entry into /etc/my.cnf but you'll have to then run the commands I provide after the /etc/my.cnf contents to get it working to log.

    Code:
    cd /var/lib/mysql
    touch slow.log
    chmod 660 slow.log
    chown mysql:mysql slow.log
    /etc/init.d/mysql restart
    Thanks.
     
  5. supportech

    supportech Member

    Joined:
    Dec 8, 2010
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    1
    Hello,

    Thank you for the reply. I made the changes exactly as you suggested. But now getting the result like this.

    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 6s (75 q [12.500 qps], 10 conn, TX: 205K, RX: 10K)
    [--] Reads / Writes: 83% / 17%
    [--] Total buffers: 2.5G global + 8.2M per thread (500 max threads)
    [!!] Maximum possible memory usage: 6.6G (112% of installed RAM) ===> having issues here.
    [OK] Slow queries: 0% (0/75)
    [OK] Highest usage of available connections: 0% (2/500)
    [!!] Key buffer size / total MyISAM indexes: 384.0M/687.7M
    [!!] Key buffer hit rate: 72.6% (288 cached / 79 reads)
    [!!] Query cache efficiency: 4.3% (2 cached / 46 selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 5 sorts)
    [OK] Temporary tables created on disk: 20% (1 on disk / 5 total)
    [OK] Thread cache hit rate: 80% (2 created / 10 connections)
    [OK] Table cache hit rate: 86% (37 open / 43 opened)
    [OK] Open file limit used: 2% (74/2K)
    [OK] Table locks acquired immediately: 100% (71 immediate / 71 locks)
    [!!] InnoDB data size / buffer pool: 2.3G/2.0G

    -------- Recommendations -----------------------------------------------------
    General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
    Variables to adjust:
    *** MySQL's maximum memory usage is dangerously high ***
    *** Add RAM before increasing MySQL buffer variables ***
    key_buffer_size (> 687.7M)
    query_cache_limit (> 1M, or use smaller result sets)
    innodb_buffer_pool_size (>= 2G)


    Could you please give some suggestions on this. Please have a look at this also:

    # free -m
    total used free shared buffers cached
    Mem: 5955 5630 324 0 300 3447
    -/+ buffers/cache: 1882 4072
    Swap: 4094 208 3886
     
  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
    First of all, here is what I see:

    It appears that you did not wait 24 hours to run the report. Please let us know after you have. The way the report works, the variables cannot be changed and then immediately re-run mysqltuner.pl to get any type of accurate gauge. You do need to wait 24 hours to re-run it.

    Next, the MySQL memory utilization that it can have is high, not your actual RAM usage. Per free -m, your system has your used memory as 1882 and free as 4072, so that isn't high at all. To properly read free -m, it is the buffers/cache field that matters as Linux typically loads memory into buffer or cache. In that cached memory, 4072 is free, which is pretty dang good.

    Instead for the MySQL memory usage, it means to take out some of the variables that you've configured to reduce the memory footprint. I won't say what to change until you've waited the 24 hours to run the report, since there is not much point until then to determine what to revise.
     
  7. supportech

    supportech Member

    Joined:
    Dec 8, 2010
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    1
    A Single VPS's database usage causing high load on the main node

    Hello,

    We have a Linux open VZ based VPS node which has 30 VPSs. The server has 16GB RAM. Recently we have seen that a single VPS's database usage causes very high load on the server. We had asked him to optimize his database, but it is still causing a very high load. The /etc/my.cnf file has been shown below :
    ============
    [mysqld]
    local-infile=0
    skip-networking
    ============

    Please do advise and provide tweaks and methods or scripts and crons to decrease this database usage and so other users do not get affected.

    Thank you.
     
  8. 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
    Is this an entirely new machine or the previous machine, because it previously had different variables. Also, did mysqltuner.pl get run on the machine after 24 hours time had passed to provide accurate results? If so, what did it suggest?
     
  9. supportech

    supportech Member

    Joined:
    Dec 8, 2010
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    1
    Hello Tristan,

    Thanks for your help and replies. The server that I mentioned was a new one.

    In the previous server where we made the changes in my.cnf, we were unable to sustain it as the load became extremely high and we had to reset them to previous configuration to make the load back to normal. But the databse issue came once again.

    Here is the new my.cnf and mysqltuner.pl outputs.
    ============================================================
    [mysqld]
    #set-variable = max_connections=500
    safe-show-database
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    local-infile = 0
    skip-locking
    skip-bdb
    #key_buffer = 128M
    key_buffer = 384M
    myisam_sort_buffer_size = 64M
    join_buffer_size = 3M
    read_buffer_size = 2M
    sort_buffer_size = 2M
    read_rnd_buffer_size = 2M
    table_cache = 2048
    record_buffer = 1M
    thread_cache_size = 32
    tmp_table_size = 70M
    max_heap_table_size = 70M
    max_allowed_packet = 16M
    max_connect_errors = 10
    query_cache_limit = 1M
    query_cache_size = 50M
    query_cache_type = 1
    thread_concurrency = 8
    max_connections = 500
    max_user_connections = 25
    interactive_timeout = 30
    wait_timeout = 30
    connect_timeout = 10
    #[safe_mysqld]
    log_slow_queries = ON
    #log=/var/log/mysql/mysqld.log
    log_error=/var/log/mysql/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
    log_queries_not_using_indexes = On
    log_slow_queries=/var/log/mysql/mysqld.slow.log
    long_query_time=2

    #newly added
    [myisamchk]
    key_buffer=64M
    sort_buffer=64M
    read_buffer=16M
    write_buffer=16M

    innodb_buffer_pool_size=2G
    innodb_additional_mem_pool_size=20M
    #innodb_log_file_size = 32M
    innodb_log_buffer_size = 32M


    ============================================================
    # /usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl

    >> MySQLTuner 1.1.2 - Major Hayden <major@mhtx.net>
    >> Bug reports, feature requests, and downloads at http://mysqltuner.com/
    >> 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.92-community-log
    [OK] Operating on 64-bit architecture

    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 3G (Tables: 17957)
    [--] Data in InnoDB tables: 2G (Tables: 1715)
    [--] Data in MEMORY tables: 1M (Tables: 57)
    [!!] Total fragmented tables: 1304

    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1h 4m 21s (475K q [123.241 qps], 8K conn, TX: 1B, RX: 67M)
    [--] Reads / Writes: 86% / 14%
    [--] Total buffers: 514.0M global + 8.2M per thread (500 max threads)
    [OK] Maximum possible memory usage: 4.5G (77% of installed RAM)
    [OK] Slow queries: 0% (932/475K)
    [OK] Highest usage of available connections: 18% (94/500)
    [OK] Key buffer size / total MyISAM indexes: 384.0M/713.7M
    [OK] Key buffer hit rate: 99.3% (5M cached / 39K reads)
    [OK] Query cache efficiency: 71.0% (295K cached / 416K selects)
    [!!] Query cache prunes per day: 524106
    [OK] Sorts requiring temporary tables: 0% (135 temp sorts / 61K sorts)
    [!!] Joins performed without indexes: 247
    [!!] Temporary tables created on disk: 36% (7K on disk / 20K total)
    [OK] Thread cache hit rate: 98% (94 created / 8K connections)
    [OK] Table cache hit rate: 34% (2K open / 5K opened)
    [OK] Open file limit used: 84% (3K/4K)
    [OK] Table locks acquired immediately: 99% (156K immediate / 156K locks)
    [!!] InnoDB data size / buffer pool: 2.3G/8.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
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
    query_cache_size (> 50M)
    join_buffer_size (> 3.0M, or always use indexes with joins)
    tmp_table_size (> 70M)
    max_heap_table_size (> 70M)
    innodb_buffer_pool_size (>= 2G)

    =================================================

    I could see that there are issues with some databases from the slow query log, but not sure. Could you please advice how should I manage the database like optimize/repair based on the slow query log.

    Thanks again!!!
     
Loading...

Share This Page