Mysql连接建立与thread cache唤醒原理

最新推荐文章于 2024-09-27 11:30:45 发布

原创最新推荐文章于 2024-09-27 11:30:45 发布 · 407 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#连接 #thread cache #mysql

mysql 专栏收录该内容

28 篇文章

订阅专栏

本文解析了MySQL中连接处理流程，包括监听循环、连接接受、线程池管理、线程缓存机制及其工作原理。深入探讨了如何利用线程缓存提高并发性能。

从main函数说起。

1 监听loop启动

Thread 1 监听socket，协议栈的连接上来后，使用现有的或新建线程处理连接。

main
  |
  mysqld_main
    |
    [connection_handler_per_thread.cc]mysqld_socket_acceptor->connection_event_loop();
      > 主进程监听连接循环while (!abort_loop)
      > 创建mgr=Connection_handler_manager::get_instance()
      > 创建channel_info= m_listener->listen_for_connection_event()
      |
      [connection_handler_manager.cc]mgr->process_new_connection(channel_info);
        |
        [do.]check_and_incr_conn_count()
          > 如果有空余连接，connection_accepted=true
          > 如果没有空余连接，结束处理流程
        [connection_handler_per_thread.cc]m_connection_handler->add_connection(channel_info)
          | 
          [do.]Per_thread_connection_handler::add_connection(Channel_info* channel_info)
            |
            [do.]check_idle_thread_and_enqueue_connection(channel_info)
              > LOCK_thread_cache锁保护下面代码
              > 如果blocked_pthread_count > wake_pthread
                > wake_pthread++;
                > waiting_channel_info_list->push_back(channel_info);
                > mysql_cond_signal(&COND_thread_cache);
                  > cond: sig 1129270852
            [do.]error= mysql_thread_create
              > 如果有空余的线程，直接return，不走这一步

启动或使用现有的线程处理新连接，如果有cache线程：

3 thread cache处理

如果没有空闲线程

2 新线程处理

2 新线程处理

[pfs.cc]extern "C" void* pfs_spawn_thread(void *arg) 
  |
  [pfs.cc](*user_start_routine)(user_arg); 
    > (user_start_routine=extern "C" void *handle_connection(void *arg))
    > (user_arg=Channel_info / m_connect_sock)
    | 
    [connection_handler_per_thread.cc]extern "C" void *handle_connection(void *arg) 
      > 用channelinfo拼接一个非常重要的结构体THD *thd= init_new_thd(channel_info)
      |
      [sql_parse.cc]do_command(thd) 
        > thd=连接信息、SQL信息等
        |
        [sql_parse.cc]dispatch_command(thd, &com_data, command) 
          |
          ... 连接已经完成，后面不在继续分析

3 thread cache处理

预留线程hold与启动流程：

3.1 holding

[connection_handler_per_thread.cc]handle_connection()
  |
  [connection_handler_per_thread.cc]block_until_new_connection()
    |
    [do.]mysql_cond_wait(&COND_thread_cache, &LOCK_thread_cache);
      |
      []my_cond_wait
        |
        []safe_cond_wait
          |
          []native_cond_wait
            |
            []pthread_cond_wait(cond, mutex)
              > 等待信号唤醒
              > cond: sig 1129270852
              > mutex: __sig 1297437786

3.2 avtiving

1线程发出mysql_cond_signal(&COND_thread_cache);和wake_pthread++;是唤醒的关键

唤醒保障机制，注意看代码

  // 注意！Poxis线程唤醒不保证只唤醒一个，所以有两重机制保障。
  // 1. 醒了后会直接加mutex锁，只有第一个醒的能拿到，其他会阻塞。
  // 2. 醒了的线程会把while条件回复，然后在出临界区（解锁）
  // 3. 其他线程醒了或醒了拿到锁后，while条件已经被恢复了，所以继续睡。

唤醒后的处理

36、37、38线程默认是空闲的，三个线程激活一个，拿出chanel_info开始处理

Channel_info* Per_thread_connection_handler::block_until_new_connection()
{
  Channel_info *new_conn= NULL;
  mysql_mutex_lock(&LOCK_thread_cache);
  if (blocked_pthread_count < max_blocked_pthreads &&
      !kill_blocked_pthreads_flag)
  {
    ...
    ...
    ...

    // Block pthread
    blocked_pthread_count++;
    while (!abort_loop && !wake_pthread && !kill_blocked_pthreads_flag)
      
      // 这里等信号
      // 注意！Poxis线程唤醒不保证只唤醒一个，所以有两重机制保障。
      // 1. 醒了后会直接加mutex锁，只有第一个醒的能拿到，其他会阻塞。
      // 2. 醒了的线程会把while条件回复，然后在出临界区（解锁）
      // 3. 其他线程醒了或醒了拿到锁后，while条件已经被恢复了，所以继续睡。
      mysql_cond_wait(&COND_thread_cache, &LOCK_thread_cache);
    blocked_pthread_count--;

    if (kill_blocked_pthreads_flag)
      mysql_cond_signal(&COND_flush_thread_cache);
    else if (wake_pthread)
    {
      // wake技术器减少1
      wake_pthread--;
      if (!waiting_channel_info_list->empty())
      {
        // 如果waiting_channel_info_list不是空的
        // 拿出来第一个等待信息，对应上面的waiting_channel_info_list->push_back(channel_info)
        new_conn = waiting_channel_info_list->front();
        
        // 删掉已经使用过的
        waiting_channel_info_list->pop_front();
        DBUG_PRINT("info", ("waiting_channel_info_list->pop %p", new_conn));
      }
      else
      {
        DBUG_ASSERT(0);
      }
    }
  }
  mysql_mutex_unlock(&LOCK_thread_cache);
  return new_conn;
}

3 thread cache说明

doc

mysql> show global status like 'thread%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 0     |
| Threads_connected | 1     |
| Threads_created   | 1     |
| Threads_running   | 1     |
+-------------------+-------+

thread_cache_size

How many threads the server should cache for reuse. When a client disconnects, the client’s threads are put in the cache if there are fewer than thread_cache_size threads there. Requests for threads are satisfied by reusing threads taken from the cache if possible, and only when the cache is empty is a new thread created. This variable can be increased to improve performance if you have a lot of new connections. Normally, this does not provide a notable performance improvement if you have a good thread implementation. However, if your server sees hundreds of connections per second you should normally set thread_cache_size high enough so that most new connections use cached threads. By examining the difference between the Connections and Threads_created status variables, you can see how efficient the thread cache is. For details, see Section 5.1.9, “Server Status Variables”.

The default value is based on the following formula, capped to a limit of 100:

8 + (max_connections / 100)

This variable has no effect for the embedded server (libmysqld) and as of MySQL 5.7.2 is no longer visible within the embedded server.

ps. 信号处理

mysql_cond_signal(&COND_thread_cache);
  |
  [mysql_thread.h]inline_mysql_cond_signal
    |
    [mysql_thread.h]result= native_cond_signal(&that->m_cond);
      |
      [thr_cond.h]pthread_cond_signal
        |
        [mysql_thread.h]result= native_cond_signal(&that->m_cond);
          ｜
          [thr_cond.h]pthread_cond_wait(cond, mutex)
            |
            [pthread]pthread_cond_wait