title: MySQL performance & variables tweaking
body: <p>I have a problem with a MySql server causing it to freeze all the time. In phpMyAdmin when this happens I can see in "Show Processlist" are lots of processes for User "unauthenticated user" with Command "connect" and State "reading from net".</p>
  
  <p>The database is using InnoDB but I also use one table running MyISAM to be able to perform full text search. The server has about 4GB memory, less than 500MB in use.</p>
  
  <p>I have been using the slow Query Log from MySql to find the queries not using indexes. I also think I need to do some changes / tweaking on the server variables. </p>
  
  <p>I really could use some help here and therefore I'm posting both SHOW GLOBAL STATUS and SHOW GLOBAL VARIABLES here and maybe you can give me some ideas how I should set the variables?</p>
  
  <p>Current SHOW GLOBAL STATUS:</p>
  
  <pre><code>Variable_name   Value
  Aborted_clients     730
  Aborted_connects    35
  Binlog_cache_disk_use   0
  Binlog_cache_use    0
  Binlog_stmt_cache_disk_use  0
  Binlog_stmt_cache_use   0
  Bytes_received  31558148370
  Bytes_sent  556410688609
  Com_admin_commands  4565
  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     3
  Com_alter_tablespace    0
  Com_analyze     0
  Com_begin   168
  Com_binlog  0
  Com_call_procedure  0
  Com_change_db   26791502
  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  584152
  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  5871056
  Com_insert_select   2833
  Com_install_plugin  0
  Com_kill    160
  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  21739455
  Com_set_option  53600251
  Com_signal  0
  Com_show_authors    0
  Com_show_binlog_events  0
  Com_show_binlogs    217
  Com_show_charsets   3
  Com_show_collations     26786645
  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   6412
  Com_show_create_trigger     84
  Com_show_databases  213
  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     4389
  Com_show_function_status    14
  Com_show_grants     2
  Com_show_keys   25
  Com_show_master_status  5
  Com_show_open_tables    0
  Com_show_plugins    560
  Com_show_privileges     0
  Com_show_procedure_status   14
  Com_show_processlist    189
  Com_show_profile    0
  Com_show_profiles   0
  Com_show_relaylog_events    0
  Com_show_slave_hosts    0
  Com_show_slave_status   5
  Com_show_status     156
  Com_show_storage_engines    16
  Com_show_table_status   4154
  Com_show_tables     280
  Com_show_triggers   4144
  Com_show_variables  26786703
  Com_show_warnings   2363
  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   168
  Com_update  2572452
  Com_update_multi    35171
  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     26787752
  Created_tmp_disk_tables     119645
  Created_tmp_files   7069
  Created_tmp_tables  58427880
  Delayed_errors  0
  Delayed_insert_threads  0
  Delayed_writes  0
  Flush_commands  1
  Handler_commit  30698922
  Handler_delete  177424
  Handler_discover    0
  Handler_prepare     0
  Handler_read_first  1239039
  Handler_read_key    2226723080
  Handler_read_last   9200
  Handler_read_next   4186985860
  Handler_read_prev   6811735740
  Handler_read_rnd    8942473
  Handler_read_rnd_next   16431972655
  Handler_rollback    495
  Handler_savepoint   0
  Handler_savepoint_rollback  0
  Handler_update  9720709
  Handler_write   15014651212
  Innodb_buffer_pool_pages_data   8173
  Innodb_buffer_pool_pages_dirty  138
  Innodb_buffer_pool_pages_flushed    34780758
  Innodb_buffer_pool_pages_free   0
  Innodb_buffer_pool_pages_misc   18
  Innodb_buffer_pool_pages_total  8191
  Innodb_buffer_pool_read_ahead_rnd   0
  Innodb_buffer_pool_read_ahead   26163447
  Innodb_buffer_pool_read_ahead_evicted   669245354
  Innodb_buffer_pool_read_requests    19793139859
  Innodb_buffer_pool_reads    1156881183
  Innodb_buffer_pool_wait_free    0
  Innodb_buffer_pool_write_requests   81288931
  Innodb_data_fsyncs  8482576
  Innodb_data_pending_fsyncs  0
  Innodb_data_pending_reads   0
  Innodb_data_pending_writes  0
  Innodb_data_read    19409628139520
  Innodb_data_reads   1184668281
  Innodb_data_writes  25747879
  Innodb_data_written     576917547008
  Innodb_dblwr_pages_written  17390379
  Innodb_dblwr_writes     255445
  Innodb_have_atomic_builtins     ON
  Innodb_log_waits    0
  Innodb_log_write_requests   6930648
  Innodb_log_writes   7850155
  Innodb_os_log_fsyncs    7976564
  Variable_name   Value
  Innodb_os_log_pending_fsyncs    0
  Innodb_os_log_pending_writes    0
  Innodb_os_log_written   7007020032
  Innodb_page_size    16384
  Innodb_pages_created    78647
  Innodb_pages_read   1184669628
  Innodb_pages_written    17390379
  Innodb_row_lock_current_waits   0
  Innodb_row_lock_time    1116
  Innodb_row_lock_time_avg    39
  Innodb_row_lock_time_max    963
  Innodb_row_lock_waits   28
  Innodb_rows_deleted     177424
  Innodb_rows_inserted    5863854
  Innodb_rows_read    14138431339
  Innodb_rows_updated     4886889
  Innodb_truncated_status_writes  0
  Key_blocks_not_flushed  0
  Key_blocks_unused   13
  Key_blocks_used     13
  Key_read_requests   3458723878
  Key_reads   49092342
  Key_write_requests  462162
  Key_writes  436874
  Last_query_cost     0.000000
  Max_used_connections    137
  Not_flushed_delayed_rows    0
  Open_files  0
  Open_streams    0
  Open_table_definitions  113
  Open_tables     4
  Opened_files    6485943
  Opened_table_definitions    116
  Opened_tables   31771706
  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     191630861
  Questions   191562751
  Rpl_status  AUTH_MASTER
  Select_full_join    7193
  Select_full_range_join  0
  Select_range    957755
  Select_range_check  0
  Select_scan     57577013
  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    445
  Sort_merge_passes   3531
  Sort_range  5655320
  Sort_rows   43857009
  Sort_scan   757681
  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   51310065
  Variable_name   Value
  Table_locks_waited  14101
  Tc_log_max_pages_used   0
  Tc_log_page_size    0
  Tc_log_page_waits   0
  Threads_cached  0
  Threads_connected   4
  Threads_created     26787751
  Threads_running     1
  Uptime  1208447
  Uptime_since_flush_status   1208447
  </code></pre>
  
  <p>Current SHOW GLOBAL VARIABLES:</p>
  
  <pre><code>Variable_name   Value
  auto_increment_increment    1
  auto_increment_offset   1
  autocommit  ON
  automatic_sp_privileges     ON
  back_log    50
  basedir     /usr
  big_tables  OFF
  binlog_cache_size   32768
  binlog_direct_non_transactional_updates     OFF
  binlog_format   STATEMENT
  binlog_stmt_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     NO_CHAIN
  concurrent_insert   AUTO
  connect_timeout     10
  datadir     /var/lib/mysql/
  date_format     %Y-%m-%d
  datetime_format     %Y-%m-%d %H:%i:%s
  default_storage_engine  InnoDB
  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   ON
  event_scheduler     OFF
  expire_logs_days    0
  flush   OFF
  flush_time  0
  foreign_key_checks  ON
  ft_boolean_syntax   + -&gt;&lt;()~*:""&amp;|
  ft_max_word_len     84
  ft_min_word_len     3
  ft_query_expansion_limit    20
  ft_stopword_file    (built-in)
  general_log     OFF
  general_log_file    /var/lib/mysql/db.log
  group_concat_max_len    1024
  have_compress   YES
  have_crypt  YES
  have_csv    YES
  have_dynamic_loading    YES
  have_geometry   YES
  have_innodb     YES
  have_ndbcluster     NO
  have_openssl    DISABLED
  have_partitioning   YES
  have_profiling  YES
  have_query_cache    YES
  have_rtree_keys     YES
  have_ssl    DISABLED
  have_symlink    YES
  hostname    silldb5
  ignore_builtin_innodb   OFF
  init_connect    
  init_file   
  init_slave  
  innodb_adaptive_flushing    ON
  innodb_adaptive_hash_index  ON
  innodb_additional_mem_pool_size     8388608
  innodb_autoextend_increment     8
  innodb_autoinc_lock_mode    1
  innodb_buffer_pool_instances    1
  innodb_buffer_pool_size     134217728
  innodb_change_buffering     all
  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_format  Antelope
  innodb_file_format_check    ON
  innodb_file_format_max  Antelope
  innodb_file_per_table   OFF
  innodb_flush_log_at_trx_commit  1
  innodb_flush_method     
  innodb_force_load_corrupted     OFF
  innodb_force_recovery   0
  innodb_io_capacity  200
  innodb_large_prefix     OFF
  innodb_lock_wait_timeout    50
  innodb_locks_unsafe_for_binlog  OFF
  innodb_log_buffer_size  8388608
  innodb_log_file_size    5242880
  innodb_log_files_in_group   2
  innodb_log_group_home_dir   ./
  Variable_name   Value
  innodb_max_dirty_pages_pct  75
  innodb_max_purge_lag    0
  innodb_mirrored_log_groups  1
  innodb_old_blocks_pct   37
  innodb_old_blocks_time  0
  innodb_open_files   300
  innodb_purge_batch_size     20
  innodb_purge_threads    0
  innodb_random_read_ahead    OFF
  innodb_read_ahead_threshold     56
  innodb_read_io_threads  4
  innodb_replication_delay    0
  innodb_rollback_on_timeout  OFF
  innodb_rollback_segments    128
  innodb_spin_wait_delay  6
  innodb_stats_method     nulls_equal
  innodb_stats_on_metadata    ON
  innodb_stats_sample_pages   8
  innodb_strict_mode  OFF
  innodb_support_xa   ON
  innodb_sync_spin_loops  30
  innodb_table_locks  ON
  innodb_thread_concurrency   0
  innodb_thread_sleep_delay   10000
  innodb_use_native_aio   ON
  innodb_use_sys_malloc   ON
  innodb_version  1.1.8
  innodb_write_io_threads     4
  interactive_timeout     28800
  join_buffer_size    131072
  keep_files_on_create    OFF
  key_buffer_size     16384
  key_cache_age_threshold     300
  key_cache_block_size    1024
  key_cache_division_limit    100
  large_files_support     ON
  large_page_size     0
  large_pages     OFF
  lc_messages     en_US
  lc_messages_dir     /usr/share/mysql/
  lc_time_names   en_US
  license     GPL
  local_infile    ON
  lock_wait_timeout   31536000
  locked_in_memory    OFF
  log     OFF
  log_bin     OFF
  log_bin_trust_function_creators     OFF
  log_error   /var/lib/mysql/db.err
  log_output  FILE
  log_queries_not_using_indexes   OFF
  log_slave_updates   OFF
  log_slow_queries    OFF
  log_warnings    1
  long_query_time     10.000000
  low_priority_updates    OFF
  lower_case_file_system  OFF
  lower_case_table_names  0
  max_allowed_packet  1048576
  max_binlog_cache_size   18446744073709547520
  max_binlog_size     1073741824
  max_binlog_stmt_cache_size  18446744073709547520
  max_connect_errors  10
  max_connections     2000
  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_long_data_size  1048576
  max_prepared_stmt_count     16382
  max_relay_log_size  0
  max_seeks_for_key   18446744073709551615
  max_sort_length     1024
  max_sp_recursion_depth  0
  max_tmp_tables  32
  max_user_connections    0
  max_write_lock_count    18446744073709551615
  metadata_locks_cache_size   1024
  min_examined_row_limit  0
  multi_range_count   256
  myisam_data_pointer_size    6
  myisam_max_sort_file_size   9223372036853727232
  myisam_mmap_size    18446744073709551615
  myisam_recover_options  OFF
  myisam_repair_threads   1
  myisam_sort_buffer_size     8388608
  myisam_stats_method     nulls_unequal
  myisam_use_mmap     OFF
  net_buffer_length   2048
  net_read_timeout    30
  net_retry_count     10
  net_write_timeout   60
  new     OFF
  old     OFF
  old_alter_table     OFF
  old_passwords   OFF
  open_files_limit    10000
  optimizer_prune_level   1
  Variable_name   Value
  optimizer_search_depth  62
  optimizer_switch    index_merge=on,index_merge_union=on,index_merge_so...
  performance_schema  OFF
  performance_schema_events_waits_history_long_size   10000
  performance_schema_events_waits_history_size    10
  performance_schema_max_cond_classes     80
  performance_schema_max_cond_instances   1000
  performance_schema_max_file_classes     50
  performance_schema_max_file_handles     32768
  performance_schema_max_file_instances   10000
  performance_schema_max_mutex_classes    200
  performance_schema_max_mutex_instances  1000000
  performance_schema_max_rwlock_classes   30
  performance_schema_max_rwlock_instances     1000000
  performance_schema_max_table_handles    100000
  performance_schema_max_table_instances  50000
  performance_schema_max_thread_classes   50
  performance_schema_max_thread_instances     1000
  pid_file    /var/run/mysqld/mysqld.pid
  plugin_dir  /usr/lib64/mysql/plugin
  port    3306
  preload_buffer_size     32768
  profiling   OFF
  profiling_history_size  15
  protocol_version    10
  query_alloc_block_size  8192
  query_cache_limit   1048576
  query_cache_min_res_unit    4096
  query_cache_size    8 388 608
  query_cache_type    ON
  query_cache_wlock_invalidate    OFF
  query_prealloc_size     8192
  range_alloc_block_size  4096
  read_buffer_size    262144
  read_only   OFF
  read_rnd_buffer_size    262144
  relay_log   
  relay_log_index     
  relay_log_info_file     relay-log.info
  relay_log_purge     ON
  relay_log_recovery  OFF
  relay_log_space_limit   0
  report_host     
  report_password     
  report_port     3306
  report_user     
  rpl_recovery_rank   0
  secure_auth     OFF
  secure_file_priv    
  server_id   1
  skip_external_locking   ON
  skip_name_resolve   ON
  skip_networking     OFF
  skip_show_database  OFF
  slave_compressed_protocol   OFF
  slave_exec_mode     STRICT
  slave_load_tmpdir   /tmp
  slave_net_timeout   3600
  slave_skip_errors   OFF
  slave_transaction_retries   10
  slave_type_conversions  
  slow_launch_time    2
  slow_query_log  OFF
  slow_query_log_file     /var/lib/mysql/slow.log
  socket  /var/lib/mysql/mysql.sock
  sort_buffer_size    65536
  sql_auto_is_null    OFF
  sql_big_selects     ON
  sql_big_tables  OFF
  sql_buffer_result   OFF
  sql_log_bin     ON
  sql_log_off     OFF
  sql_low_priority_updates    OFF
  sql_max_join_size   18446744073709551615
  sql_mode    
  sql_notes   ON
  sql_quote_show_create   ON
  sql_safe_updates    OFF
  sql_select_limit    18446744073709551615
  sql_slave_skip_counter  0
  sql_warnings    OFF
  ssl_ca  
  ssl_capath  
  ssl_cert    
  ssl_cipher  
  ssl_key     
  storage_engine  InnoDB
  sync_binlog     0
  sync_frm    ON
  sync_master_info    0
  sync_relay_log  0
  sync_relay_log_info     0
  system_time_zone    CET
  table_definition_cache  400
  table_open_cache    4
  thread_cache_size   100
  thread_concurrency  8
  thread_handling     one-thread-per-connection
  thread_stack    524288
  time_format     %H:%i:%s
  Variable_name   Value
  time_zone   SYSTEM
  timed_mutexes   OFF
  tmp_table_size  16777216
  tmpdir  /tmp
  transaction_alloc_block_size    8192
  transaction_prealloc_size   4096
  tx_isolation    REPEATABLE-READ
  unique_checks   ON
  updatable_views_with_limit  YES
  version     5.5.20
  version_comment     MySQL Community Server (GPL) by Remi
  version_compile_machine     x86_64
  version_compile_os  Linux
  wait_timeout    28800
  </code></pre>