MySQL query randomly accepts "forever"

Question

We have an XMPP application that uses MySQL to store information. While we do not experience any specific load problem, but I try to be prepared for the worst (or better, from the point of view of users;)).

The host on which this MySQL server is installed is a Slicehost slice with 2 GB of RAM.

Yesterday, I activated the slow query logging to make sure that we actually had nothing. Unfortunately, it looks like a lot of slow queries were actually found:

  Reading mysql slow query log from /var/log/mysql/mysql-slow.log
 Count: 109 Time = 25.57s (2787s) Lock = 0.00s (0s) Rows = 1.0 (109), xxxxx [xxxxx] @ [172.21.xxx.xxx]
   SELECT * FROM `feeds` WHERE (` id` = 'S') LIMIT N

This is really strange for me, since id is actually the primary key ... Table - InnoDB

I did EXPLAIN:

 mysql> EXPLAIN SELECT * FROM `feeds` WHERE (` id` = '2650') LIMIT 1;  + ---- + ------------- + ------- + ------- + -------------- - + --------- + --------- + ------- + ------ + ------- + |  id |  select_type |  table |  type |  possible_keys |  key |  key_len |  ref |  rows |  Extra |  + ---- + ------------- + ------- + ------- + -------------- - + --------- + --------- + ------- + ------ + ------- + |  1 |  SIMPLE |  feeds |  const |  PRIMARY |  PRIMARY |  4 |  const |  1 |  |  + ---- + ------------- + ------- + ------- + -------------- - + --------- + --------- + ------- + ------ + ------- + 1 row in set ( 0.00 sec) 

There must be another reason why this is happening. And in our journal there are many similar slow queries (queries that use primary keys).

I think it would be wise to post MySQL settings here:

 mysql> SHOW VARIABLES;  + --------------------------------- + --------------- -------------- + |  Variable_name |  Value |  + --------------------------------- + --------------- -------------- + |  auto_increment_increment |  1 |  |  auto_increment_offset |  1 |  |  automatic_sp_privileges |  ON |  |  back_log |  50 |  |  basedir |  / usr / |  |  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 |  10 |  |  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 |  |  keep_files_on_create |  OFF |  |  engine_condition_pushdown |  OFF |  |  expire_logs_days |  10 |  |  flush |  OFF |  |  flush_time |  0 |  |  ft_boolean_syntax |  + -> <() ~ *: "" & |  |  |  ft_max_word_len |  84 |  |  ft_min_word_len |  4 |  |  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 |  NO |  |  have_federated_engine |  DISABLED |  |  have_geometry |  YES |  |  have_innodb |  YES |  |  have_isam |  NO |  |  have_merge_engine |  YES |  |  have_ndbcluster |  DISABLED |  |  have_openssl |  DISABLED |  |  have_ssl |  DISABLED |  |  have_query_cache |  YES |  |  have_raid |  NO |  |  have_rtree_keys |  YES |  |  have_symlink |  YES |  |  hostname |  SuperfeedrDatabase |  |  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 |  1073741824 |  |  innodb_checksums |  ON |  |  innodb_commit_concurrency |  0 |  |  innodb_concurrency_tickets |  500 |  |  innodb_data_file_path |  ibdata1: 10M: autoextend |  |  innodb_data_home_dir |  |  |  innodb_adaptive_hash_index |  ON |  |  innodb_doublewrite |  ON |  |  innodb_fast_shutdown |  1 |  |  innodb_file_io_threads |  4 |  |  innodb_file_per_table |  ON |  |  innodb_flush_log_at_trx_commit |  2 |  |  innodb_flush_method |  O_DIRECT |  |  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 |  4194304 |  |  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 |  131072 |  |  key_buffer_size |  16777216 |  |  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 |  ON |  |  log_slave_updates |  OFF |  |  log_slow_queries |  ON |  |  log_warnings |  1 |  |  long_query_time |  3 |  |  low_priority_updates |  OFF |  |  lower_case_file_system |  OFF |  |  lower_case_table_names |  0 |  |  max_allowed_packet |  16777216 |  |  max_binlog_cache_size |  18446744073709547520 |  |  max_binlog_size |  104857600 |  |  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_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 |  |  multi_range_count |  256 |  |  myisam_data_pointer_size |  6 |  |  myisam_max_sort_file_size |  9223372036853727232 |  |  myisam_recover_options |  BACKUP |  |  myisam_repair_threads |  1 |  |  myisam_sort_buffer_size |  8388608 |  |  myisam_stats_method |  nulls_unequal |  |  ndb_autoincrement_prefetch_sz |  1 |  |  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/run/mysqld/mysqld.pid |  |  plugin_dir |  |  |  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 |  16777216 |  |  query_cache_type |  ON |  |  query_cache_wlock_invalidate |  OFF |  |  query_prealloc_size |  8192 |  |  range_alloc_block_size |  4096 |  |  read_buffer_size |  131072 |  |  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_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 |  / tmp / |  |  slave_net_timeout |  3600 |  |  slave_skip_errors |  OFF |  |  slave_transaction_retries |  10 |  |  slow_launch_time |  2 |  |  socket |  /var/run/mysqld/mysqld.sock |  |  sort_buffer_size |  2097144 |  |  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 |  UTC |  |  table_cache |  64 |  |  table_lock_wait_timeout |  50 |  |  table_type |  MyISAM |  |  thread_cache_size |  8 |  |  thread_stack |  131072 |  |  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 |  READ-COMMITTED |  |  updatable_views_with_limit |  YES |  |  version |  5.0.67-0ubuntu6-log |  |  version_comment |  (Ubuntu) |  |  version_compile_machine |  x86_64 |  |  version_compile_os |  debian-linux-gnu |  |  wait_timeout |  28800 |  + --------------------------------- + --------------- -------------- + 237 rows in set (0.00 sec) 

