Запрос MySQL случайным образом занимает вечность

У нас есть приложение XMPP, которое использует MySQL для хранения информации. У нас пока нет особых проблем с загрузкой, но я стараюсь быть готовым к худшему (или лучшему, с точки зрения пользователей;)).

Хост, на котором установлен этот сервер MySQL, представляет собой слайс Slicehost с 2 ГБ ОЗУ.

Вчера я активировал ведение журнала медленных запросов, чтобы убедиться, что на самом деле у нас нет ничего медленного. К сожалению, похоже, что на самом деле было обнаружено много медленных запросов:

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

Для меня это действительно странно, поскольку id на самом деле является первичным ключом ... Таблица - это InnoDB

Я сделал ОБЪЯСНЕНИЕ:

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)

Должна быть другая причина, почему это происходит. И на самом деле в нашем журнале много похожих медленных запросов (запросов, использующих первичные ключи).

Думаю, имеет смысл разместить здесь настройки MySQL:

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)

Большинство наших запросов являются «базовыми», однако нам требуется огромная скорость!

Есть мысли о том, что на самом деле могло сделать MySQL настолько медленным?

[РЕЗЮМЕ] Подводя итог различным ответам:

  • Удалите "LIMIT", замените WHERE id = "X" на WHERE id = X
  • Убедитесь, что у меня нет сценария (резервного копирования или другого), который время от времени запускается и потребляет много ресурсов.
  • Убедитесь, что "хозяин" на самом деле не виноват.

person Julien Genestoux    schedule 10.06.2009    source источник
comment
Сколько записей в таблице? У меня такое случалось с таблицами, которые не были исключительно большими, и я исправил это, добавив индекс ..   -  person Tim    schedule 10.06.2009
comment
Действительно, таблица теперь небольшая: 20 тыс. Строк.   -  person Julien Genestoux    schedule 10.06.2009
comment
не индексируется ли первичный ключ автоматически в силу того, что он является первичным ключом?   -  person Kip    schedule 10.06.2009
comment
не уверен, что понимаю ... (эй, вы нарушили макет;)). Я бы предположил, что первичные ключи являются индексами.   -  person Julien Genestoux    schedule 10.06.2009
comment
извините, исправил макет сейчас, не знаю, почему он это сделал ... я пытался изменить блок кода (с отступом 4 пробела) на предварительный блок, чтобы он не пытался выделить его, как если бы это был код. мой вопрос был к Тиму, который сказал, что исправил аналогичные проблемы, добавив индекс, который здесь не кажется применимым, потому что столбец первичного ключа должен быть проиндексирован (как первичный ключ)   -  person Kip    schedule 10.06.2009
comment
@Kip: Угловые скобки запрещены внутри блоков ‹pre›, они должны быть экранированы. В любом случае - этот вопрос сейчас на доработке №6. Возможно еще одно изменение, прежде чем оно будет преобразовано в вики сообщества, поэтому последнее изменение должно быть хорошим.   -  person Tomalak    schedule 10.06.2009
comment
Ладно, я все равно закончил, этот вопрос выходит за рамки моей компетенции. :) Не уверен, что я понимаю, почему они вносят википедию вопроса после определенного количества правок, похоже, это просто побуждает других людей не полировать вопросы. (или позволяет злоумышленникам навязывать вопрос в вики сообщества, если они не хотят, чтобы автор получил репутацию ...)   -  person Kip    schedule 10.06.2009
comment
@ Кип: Я думаю, это должно побудить людей быть ясными с самого начала. В любом случае всегда будет опасность сопутствующего ущерба для вопросов со сложной проблемой форматирования, как здесь.   -  person Tomalak    schedule 10.06.2009


Ответы (8)


Глядя на среднее значение и дисперсию замедлений, у вас есть проблема с хост-машиной виртуальной машины (которая, к сожалению, не находится под вашим контролем).

Для тех из вас, кто указывает на ввод-вывод памяти / диска, эти числа слишком велики для этого. Диск должен вернуться через 100 мс, а не через несколько секунд.

person Joshua    schedule 10.06.2009
comment
Я склонен думать, что Slicehost Slice будет давать периодические 25-секундные задержки, а не обвинять MySQL. - person Karl; 10.06.2009

