mysql占cpu高怎么优化

loveni · 发表于 2012-3-15 03:02:19

--+
2 rows in set (0.00 sec)

mysql> show status;
+-----------------------------------+----------+
| Variable_name                   | Value |
+-----------------------------------+----------+
| Aborted_clients                | 2       |
| Aborted_connects                | 10    |
| Binlog_cache_disk_use          | 0       |
| Binlog_cache_use                | 0       |
| Bytes_received                   | 141    |
| Bytes_sent                      | 1365    |
| Com_admin_commands             | 0       |
| Com_assign_to_keycache          | 0       |
| Com_alter_db                   | 0       |
| Com_alter_db_upgrade             | 0       |
| Com_alter_event                | 0       |
| Com_alter_function             | 0       |
| Com_alter_procedure             | 0       |
| Com_alter_server                | 0       |
| Com_alter_table                | 0       |
| Com_alter_tablespace             | 0       |
| Com_analyze                      | 0       |
| Com_backup_table                | 0       |
| Com_begin                      | 0       |
| Com_binlog                      | 0       |
| Com_call_procedure             | 0       |
| Com_change_db                   | 0       |
| Com_change_master                | 0       |
| Com_check                      | 0       |
| Com_checksum                   | 0       |
| Com_commit                      | 0       |
| Com_create_db                   | 0       |
| Com_create_event                | 0       |
| Com_create_function             | 0       |
| Com_create_index                | 0       |
| Com_create_procedure             | 0       |
| Com_create_server                | 0       |
| Com_create_table                | 0       |
| Com_create_trigger             | 0       |
| Com_create_udf                   | 0       |
| Com_create_user                | 0       |
| Com_create_view                | 0       |
| Com_dealloc_sql                | 0       |
| Com_delete                      | 0       |
| Com_delete_multi                | 0       |
| Com_do                         | 0       |
| Com_drop_db                      | 0       |
| Com_drop_event                   | 0       |
| Com_drop_function                | 0       |
| Com_drop_index                   | 0       |
| Com_drop_procedure             | 0       |
| Com_drop_server                | 0       |
| Com_drop_table                   | 0       |
| Com_drop_trigger                | 0       |
| Com_drop_user                   | 0       |
| Com_drop_view                   | 0       |
| Com_empty_query                | 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                      | 0       |
| Com_insert_select                | 0       |
| Com_install_plugin             | 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_preload_keys                | 0       |
| Com_prepare_sql                | 0       |
| Com_purge                      | 0       |
| Com_purge_before_date          | 0       |
| Com_release_savepoint          | 0       |
| Com_rename_table                | 0       |
| Com_rename_user                | 0       |
| Com_repair                      | 0       |
| 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_rollback_to_savepoint       | 0       |
| Com_savepoint                   | 0       |
| Com_select                      | 0       |
| Com_set_option                   | 0       |
| Com_show_authors                | 0       |
| Com_show_binlog_events          | 0       |
| Com_show_binlogs                | 0       |
| Com_show_charsets                | 0       |
| Com_show_collations             | 0       |
| Com_show_column_types          | 0       |
| Com_show_contributors          | 0       |
| Com_show_create_db             | 0       |
| Com_show_create_event          | 0       |
| Com_show_create_func             | 0       |
| Com_show_create_proc             | 0       |
| Com_show_create_table          | 0       |
| Com_show_create_trigger          | 0       |
| Com_show_databases             | 0       |
| Com_show_engine_logs             | 0       |
| Com_show_engine_mutex          | 0       |
| Com_show_engine_status          | 0       |
| Com_show_events                | 0       |
| Com_show_errors                | 0       |
| Com_show_fields                | 0       |
| Com_show_function_status       | 0       |
| Com_show_grants                | 0       |
| Com_show_keys                   | 0       |
| Com_show_master_status          | 0       |
| Com_show_new_master             | 0       |
| Com_show_open_tables             | 0       |
| Com_show_plugins                | 0       |
| Com_show_privileges             | 0       |
| Com_show_procedure_status       | 0       |
| Com_show_processlist             | 3       |
| Com_show_profile                | 0       |
| Com_show_profiles                | 0       |
| Com_show_slave_hosts             | 0       |
| Com_show_slave_status          | 0       |
| Com_show_status                | 1       |
| Com_show_storage_engines       | 0       |
| Com_show_table_status          | 0       |
| Com_show_tables                | 0       |
| Com_show_triggers                | 0       |
| Com_show_variables             | 0       |
| 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_reprepare             | 0       |
| Com_stmt_reset                   | 0       |
| Com_stmt_send_long_data          | 0       |
| Com_truncate                   | 0       |
| Com_uninstall_plugin             | 0       |
| Com_unlock_tables                | 0       |
| Com_update                      | 0       |
| 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                      | 3301    |
| Created_tmp_disk_tables          | 0       |
| Created_tmp_files                | 5       |
| Created_tmp_tables             | 0       |
| Delayed_errors                   | 0       |
| Delayed_insert_threads          | 0       |
| Delayed_writes                   | 0       |
| Flush_commands                   | 1       |
| Handler_commit                   | 0       |
| Handler_delete                   | 0       |
| Handler_discover                | 0       |
| Handler_prepare                | 0       |
| Handler_read_first             | 0       |
| Handler_read_key                | 0       |

loveni · 发表于 2012-3-15 03:05:51

