MySQL, убитый OOM после поврежденной базы данных


4

У одного из наших серверов веб-приложений недавно возникла проблема, когда MySQL занимала больше памяти, чем физическая RAM. Этот сервер имеет 64 ГБ памяти и 8 ГБ в swap, а MySQL был OOM-ed, потому что он использовал более 69 ГБ памяти.

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

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

max_connections = 1000 
table_open_cache = 1000 
query_cache_size = 1G 

innodb_buffer_pool_size = 16GB 
innodb_buffer_pool_instances = 4 
innodb_log_file_size = 1G 
innodb_log_buffer_size = 24M 

Я также прочитал эти же вопросы:

Я не верю ответы к этим вопросам применяются (я проверял переменные, о которых они упоминают), и я не вижу, как MySQL мог разумно использовать ~ 49 ГБ памяти для 1000 подключений. Однако во время сбоя было открыто множество соединений, и я заметил, что запрос insert ... on duplicate key update ... на поврежденный стол (который был MyISAM) висел бесконечно.

Кто-нибудь знает, почему MySQL так сильно поглотил столько памяти (MySQL обычно использует ~ 24 ГБ памяти на этом сервере)? Связана ли поврежденная таблица, или это было вызвано неправильной конфигурацией?

Примечание: Я завершил проверку памяти на этом сервере без ошибок.


Показать переменные:

+---------------------------------------------------+--- 
| Variable_name          | Value 
+---------------------------------------------------+--- 
| auto_increment_increment       | 1 
| auto_increment_offset        | 1 
| autocommit          | ON 
| automatic_sp_privileges       | ON 
| back_log           | 50 
| big_tables          | OFF 
| binlog_cache_size         | 32768 
| binlog_direct_non_transactional_updates   | OFF 
| binlog_format          | MIXED 
| binlog_stmt_cache_size       | 32768 
| bulk_insert_buffer_size       | 8388608 
| completion_type         | NO_CHAIN 
| concurrent_insert         | AUTO 
| connect_timeout         | 10 
| default_storage_engine       | InnoDB 
| delay_key_write         | ON 
| delayed_insert_limit        | 100 
| delayed_insert_timeout       | 300 
| delayed_queue_size        | 1000 
| div_precision_increment       | 4 
| engine_condition_pushdown       | ON 
| error_count          | 0 
| event_scheduler         | OFF 
| foreign_key_checks        | ON 
| group_concat_max_len        | 1024 
| ignore_builtin_innodb        | OFF 
| 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      | 4 
| innodb_buffer_pool_size       | 17179869184 
| innodb_change_buffering       | all 
| innodb_checksums         | ON 
| innodb_commit_concurrency       | 0 
| innodb_concurrency_tickets      | 500 
| innodb_data_file_path        | ibdata1:10M:autoextend 
| 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        | ON 
| innodb_flush_log_at_trx_commit     | 0 
| innodb_flush_method        | O_DIRECT 
| 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       | 25165824 
| innodb_log_file_size        | 1073741824 
| innodb_log_files_in_group       | 2 
| 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_print_all_deadlocks      | OFF 
| 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_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         | 5.5.44 
| innodb_write_io_threads       | 4 
| interactive_timeout        | 28800 
| join_buffer_size         | 131072 
| keep_files_on_create        | OFF 
| key_buffer_size         | 67108864 
| 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 
| local_infile          | ON 
| lock_wait_timeout         | 31536000 
| locked_in_memory         | OFF 
| low_priority_updates        | OFF 
| max_allowed_packet        | 134217728 
| max_binlog_size         | 104857600 
| max_connect_errors        | 10 
| max_connections         | 1000 
| max_delayed_threads        | 20 
| max_error_count         | 64 
| max_heap_table_size        | 16777216 
| max_insert_delayed_threads      | 20 
| max_length_for_sort_data       | 1024 
| max_long_data_size        | 134217728 
| max_prepared_stmt_count       | 16382 
| max_relay_log_size        | 0 
| max_sort_length         | 1024 
| max_sp_recursion_depth       | 0 
| max_tmp_tables         | 32 
| max_user_connections        | 0 
| metadata_locks_cache_size       | 1024 
| min_examined_row_limit       | 0 
| multi_range_count         | 256 
| myisam_data_pointer_size       | 6 
| myisam_recover_options       | BACKUP 
| myisam_repair_threads        | 1 
| myisam_sort_buffer_size       | 8388608 
| myisam_stats_method        | nulls_unequal 
| myisam_use_mmap         | OFF 
| net_buffer_length         | 16384 
| net_read_timeout         | 30 
| net_retry_count         | 10 
| net_write_timeout         | 60 
| open_files_limit         | 65500 
| optimizer_prune_level        | 1 
| optimizer_search_depth       | 62 
| preload_buffer_size        | 32768 
| protocol_version         | 10 
| query_alloc_block_size       | 8192 
| query_cache_limit         | 1048576 
| query_cache_min_res_unit       | 4096 
| query_cache_size         | 1073741824 
| 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 
| rpl_recovery_rank         | 0 
| skip_external_locking        | ON 
| sort_buffer_size         | 2097152 
| sql_big_selects         | ON 
| sql_big_tables         | OFF 
| sql_buffer_result         | OFF 
| storage_engine         | InnoDB 
| stored_program_cache        | 256 
| sync_binlog          | 0 
| sync_frm           | ON 
| sync_master_info         | 0 
| sync_relay_log         | 0 
| sync_relay_log_info        | 0 
| table_definition_cache       | 400 
| table_open_cache         | 1000 
| thread_cache_size         | 8 
| thread_concurrency        | 10 
| thread_handling         | one-thread-per-connection 
| thread_stack          | 196608 
| time_zone           | SYSTEM 
| timed_mutexes          | OFF 
| tmp_table_size         | 16777216 
| transaction_alloc_block_size      | 8192 
| transaction_prealloc_size       | 4096 
| tx_isolation          | REPEATABLE-READ 
| unique_checks          | ON 
| updatable_views_with_limit      | YES 
| wait_timeout          | 28800 
| warning_count          | 0 
+---------------------------------------------------+-- 