Ваш запрос очень прост, и, учитывая, что id является первичным ключом, при нормальных обстоятельствах это не должно занимать столько времени даже в огромной таблице. Только предположение, но, может быть, проблема в сервере? Насколько я понимаю (после 30 секунд просмотра их домашней страницы), Slicehost предлагает вам виртуальную машину «срез» более мощного сервера. Может ли быть так, что другие части на том же сервере время от времени выполняют тяжелые операции чтения с диска, временно крадя все ваши ресурсы? Или, может быть, это происходит, когда администраторы создают / удаляют слайсы с машины для других пользователей.

Это случается очень часто?

person Kip    schedule 10.06.2009
comment
Технически ресурсы гарантированы с точки зрения ЦП, хотя я не знаю о доступе к диску, но я ожидал бы такой же гарантии. Это произошло 109 раз примерно за 12 часов. Так что это много раз ... но небольшой процент, поскольку такие запросы выполняются примерно 1 миллион раз за 12 часов. - person Julien Genestoux; 10.06.2009

Если id является первичным ключом, зачем вы добавляете предложение LIMIT?

Вы пытались указать нужные имена столбцов вместо использования *?

Кроме того, ваш столбец Id int? Указав «1» вместо 1, вы можете не использовать индекс.

Пытаться

SELECT * FROM Feeds WHERE id = 1

скорее, чем

SELECT * FROM Feeds WHERE id = '1'

Редактировать для комментария

На мой взгляд, лучше явно указывать имена столбцов, потому что в будущем вам может потребоваться добавить столбцы в эту таблицу, которые не нужны вашему приложению. В этот момент вы начинаете получать больше данных, чем необходимо.

person scottm    schedule 10.06.2009
comment
Хм ... ты прав, здесь LIMIT бесполезен ... но нам нужны все поля. Как вы думаете, их лучше указать, чем *? - person Julien Genestoux; 10.06.2009
comment
LIMIT в этом случае бесполезен, но это не должно привести к тому, что на получение 1 строки из 10 столбцов данных потребуется 25 секунд, верно? - person Kip; 10.06.2009
comment
@Julien, я думаю, вы на самом деле не используете индекс - person scottm; 10.06.2009
comment
Хорошо, я укажу столбцы, которые нам понадобятся для будущей производительности. - person Julien Genestoux; 10.06.2009
comment
Я пробовал с цитатами и без них ... проблема в том, что, учитывая тот факт, что я не могу легко воспроизвести проблему, у меня нет никакой разницы между ними ... может ли это быть проблемой? - person Julien Genestoux; 10.06.2009
comment
Разве EXPLAIN не сообщает нам, что использует первичный ключ? - person VVS; 10.06.2009

Я видел эту проблему раньше.

Ваш индекс находится в поле целого числа, а ключ в предложении where представляет собой строку. Ваш индекс терпит поражение из-за того, что вы вызываете преобразование типа. Удалите кавычки из вашего ключа в предложении where.

Я был очень удивлен, что mysql ведет себя таким образом, и очень разочаровывает то, что он не может определить, когда это происходит.

person Don Neufeld    schedule 10.06.2009
comment
Но тогда почему это происходит только для небольшого набора запросов? - person Julien Genestoux; 10.06.2009

К сожалению, за последний год я получил большой опыт в подобных делах.

Я согласен с другими, это может быть проблема с задержкой процессора / диска (из-за виртуального хостинга). Есть ли способ получить данные о задержке диска с хоста? Может есть шипы.

Я также согласен с тем, что запрос немного странный с указанием предложения limit и цитированием индекса. Бит SELECT * я полностью понимаю.

Я бы предположил, что InnoDB не хватает памяти, но с таким небольшим количеством строк и предоставлением InnoDB 1 гигабайт, это не так.

Я предполагаю, что запрос неверен. Я раньше видел, как MySQL делает такие вещи. Один запрос занимает слишком много времени или приводит к тому, что другие начинают накапливаться. Но запросы, которые, как вы видите, занимают слишком много времени, - это простые мелкие вещи, которые никогда не должны занимать много времени.

