Sample Header Ad - 728x90

MariaDB 10.1 shutdown stuck at "Event Scheduler: Purging the queue"

0 votes
2 answers
1427 views
I have read many different questions about slow MariaDB/MySQL shutdown, but I have not found an answer which solves my problem yet. On one of our servers with the highest load, when I issue a stop command for MariaDB, in the logfile I see: 2020-05-26 13:26:02 140304972457728 [Note] /opt/lampp/sbin/mysqld: Normal shutdown 2020-05-26 13:26:02 140304972457728 [Note] Event Scheduler: Purging the queue. 0 events From there, it won't move, at least not in the 15-20 minutes I waited, and I usually have to kill the mysqld daemon. By following other questions and answers found online, the shutdown command is given through the mysqladmin command via TCP (instead of socket), and also I check that the dirty pages are at or close to zero. mysqladmin --user=shutdown --password=xxx --port=3306 --host=127.0.0.1 --protocol=tcp shutdown The log does not show any specific error or warning message which might explain such a slow shutdown, and when monitoring CPU/RAM usage during the command, the server seems just idle. I also checked the OS logs, but found nothing unusual. Finally, the server uses MariaDB 10.1.36 and hosts 10 databases, each a clone of the other, which are made of ~2000 tables each, all InnoDB; it's a dual core server with 8 GB RAM and CentOS 7.8 as its OS. innodb_fast_shutdown is set to 1. What can I do to lower the database shutdown time to a more acceptable 1-2 minutes? Here is the database configuration from my.cnf: innodb_data_home_dir = /opt/agews64/data/mysql/ innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /opt/agews64/data/mysql/ innodb_lock_wait_timeout = 60 innodb_file_format = Barracuda innodb_file_format_max = Barracuda innodb_large_prefix = 1 innodb_fast_shutdown = 1 default_storage_engine = InnoDB innodb_flush_log_at_trx_commit = 1 sync_binlog = 0 innodb_flush_method = O_DIRECT #innodb_thread_concurrency = 8 innodb_io_capacity = 1000 innodb_io_capacity_max = 2000 innodb_stats_on_metadata = 0 innodb_buffer_pool_dump_at_shutdown = 0 innodb_buffer_pool_load_at_startup = 0 innodb_buffer_pool_dump_pct = 100 innodb_adaptive_hash_index_partitions = 8 innodb_checksum_algorithm = crc32 innodb_log_checksum_algorithm = crc32 #table_open_cache_instances = 16 innodb_read_io_threads = 8 innodb_write_io_threads = 4 innodb_max_dirty_pages_pct = 90 innodb_max_dirty_pages_pct_lwm = 10 max_connections = 20 sort_buffer_size = 16M read_buffer_size = 1M read_rnd_buffer_size = 4M join_buffer_size = 4M thread_stack = 512K binlog_cache_size = 32K myisam_sort_buffer_size = 1M net_buffer_length = 16K wait_timeout = 3600 interactive_timeout = 3600 # BUFFER GLOBALI key_buffer_size = 64M query_cache_type = 0 query_cache_size = 0 tmp_table_size = 64M max_heap_table_size = 64M aria_pagecache_buffer_size = 128M innodb_buffer_pool_size = 3072M innodb_buffer_pool_instances = 3 innodb_log_file_size = 256M innodb_log_files_in_group = 3 innodb_log_buffer_size = 64M table_open_cache = 30000 table_definition_cache = 15400 open_files_limit = 90000 innodb_open_files = 90000 thread_cache_size = 8 thread_handling = one-thread-per-connection max_sp_recursion_depth = 16 log_output = NONE general_log = 0 slow_query_log = 0 #slow_query_log_file = /opt/agews64/logs/slow_queries.log long_query_time = 3 min_examined_row_limit = 0 log_queries_not_using_indexes = 0 log_slow_admin_statements = 0 plugin_load_add = query_response_time query_response_time_stats = 0 userstat = 0 #innodb_monitor_enable = all performance_schema = 1 performance_schema_instrument = "%=off" performance_schema_consumer_global_instrumentation = 0 performance_schema_consumer_thread_instrumentation = 0 performance_schema_consumer_events_stages_current = 0 performance_schema_consumer_events_stages_history = 0 performance_schema_consumer_events_stages_history_long = 0 performance_schema_consumer_events_statements_current = 0 performance_schema_consumer_events_statements_history = 0 performance_schema_consumer_events_statements_history_long = 0 performance_schema_consumer_events_waits_current = 0 performance_schema_consumer_events_waits_history = 0 performance_schema_consumer_events_waits_history_long = 0 performance_schema_consumer_statements_digest = 0 performance_schema_events_stages_history_size = 100 performance_schema_events_stages_history_long_size = 10000 performance_schema_events_statements_history_size = 100 performance_schema_events_statements_history_long_size = 10000 performance_schema_events_waits_history_size = 100 performance_schema_events_waits_history_long_size = 10000 performance_schema_digests_size = -1 plugin_load_add = "federated=ha_federatedx.so" # gestione della criptazione dei dati plugin_load_add = file_key_management file_key_management_filename = /opt/agews64/data/mysql/.keys file_key_management_filekey = FILE:/opt/agews64/etc/mysql/.key file_key_management_encryption_algorithm = aes_ctr innodb_encrypt_tables = OFF innodb_encrypt_log = 0 encrypt_tmp_disk_tables = 0 encrypt_tmp_files = 0 encrypt_binlog = 0
Asked by Matteo Tassinari (187 rep)
May 26, 2020, 11:55 AM
Last activity: Mar 28, 2023, 09:00 PM