Hi,
We are running a fairly busy social networking website which is suffering from intermittent performance problems. This usually manifests itself as MySql entering a deadlock for no apparent reason and/or apache failing to load libraries and crashing. We are considering upgrading to a larger server but in the meantime I wanted to check that our MySql config is optimal. I have already run tuning-primer but I’m concerned that we are not using the query cache effectively and that our Questions figure is very high.
Thanks for any help you can give!
Our server is a VPS with 2GB guaranteed RAM (4GB burst) running Redhat 9 (details at [URL]ServInt - Leaseweb)
SHOW GLOBAL STATUS
Aborted_clients|9114
Aborted_connects|8
Binlog_cache_disk_use|0
Binlog_cache_use|0
Bytes_received|1124171962
Bytes_sent|3544123476
Com_admin_commands|2
Com_alter_db|0
Com_alter_table|2
Com_analyze|0
Com_backup_table|0
Com_begin|0
Com_call_procedure|0
Com_change_db|332313
Com_change_master|0
Com_check|0
Com_checksum|0
Com_commit|0
Com_create_db|0
Com_create_function|0
Com_create_index|0
Com_create_table|44570
Com_create_user|0
Com_dealloc_sql|0
Com_delete|83912
Com_delete_multi|1
Com_do|0
Com_drop_db|0
Com_drop_function|0
Com_drop_index|0
Com_drop_table|0
Com_drop_user|0
Com_execute_sql|0
Com_flush|6
Com_grant|12
Com_ha_close|0
Com_ha_open|0
Com_ha_read|0
Com_help|0
Com_insert|718842
Com_insert_select|322
Com_kill|180
Com_load|0
Com_load_master_data|0
Com_load_master_table|0
Com_lock_tables|730611
Com_optimize|260
Com_preload_keys|0
Com_prepare_sql|0
Com_purge|0
Com_purge_before_date|0
Com_rename_table|0
Com_repair|0
Com_replace|2
Com_replace_select|0
Com_reset|0
Com_restore_table|0
Com_revoke|0
Com_revoke_all|0
Com_rollback|7
Com_savepoint|0
Com_select|2031103
Com_set_option|681931
Com_show_binlog_events|0
Com_show_binlogs|2
Com_show_charsets|56
Com_show_collations|56
Com_show_column_types|0
Com_show_create_db|0
Com_show_create_table|280
Com_show_databases|64
Com_show_errors|0
Com_show_fields|319
Com_show_grants|26
Com_show_innodb_status|2
Com_show_keys|37
Com_show_logs|0
Com_show_master_status|0
Com_show_ndb_status|0
Com_show_new_master|0
Com_show_open_tables|0
Com_show_privileges|0
Com_show_processlist|1508
Com_show_slave_hosts|0
Com_show_slave_status|0
Com_show_status|111
Com_show_storage_engines|0
Com_show_tables|83
Com_show_triggers|1
Com_show_variables|323
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_reset|0
Com_stmt_send_long_data|0
Com_truncate|0
Com_unlock_tables|367672
Com_update|375570
Com_update_multi|3240
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|703306
Created_tmp_disk_tables|45430
Created_tmp_files|17
Created_tmp_tables|188381
Delayed_errors|0
Delayed_insert_threads|0
Delayed_writes|0
Flush_commands|1
Handler_commit|0
Handler_delete|364924
Handler_discover|0
Handler_prepare|0
Handler_read_first|731862
Handler_read_key|202353922
Handler_read_next|1352980025
Handler_read_prev|19295695
Handler_read_rnd|37431567
Handler_read_rnd_next|2495242772
Handler_rollback|0
Handler_savepoint|0
Handler_savepoint_rollback|0
Handler_update|47142466
Handler_write|40706380
Innodb_buffer_pool_pages_data|0
Innodb_buffer_pool_pages_dirty|0
Innodb_buffer_pool_pages_flushed|0
Innodb_buffer_pool_pages_free|0
Innodb_buffer_pool_pages_latched|0
Innodb_buffer_pool_pages_misc|0
Innodb_buffer_pool_pages_total|0
Innodb_buffer_pool_read_ahead_rnd|0
Innodb_buffer_pool_read_ahead_seq|0
Innodb_buffer_pool_read_requests|0
Innodb_buffer_pool_reads|0
Innodb_buffer_pool_wait_free|0
Innodb_buffer_pool_write_requests|0
Innodb_data_fsyncs|0
Innodb_data_pending_fsyncs|0
Innodb_data_pending_reads|0
Innodb_data_pending_writes|0
Innodb_data_read|0
Innodb_data_reads|0
Innodb_data_writes|0
Innodb_data_written|0
Innodb_dblwr_pages_written|0
Innodb_dblwr_writes|0
Innodb_log_waits|0
Innodb_log_write_requests|0
Innodb_log_writes|0
Innodb_os_log_fsyncs|0
Innodb_os_log_pending_fsyncs|0
Innodb_os_log_pending_writes|0
Innodb_os_log_written|0
Innodb_page_size|0
Innodb_pages_created|0
Innodb_pages_read|0
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|71314
Key_blocks_used|44674
Key_read_requests|545151772
Key_reads|283540
Key_write_requests|23830203
Key_writes|1950286
Last_query_cost|0.000000
Max_used_connections|178
Ndb_cluster_node_id|0
Ndb_config_from_host|
Ndb_config_from_port|0
Ndb_number_of_data_nodes|0
Not_flushed_delayed_rows|0
Open_files|1243
Open_streams|0
Open_tables|881
Opened_tables|1189
Prepared_stmt_count|0
Qcache_free_blocks|73
Qcache_free_memory|25082376
Qcache_hits|3767678
Qcache_inserts|1475995
Qcache_lowmem_prunes|254523
Qcache_not_cached|556189
Qcache_queries_in_cache|26260
Qcache_total_blocks|59156
Questions|9839944
Rpl_status|NULL
Select_full_join|204
Select_full_range_join|0
Select_range|112668
Select_range_check|0
Select_scan|120492
Slave_open_temp_tables|0
Slave_retried_transactions|0
Slave_running|OFF
Slow_launch_threads|3
Slow_queries|1602
Sort_merge_passes|8
Sort_range|319481
Sort_rows|310033602
Sort_scan|144045
Table_locks_immediate|4185928
Table_locks_waited|40180
Tc_log_max_pages_used|0
Tc_log_page_size|0
Tc_log_page_waits|0
Threads_cached|99
Threads_connected|1
Threads_created|178
Threads_running|1
Uptime|439639
Uptime_since_flush_status|439639
SHOW GLOBAL VARIABLES
auto_increment_increment|1
auto_increment_offset|1
automatic_sp_privileges|ON
back_log|50
basedir|/
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|/usr/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|/var/lib/mysql/
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|YES
have_compress|YES
have_crypt|YES
have_csv|YES
have_dynamic_loading|YES
have_example_engine|YES
have_federated_engine|YES
have_geometry|YES
have_innodb|DISABLED
have_isam|NO
have_merge_engine|YES
have_ndbcluster|DISABLED
have_openssl|NO
have_ssl|NO
have_query_cache|YES
have_raid|NO
have_rtree_keys|YES
have_symlink|YES
hostname|XXX.XXX.XXX
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|8388608
innodb_checksums|ON
innodb_commit_concurrency|0
innodb_concurrency_tickets|500
innodb_data_file_path|
innodb_data_home_dir|
innodb_doublewrite|ON
innodb_fast_shutdown|1
innodb_file_io_threads|4
innodb_file_per_table|OFF
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|1048576
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|300
innodb_rollback_on_timeout|OFF
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|134217728
key_cache_age_threshold|300
key_cache_block_size|1024
key_cache_division_limit|100
language|/usr/share/mysql/english/
large_files_support|ON
large_page_size|0
large_pages|OFF
lc_time_names|en_US
license|GPL
local_infile|ON
locked_in_memory|OFF
log|OFF
log_bin|OFF
log_bin_trust_function_creators|OFF
log_error|
log_queries_not_using_indexes|OFF
log_slave_updates|OFF
log_slow_queries|ON
log_warnings|1
long_query_time|4
low_priority_updates|OFF
lower_case_file_system|OFF
lower_case_table_names|0
max_allowed_packet|1048576
max_binlog_cache_size|4294967295
max_binlog_size|1073741824
max_connect_errors|10
max_connections|500
max_delayed_threads|5
max_error_count|64
max_heap_table_size|33554432
max_insert_delayed_threads|5
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|4
max_user_connections|0
max_write_lock_count|4294967295
multi_range_count|256
myisam_data_pointer_size|6
myisam_max_sort_file_size|2147483647
myisam_recover_options|OFF
myisam_repair_threads|1
myisam_sort_buffer_size|8388608
myisam_stats_method|nulls_unequal
ndb_autoincrement_prefetch_sz|32
ndb_force_send|ON
ndb_use_exact_count|ON
ndb_use_transactions|ON
ndb_cache_check_time|0
ndb_connectstring|
net_buffer_length|16384
net_read_timeout|30
net_retry_count|10
net_write_timeout|60
new|OFF
old_passwords|OFF
open_files_limit|10000
optimizer_prune_level|1
optimizer_search_depth|62
pid_file|/var/lib/mysql/XXX.XXX.XXX.pid
port|3306
preload_buffer_size|32768
profiling|OFF
profiling_history_size|15
protocol_version|10
query_alloc_block_size|8192
query_cache_limit|3145728
query_cache_min_res_unit|4096
query_cache_size|134217728
query_cache_type|ON
query_cache_wlock_invalidate|OFF
query_prealloc_size|8192
range_alloc_block_size|2048
read_buffer_size|3141632
read_only|OFF
read_rnd_buffer_size|3141632
relay_log_purge|ON
relay_log_space_limit|0
rpl_recovery_rank|0
secure_auth|OFF
secure_file_priv|
server_id|0
skip_external_locking|ON
skip_networking|OFF
skip_show_database|OFF
slave_compressed_protocol|OFF
slave_load_tmpdir|/home/mysql/
slave_net_timeout|3600
slave_skip_errors|OFF
slave_transaction_retries|10
slow_launch_time|2
socket|/var/lib/mysql/mysql.sock
sort_buffer_size|3145720
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|MST
table_cache|2048
table_lock_wait_timeout|50
table_type|MyISAM
thread_cache_size|100
thread_stack|196608
time_format|%H:%i:%s
time_zone|SYSTEM
timed_mutexes|OFF
tmp_table_size|134217728
tmpdir|/home/mysql
transaction_alloc_block_size|8192
transaction_prealloc_size|4096
tx_isolation|REPEATABLE-READ
updatable_views_with_limit|YES
version|5.0.45-community-log
version_comment|MySQL Community Edition (GPL)
version_compile_machine|i686
version_compile_os|pc-linux-gnu
wait_timeout|15