mysql线程级别的内存参数怎么根据内存规格进行设置 mysql线程池

转载

mob64ca141677f9 2023-08-25 23:36:52

一、MySql中的线程

在mysql中，每一个连接上来，就会分配给一个相关的THD数据类。在前面的分析中可以看到，连接器（Connectors）连接到的直接就是连接池，在连接池的线程处理中分为三部分，即一对一（一个连接对应一个线程），多对一（多个连接对应一个线程）和线程池（多对多）。
线程池和线程可以针对不同的具体场景来处理具体的事务，这样既兼顾了效率又提高了适应性，对于新手来说，这就是设计的一个缩影。MySql中的线程较Redis要复杂一些（当然针对的是REDIS6以前版本），在下面的分析中会对三种方式进行逐一的解读。

mysql线程级别的内存参数怎么根据内存规格进行设置 mysql线程池_ide

更详实的代码可参看官网的说明：https://dev.mysql.com/doc/dev/mysql-server/latest/classTHD.html

二、主要方式

上面提到了连接池中的处理方式有三种，即：
1、Per_thread_connection_handler
2、One_thread_connection_handler
3、Plugin_connection_handler
连接控制类和这三种对应到源码中如下：

//sql_class.h

class THD : public MDL_context_owner,
            public Query_arena,
            public Open_tables_state {
 private:
  inline bool is_stmt_prepare() const {
    assert(0);
    return Query_arena::is_stmt_prepare();
  }

  inline bool is_stmt_prepare_or_first_sp_execute() const {
    assert(0);
    return Query_arena::is_stmt_prepare_or_first_sp_execute();
  }

  inline bool is_stmt_prepare_or_first_stmt_execute() const {
    assert(0);
    return Query_arena::is_stmt_prepare_or_first_stmt_execute();
  }

  inline bool is_regular() const {
    assert(0);
    return Query_arena::is_regular();
  }

 public:
  MDL_context mdl_context;

......
  String m_rewritten_query;

 public:
  Relay_log_info * rli_fake;

  Relay_log_info * rli_slave;

......

  THR_LOCK_INFO lock_info;  // Locking info of this thread

  mysql_mutex_t LOCK_thd_data;


    Protects THD::m_query_string. No other mutexes should be locked
    while having this mutex locked.

  mysql_mutex_t LOCK_thd_query;


  mysql_mutex_t LOCK_thd_sysvar;

  mysql_mutex_t LOCK_thd_protocol;

......
  bool is_current_stmt_binlog_row_enabled_with_write_set_extraction() const;


  inline bool optimizer_switch_flag(ulonglong flag) const {
    return (variables.optimizer_switch & flag);
  }

  enum binlog_filter_state {
    BINLOG_FILTER_UNKNOWN,
    BINLOG_FILTER_CLEAR,
    BINLOG_FILTER_SET
  };

  inline void reset_binlog_local_stmt_filter() {
    m_binlog_filter_state = BINLOG_FILTER_UNKNOWN;
  }

  inline void clear_binlog_local_stmt_filter() {
    assert(m_binlog_filter_state == BINLOG_FILTER_UNKNOWN);
    m_binlog_filter_state = BINLOG_FILTER_CLEAR;
  }

  inline void set_binlog_local_stmt_filter() {
    assert(m_binlog_filter_state == BINLOG_FILTER_UNKNOWN);
    m_binlog_filter_state = BINLOG_FILTER_SET;
  }

  binlog_filter_state get_binlog_local_stmt_filter() const {
    return m_binlog_filter_state;
  }

  /** Holds active timer object */
  struct THD_timer_info * timer;

  struct THD_timer_info * timer_cache;

 private:
  /*
    Indicates that the command which is under execution should ignore the
    'read_only' and 'super_read_only' options.
  */
  bool skip_readonly_check;

  binlog_filter_state m_binlog_filter_state;

  enum_binlog_format current_stmt_binlog_format;

  uint32 binlog_unsafe_warning_flags;
......
  table_map table_map_for_update;

  /* Tells if LAST_INSERT_ID(#) was called for the current statement */
  bool arg_of_last_insert_id_function;

    ALL OVER THIS FILE, "insert_id" means "*automatically generated* value for
    insertion into an auto_increment column".

  ulonglong first_successful_insert_id_in_prev_stmt;

  ulonglong first_successful_insert_id_in_prev_stmt_for_binlog;

  ulonglong first_successful_insert_id_in_cur_stmt;

  inline void record_first_successful_insert_id_in_cur_stmt(ulonglong id_arg) {
    if (first_successful_insert_id_in_cur_stmt == 0)
      first_successful_insert_id_in_cur_stmt = id_arg;
  }
......

  longlong m_row_count_func; /* For the ROW_COUNT() function */

 public:
  inline longlong get_row_count_func() const { return m_row_count_func; }

  inline void set_row_count_func(longlong row_count_func) {
    m_row_count_func = row_count_func;
  }

  ha_rows num_truncated_fields;

 private:

  ha_rows m_sent_row_count;
  ha_rows m_examined_row_count;

 private:
  USER_CONN *m_user_connect;

 public:
  void set_user_connect(USER_CONN *uc);
  const USER_CONN *get_user_connect() const { return m_user_connect; }

  void increment_user_connections_counter();
  void decrement_user_connections_counter();

  void increment_con_per_hour_counter();

  void increment_updates_counter();

  void increment_questions_counter();

  void time_out_user_resource_limits();

 public:
  ha_rows get_sent_row_count() const { return m_sent_row_count; }

  ha_rows get_examined_row_count() const { return m_examined_row_count; }

  void set_sent_row_count(ha_rows count);

  void inc_sent_row_count(ha_rows count);
  void inc_examined_row_count(ha_rows count);

  void inc_status_created_tmp_disk_tables();
  void inc_status_created_tmp_tables();
  void inc_status_select_full_join();
  void inc_status_select_full_range_join();
  void inc_status_select_range();
  void inc_status_select_range_check();
  void inc_status_select_scan();
  void inc_status_sort_merge_passes();
  void inc_status_sort_range();
  void inc_status_sort_rows(ha_rows count);
  void inc_status_sort_scan();
  void set_status_no_index_used();
  void set_status_no_good_index_used();

  const CHARSET_INFO *db_charset;
#if defined(ENABLED_PROFILING)
  std::unique_ptr<PROFILING> profiling;
#endif

  /** Current stage progress instrumentation. */
  PSI_stage_progress *m_stage_progress_psi;
  /** Current statement digest. */
  sql_digest_state *m_digest;
  /** Current statement digest token array. */
  unsigned char *m_token_array;
  /** Top level statement digest. */
  sql_digest_state m_digest_state;

......
inline void THD::set_system_user(bool system_user_flag) {
  m_is_system_user.store(system_user_flag, std::memory_order_seq_cst);
}

相关具体的代码：

#include "my_inttypes.h"

class THD;
class Channel_info;
class Connection_handler_manager;

/**
  This abstract base class represents how connections are processed,
  most importantly how they map to OS threads.
*/
class Connection_handler {
 protected:
  friend class Connection_handler_manager;

  Connection_handler() {}
  virtual ~Connection_handler() {}

  /**
    Add a connection.

    @param channel_info     Pointer to the Channel_info object.

    @note If this function is successful (returns false), the ownership of
    channel_info is transferred. Otherwise the caller is responsible for
    its destruction.

    @return true if processing of the new connection failed, false otherwise.
  */
  virtual bool add_connection(Channel_info *channel_info) = 0;

  /**
    @return Maximum number of threads that can be created
            by this connection handler.
  */
  virtual uint get_max_threads() const = 0;
};
class Channel_info;
class THD;

/**
  This class represents the connection handling functionality
  that each connection is being handled in a single thread
*/
class Per_thread_connection_handler : public Connection_handler {
  Per_thread_connection_handler(const Per_thread_connection_handler &);
  Per_thread_connection_handler &operator=(
      const Per_thread_connection_handler &);

  /**
    Check if idle threads to handle connection in
    thread cache. If so enqueue the new connection
    to be picked by the idle thread in thread cache.

    @retval false if idle pthread was found, else true.
  */
  bool check_idle_thread_and_enqueue_connection(Channel_info *channel_info);

  /**
    List of pending channel info objects to be picked by idle
    threads. Protected by LOCK_thread_cache.
  */
  static std::list<Channel_info *> *waiting_channel_info_list;

  static mysql_mutex_t LOCK_thread_cache;
  static mysql_cond_t COND_thread_cache;
  static mysql_cond_t COND_flush_thread_cache;

 public:
  // Status variables related to Per_thread_connection_handler
  static ulong blocked_pthread_count;  // Protected by LOCK_thread_cache.
  static ulong slow_launch_threads;
  static bool shrink_cache;  // Protected by LOCK_thread_cache
  // System variable
  static ulong max_blocked_pthreads;

  static void init();
  static void destroy();

  /**
    Wake blocked pthreads and wait until they have terminated.
  */
  static void kill_blocked_pthreads();

  /**
    Block until a new connection arrives.
  */
  static Channel_info *block_until_new_connection();

  /**
    Modify the thread cache size.

    @param thread_cache_size size of thread cache.
  */
  static void modify_thread_cache_size(const ulong thread_cache_size);

  Per_thread_connection_handler() {}
  ~Per_thread_connection_handler() override {}

 protected:
  bool add_connection(Channel_info *channel_info) override;

  uint get_max_threads() const override;
};

/**
  This class represents the connection handling functionality
  of all connections being handled in a single worker thread.
*/
class One_thread_connection_handler : public Connection_handler {
  One_thread_connection_handler(const One_thread_connection_handler &);
  One_thread_connection_handler &operator=(
      const One_thread_connection_handler &);

 public:
  One_thread_connection_handler() {}
  ~One_thread_connection_handler() override {}

 protected:
  bool add_connection(Channel_info *channel_info) override;

  uint get_max_threads() const override { return 1; }
};

class One_thread_connection_handler : public Connection_handler {
  One_thread_connection_handler(const One_thread_connection_handler &);
  One_thread_connection_handler &operator=(
      const One_thread_connection_handler &);

 public:
  One_thread_connection_handler() {}
  ~One_thread_connection_handler() override {}

 protected:
  bool add_connection(Channel_info *channel_info) override;

  uint get_max_threads() const override { return 1; }
};
class THD;

class Plugin_connection_handler : public Connection_handler {
  Connection_handler_functions *m_functions;

  Plugin_connection_handler(const Plugin_connection_handler &);
  Plugin_connection_handler &operator=(const Plugin_connection_handler &);

 public:
  Plugin_connection_handler(Connection_handler_functions *functions)
      : m_functions(functions) {}

  ~Plugin_connection_handler() override { m_functions->end(); }

 protected:
  bool add_connection(Channel_info *channel_info) override {
    return m_functions->add_connection(channel_info);
  }

  uint get_max_threads() const override { return m_functions->max_threads; }
};

这里看到其实线程池没啥特别的，倒是THD复杂的很，代码就有几千行，让人感到头大，不过不要害怕，其实没啥。THD对象在官网的文档中说是线程处理用户连接请求的一个核心类，对于这种拥有几千行代码的大类，要抓住重点，不必一下子必须要掌握每个细节。
THD类继承了三个父类：
MDL_context_owner：作为Metadata Lock 拥有者相关的接口，其中元数据边界控制（进/退），元数据信息通知等，它是一个虚类。
Query_arena：管理语句元素的类，主要针对指语句（包括store procedure）解释成抽象语法树后的节点
Open_tables_state ：管理线程打开和加锁的表的状态，保持了一个临时表信息和锁信息，子类有Open_tables_backup
除这些外，还包括与锁相关的数据结构如MDL_context，接口语法语义相关如LEX，日志和事务以及执行数据和优化参数等。

三、源码流程

在软件中用不到线程（进程）的软件可以说太少了，线程一般会抽象出来封装一个独立的模块，用来处理跨平台或者细节的不同,下面再看一下线程分配的过程：
在handle_connection这个函数中：

//在win_main/mysqld_main中调用  mysqld_socket_acceptor->check_and_spawn_admin_connection_handler_thread();和
//  mysqld_socket_acceptor->connection_event_loop();在这其中调用：
void connection_event_loop() {
  Connection_handler_manager *mgr =
      Connection_handler_manager::get_instance();
  while (!connection_events_loop_aborted()) {
    Channel_info *channel_info = m_listener->listen_for_connection_event();
    if (channel_info != nullptr) mgr->process_new_connection(channel_info);
  }
}
void Connection_handler_manager::process_new_connection(
    Channel_info *channel_info) {
  if (connection_events_loop_aborted() ||
      !check_and_incr_conn_count(channel_info->is_admin_connection())) {
    channel_info->send_error_and_close_channel(ER_CON_COUNT_ERROR, 0, true);
    delete channel_info;
    return;
  }

  if (m_connection_handler->add_connection(channel_info)) {
    inc_aborted_connects();
    delete channel_info;
  }
}
bool Per_thread_connection_handler::add_connection(Channel_info *channel_info) {
  int error = 0;
  my_thread_handle id;

  DBUG_TRACE;

  // Simulate thread creation for test case before we check thread cache
  DBUG_EXECUTE_IF("fail_thread_create", error = 1; goto handle_error;);

  if (!check_idle_thread_and_enqueue_connection(channel_info)) return false;

  /*
    There are no idle threads avaliable to take up the new
    connection. Create a new thread to handle the connection
  */
  channel_info->set_prior_thr_create_utime();
  error =
  //下面调用线程创建
      mysql_thread_create(key_thread_one_connection, &id, &connection_attrib,
                          handle_connection, (void *)channel_info);
......
  }
static void *handle_connection(void *arg) {
  Global_THD_manager *thd_manager = Global_THD_manager::get_instance();
  Connection_handler_manager *handler_manager =
      Connection_handler_manager::get_instance();
  Channel_info *channel_info = static_cast<Channel_info *>(arg);
  bool pthread_reused MY_ATTRIBUTE((unused)) = false;

  if (my_thread_init()) {
    connection_errors_internal++;
    channel_info->send_error_and_close_channel(ER_OUT_OF_RESOURCES, 0, false);
    handler_manager->inc_aborted_connects();
    Connection_handler_manager::dec_connection_count();
    delete channel_info;
    my_thread_exit(nullptr);
    return nullptr;
  }

  for (;;) {
    THD *thd = init_new_thd(channel_info);
    if (thd == nullptr) {
      connection_errors_internal++;
      handler_manager->inc_aborted_connects();
      Connection_handler_manager::dec_connection_count();
      break;  // We are out of resources, no sense in continuing.
    }
    .......
  }
  static THD *init_new_thd(Channel_info *channel_info) {
  THD *thd = channel_info->create_thd();
  if (thd == nullptr) {
    channel_info->send_error_and_close_channel(ER_OUT_OF_RESOURCES, 0, false);
    delete channel_info;
    return nullptr;
  }

  thd->set_new_thread_id();

  if (channel_info->get_prior_thr_create_utime() != 0) {
    /*
      A pthread was created to handle this connection:
      increment slow_launch_threads counter if it took more than
      slow_launch_time seconds to create the pthread.
    */
    ulonglong launch_time =
        thd->start_utime - channel_info->get_prior_thr_create_utime();
    if (launch_time >= slow_launch_time * 1000000ULL)
      Per_thread_connection_handler::slow_launch_threads++;
  }
  delete channel_info;

  /*
    handle_one_connection() is normally the only way a thread would
    start and would always be on the very high end of the stack ,
    therefore, the thread stack always starts at the address of the
    first local variable of handle_one_connection, which is thd. We
    need to know the start of the stack so that we could check for
    stack overruns.
  */
  thd_set_thread_stack(thd, (char *)&thd);
  thd->store_globals();

  return thd;
}

THD *Channel_info::create_thd() {
  DBUG_EXECUTE_IF("simulate_resource_failure", return nullptr;);

  Vio *vio_tmp = create_and_init_vio();
  if (vio_tmp == nullptr) return nullptr;

  THD *thd = new (std::nothrow) THD;
  if (thd == nullptr) {
    vio_delete(vio_tmp);
    return nullptr;
  }

  thd->get_protocol_classic()->init_net(vio_tmp);

  return thd;
}

在前面的网络流程中知道了，MYSQL支持TCP，PIPE和共享内存三种方式来实现数据的交互通信，所以就需要在这三个类中的监听中进行控制，下面就是管理中的初始化等动作：

//代码在getoptions中，此函数仍然是在上面的win_main中调用：
bool Connection_handler_manager::init() {
  /*
    This is a static member function.
    Per_thread_connection_handler's static members need to be initialized
    even if One_thread_connection_handler is used instead.
  */
  Per_thread_connection_handler::init();

  Connection_handler *connection_handler = nullptr;
  switch (Connection_handler_manager::thread_handling) {
    case SCHEDULER_ONE_THREAD_PER_CONNECTION:
      connection_handler = new (std::nothrow) Per_thread_connection_handler();
      break;
    case SCHEDULER_NO_THREADS:
      connection_handler = new (std::nothrow) One_thread_connection_handler();
      break;
    default:
      assert(false);
  }

  if (connection_handler == nullptr) {
    // This is a static member function.
    Per_thread_connection_handler::destroy();
    return true;
  }

  m_instance =
      new (std::nothrow) Connection_handler_manager(connection_handler);

  if (m_instance == nullptr) {
    delete connection_handler;
    // This is a static member function.
    Per_thread_connection_handler::destroy();
    return true;
  }

#ifdef HAVE_PSI_INTERFACE
  int count = static_cast<int>(array_elements(all_conn_manager_mutexes));
  mysql_mutex_register("sql", all_conn_manager_mutexes, count);

  count = static_cast<int>(array_elements(all_conn_manager_conds));
  mysql_cond_register("sql", all_conn_manager_conds, count);
#endif

  mysql_mutex_init(key_LOCK_connection_count, &LOCK_connection_count,
                   MY_MUTEX_INIT_FAST);

  mysql_cond_init(key_COND_connection_count, &COND_connection_count);

  max_threads = connection_handler->get_max_threads();

  // Init common callback functions.
  thr_set_lock_wait_callback(scheduler_wait_lock_begin,
                             scheduler_wait_lock_end);
  thr_set_sync_wait_callback(scheduler_wait_sync_begin,
                             scheduler_wait_sync_end);
  return false;
}

是不是看到了相关的初始化动作，这个是和具体的配置有关系的。然后下来应该就是监听：

Channel_info *Mysqld_socket_listener::listen_for_connection_event() {
#ifdef HAVE_POLL
  int retval = poll(&m_poll_info.m_fds[0], m_socket_vector.size(), -1);
#else
  m_select_info.m_read_fds = m_select_info.m_client_fds;
  int retval = select((int)m_select_info.m_max_used_connection,
                      &m_select_info.m_read_fds, 0, 0, 0);
#endif

  if (retval < 0 && socket_errno != SOCKET_EINTR) {
    /*
      select(2)/poll(2) failed on the listening port.
      There is not much details to report about the client,
      increment the server global status variable.
    */
    ++connection_errors_query_block;
    if (!select_errors++ && !connection_events_loop_aborted())
      LogErr(ERROR_LEVEL, ER_CONN_SOCKET_SELECT_FAILED, socket_errno);
  }

  if (retval < 0 || connection_events_loop_aborted()) return nullptr;

  /* Is this a new connection request ? */
  const Listen_socket *listen_socket = get_listen_socket();
  /*
    When poll/select returns control flow then at least one ready server socket
    must exist. Check that get_ready_socket() returns a valid socket.
  */
  assert(listen_socket != nullptr);
  MYSQL_SOCKET connect_sock;
#ifdef HAVE_SETNS
  /*
    If a network namespace is specified for a listening socket then set this
    network namespace as active before call to accept().
    It is not clear from manuals whether a socket returned by a call to
    accept() borrows a network namespace from a server socket used for
    accepting a new connection. For that reason, assign a network namespace
    explicitly before calling accept().
  */
  std::string network_namespace_for_listening_socket;
  if (listen_socket->m_socket_type == Socket_type::TCP_SOCKET) {
    network_namespace_for_listening_socket =
        (listen_socket->m_network_namespace != nullptr
             ? *listen_socket->m_network_namespace
             : std::string(""));
    if (!network_namespace_for_listening_socket.empty() &&
        set_network_namespace(network_namespace_for_listening_socket))
      return nullptr;
  }
#endif
//这段代码才是干活的  *********
  if (accept_connection(listen_socket->m_socket, &connect_sock)) {
#ifdef HAVE_SETNS
    if (!network_namespace_for_listening_socket.empty())
      (void)restore_original_network_namespace();
#endif
    return nullptr;
  }

#ifdef HAVE_SETNS
  if (!network_namespace_for_listening_socket.empty() &&
      restore_original_network_namespace())
    return nullptr;
#endif

#ifdef HAVE_LIBWRAP
  if ((listen_socket->m_socket_type == Socket_type::TCP_SOCKET) &&
      check_connection_refused_by_tcp_wrapper(connect_sock)) {
    return nullptr;
  }
#endif  // HAVE_LIBWRAP

  Channel_info *channel_info = nullptr;
  if (listen_socket->m_socket_type == Socket_type::UNIX_SOCKET)
    channel_info = new (std::nothrow) Channel_info_local_socket(connect_sock);
  else
    channel_info = new (std::nothrow) Channel_info_tcpip_socket(
        connect_sock, (listen_socket->m_socket_interface ==
                       Socket_interface_type::ADMIN_INTERFACE));
  if (channel_info == nullptr) {
    (void)mysql_socket_shutdown(connect_sock, SHUT_RDWR);
    (void)mysql_socket_close(connect_sock);
    connection_errors_internal++;
    return nullptr;
  }

#ifdef HAVE_SETNS
  if (listen_socket->m_socket_type == Socket_type::TCP_SOCKET &&
      !network_namespace_for_listening_socket.empty())
    static_cast<Channel_info_tcpip_socket *>(channel_info)
        ->set_network_namespace(network_namespace_for_listening_socket);
#endif
  return channel_info;
}

这个函数同样被上面的acceptor中的loop函数调用：

template <typename Listener>
class Connection_acceptor {
  Listener *m_listener;

 public:
  Connection_acceptor(Listener *listener) : m_listener(listener) {}

  ~Connection_acceptor() { delete m_listener; }

  /**
    Initialize a connection acceptor.

    @retval   return true if initialization failed, else false.
  */
  bool init_connection_acceptor() { return m_listener->setup_listener(); }

  /**
    Connection acceptor loop to accept connections from clients.
  */
  void connection_event_loop() {
    Connection_handler_manager *mgr =
        Connection_handler_manager::get_instance();
    while (!connection_events_loop_aborted()) {
      Channel_info *channel_info = m_listener->listen_for_connection_event();
      if (channel_info != nullptr) mgr->process_new_connection(channel_info);
    }
  }
  ......
}

这样看是不是就有一种豁然开朗的感觉。代码要认真看，总结一下，最好有官方的文档辅助，这样更容易。