Показать Global Status:

+------------------------------------------+---------------+ 
| Variable_name       | Value   | 
+------------------------------------------+---------------+ 
| Aborted_clients       | 9    | 
| Aborted_connects       | 5201   | 
| Binlog_cache_disk_use     | 566   | 
| Binlog_cache_use       | 59455472  | 
| Binlog_stmt_cache_disk_use    | 170   | 
| Binlog_stmt_cache_use     | 7980756  | 
| Bytes_received       | 118197326576 | 
| Bytes_sent        | 936083868833 | 
| Connections        | 15785450  | 
| Created_tmp_disk_tables     | 37787519  | 
| Created_tmp_files      | 1385   | 
| Created_tmp_tables      | 70916100  | 
| Delayed_errors       | 0    | 
| Delayed_insert_threads     | 0    | 
| Delayed_writes       | 0    | 
| Flush_commands       | 78   | 
| Handler_commit       | 306649532  | 
| Handler_delete       | 232632  | 
| Handler_discover       | 0    | 
| Handler_prepare       | 93231428  | 
| Handler_read_first      | 60815625  | 
| Handler_read_key       | 885609313  | 
| Handler_read_last      | 838614  | 
| Handler_read_next      | 4197678356 | 
| Handler_read_prev      | 357472833  | 
| Handler_read_rnd       | 101193542  | 
| Handler_read_rnd_next     | 52819751104 | 
| Handler_rollback       | 28462   | 
| Handler_savepoint      | 0    | 
| Handler_savepoint_rollback    | 0    | 
| Handler_update       | 64446304  | 
| Handler_write       | 412627899  | 
| Innodb_buffer_pool_pages_data   | 1048088  | 
| Innodb_buffer_pool_bytes_data   | 17171873792 | 
| Innodb_buffer_pool_pages_dirty   | 431   | 
| Innodb_buffer_pool_bytes_dirty   | 7061504  | 
| Innodb_buffer_pool_pages_flushed   | 43659545  | 
| Innodb_buffer_pool_pages_free   | 2    | 
| Innodb_buffer_pool_pages_misc   | 482   | 
| Innodb_buffer_pool_pages_total   | 1048572  | 
| Innodb_buffer_pool_read_ahead_rnd  | 0    | 
| Innodb_buffer_pool_read_ahead   | 848473  | 
| Innodb_buffer_pool_read_ahead_evicted | 358935  | 
| Innodb_buffer_pool_read_requests   | 23851889088 | 
| Innodb_buffer_pool_reads     | 132872602  | 
| Innodb_buffer_pool_wait_free    | 0    | 
| Innodb_buffer_pool_write_requests  | 454509933  | 
| Innodb_data_fsyncs      | 14157861  | 
| Innodb_data_pending_fsyncs    | 0    | 
| Innodb_data_pending_reads    | 0    | 
| Innodb_data_pending_writes    | 0    | 
| Innodb_data_read       | 2191680999424 | 
| Innodb_data_reads      | 134331079  | 
| Innodb_data_writes      | 49646417  | 
| Innodb_data_written      | 1460362066432 | 
| Innodb_dblwr_pages_written    | 43659545  | 
| Innodb_dblwr_writes      | 1967389  | 
| Innodb_have_atomic_builtins    | ON   | 
| Innodb_log_waits       | 0    | 
| Innodb_log_write_requests    | 55918265  | 
| Innodb_log_writes      | 2817833  | 
| Innodb_os_log_fsyncs      | 3218361  | 
| Innodb_os_log_pending_fsyncs    | 0    | 
| Innodb_os_log_pending_writes    | 0    | 
| Innodb_os_log_written     | 29521291776 | 
| Innodb_page_size       | 16384   | 
| Innodb_pages_created      | 1082758  | 
| Innodb_pages_read      | 133769705  | 
| Innodb_pages_written      | 43659545  | 
| Innodb_row_lock_current_waits   | 0    | 
| Innodb_row_lock_time      | 441363  | 
| Innodb_row_lock_time_avg     | 1219   | 
| Innodb_row_lock_time_max     | 51820   | 
| Innodb_row_lock_waits     | 362   | 
| Innodb_rows_deleted      | 210683  | 
| Innodb_rows_inserted      | 6673020  | 
| Innodb_rows_read       | 55723159186 | 
| Innodb_rows_updated      | 61534004  | 
| Innodb_truncated_status_writes   | 0    | 
| Key_blocks_not_flushed     | 0    | 
| Key_blocks_unused      | 50984   | 
| Key_blocks_used       | 53585   | 
| Key_read_requests      | 1649235730 | 
| Key_reads        | 90119161  | 
| Key_write_requests      | 4078153  | 
| Key_writes        | 2287803  | 
| Last_query_cost       | 0.000000  | 
| Max_used_connections      | 219   | 
| Not_flushed_delayed_rows     | 0    | 
| Open_files        | 842   | 
| Open_streams        | 0    | 
| Open_table_definitions     | 921   | 
| Open_tables        | 1000   | 
| Opened_files        | 504435699  | 
| Opened_table_definitions     | 115393300  | 
| Opened_tables       | 112827119  | 
| Prepared_stmt_count      | 0    | 
| Qcache_free_blocks      | 58397   | 
| Qcache_free_memory      | 377606768  | 
| Qcache_hits        | 600978542  | 
| Qcache_inserts       | 160064226  | 
| Qcache_lowmem_prunes      | 17296550  | 
| Qcache_not_cached      | 66882759  | 
| Qcache_queries_in_cache     | 471854  | 
| Qcache_total_blocks      | 1013507  | 
| Queries         | 1204594580 | 
| Questions        | 1201397855 | 
| Rpl_status        | AUTH_MASTER | 
| Select_full_join       | 152194  | 
| Select_full_range_join     | 0    | 
| Select_range        | 9798991  | 
| Select_range_check      | 0    | 
| Select_scan        | 116607434  | 
| Slave_heartbeat_period     | 30.000  | 
| Slave_open_temp_tables     | 0    | 
| Slave_received_heartbeats    | 2559   | 
| Slave_retried_transactions    | 0    | 
| Slave_running       | OFF   | 
| Slow_launch_threads      | 0    | 
| Slow_queries        | 3255   | 
| Sort_merge_passes      | 3    | 
| Sort_range        | 14976751  | 
| Sort_rows        | 144820896  | 
| Sort_scan        | 11605905  | 
| Table_locks_immediate     | 380794869  | 
| Table_locks_waited      | 105   | 
| Tc_log_max_pages_used     | 0    | 
| Tc_log_page_size       | 0    | 
| Tc_log_page_waits      | 0    | 
| Threads_cached       | 7    | 
| Threads_connected      | 3    | 
| Threads_created       | 2873   | 
| Threads_running       | 3    | 
| Uptime         | 4892974  | 
| Uptime_since_flush_status    | 4892974  | 
+------------------------------------------+---------------+ 
  0

