Handler_read_md and Handler_read_md_next showing very high

[City Builder]

Hello,
My webhost has broght it to my attention that my Handler_read_md and Handler_read_md_next are quite high, they are listed below:

Handler_read_rnd     26 M     
Handler_read_rnd_next    805 M    


When looking in MySQL's run process page it lists the following infomation for possibilities on why those two numbers are high:

Handler_read_rnd     26 M     The number of requests to read a row based on a fixed position. This is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan whole tables or you have joins that don't use keys properly.


Handler_read_rnd_next    805 M    The number of requests to read the next row in the data file. This is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.

We are on shared hosting and noticed our performance getting slower and slower, having browsers timeout, etc.  The host seems to be pointing their finger at those two options from the MySQL.

They told us that it's possible these numbers are so high because "all" of our mysql databases were all using one user, so they told us to create one user for each database which we did, but the numbers have still gone up since doing so.

We don't know what the numbers were prior to the latest install of our newest SMF 1.1.4 forums so we don't know if it's related or not, but we recently bought a new domain name and created a 1.1.4 forum there, and we also created 12 subdomains and put forums on all of them too.  They all use one database so that people can just login to any of the 13 forums without having to register as a new user on each one seperately.

None of our forum sites are what I would term to be ultra busy or anything like that, so I am at a loss for an explanation or what to do about it and am looking to you experts since you all have th experience in your own software and running it to maybe help us figure out what's going on.

All of our websites also use Tiny Portal as well.

Thanks for any information you can pass our way.
CB

stats.php yields the following:

Code Select Expand

Simple Machines
Server Status
Basic Information
May 01, 2008, 11:32:49 PM
Operating System: Debian 3.1
Processor: Dual Core AMD Opteron(tm) Processor 175 (2194.535MHz)
Load averages: 3.62, 3.17, 3.15
Current processes: 4 (2 sleeping, 2 running, 0 zombie)
Processes by CPU: (other) (3) 0.0%
Memory usage: 96.012% (3912556k / 4075060k)
Swap: 4.491% (283524k / 6313512k)
MySQL Statistics
MySQL 5.0.x
Connections per second: 1.0612
Kilobytes received per second: 1.0864
Kilobytes sent per second: 2.9161
Queries per second: 51.2976
Percentage of slow queries: 0.1727
Opened vs. Open tables:
(table_cache) 5636.7257 (should be <= 80)
Table cache usage:
(table_cache) 1 (should be >= 0.5 and <= 0.9)
Key buffer read hit rate:
(key_buffer_size) 0.0105 (should be <= 0.01)
Key buffer write hit rate:
(key_buffer_size) 0.1019 (should be <= 0.5)
Thread cache hit rate:
(thread_cache_size) 1222.2843 (should be >= 30 )
Thread cache usage:
(thread_cache_size) 0.4 (should be >= 0.7 and <= 0.9)
Temporary table disk usage:
(tmp_table_size) 0.279 (should be <= 0.5)
Sort merge pass rate:
(sort_buffer) 0 (should be <= 0.001)
Query cache enabled:
(query_cache_type) 1 (should be >= 1 and <= 1)
Query cache miss rate:
(query_cache_limit) 1 (should be <= 0.5)
Query cache prune rate:
(query_cache_size) 0 (should be <= 0.05)

