| CPU 100% [message #1644] |
Fri, 17 August 2007 16:46  |
 Domanet
Messages: 1
Registered: August 2007 |
Junior Member |
|
|
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 |
+--------------------------+------------+
|
|
|
|
|
| Re: CPU 100% [message #1791 is a reply to message #1644 ] |
Tue, 11 September 2007 10:29  |
|
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).
Alexey Kovyrin, MySQL Performance Expert
MySQL Performance Blog
MySQL Consulting
|
|
|
Members
Search
Help
Register
Login
Home
