How to correct this MySQL database set up? -

one of mysql databases using on 99% cpu when running php/drupal queries.

i investigating root cause.

using following profiling , configuration data can suggest main db setup problems or missing/incorrect configuration ?

bash free:

             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 

here my.cnf:

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

here sql show status:

sql result  host: localhost database: db generation time: jun 11, 2012 @ 09:22 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 

what i'd examining queries performed , on tables. don't know if drupal has kind of debugging/profiling mode queries, it's worth try investigate this.

from these, trying examine them explain (f.ex. tables may need indexes).

i understand site in drupal, should optimized @ least on core tables; use custom queries/tables?