GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

CPU 100%

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • CPU 100%

    Hi all new to mysql,

    Here's a report from my server could some one point me in the direction to improve the performance.

    I guessing i need to increase my table_cache (64) & thread_cache_size (0)

    Thanks in advance.
    +--------------------------+------------+
    | Variable_name | Value |
    +--------------------------+------------+
    | Aborted_clients | 1124 |
    | Aborted_connects | 1 |
    | Bytes_received | 2542124223 |
    | Bytes_sent | 3574814365 |
    | Com_admin_commands | 4 |
    | Com_alter_table | 0 |
    | Com_analyze | 0 |
    | Com_backup_table | 0 |
    | Com_begin | 0 |
    | Com_change_db | 26098148 |
    | Com_change_master | 0 |
    | Com_check | 0 |
    | Com_commit | 78978 |
    | Com_create_db | 0 |
    | Com_create_function | 0 |
    | Com_create_index | 0 |
    | Com_create_table | 0 |
    | Com_delete | 103337 |
    | Com_delete_multi | 0 |
    | Com_drop_db | 0 |
    | Com_drop_function | 0 |
    | Com_drop_index | 0 |
    | Com_drop_table | 0 |
    | Com_flush | 0 |
    | Com_grant | 0 |
    | Com_ha_close | 0 |
    | Com_ha_open | 0 |
    | Com_ha_read | 0 |
    | Com_insert | 2349661 |
    | 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 | 0 |
    | Com_purge | 0 |
    | Com_rename_table | 0 |
    | Com_repair | 1 |
    | Com_replace | 0 |
    | Com_replace_select | 0 |
    | Com_reset | 0 |
    | Com_restore_table | 0 |
    | Com_revoke | 0 |
    | Com_rollback | 0 |
    | Com_savepoint | 0 |
    | Com_select | 326801168 |
    | Com_set_option | 50840937 |
    | Com_show_binlog_events | 0 |
    | Com_show_binlogs | 0 |
    | Com_show_create | 1 |
    | Com_show_databases | 49 |
    | Com_show_fields | 2904 |
    | Com_show_grants | 0 |
    | Com_show_keys | 0 |
    | Com_show_logs | 0 |
    | Com_show_master_status | 0 |
    | Com_show_new_master | 0 |
    | Com_show_open_tables | 0 |
    | Com_show_processlist | 0 |
    | Com_show_slave_hosts | 0 |
    | Com_show_slave_status | 0 |
    | Com_show_status | 4 |
    | Com_show_innodb_status | 0 |
    | Com_show_tables | 140 |
    | Com_show_variables | 25380908 |
    | Com_slave_start | 0 |
    | Com_slave_stop | 0 |
    | Com_truncate | 1 |
    | Com_unlock_tables | 0 |
    | Com_update | 2253140 |
    | Connections | 28439606 |
    | Created_tmp_disk_tables | 0 |
    | Created_tmp_tables | 0 |
    | Created_tmp_files | 0 |
    | Delayed_insert_threads | 0 |
    | Delayed_writes | 0 |
    | Delayed_errors | 0 |
    | Flush_commands | 1 |
    | Handler_commit | 0 |
    | Handler_delete | 98747 |
    | Handler_read_first | 40068930 |
    | Handler_read_key | 276186749 |
    | Handler_read_next | 4240045576 |
    | Handler_read_prev | 0 |
    | Handler_read_rnd | 72326292 |
    | Handler_read_rnd_next | 254358603 |
    | Handler_rollback | 0 |
    | Handler_update | 1782669 |
    | Handler_write | 2349661 |
    | Key_blocks_used | 7764 |
    | Key_read_requests | 3874640141 |
    | Key_reads | 6253387 |
    | Key_write_requests | 5359120 |
    | Key_writes | 5227511 |
    | Max_used_connections | 115 |
    | Not_flushed_key_blocks | 0 |
    | Not_flushed_delayed_rows | 0 |
    | Open_tables | 13 |
    | Open_files | 21 |
    | Open_streams | 0 |
    | Opened_tables | 30543 |
    | Questions | 462348492 |
    | Qcache_queries_in_cache | 0 |
    | Qcache_inserts | 0 |
    | Qcache_hits | 0 |
    | Qcache_lowmem_prunes | 0 |
    | Qcache_not_cached | 0 |
    | Qcache_free_memory | 0 |
    | Qcache_free_blocks | 0 |
    | Qcache_total_blocks | 0 |
    | Rpl_status | NULL |
    | Select_full_join | 0 |
    | Select_full_range_join | 0 |
    | Select_range | 269323213 |
    | Select_range_check | 0 |
    | Select_scan | 7865467 |
    | Slave_open_temp_tables | 0 |
    | Slave_running | OFF |
    | Slow_launch_threads | 123582 |
    | Slow_queries | 2 |
    | Sort_merge_passes | 0 |
    | Sort_range | 72549160 |
    | Sort_rows | 72326292 |
    | Sort_scan | 5591484 |
    | Table_locks_immediate | 306206298 |
    | Table_locks_waited | 156 |
    | Threads_cached | 0 |
    | Threads_created | 28439605 |
    | Threads_connected | 3 |
    | Threads_running | 1 |
    | Uptime | 3743312 |
    +--------------------------+------------+

  • #2
    You don't need to change the thread_cache since there are still very few opened tables/queries relation.

    You could try increasing the thread_cache_size to about 8 but I don't think that will do that much.

    Usually when the CPU hits the roof you have sort/group by queries that can't use an index to retrieve the rows in order and hence is has to sort them on the fly when it is executing the query.

    Try to use the slow query log and see if you can get any lead on queries that takes a long time to execute.

    Comment


    • #3
      You can do a SHOW PROCESSLIST when your CPU is at 100%
      and see what queries are going on....

      Comment


      • #4
        You definitely should increase table and thread caches.

        But I don't think it could cause 100% cpu usage. Looking at your stats I'd say you have really many full table scans so I'd suggest to optimize your queries (enable slow log with 1 sec long_query_time and log queries not using indexes, wait for some time and then use mysqlsla to find out what queries should be optimized first).

        Comment

        Working...
        X