mysql - 如何更正此 MySQL 数据库设置？

Question

我的一个 MySQL 数据库当前在运行 PHP/Drupal 时使用超过 99% 的 CPU查询。

我正在调查根本原因是什么。

使用以下分析和配置数据，您能否建议主要数据库设置问题或丢失/不正确的配置？

free的输出在 Bash 中:

             total       used       free     shared    buffers     cached
Mem:          2048       1635        412          0          0          0
-/+ buffers/cache:       1635        412
Swap:            0          0          0

ps auxf的输出:

root      7430  0.0  0.0   9176  1400 ?        S    May30   0:00
/bin/sh /usr/local/mysql/bin/mysqld_safe --user=mysql --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/db.pid
mysql     7617  0.6 12.3 486900 258956 ?       Sl   May30 114:26  \_ /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/usr/local/mysql/data/db.err --pid-file=/usr/local/mysql/data/db.pid --socket=/tmp/mysql.sock

这是 my.cnf 文件:

[mysqld]
socket=/tmp/mysql.sock
local-infile=0
tmpdir=/dev/shm
max_allowed_packet=100M

这是sql show status的输出:

SQL result

Host: localhost
Database: db
Generation Time: Jun 11, 2012 at 09:22 AM
Generated by: phpMyAdmin 3.4.7.1 / MySQL 5.5.24
SQL query: SHOW STATUS;
Rows: 310

Variable_name    Value
Aborted_clients    242
Aborted_connects    50
Binlog_cache_disk_use    0
Binlog_cache_use    0
Binlog_stmt_cache_disk_use    0
Binlog_stmt_cache_use    0
Bytes_received    221
Bytes_sent    1421
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_begin    0
Com_binlog    0
Com_call_procedure    0
Com_change_db    1
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_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_resignal    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    2
Com_signal    0
Com_show_authors    0
Com_show_binlog_events    0
Com_show_binlogs    0
Com_show_charsets    0
Com_show_collations    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
Variable_name     Value
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_open_tables    0
Com_show_plugins    1
Com_show_privileges    0
Com_show_procedure_status    0
Com_show_processlist    0
Com_show_profile    0
Com_show_profiles    0
Com_show_relaylog_events    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    9376
Created_tmp_disk_tables    1
Created_tmp_files    6
Created_tmp_tables    1
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
Handler_read_last    0
Handler_read_next    0
Handler_read_prev    0
Handler_read_rnd    0
Handler_read_rnd_next    21
Handler_rollback    0
Handler_savepoint    0
Handler_savepoint_rollback    0
Handler_update    0
Handler_write    20
Innodb_buffer_pool_pages_data    8142
Innodb_buffer_pool_pages_dirty    0
Innodb_buffer_pool_pages_flushed    1269726
Innodb_buffer_pool_pages_free    0
Innodb_buffer_pool_pages_misc    49
Innodb_buffer_pool_pages_total    8191
Innodb_buffer_pool_read_ahead_rnd    0
Innodb_buffer_pool_read_ahead    1306
Innodb_buffer_pool_read_ahead_evicted    8907
Innodb_buffer_pool_read_requests    139892912
Innodb_buffer_pool_reads    308579
Innodb_buffer_pool_wait_free    0
Innodb_buffer_pool_write_requests    5066182
Innodb_data_fsyncs    313257
Innodb_data_pending_fsyncs    0
Innodb_data_pending_reads    0
Innodb_data_pending_writes    0
Innodb_data_read    5084336128
Innodb_data_reads    308981
Innodb_data_writes    658963
Innodb_data_written    22884830208
Innodb_dblwr_pages_written    634863
Innodb_dblwr_writes    19355
Innodb_have_atomic_builtins    ON
Innodb_log_waits    0
Innodb_log_write_requests    3971026
Innodb_log_writes    266658
Innodb_os_log_fsyncs    273603
Variable_name     Value
Innodb_os_log_pending_fsyncs    0
Innodb_os_log_pending_writes    0
Innodb_os_log_written    2077984256
Innodb_page_size    16384
Innodb_pages_created    55793
Innodb_pages_read    310189
Innodb_pages_written    634863
Innodb_row_lock_current_waits    0
Innodb_row_lock_time    185765
Innodb_row_lock_time_avg    476
Innodb_row_lock_time_max    30082
Innodb_row_lock_waits    390
Innodb_rows_deleted    96261
Innodb_rows_inserted    243234
Innodb_rows_read    73669737
Innodb_rows_updated    367966
Innodb_truncated_status_writes    0
Key_blocks_not_flushed    0
Key_blocks_unused    6657
Key_blocks_used    851
Key_read_requests    33873766
Key_reads    3991
Key_write_requests    5515715
Key_writes    11500
Last_query_cost    2.399000
Max_used_connections    22
Not_flushed_delayed_rows    0
Open_files    41
Open_streams    0
Open_table_definitions    400
Open_tables    400
Opened_files    141222
Opened_table_definitions    0
Opened_tables    0
Performance_schema_cond_classes_lost    0
Performance_schema_cond_instances_lost    0
Performance_schema_file_classes_lost    0
Performance_schema_file_handles_lost    0
Performance_schema_file_instances_lost    0
Performance_schema_locker_lost    0
Performance_schema_mutex_classes_lost    0
Performance_schema_mutex_instances_lost    0
Performance_schema_rwlock_classes_lost    0
Performance_schema_rwlock_instances_lost    0
Performance_schema_table_handles_lost    0
Performance_schema_table_instances_lost    0
Performance_schema_thread_classes_lost    0
Performance_schema_thread_instances_lost    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
Queries    4346053
Questions    5
Rpl_status    AUTH_MASTER
Select_full_join    0
Select_full_range_join    0
Select_range    0
Select_range_check    0
Select_scan    1
Slave_heartbeat_period    0.000
Slave_open_temp_tables    0
Slave_received_heartbeats    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    5113600
Variable_name     Value
Table_locks_waited    17
Tc_log_max_pages_used    0
Tc_log_page_size    0
Tc_log_page_waits    0
Threads_cached    0
Threads_connected    1
Threads_created    9375
Threads_running    1
Uptime    1074227
Uptime_since_flush_status    1074227

Answer 1

我还会仔细检查执行哪些查询以及在哪些表上执行。我不知道 Drupal 是否有某种查询调试/分析模式，但值得尝试研究一下。

从中，还尝试使用 EXPLAIN 检查它们(例如，某些表可能需要索引)。

我了解您的网站位于 Drupal 中，它至少应该已经在其核心表上进行了优化。