У меня есть для вас несколько предложений:

  • Есть ли какое-то автоматическое резервное копирование, которое могло бы заблокировать таблицу?
  • Происходит ли это на каком-либо регулярном или предсказуемом интервале?
  • Вы когда-нибудь входили в систему и видели полный список процессов, когда это происходит?
  • Совпадает ли это с чем-то конкретным (каждый раз, когда люди запускают определенный отчет и т. Д.)?
  • Есть ли у вас какие-нибудь очень большие таблицы, которые могут занимать всю вашу память, пока работают с запросами, предотвращая попадание этой таблицы (маловероятно)?
  • Так было всегда? Это началось недавно? Изменилась ли версия MySQL? Можете ли вы попробовать другую сборку MySQL (более новую версию, сборку Percona Performance и т. Д.)?

Иногда просмотр полного списка процессов во время этого процесса может оказаться наиболее полезным.

Когда мы столкнулись с подобными проблемами в прошлом году, именно просмотр списка процессов наконец-то выявил реальную проблему.

person MBCook    schedule 10.06.2009
comment
PS: Надеюсь, вы наконец закончите свой ответ? :) - person VVS; 10.06.2009
comment
Спасибо за множество вопросов. Я не знаю, какова задержка диска. Как я могу их получить? - Есть бэкап, но на уровне дисков: снэпшоты. - Интервал: не знаю. Есть хороший способ узнать? - Я никогда не мог «увидеть» шип. - у нас нет очень большой таблицы (не более 500 тыс. строк) - На самом деле мы только что приступили к работе ... так что технически это только началось, но это, как всегда, получилось! Спасибо, в любом случае! - person Julien Genestoux; 10.06.2009
comment
Я спрошу вашего хозяина, они смогут вам сказать. Если вы объясните проблему и расскажете, что происходит, они должны знать, что искать. Тем не менее, я ожидаю, что это другие вопросы. У вас должна быть УЖАСНАЯ задержка диска, чтобы это стало проблемой. - person MBCook; 10.06.2009
comment
Хм ... ладно, тогда мне нужно найти какие запросы! Есть подсказка? - person Julien Genestoux; 10.06.2009

Если таблица больше, чем то, что может храниться в кэшах памяти, то, возможно, некоторым из этих запросов потребовалось прикоснуться к диску в какие-то неудачные моменты, когда что-то еще оказывало на них большую нагрузку?

Настройка MySQL иногда является чем-то вроде черного искусства. Высокая конкуренция за кеш-буфер между ключами также может привести к заметному замедлению работы.

Вы также можете попробовать поискать подсказки и правдоподобные теории в блоге о производительности MySQL.

person Chris Vest    schedule 10.06.2009
comment
Высокая конкуренция кеш-буфера ключей также может привести к заметному замедлению работы. ... В самом деле? Я думал, что цель кеша на самом деле заключалась в том, чтобы предотвратить замедление? Я отправлю переменные MySQL, чтобы вы могли сказать мне, что вы думаете. - person Julien Genestoux; 10.06.2009
comment
@Julien, конфликт кеша происходит, когда выполняется один запрос и некоторые данные кэшируются, удаляя все предыдущие данные, этот будет быстрым, а другие - медленным. Если вы запускаете один и тот же запрос несколько раз, среднее значение будет хорошим, но если вы чередуете два типа запросов, вы потеряете преимущество, которое может дать кеш, и принудительно выполните каждое чтение на диск. У них есть конкуренция за кеш, потому что они борются за кеш. - person Karl; 10.06.2009
comment
Могу ли я на самом деле увеличить кеш, чтобы предотвратить это? - person Julien Genestoux; 10.06.2009
comment
В кэше ключевого буфера есть мьютексы. Я слышал о случаях, когда отключение кеша буфера ключей (и, следовательно, его мьютекса) позволяло больший параллелизм, тем самым улучшая производительность. Однако измерьте дважды, отрежьте один раз, иначе производительность ухудшится. - person Chris Vest; 11.06.2009

Это может быть вызвано задержкой разрешения имени, в зависимости от вашей настройки. Вы можете проверить скорость поиска DNS с сервера с помощью:

nslookup www.domain.com

Если вы получаете медленный ответ, попробуйте установить следующее в вашем /etc/my.cnf файле:

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

Также рекомендуется привязать IP-адрес и номер порта, чтобы не сомневаться в пути подключения:

bind-address=127.0.0.1
port=3306

В противном случае я бы посмотрел на блокировку таблицы и оттуда решил устранить неполадки.

person Jongosi    schedule 21.06.2017

Если вы используете MyISAM, вы можете столкнуться с проблемами параллелизма.

person Robert Munteanu    schedule 10.06.2009