Most of our requests are "basic", but we need great speed!

Any thoughts on what could make MySQL so slow?

[ SUMMARY ] Summing up the various answers:

• Remove "LIMIT", change WHERE id = "X" to WHERE id = X
• Make sure I don't have a script (backup or other) that runs from time to time that will consume a lot of resources
• Make sure that the "host" is not really the culprit.

Answer 1

If you look at the average value and variance of decelerations, you have a problem with the VM machine (which, unfortunately, is not under your control).

For those of you who point to memory / disk I / O, these numbers are too big for that. The disk should return after 100 ms, and not a few seconds.

Answer 2

If id is the primary key, why are you adding a LIMIT clause?

Have you tried to specify the column names you want instead of using *?

Also your int column id? By specifying "1" instead of 1, you cannot use the index.

Try

SELECT * FROM Feeds WHERE id = 1 

but not

 SELECT * FROM Feeds WHERE id = '1' 

Edit Comment

It is better to specify column names explicitly, in my opinion, because you may need to add columns to this table in the future that your application does not need. At this point, you begin to extract more data than required.

Answer 3

Your query is very simple, and given that the identifier is the primary key, it should not go so long even on a huge table under normal circumstances. Just guess, but maybe the server problem? As I understand it (with 30 seconds of viewing your home page), Slicehost offers you a virtual machine to "cut" a more powerful server. Could it be that other fragments on the same server from time to time do heavy disk reads, temporarily stealing all your resources? Or, perhaps this happens when administrators create / delete fragments from the machine for other users.

Does this happen very often?

Answer 4

Unfortunately, I have gained a lot of experience in this matter over the past year.

I agree with others that this could be a CPU / disk latency issue (due to shared hosting). Is there a way to get disk latency numbers from the host? Perhaps there are spikes.

I also agree that the request is a bit strange, specifying a limit clause and quoting an index. The SELECT * bit, which I can fully understand.

I would suggest that InnoDB does not have enough memory, but with so many lines and the output of a concert, InnoDB 1 is not.

I assume the request is incorrect. I have seen MySQL do this before. Some queries take too long or cause others to add up. But the queries that you see for too long are simple things that should not last long.

I have some tips for you:

• Is there some kind of automatic backup start that can lock the table?
• Does this occur at any regular or predictable interval?
• Have you ever logged in and seen a complete list of processes when this happens?
• Is it compatible with anything specific (when people run a specific report, etc.)?
• Do you have very large tables that could bind all your memory when they work on queries, while not allowing this table (unlikely)?
• It's always like this? Did it start recently? Is the version of MySQL changed? Can you try another MySQL build (newer release point, Percona Performance build, etc.)?

Several times, looking at a complete list of processes while this is happening may be most useful.

When we came across similar things last year, he looked at a list of processes that finally got into a real problem.

Answer 5

I have seen this problem before.

Your index is in the integer field, and the key of your where clause is the string . Your index fails because you are invoking a type conversion. Do not quote your key in the where clause.

I was very surprised that mysql behaves this way, it is rather disappointing that it cannot detect when this happens.

Answer 6

If the table is larger than the one that can be stored in memory caches, maybe some of these queries should have touched the disk at some unfortunate moments when something else put a heavy load on them?

Tuning MySQL is sometimes a bit of a black art. A key buffer cache conflict can also lead to noticeable slowdowns.

You can also try finding a mysql performance blog for tips and plausible theories.

Answer 7

This may be caused by delayed name resolution, depending on your configuration. You can check the DNS lookup speed from the server:

 nslookup www.domain.com 

If you get a slow response, try setting the following in the /etc/my.cnf file:

 skip-name-resolve # and/or: skip-networking 

It is also recommended that you connect the IP address and port number to resolve doubts about the connection path:

 bind-address=127.0.0.1 port=3306 

Otherwise, I would look at table locking and troubleshooting.

Answer 8

If you use MyISAM, you may run into concurrency issues.