In the ORACLE SHUTDOWN

MySQL SHUTDOWN LEVEL MySQL SHUTDOWN LEVEL MySQL SHUTDOWN LEVEL MySQL SHUTDOWN LEVEL

Borrow Oracle’s SHUTDOWN LEVEL analysis here

There are four Oracle SHUTDOWN levels: ABORT, IMMEDIATE, NORMAL, and TRANSACTIONAL

ABORT

• End all SQL immediately
• Roll back uncommitted transactions
• Disconnect all users
• Recovery is required the next time the instance is started

IMMEDIATE

• Allows running SQL to complete execution
• Roll back uncommitted transactions
• Disconnect all users

NORMAL

• New connections are not allowed
• Wait for the current connection to disconnect
• Recovery is not required the next time the instance is started

TRANSACTIONAL

• Wait for a transaction to commit or end
• New connections are not allowed
• Disconnect after the transaction commits or completes

The SHUTDOWN in MySQL is the same as the SHUTDOWN IMMEDIATE in Oracle. Recovery is not required for restarting the instance, but the transaction rollback process may take a long time

MySQL SHUTDOWN process Analysis

• Mysql_shutdown Sends the SHUTDOWN command
• Dispatch_command () receives COM_SHUTDOWN command and calls kill_mysql()
• Create kill_server_thread kill_mysql ()
• Kill_server_thread call kill_server ()
• kill_server()
  • close_connections()
    • Close port
    • disconnect
    • Rollback transactions (which can take a long time)
  • unireg_end
    • clean_up
      • innobase_shutdown_for_mysql
      • delete_pid_file

The InnoDB shutdown speed depends on the innodb_fast_shutdown parameter

• 0: slowest, wait for purge to complete and change buffer merge to complete
• 1: default, there is no need to wait for the Purge and change buffer merge to complete
• 2: Row_drop_tables_for_mysql_in_BACKGROUND does not wait for dirty pages to be flushed. If innodb_buffer_pool_dump_at_shutdown is set, buffer dump is not required.

  caseCOM_SHUTDOWN: // Receive SHUTDOWN command {if (packet_length < 1)
    {    
      my_error(ER_MALFORMED_PACKET, MYF(0));
      break;
    }    
    status_var_increment(thd->status_var.com_other);
    ifCheck_global_access (THD,SHUTDOWN_ACL) // Check permissionsbreak; /* purecov: inspected */
    /*   
      If the client is < 4.1.3, it is going to send us no argument; thenPacket_length is 0, packet[0] is the end 0 of the packet. Note that SHUTDOWN_DEFAULT is 0. the shutdown level isinpacket[0]. */ enum mysql_enum_shutdown_level level; // the default LEVEL is not implementedif(! thd->is_valid_time()) level= SHUTDOWN_DEFAULT;else 
      level= (enum mysql_enum_shutdown_level) (uchar) packet[0];
    if (level == SHUTDOWN_DEFAULT)
      level= SHUTDOWN_WAIT_ALL_BUFFERS; // soon default will be configurable
    else if(level ! = SHUTDOWN_WAIT_ALL_BUFFERS) { my_error(ER_NOT_SUPPORTED_YET, MYF(0),"this shutdown level");
      break;
    }    
    DBUG_PRINT("quit", ("Got shutdown command for level %u", level));
    general_log_print(thd, command, NullS); // Record general_log my_eof(THD); kill_mysql(); // call kill_mysql() to create the kill_server_thread thread error=TRUE;break;
  }
  
Copy the code

Kill_server () calls close_connections() and unireg_end()

