是每个mysql用户的mysql并发连接限制还是每个localhost的限制

Question

I'm new here but got log of benefit from this site. I'm planning on large php/mysql website, but confusing about mysql concurrent connection limit. Is that limit per mysql database or limit per localhost/cpanel account? If it limit per mysql database. So in case I have a dedicated mysql database for each end-user, then for example 1000 databases per 1000 end-user, but I only have 1 mysql-user that grant access to that all 1000 databases. Can it be ok? If it limit per mysql-user. Is pool connections can be a solution this case?

Answer 1

There are system variables to control both. As stated in the manual:

• max_connections

  +----------------------+-----------------------+
  | Command-Line Format  | --max_connections=#   |
  +----------------------+-----------------------+
  | Option-File Format   | max_connections       |
  +----------------------+-----------------------+
  | Option Sets Variable | Yes, max_connections  |
  +----------------------+-----------------------+
  | Variable Name        | max_connections       |
  +----------------------+-----------------------+
  | Variable Scope       | Global                |
  +----------------------+-----------------------+
  | Dynamic Variable     | Yes                   |
  +----------------------+-----------------------+
  |                      | Permitted Values      |
  +                      +---------+-------------+
  |                      | Type    | numeric     |
  +                      +---------+-------------+
  |                      | Default | 151         |
  +                      +---------+-------------+
  |                      | Range   | 1 .. 100000 |
  +----------------------+-----------------------+
  

  The maximum permitted number of simultaneous client connections. By default, this is 151. See Section C.5.2.7, “Too many connections”, for more information.

  Increasing this value increases the number of file descriptors that mysqld requires. See Section 8.4.3.1, “How MySQL Opens and Closes Tables”, for comments on file descriptor limits.

• max_user_connections

  +----------------------+---------------------------+
  | Command-Line Format  | --max_user_connections=#  |
  +----------------------+---------------------------+
  | Option-File Format   | max_user_connections      |
  +----------------------+---------------------------+
  | Option Sets Variable | Yes, max_user_connections |
  +----------------------+---------------------------+
  | Variable Name        | max_user_connections      |
  +----------------------+---------------------------+
  | Variable Scope       | Global, Session           |
  +----------------------+---------------------------+
  | Dynamic Variable     | Yes                       |
  +----------------------+---------------------------+
  |                      | Permitted Values          |
  +                      +---------+-----------------+
  |                      | Type    | numeric         |
  +                      +---------+-----------------+
  |                      | Default | 0               |
  +                      +---------+-----------------+
  |                      | Range   | 0 .. 4294967295 |
  +----------------------+---------------------------+
  

  The maximum number of simultaneous connections permitted to any given MySQL user account. A value of 0 (the default) means “no limit.”

  This variable has a global value that can be set at server startup or runtime. It also has a read-only session value that indicates the effective simultaneous-connection limit that applies to the account associated with the current session. The session value is initialized as follows:

  If the user account has a nonzero MAX_USER_CONNECTIONS resource limit, the session max_user_connections value is set to that limit.

  Otherwise, the session max_user_connections value is set to the global value.

  Account resource limits are specified using the GRANT statement. See Section 6.3.4, “Setting Account Resource Limits”, and Section 13.7.1.3, “GRANT Syntax”.