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 , High load avg and high cpu usage

Discussion in 'Workarounds and Optimization' started by minitech, Mar 14, 2011.

  1. minitech

    minitech Active Member

    Joined:
    Mar 14, 2011
    Messages:
    35
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Website Owner
    Hello.
    i am owner of dedicated server and i would like you to help me please.

    i have very high load avg and very high cpu usage by mysql , around 150%+

    i am running on the server 3 sites , one with 10000 visitors a day , and the other two are around 200+- visitors.

    here is the info:

    root@server [~]# mysqladmin extended-status
    +-----------------------------------+-----------+
    | Variable_name | Value |
    +-----------------------------------+-----------+
    | Aborted_clients | 0 |
    | Aborted_connects | 0 |
    | Binlog_cache_disk_use | 0 |
    | Binlog_cache_use | 0 |
    | Bytes_received | 7082045 |
    | Bytes_sent | 175569634 |
    | Com_admin_commands | 10 |
    | Com_alter_db | 0 |
    | Com_alter_table | 0 |
    | Com_analyze | 0 |
    | Com_backup_table | 0 |
    | Com_begin | 0 |
    | Com_call_procedure | 0 |
    | Com_change_db | 1364 |
    | Com_change_master | 0 |
    | Com_check | 0 |
    | Com_checksum | 0 |
    | Com_commit | 0 |
    | Com_create_db | 0 |
    | Com_create_function | 0 |
    | Com_create_index | 0 |
    | Com_create_table | 0 |
    | Com_create_user | 0 |
    | Com_dealloc_sql | 0 |
    | Com_delete | 4 |
    | Com_delete_multi | 0 |
    | Com_do | 0 |
    | Com_drop_db | 0 |
    | Com_drop_function | 0 |
    | Com_drop_index | 0 |
    | Com_drop_table | 0 |
    | Com_drop_user | 0 |
    | Com_execute_sql | 0 |
    | Com_flush | 0 |
    | Com_grant | 0 |
    | Com_ha_close | 0 |
    | Com_ha_open | 0 |
    | Com_ha_read | 0 |
    | Com_help | 0 |
    | Com_insert | 57 |
    | Com_insert_select | 0 |
    | Com_kill | 0 |
    | Com_load | 0 |
    | Com_load_master_data | 0 |
    | Com_load_master_table | 0 |
    | Com_lock_tables | 0 |
    | Com_optimize | 1 |
    | Com_preload_keys | 0 |
    | Com_prepare_sql | 0 |
    | Com_purge | 0 |
    | Com_purge_before_date | 0 |
    | Com_rename_table | 0 |
    | Com_repair | 184 |
    | Com_replace | 0 |
    | Com_replace_select | 0 |
    | Com_reset | 0 |
    | Com_restore_table | 0 |
    | Com_revoke | 0 |
    | Com_revoke_all | 0 |
    | Com_rollback | 0 |
    | Com_savepoint | 0 |
    | Com_select | 58524 |
    | Com_set_option | 1361 |
    | Com_show_binlog_events | 0 |
    | Com_show_binlogs | 0 |
    | Com_show_charsets | 0 |
    | Com_show_collations | 0 |
    | Com_show_column_types | 0 |
    | Com_show_create_db | 0 |
    | Com_show_create_table | 0 |
    | Com_show_databases | 2 |
    | Com_show_errors | 0 |
    | Com_show_fields | 0 |
    | Com_show_grants | 0 |
    | Com_show_innodb_status | 0 |
    | Com_show_keys | 0 |
    | Com_show_logs | 0 |
    | Com_show_master_status | 0 |
    | Com_show_ndb_status | 0 |
    | Com_show_new_master | 0 |
    | Com_show_open_tables | 0 |
    | Com_show_privileges | 0 |
    | Com_show_processlist | 3 |
    | Com_show_slave_hosts | 0 |
    | Com_show_slave_status | 0 |
    | Com_show_status | 4 |
    | Com_show_storage_engines | 3 |
    | Com_show_tables | 11 |
    | Com_show_triggers | 0 |
    | Com_show_variables | 4 |
    | Com_show_warnings | 0 |
    | Com_slave_start | 0 |
    | Com_slave_stop | 0 |
    | Com_stmt_close | 0 |
    | Com_stmt_execute | 0 |
    | Com_stmt_fetch | 0 |
    | Com_stmt_prepare | 0 |
    | Com_stmt_reset | 0 |
    | Com_stmt_send_long_data | 0 |
    | Com_truncate | 0 |
    | Com_unlock_tables | 0 |
    | Com_update | 99 |
    | Com_update_multi | 0 |
    | Com_xa_commit | 0 |
    | Com_xa_end | 0 |
    | Com_xa_prepare | 0 |
    | Com_xa_recover | 0 |
    | Com_xa_rollback | 0 |
    | Com_xa_start | 0 |
    | Compression | OFF |
    | Connections | 1398 |
    | Created_tmp_disk_tables | 1718 |
    | Created_tmp_files | 3839 |
    | Created_tmp_tables | 4918 |
    | Delayed_errors | 0 |
    | Delayed_insert_threads | 0 |
    | Delayed_writes | 0 |
    | Flush_commands | 1 |
    | Handler_commit | 0 |
    | Handler_delete | 4 |
    | Handler_discover | 0 |
    | Handler_prepare | 0 |
    | Handler_read_first | 15 |
    | Handler_read_key | 7131054 |
    | Handler_read_next | 9547923 |
    | Handler_read_prev | 1033704 |
    | Handler_read_rnd | 218022 |
    | Handler_read_rnd_next | 85161390 |
    | Handler_rollback | 0 |
    | Handler_savepoint | 0 |
    | Handler_savepoint_rollback | 0 |
    | Handler_update | 6555201 |
    | Handler_write | 17095101 |
    | Innodb_buffer_pool_pages_data | 51 |
    | Innodb_buffer_pool_pages_dirty | 0 |
    | Innodb_buffer_pool_pages_flushed | 1 |
    | Innodb_buffer_pool_pages_free | 461 |
    | Innodb_buffer_pool_pages_misc | 0 |
    | Innodb_buffer_pool_pages_total | 512 |
    | Innodb_buffer_pool_read_ahead_rnd | 3 |
    | Innodb_buffer_pool_read_ahead_seq | 0 |
    | Innodb_buffer_pool_read_requests | 5908 |
    | Innodb_buffer_pool_reads | 26 |
    | Innodb_buffer_pool_wait_free | 0 |
    | Innodb_buffer_pool_write_requests | 1 |
    | Innodb_data_fsyncs | 7 |
    | Innodb_data_pending_fsyncs | 0 |
    | Innodb_data_pending_reads | 0 |
    | Innodb_data_pending_writes | 0 |
    | Innodb_data_read | 3608576 |
    | Innodb_data_reads | 78 |
    | Innodb_data_writes | 7 |
    | Innodb_data_written | 35328 |
    | Innodb_dblwr_pages_written | 1 |
    | Innodb_dblwr_writes | 1 |
    | Innodb_log_waits | 0 |
    | Innodb_log_write_requests | 0 |
    | Innodb_log_writes | 2 |
    | Innodb_os_log_fsyncs | 5 |
    | Innodb_os_log_pending_fsyncs | 0 |
    | Innodb_os_log_pending_writes | 0 |
    | Innodb_os_log_written | 1024 |
    | Innodb_page_size | 16384 |
    | Innodb_pages_created | 0 |
    | Innodb_pages_read | 51 |
    | Innodb_pages_written | 1 |
    | Innodb_row_lock_current_waits | 0 |
    | Innodb_row_lock_time | 0 |
    | Innodb_row_lock_time_avg | 0 |
    | Innodb_row_lock_time_max | 0 |
    | Innodb_row_lock_waits | 0 |
    | Innodb_rows_deleted | 0 |
    | Innodb_rows_inserted | 0 |
    | Innodb_rows_read | 0 |
    | Innodb_rows_updated | 0 |
    | Key_blocks_not_flushed | 0 |
    | Key_blocks_unused | 6854 |
    | Key_blocks_used | 2697 |
    | Key_read_requests | 4316324 |
    | Key_reads | 7848 |
    | Key_write_requests | 754597 |
    | Key_writes | 9613 |
    | Last_query_cost | 0.000000 |
    | Max_used_connections | 32 |
    | Not_flushed_delayed_rows | 0 |
    | Open_files | 99 |
    | Open_streams | 0 |
    | Open_tables | 64 |
    | Opened_tables | 2101 |
    | Prepared_stmt_count | 0 |
    | Qcache_free_blocks | 0 |
    | Qcache_free_memory | 0 |
    | Qcache_hits | 0 |
    | Qcache_inserts | 0 |
    | Qcache_lowmem_prunes | 0 |
    | Qcache_not_cached | 0 |
    | Qcache_queries_in_cache | 0 |
    | Qcache_total_blocks | 0 |
    | Queries | 63004 |
    | Questions | 63004 |
    | Rpl_status | NULL |
    | Select_full_join | 0 |
    | Select_full_range_join | 0 |
    | Select_range | 8237 |
    | Select_range_check | 0 |
    | Select_scan | 3479 |
    | Slave_open_temp_tables | 0 |
    | Slave_retried_transactions | 0 |
    | Slave_running | OFF |
    | Slow_launch_threads | 0 |
    | Slow_queries | 14 |
    | Sort_merge_passes | 1917 |
    | Sort_range | 6053 |
    | Sort_rows | 46684233 |
    | Sort_scan | 5929 |
    | Table_locks_immediate | 64903 |
    | Table_locks_waited | 6 |
    | Tc_log_max_pages_used | 0 |
    | Tc_log_page_size | 0 |
    | Tc_log_page_waits | 0 |
    | Threads_cached | 0 |
    | Threads_connected | 14 |
    | Threads_created | 1397 |
    | Threads_running | 11 |
    | Uptime | 7858 |
    +-----------------------------------+-----------+


    >> MySQLTuner 1.2.0 - 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.91-community
    [OK] Operating on 32-bit architecture with less than 2GB RAM

    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB +Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 33M (Tables: 158)
    [--] Data in InnoDB tables: 144K (Tables: 9)
    [!!] Total fragmented tables: 1

    -------- Security Recommendations -------------------------------------------
    [OK] All database users have passwords assigned

    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 2h 11m 33s (65K q [8.339 qps], 1K conn, TX: 183M, RX: 7M)
    [--] Reads / Writes: 99% / 1%
    [--] Total buffers: 34.0M global + 2.7M per thread (500 max threads)
    [OK] Maximum possible memory usage: 1.3G (68% of installed RAM)
    [OK] Slow queries: 0% (14/65K)
    [OK] Highest usage of available connections: 6% (32/500)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/9.5M
    [OK] Key buffer hit rate: 99.8% (4M cached / 8K reads)
    [!!] Query cache is disabled
    [!!] Sorts requiring temporary tables: 15% (1K temp sorts / 12K sorts)
    [OK] Temporary tables created on disk: 25% (1K on disk / 6K total)
    [!!] Thread cache is disabled
    [!!] Table cache hit rate: 2% (64 open / 2K opened)
    [OK] Open file limit used: 4% (101/2K)
    [OK] Table locks acquired immediately: 99% (67K immediate / 67K locks)
    [OK] InnoDB data size / buffer pool: 144.0K/8.0M

    -------- Recommendations -----------------------------------------------------
    General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Set thread_cache_size to 4 as a starting value
    Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
    query_cache_size (>= 8M)
    sort_buffer_size (> 1M)
    read_rnd_buffer_size (> 256K)
    thread_cache_size (start at 4)
    table_cache (> 64)


    root@server [~]# cat /etc/my.cnf
    [mysqld]
    set-variable = max_connections=500
    safe-show-database

    processor : 0
    vendor_id : GenuineIntel
    cpu family : 6
    model : 15
    model name : Intel(R) Xeon(R) CPU X3210 @ 2.13GHz
    stepping : 11
    cpu MHz : 2133.000
    cache size : 4096 KB
    physical id : 0
    siblings : 4
    core id : 0
    cpu cores : 4
    apicid : 0
    fdiv_bug : no
    hlt_bug : no
    f00f_bug : no
    coma_bug : no
    fpu : yes
    fpu_exception : yes
    cpuid level : 10
    wp : yes
    flags : fpu vme de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe nx lm constant_tsc pni monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr lahf_lm
    bogomips : 4266.76

    processor : 1
    vendor_id : GenuineIntel
    cpu family : 6
    model : 15
    model name : Intel(R) Xeon(R) CPU X3210 @ 2.13GHz
    stepping : 11
    cpu MHz : 2133.000
    cache size : 4096 KB
    physical id : 0
    siblings : 4
    core id : 1
    cpu cores : 4
    apicid : 1
    fdiv_bug : no
    hlt_bug : no
    f00f_bug : no
    coma_bug : no
    fpu : yes
    fpu_exception : yes
    cpuid level : 10
    wp : yes
    flags : fpu vme de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe nx lm constant_tsc pni monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr lahf_lm
    bogomips : 4266.06

    processor : 2
    vendor_id : GenuineIntel
    cpu family : 6
    model : 15
    model name : Intel(R) Xeon(R) CPU X3210 @ 2.13GHz
    stepping : 11
    cpu MHz : 2133.000
    cache size : 4096 KB
    physical id : 0
    siblings : 4
    core id : 2
    cpu cores : 4
    apicid : 2
    fdiv_bug : no
    hlt_bug : no
    f00f_bug : no
    coma_bug : no
    fpu : yes
    fpu_exception : yes
    cpuid level : 10
    wp : yes
    flags : fpu vme de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe nx lm constant_tsc pni monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr lahf_lm
    bogomips : 4266.08

    processor : 3
    vendor_id : GenuineIntel
    cpu family : 6
    model : 15
    model name : Intel(R) Xeon(R) CPU X3210 @ 2.13GHz
    stepping : 11
    cpu MHz : 2133.000
    cache size : 4096 KB
    physical id : 0
    siblings : 4
    core id : 3
    cpu cores : 4
    apicid : 3
    fdiv_bug : no
    hlt_bug : no
    f00f_bug : no
    coma_bug : no
    fpu : yes
    fpu_exception : yes
    cpuid level : 10
    wp : yes
    flags : fpu vme de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe nx lm constant_tsc pni monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr lahf_lm
    bogomips : 4266.08




    Please help me with it.
     
  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
    For this suggestion:

    Run this command:

    Code:
    mysqlcheck -u root --auto-repair --optimize --all-databases
    For this suggestion:

    Wait next time 24 hours to run the script after a prior MySQL restart. Normally, I will not provide advice if you haven't waited the 24 hours to run the script after the last MySQL restart due to the fact it is utterly inaccurate if you don't wait long enough, which is why it provides that recommendation.

    For this suggestion:

    Code:
    Enable the slow query log to troubleshoot bad queries
    Put the following into your /etc/my.cnf file (this is the MySQL 5.0 entry as you appear to be running MySQL 5.0):

    Code:
    log-slow-queries=/var/lib/mysql/slow.log
    Save the file, then run these commands:

    Code:
    touch /var/lib/mysql/slow.log
    chown mysql:mysql /var/lib/mysql/slow.log
    chmod 660 /var/lib/mysql/slow.log
    For the other variables to adjust, put the following into the /etc/my.cnf so it looks like the following:

    Code:
    [mysqld]
    key_buffer_size=16M
    log-slow-queries=/var/lib/mysql/slow.log
    max_connections=500
    query_cache_limit=1M
    query_cache_size=8M
    query_cache_type=1
    read_rnd_buffer_size=512K
    sort_buffer_size=2M
    table_cache=80
    thread_cache_size=4
    After revising /etc/my.cnf file, then restart MySQL for the changes to take effect. After restarting MySQL, wait at least 24 hours before re-running mysqltuner.pl
     
  3. minitech

    minitech Active Member

    Joined:
    Mar 14, 2011
    Messages:
    35
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Website Owner
    Thanks you very much!
    lets hope everything will be alright!
     
Loading...

Share This Page