Недостаточно. версия отсутствует. Время ожидания отсутствует, поэтому счетчики бесполезны - не могут получить «в секунду». (Возможно, нужно использовать какой-то сайт postit из-за длины.) 08 апр. 162016-04-08 15:32:01

  0

Я обновил раздел Global Status с временем безотказной работы и т. Д. Версия 5.5.44. 08 апр. 162016-04-08 15:48:16

  0

К сожалению, для тщательного анализа мне нужны все как «ПЕРЕМЕННЫЕ», так и «СТАТУС». 08 апр. 162016-04-08 17:35:23

  0

Я поставил все «СТАТУС», но все «ПЕРЕМЕННЫЕ», конечно, не подойдут. Тем не менее, я пытался только вытаскивать такие вещи, как пути к файлам, размеры предельных лимитов (почти все из которых являются 'LONG_MAX' или' ULONG_MAX'), наборы символов и т. Д. Есть ли что-то конкретное, что вам нужно, которое отсутствует? 08 апр. 162016-04-08 17:45:17

2

query_cache_size = 1G - Это очень плохо. Не устанавливайте его больше, чем около 50M. Это не приведет к сбою, но может вызвать длительные ларьки, что может привести к краху.

Когда любой записи происходит в таблице, все записей в КК для этой таблицы продувается. Чем больше QC, тем дольше это занимает.