MySQL status
Aborted_clients: 2468
Aborted_connects: 1360
Binlog_cache_disk_use: 0
Binlog_cache_use: 0
Bytes_received: 1009725379
Bytes_sent: 2710260254
Com_admin_commands: 1072
Com_alter_db: 1
Com_alter_table: 676
Com_analyze: 1
Com_backup_table: 0
Com_begin: 6502
Com_change_db: 968442
Com_change_master: 0
Com_check: 12906
Com_checksum: 0
Com_commit: 4130
Com_create_db: 9
Com_create_function: 0
Com_create_index: 0
Com_create_table: 1830
Com_dealloc_sql: 0
Com_delete: 137365
Com_delete_multi: 0
Com_do: 0
Com_drop_db: 21
Com_drop_function: 0
Com_drop_index: 0
Com_drop_table: 1453
Com_drop_user: 0
Com_execute_sql: 0
Com_flush: 127
Com_grant: 170
Com_ha_close: 0
Com_ha_open: 0
Com_ha_read: 0
Com_help: 0
Com_insert: 178076
Com_insert_select: 2002
Com_kill: 0
Com_load: 0
Com_load_master_data: 0
Com_load_master_table: 0
Com_lock_tables: 5676
Com_optimize: 5682
Com_preload_keys: 0
Com_prepare_sql: 0
Com_purge: 0
Com_purge_before_date: 0
Com_rename_table: 0
Com_repair: 1
Com_replace: 23011
Com_replace_select: 0
Com_reset: 0
Com_restore_table: 0
Com_revoke: 465
Com_revoke_all: 0
Com_rollback: 9
Com_savepoint: 0
Com_select: 39888339
Com_set_option: 217033
Com_show_binlog_events: 0
Com_show_binlogs: 138
Com_show_charsets: 589
Com_show_collations: 589
Com_show_column_types: 0
Com_show_create_db: 0
Com_show_create_table: 123821
Com_show_databases: 592
Com_show_errors: 0
Com_show_fields: 191893
Com_show_grants: 259
Com_show_innodb_status: 0
Com_show_keys: 323
Com_show_logs: 0
Com_show_master_status: 0
Com_show_ndb_status: 0
Com_show_new_master: 0
Com_show_open_tables: 1
Com_show_privileges: 0
Com_show_processlist: 57
Com_show_slave_hosts: 0
Com_show_slave_status: 0
Com_show_status: 33668
Com_show_storage_engines: 6
Com_show_tables: 2918585
Com_show_triggers: 123549
Com_show_variables: 7062
Com_show_warnings: 3
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_reset: 0
Com_stmt_send_long_data: 0
Com_truncate: 6753
Com_unlock_tables: 6396
Com_update: 610817
Com_update_multi: 913
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: 963160
Created_tmp_disk_tables: 1485046
Created_tmp_files: 370
Created_tmp_tables: 5322042
Delayed_errors: 0
Delayed_insert_threads: 0
Delayed_writes: 0
Flush_commands: 12
Handler_commit: 31346
Handler_delete: 95021
Handler_discover: 0
Handler_prepare: 0
Handler_read_first: 1818871
Handler_read_key: 335442548
Handler_read_next: 2534534365
Handler_read_prev: 73079073
Handler_read_rnd: 66241143
Handler_read_rnd_next: 2197398900
Handler_rollback: 1131
Handler_savepoint: 0
Handler_savepoint_rollback: 0
Handler_update: 95891583
Handler_write: 194000995
Innodb_buffer_pool_pages_data: 255
Innodb_buffer_pool_pages_dirty: 0
Innodb_buffer_pool_pages_flushed: 22052
Innodb_buffer_pool_pages_free: 188
Innodb_buffer_pool_pages_latched: 0
Innodb_buffer_pool_pages_misc: 5
Innodb_buffer_pool_pages_total: 448
Innodb_buffer_pool_read_ahead_rnd: 1895
Innodb_buffer_pool_read_ahead_seq: 408
Innodb_buffer_pool_read_requests: 51084691
Innodb_buffer_pool_reads: 84273
Innodb_buffer_pool_wait_free: 0
Innodb_buffer_pool_write_requests: 140872
Innodb_data_fsyncs: 20833
Innodb_data_pending_fsyncs: 0
Innodb_data_pending_reads: 0
Innodb_data_pending_writes: 0
Innodb_data_read: 1620938752
Innodb_data_reads: 90264
Innodb_data_writes: 26839
Innodb_data_written: 748170240
Innodb_dblwr_pages_written: 22052
Innodb_dblwr_writes: 2932
Innodb_log_waits: 0
Innodb_log_write_requests: 46972
Innodb_log_writes: 6570
Innodb_os_log_fsyncs: 8629
Innodb_os_log_pending_fsyncs: 0
Innodb_os_log_pending_writes: 0
Innodb_os_log_written: 24518656
Innodb_page_size: 16384
Innodb_pages_created: 1114
Innodb_pages_read: 98801
Innodb_pages_written: 22052
Innodb_row_lock_current_waits: 0
Innodb_row_lock_time: 6463
Innodb_row_lock_time_avg: 646
Innodb_row_lock_time_max: 2353
Innodb_row_lock_waits: 10
Innodb_rows_deleted: 4846
Innodb_rows_inserted: 10740
Innodb_rows_read: 212158080
Innodb_rows_updated: 4197
Key_blocks_not_flushed: 0
Key_blocks_unused: 1801
Key_blocks_used: 6365
Key_read_requests: 713705196
Key_reads: 7473488
Key_write_requests: 6298288
Key_writes: 641840
Last_query_cost: 0.000000
Max_used_connections: 117
Not_flushed_delayed_rows: 0
Open_files: 696
Open_streams: 0
Open_tables: 350
Opened_tables: 1972854
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: 46558883
Rpl_status: NULL
Select_full_join: 51005
Select_full_range_join: 1427
Select_range: 1755309
Select_range_check: 0
Select_scan: 7970845
Slave_open_temp_tables: 0
Slave_retried_transactions: 0
Slave_running: OFF
Slow_launch_threads: 0
Slow_queries: 8038929
Sort_merge_passes: 257
Sort_range: 6490909
Sort_rows: 372713411
Sort_scan: 2854449
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: 49234883
Table_locks_waited: 3707
Tc_log_max_pages_used: 0
Tc_log_page_size: 0
Tc_log_page_waits: 0
Threads_cached: 8
Threads_connected: 7
Threads_created: 788
Threads_running: 1
Uptime: 907623