| Handler_read_next                | 0       |
| Handler_read_prev                | 0       |
| Handler_read_rnd                | 0       |
| Handler_read_rnd_next          | 0       |
| Handler_rollback                | 0       |
| Handler_savepoint                | 0       |
| Handler_savepoint_rollback       | 0       |
| Handler_update                   | 0       |
| Handler_write                   | 0       |
| Innodb_buffer_pool_pages_data    | 21    |
| Innodb_buffer_pool_pages_dirty | 0       |
| Innodb_buffer_pool_pages_flushed  | 0       |
| Innodb_buffer_pool_pages_free    | 491    |
| Innodb_buffer_pool_pages_misc    | 0       |
| Innodb_buffer_pool_pages_total | 512    |
| Innodb_buffer_pool_read_ahead_rnd | 1       |
| Innodb_buffer_pool_read_ahead_seq | 0       |
| Innodb_buffer_pool_read_requests  | 629    |
| Innodb_buffer_pool_reads       | 14    |
| Innodb_buffer_pool_wait_free    | 0       |
| Innodb_buffer_pool_write_requests | 0       |
| Innodb_data_fsyncs             | 3       |
| Innodb_data_pending_fsyncs       | 0       |
| Innodb_data_pending_reads       | 0       |
| Innodb_data_pending_writes       | 0       |
| Innodb_data_read                | 2527232  |
| Innodb_data_reads                | 31    |
| Innodb_data_writes             | 3       |
| Innodb_data_written             | 1536    |
| Innodb_dblwr_pages_written       | 0       |
| Innodb_dblwr_writes             | 0       |
| Innodb_log_waits                | 0       |
| Innodb_log_write_requests       | 0       |
| Innodb_log_writes                | 1       |
| Innodb_os_log_fsyncs             | 3       |
| Innodb_os_log_pending_fsyncs    | 0       |
| Innodb_os_log_pending_writes    | 0       |
| Innodb_os_log_written          | 512    |
| Innodb_page_size                | 16384 |
| Innodb_pages_created             | 0       |
| Innodb_pages_read                | 21    |
| Innodb_pages_written             | 0       |
| 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                | 228920 |
| Key_blocks_used                | 634    |
| Key_read_requests                | 2050891  |
| Key_reads                      | 565    |
| Key_write_requests             | 517    |
| Key_writes                      | 513    |
| Last_query_cost                | 0.000000 |
| Max_used_connections             | 5       |
| Not_flushed_delayed_rows       | 0       |
| Open_files                      | 47    |
| Open_streams                   | 0       |
| Open_table_definitions          | 28    |
| Open_tables                      | 26    |
| Opened_files                   | 2329    |
| Opened_table_definitions       | 0       |
| Opened_tables                   | 0       |
| 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       |
| Questions                      | 412419 |
| Rpl_status                      | NULL    |
| Select_full_join                | 0       |
| Select_full_range_join          | 0       |
| Select_range                   | 0       |
| Select_range_check             | 0       |
| Select_scan                      | 0       |
| Slave_open_temp_tables          | 0       |
| Slave_retried_transactions       | 0       |
| Slave_running                   | OFF    |
| Slow_launch_threads             | 0       |
| Slow_queries                   | 0       |
| Sort_merge_passes                | 0       |
| Sort_range                      | 0       |
| Sort_rows                      | 0       |
| Sort_scan                      | 0       |
| Ssl_accept_renegotiates          | 0       |
| Ssl_accepts                      | 0       |
| Ssl_callback_cache_hits          | 0       |
| Ssl_cipher                      |       |
| Ssl_cipher_list                |       |
| Ssl_client_connects             | 0       |
| Ssl_connect_renegotiates       | 0       |
| Ssl_ctx_verify_depth             | 0       |
| Ssl_ctx_verify_mode             | 0       |
| Ssl_default_timeout             | 0       |
| Ssl_finished_accepts             | 0       |
| Ssl_finished_connects          | 0       |
| Ssl_session_cache_hits          | 0       |
| Ssl_session_cache_misses       | 0       |
| Ssl_session_cache_mode          | NONE    |
| Ssl_session_cache_overflows    | 0       |
| Ssl_session_cache_size          | 0       |
| Ssl_session_cache_timeouts       | 0       |
| Ssl_sessions_reused             | 0       |
| Ssl_used_session_cache_entries | 0       |
| Ssl_verify_depth                | 0       |
| Ssl_verify_mode                | 0       |
| Ssl_version                      |       |
| Table_locks_immediate          | 192618 |
| Table_locks_waited             | 116    |
| Tc_log_max_pages_used          | 0       |
| Tc_log_page_size                | 0       |
| Tc_log_page_waits                | 0       |
| Threads_cached                   | 3       |
| Threads_connected                | 2       |
| Threads_created                | 5       |
| Threads_running                | 1       |
| Uptime                         | 1205    |
+-----------------------------------+----------+
289 rows in set (0.02 sec)

mysql>

lazyzhu · 发表于 2012-3-15 03:06:52

圈圈在忙,明天来回复你

Poison · 发表于 2012-3-15 03:23:12

配置上就是给mysql更多的资源

程序上就是优化查询

loveni · 发表于 2012-3-15 03:44:11

[mysqld]
basedir=D:/APMServ5.2.6/MySQL5.1
datadir=D:/APMServ5.2.6/MySQL5.1/data
port=3306
key_buffer=384M
max_allowed_packet=4M
table_cache=1024
thread_cache=64
join_buffer_size=32M
sort_buffer=32M
record_buffer=32M
max_connections=500
wait_timeout=120
interactive_timeout=120
max_connect_errors=30000
long_query_time=1
max_heap_table_size=256M
tmp_table_size=128M
thread_concurrency=8
myisam_sort_buffer_size=128M

house · 发表于 2012-3-15 03:55:16

同求mysql优化

		自动登录	找回密码
密码			注册

[Windows VPS] mysql占cpu高 怎么优化

[Windows VPS] mysql占cpu高怎么优化