问题1: 连接数为214， 登录经常报错 too many connections

在公司MySQL企业版服务化开发的初期，我们曾经遇到一个问题，即在连接MySQL的时候报错 too many connections, 即使是新安装的MySQL。在以前的社区版MySQL也曾经遇到过类似的问题，当时MySQL是用rpm安装并使用systemd启动的方式。 此次企业版的MySQL启动并未托管到systemd，因此解决办法不能照搬。

定位过程

为了能够登录，首先只能重启MySQL，执行

show variables like "max_conne%";

发现连接数并非配置文件中定义的 2000，而是一个奇怪的数字 214；

执行

ulimit -a 或者 cat /proc/pidof mysqld/limits

发现 open files 为一个较低的默认值 1024；（代码中有改动该值的逻辑，但是最终并未生效，最终发现是公司系统镜
像/etc/security/limits.d/...的默认值有问题，此处不延伸）

core file size (blocks, -c) 0 data seg size (kbytes, -d) unlimited scheduling priority (-e) 0 file size (blocks, -f) unlimited pending signals (-i) 23883 max locked memory (kbytes, -l) 64 max memory size (kbytes, -m) unlimited open files (-n) 1024 pipe size (512 bytes, -p) 8 POSIX message queues (bytes, -q) 819200 real-time priority (-r) 0 stack size (kbytes, -s) 8192 cpu time (seconds, -t) unlimited max user processes (-u) 23883 virtual memory (kbytes, -v) unlimited file locks (-x) unlimited

=== 为什么是214？
查看mysqld.cc:adjust_max_connections发现原因:

[source, c++]

void adjust_max_connections(ulong requested_open_files) { ulong limit;

// TABLE_OPEN_CACHE_MIN = 400
// requested_open_files = 1024
limit= requested_open_files - 10 - TABLE_OPEN_CACHE_MIN * 2;

if (limit < max_connections)
{
  sql_print_warning("Changed limits: max_connections: %lu (requested %lu)",
                    limit, max_connections);

    // This can be done unprotected since it is only called on startup.
    max_connections= limit;
  }
}

计算很简单，看一下此处，应该会打印出一行警告日志，可以试试看，日志中是否可以找到这样的信息。

sql_print_warning(“Changed limits: max_connections: %lu (requested %lu)”, limit, max_connections);

值得关注的点是，这个requested_open_files有一个比较复杂的计算过程。

.mysqld.cc:adjust_related_options
[source, c++]

void adjust_related_options(ulong requested_open_files) { / In bootstrap, disable grant tables (we are about to create them) */ if (opt_bootstrap) opt_noacl= 1;

  /* The order is critical here, because of dependencies. */
  adjust_open_files_limit(requested_open_files);
  adjust_max_connections(*requested_open_files);
  adjust_table_cache_size(*requested_open_files);
  adjust_table_def_size();
}

.mysqld.cc:adjust_open_files_limit
[source, c++]

/** Adjust @c open_files_limit. Computation is based on: - @c max_connections, - @c table_cache_size, - the platform max open file limit. */ void adjust_open_files_limit(ulong *requested_open_files) { ulong limit_1; ulong limit_2; ulong limit_3; ulong request_open_files; ulong effective_open_files;

/*这里会有三种计算方案*/

/* MyISAM requires two file handles per table. */
limit_1= 10 + max_connections + table_cache_size * 2;

/*
  We are trying to allocate no less than max_connections*5 file
  handles (i.e. we are trying to set the limit so that they will
  be available).
*/
limit_2= max_connections * 5;

/* Try to allocate no less than 5000 by default. */
//这里可以解释了，为什么很多的系统安装后， /proc/`pidof mysqld`/limits中的值为5000
//但是这里的代码，是否应该改为 open_files_limit> 5000 ? open_files_limit : 5000;
limit_3= open_files_limit ? open_files_limit : 5000;

// 取三种方案的最大值
request_open_files= max<ulong>(max<ulong>(limit_1, limit_2), limit_3);

/* Notice: my_set_max_open_files() may return more than requested. */
effective_open_files= my_set_max_open_files(request_open_files);

if (effective_open_files < request_open_files)
{
  if (open_files_limit == 0)
  {
    sql_print_warning("Changed limits: max_open_files: %lu (requested %lu)",
                      effective_open_files, request_open_files);
  }
  else
  {
    sql_print_warning("Could not increase number of max_open_files to "
                      "more than %lu (request: %lu)",
                      effective_open_files, request_open_files);
  }
}

  open_files_limit= effective_open_files;
  if (requested_open_files)
    *requested_open_files= min<ulong>(effective_open_files, request_open_files);
}

.my_file.c:my_set_max_open_files
[source, c++]

uint my_set_max_open_files(uint files) { struct st_my_file_info *tmp; DBUG_ENTER(“my_set_max_open_files”); DBUG_PRINT(“enter”,(“files: %u my_file_limit: %u”, files, my_file_limit));

files+= MY_FILE_MIN;
files= set_max_open_files(MY_MIN(files, OS_FILE_LIMIT));
if (files <= MY_NFILE)
  DBUG_RETURN(files);

if (!(tmp= (struct st_my_file_info*) my_malloc(key_memory_my_file_info,
                                               sizeof(*tmp) * files,
                                               MYF(MY_WME))))
  DBUG_RETURN(MY_NFILE);

  /* Copy any initialized files */
  memcpy((char*) tmp, (char*) my_file_info,
         sizeof(*tmp) * MY_MIN(my_file_limit, files));
  memset((tmp + my_file_limit), 0,
        MY_MAX((int) (files - my_file_limit), 0) * sizeof(*tmp));
  my_free_open_file_info();                     /* Free if already allocated */
  my_file_info= tmp;
  my_file_limit= files;
  DBUG_PRINT("exit",("files: %u", files));
  DBUG_RETURN(files);
}

因此，如果希望max_connections=2000，requested_open_files不能小于stem:[2000+10+400 \times 2=2810].