MySQL variables
auto_increment_increment: 1
auto_increment_offset: 1
automatic_sp_privileges: ON
back_log: 50
basedir: /data/mysql/dspain/
binlog_cache_size: 32768
bulk_insert_buffer_size: 8388608
character_set_client: latin1
character_set_connection: latin1
character_set_database: latin1
character_set_filesystem: binary
character_set_results: latin1
character_set_server: latin1
character_set_system: utf8
character_sets_dir: /data/mysql/dspain/share/mysql/charsets/
collation_connection: latin1_swedish_ci
collation_database: latin1_swedish_ci
collation_server: latin1_swedish_ci
completion_type: 0
concurrent_insert: 1
connect_timeout: 5
datadir: /dh/mysql/dspain/data/
date_format: %Y-%m-%d
datetime_format: %Y-%m-%d %H:%i:%s
default_week_format: 0
delay_key_write: ON
delayed_insert_limit: 100
delayed_insert_timeout: 300
delayed_queue_size: 1000
div_precision_increment: 4
engine_condition_pushdown: OFF
expire_logs_days: 0
flush: OFF
flush_time: 0
ft_boolean_syntax: + -><()~*:""&|
ft_max_word_len: 84
ft_min_word_len: 3
ft_query_expansion_limit: 20
ft_stopword_file: (built-in)
group_concat_max_len: 1024
have_archive: YES
have_bdb: NO
have_blackhole_engine: NO
have_compress: YES
have_crypt: YES
have_csv: NO
have_dynamic_loading: NO
have_example_engine: NO
have_federated_engine: NO
have_geometry: YES
have_innodb: YES
have_isam: NO
have_ndbcluster: NO
have_merge_engine: YES
have_openssl: DISABLED
have_query_cache: YES
have_raid: NO
have_rtree_keys: YES
have_symlink: YES
init_connect:
init_file:
init_slave:
innodb_additional_mem_pool_size: 1048576
innodb_autoextend_increment: 8
innodb_buffer_pool_awe_mem_mb: 0
innodb_buffer_pool_size: 7340032
innodb_checksums: ON
innodb_commit_concurrency: 0
innodb_concurrency_tickets: 500
innodb_data_file_path: ibdata1:10M:autoextend
innodb_data_home_dir:
innodb_doublewrite: ON
innodb_fast_shutdown: 1
innodb_file_io_threads: 4
innodb_file_per_table: ON
innodb_flush_log_at_trx_commit: 1
innodb_flush_method:
innodb_force_recovery: 0
innodb_lock_wait_timeout: 50
innodb_locks_unsafe_for_binlog: OFF
innodb_log_arch_dir:
innodb_log_archive: OFF
innodb_log_buffer_size: 2097152
innodb_log_file_size: 5242880
innodb_log_files_in_group: 2
innodb_log_group_home_dir: ./
innodb_max_dirty_pages_pct: 90
innodb_max_purge_lag: 0
innodb_mirrored_log_groups: 1
innodb_open_files: 1000
innodb_support_xa: ON
innodb_sync_spin_loops: 20
innodb_table_locks: ON
innodb_thread_concurrency: 8
innodb_thread_sleep_delay: 10000
interactive_timeout: 28800
join_buffer_size: 1044480
key_buffer_size: 7340032
key_cache_age_threshold: 300
key_cache_block_size: 1024
key_cache_division_limit: 100
language: /data/mysql/dspain/share/mysql/english/
large_files_support: ON
large_page_size: 0
large_pages: OFF
license: GPL
local_infile: ON
locked_in_memory: OFF
log: ON
log_bin: OFF
log_bin_trust_function_creators: OFF
log_error: /dh/mysql/logs/mysql.dspain.err
log_queries_not_using_indexes: ON
log_slave_updates: OFF
log_slow_queries: ON
log_warnings: 2
long_query_time: 10
low_priority_updates: OFF
lower_case_file_system: OFF
lower_case_table_names: 0
max_allowed_packet: 16776192
max_binlog_cache_size: 4294967295
max_binlog_size: 1073741824
max_connect_errors: 10000
max_connections: 1200
max_delayed_threads: 20
max_error_count: 64
max_heap_table_size: 16777216
max_insert_delayed_threads: 20
max_join_size: 18446744073709551615
max_length_for_sort_data: 1024
max_prepared_stmt_count: 16382
max_relay_log_size: 0
max_seeks_for_key: 4294967295
max_sort_length: 1024
max_sp_recursion_depth: 0
max_tmp_tables: 32
max_user_connections: 300
max_write_lock_count: 4294967295
multi_range_count: 256
myisam_data_pointer_size: 6
myisam_max_sort_file_size: 2147483647
myisam_recover_options: BACKUP,FORCE
myisam_repair_threads: 1
myisam_sort_buffer_size: 8388608
myisam_stats_method: nulls_unequal
net_buffer_length: 16384
net_read_timeout: 30
net_retry_count: 10
net_write_timeout: 60
new: ON
old_passwords: ON
open_files_limit: 65535
optimizer_prune_level: 1
optimizer_search_depth: 62
pid_file: /tmp/mysql.dspain.pid
port: 3306
preload_buffer_size: 32768
prepared_stmt_count: 0
protocol_version: 10
query_alloc_block_size: 8192
query_cache_limit: 1048576
query_cache_min_res_unit: 4096
query_cache_size: 0
query_cache_type: ON
query_cache_wlock_invalidate: OFF
query_prealloc_size: 8192
range_alloc_block_size: 2048
read_buffer_size: 1044480
read_only: OFF
read_rnd_buffer_size: 262144
relay_log_purge: ON
relay_log_space_limit: 0
rpl_recovery_rank: 0
secure_auth: OFF
server_id: 1506
skip_external_locking: ON
skip_networking: OFF
skip_show_database: OFF
slave_compressed_protocol: OFF
slave_load_tmpdir: /tmp/
slave_net_timeout: 3600
slave_skip_errors: OFF
slave_transaction_retries: 10
slow_launch_time: 2
socket: /tmp/mysql.dspain.sock
sort_buffer_size: 1048568
sql_big_selects: ON
sql_mode:
sql_notes: ON
sql_warnings: OFF
ssl_ca:
ssl_capath:
ssl_cert:
ssl_cipher:
ssl_key:
storage_engine: MyISAM
sync_binlog: 0
sync_frm: ON
system_time_zone: PDT
table_cache: 350
table_lock_wait_timeout: 50
table_type: MyISAM
thread_cache_size: 20
thread_stack: 126976
time_format: %H:%i:%s
time_zone: SYSTEM
timed_mutexes: OFF
tmp_table_size: 33554432
tmpdir: /tmp/
transaction_alloc_block_size: 8192
transaction_prealloc_size: 4096
tx_isolation: REPEATABLE-READ
updatable_views_with_limit: YES
version: 5.0.24a-standard-log
version_comment: MySQL Community Edition - Standard (GPL)
version_compile_machine: i686
version_compile_os: pc-linux-gnu
wait_timeout: 14400

[karlbenson]

Have a look at (if you haven't already)
http://www.simplemachines.org/community/index.php?topic=49999.0