static void __cdecl kill_server(int sig_ptr)
{
	......
	close_connections();
   	if(sig ! = MYSQL_KILL_SIGNAL && sig ! = 0) unireg_abort(1); /* purecov: inspected */else
      unireg_end();

Copy the code

The main logic for terminating a thread is in mysqld.cc:close_connections()

static void close_connections(void) ...... / /* Abort listening to new connections */ DBUG_PRINT("quit", ("Closing sockets"));
  if(! opt_disable_networking ) {if(mysql_socket_getfd(base_ip_sock) ! = INVALID_SOCKET) { (void) mysql_socket_shutdown(base_ip_sock, SHUT_RDWR); (void) mysql_socket_close(base_ip_sock); base_ip_sock= MYSQL_INVALID_SOCKET; }if(mysql_socket_getfd(extra_ip_sock) ! = INVALID_SOCKET) { (void) mysql_socket_shutdown(extra_ip_sock, SHUT_RDWR); (void) mysql_socket_close(extra_ip_sock); extra_ip_sock= MYSQL_INVALID_SOCKET; }}... /* First run through thread list */ sql_print_information("Giving %d client threads a chance to die gracefully",
                        static_cast<int>(get_thread_count()));

  mysql_mutex_lock(&LOCK_thread_count);
  
  Thread_iterator it= global_thread_list->begin();
  for(; it ! = global_thread_list->end(); ++it) { THD *tmp= *it; DBUG_PRINT("quit", ("Informing thread %ld that it's time to die", tmp->thread_id)); /* We skip slave threads & scheduler on this first loop through. */ * We skip slave threads & scheduler on this first loop through. */if (tmp->slave_thread) 
      continue;
    if (tmp->get_command() == COM_BINLOG_DUMP ||
        tmp->get_command() == COM_BINLOG_DUMP_GTID)
    {
      ++dump_thread_count;
      continue; } / / TMP -> KILL = THD::KILL_CONNECTION; . } mysql_mutex_unlock(&LOCK_thread_count); Events::deinit(); sql_print_information("Shutting down slave threads"); /* Disconnect the slave thread */ end_slave(); /* Iterate over the thread list for the second time */if (dump_thread_count)
  {                                                                                                                                                                                                         
    /*
      Replication dump thread should be terminated after the clients are
      terminated. Wait for few more seconds for other sessions to end.
     */
    while (get_thread_count() > dump_thread_count && dump_thread_kill_retries)
    {
      sleep(1);
      dump_thread_kill_retries--;
    }
    mysql_mutex_lock(&LOCK_thread_count);
    for(it= global_thread_list->begin(); it ! = global_thread_list->end(); ++it) { THD *tmp= *it; DBUG_PRINT("quit", ("Informing dump thread %ld that it's time to die",
                         tmp->thread_id));
      if(TMP - > get_command () = = COM_BINLOG_DUMP | | TMP - > get_command () = = COM_BINLOG_DUMP_GTID) {/ * close the thread DUMP * / TMP - > killed = THD::KILL_CONNECTION; . } } mysql_mutex_unlock(&LOCK_thread_count); }... /* Iterate through the thread list for the third time */for(it= global_thread_list->begin(); it ! = global_thread_list->end(); ++it) { THD *tmp= *it;if (tmp->vio_ok())
    {
      if (log_warnings)
        sql_print_warning(ER_DEFAULT(ER_FORCING_CLOSE),my_progname,
                          tmp->thread_id,
                          (tmp->main_security_ctx.user ?
                           tmp->main_security_ctx.user : "")); /* Close the connection without waiting for the statement to end, but rollback the uncommitted thread */ close_connection(TMP); }}Copy the code

THD::disconnect() is called in close_connection() to disconnect

The transaction is rolled back after the connection is disconnected

bool do_command(THD *thd) { ...... packet_length= my_net_read(net); // THD ->disconnect() directly return to...... } void do_handle_one_connection(THD *thd_arg) { ......while (thd_is_connection_alive(thd))
	{
  		if(do_command(THD)) //do_command returns error, breaking the loopbreak; } end_connection(thd); end_thread: close_connection(thd); /* One_thread_per_connection_end () */if (MYSQL_CALLBACK_ELSE(thd->scheduler, end_thread, (thd, 1), 0))
      return;                                 // Probably no-threads


	......
}

Copy the code

The transaction rolls back the invocation chain

trans_rollback(THD*) ()
THD::cleanup() ()
THD::release_resources() ()
one_thread_per_connection_end(THD*, bool) ()
do_handle_one_connection(THD*) ()
handle_one_connection ()
Copy the code

Unireg_end called clean_up ()

Void clean_UP (bool print_message) {/* Here are some operations to free memory and locks */...... Innobase_shutdown_for_mysql purge all (innodb_fast_shutdown = 0) merge change buffer (innodb_fast_shutdown = 0) Flush dirty page (innodb_fast_shutdown = 0,1) flushlog*/ plugin_shutdown(); /* Here are some operations to free memory and locks */...... */ delete_pid_file(MYF(0)); */ delete_pid_file(MYF(0)); /* Here are some operations to free memory and locks */......Copy the code

Innodb shutdown analysis

The main innoDB shutdown operation is in logs_empty_and_mark_files_at_shutdown()

• Wait for the background thread to finish
  • srv_error_monitor_thread
  • srv_lock_timeout_thread
  • srv_monitor_thread
  • buf_dump_thread
  • dict_stats_thread
• Trx_sys_any_active_transactions Wait for everything to end
• Wait for the background thread to finish
  • worker threads: srv_worker_thread
  • master thread: srv_master_thread
  • purge thread: srv_purge_coordinator_thread
• Wait until buf_flush_LRU_Manager_thread ends
• Wait for buf_FLUSh_PAGE_cleaner_thread to end
• Waiting for Pending checkpoint_writes, Pending log flush writes ends
• Wait for the BUFFER pool PENDING I/O to end
• if (innodb_fast_shutdown == 2)
  • Flush log buffer return
• log_make_checkpoint_at
  • flush buffer pool
  • write checkpoint
• Fil_write_flushed_lsn_to_data_files ()
• Close all files

After logs_empty_and_mark_files_at_shutdown() is complete, Innobase_shutdown_for_mysql () does some resource cleaning to end the shutdown process

This article is the original content of the cloud habitat community, shall not be reproduced without permission, if need to be reproduced, please send email to [email protected]; If you find any content suspected of plagiarism in our community, you are welcome to send an email to [email protected] to report and provide relevant evidence. Once verified, our community will immediately delete the content suspected of infringement.