QC «перед» двигателями (MyISAM и InnoDB). То есть сначала проверяется контроль качества, чтобы проверить, есть ли точный запрос, и в этом случае возвращается результат. В противном случае движок берет на себя и выполняет другой уровень кэширования (key_buffer + пространство ОС для MyISAM; buffer_pool для InnoDB).

max_connections = 1000 - Какой у вас «клиент»? Может ли это действительно создать много связей?

Вероятные причины tmp_table_size и max_heap_table_size не должны превышать, скажем, 1% ОЗУ. Кроме того, вы подвергаетесь большому риску исчерпания ОЗУ, когда множество соединений выполняют сложные запросы.

Индексы MyISAM становятся поврежденными всякий раз, когда происходит несправедливое завершение работы. Это серьезная причина для перехода на InnoDB.

Вы поднимали key_buffer_size? Он не должен превышать около 20% оперативной памяти.

Предоставить SHOW VARIABLES; и SHOW GLOBAL STATUS;; Я проверю, что еще может быть лишним.

  0

Нет, я никогда не видел много одновременных подключений, но у меня было максимальное значение по умолчанию 150. Я не поднимал 'tmp_table_size' или' max_heap_table_size'. 'key_buffer_size' немного больше, но нигде около 20% ОЗУ. См. Мой отредактированный ответ для переменных и статуса. 08 апр. 162016-04-08 12:49:37

  0

Кроме того, не могли бы вы помочь мне понять, почему большой 'query_cache_size' вызовет длительные киоски? Я думал, что кеш запросов в какой-то степени для MyISAM, для пула буферов для InnoDB. 08 апр. 162016-04-08 13:00:58

  0

добавить пару параграфов о QC 08 апр. 162016-04-08 15:35:54

  0

'table_open_cache' может быть слишком низким, исходя из очень высоких' Opened_tables'. (Не могу точно сказать без 'Uptime'.) 08 апр. 162016-04-08 15:45:24