title: How can I correct this MySQL database set up?
body: <p>One of my MySQL databases is currently using over 99% CPU when running PHP/<a href="https://en.wikipedia.org/wiki/Drupal" rel="nofollow noreferrer">Drupal</a> queries.</p>
  <p>I am investigating what the root cause is.</p>
  <p>Using the following profiling and configuration data, can you suggest a main database setup problems or missing/incorrect configuration?</p>
  <p>Output of <a href="https://linux.die.net/man/1/free" rel="nofollow noreferrer"><code>free</code></a> in Bash:</p>
  <pre class="lang-none prettyprint-override"><code>             total       used       free     shared    buffers     cached
  Mem:          2048       1635        412          0          0          0
  -/+ buffers/cache:       1635        412
  Swap:            0          0          0
  </code></pre>
  <p>Output of <code>ps auxf</code>:</p>
  <pre class="lang-none prettyprint-override"><code>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
  </code></pre>
  <p>Here is the <code>my.cnf</code> file:</p>
  <pre class="lang-none prettyprint-override"><code>[mysqld]
  socket=/tmp/mysql.sock
  local-infile=0
  tmpdir=/dev/shm
  max_allowed_packet=100M
  </code></pre>
  <p>Here is the output of <code>sql show status</code>:</p>
  <pre class="lang-none prettyprint-override"><code>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
  </code></pre>