Is mysql concurrent connection limit per mysql user or limit per 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”.

Answer 2

That's crazy. You can't have 1 database for each user. That's an over kill. (if you are not a hosting provider)

Anyway MySQL suggest to don't over come 4096 concurrent requests per machine. I believe you will have many other problems than database if you will ever have 4096 concurrent requests.

Reference: MySQL Best Pratices