MySQL settings/view MySQL connection number

MySQL database connection Too many causes the system error, the system can not connect to the database, the key to see two of data:
1, the database system allows the maximum number of connections max_connections. This parameter can be set. If not set, the default is 100. The maximum is 16384.
2, the database current number of connection threads threads_connected. This is a dynamic change.
See Max_connections, max_connections the way to see later.
if threads_connected = = max_connections, the database system can not provide more connections, at this time, if the program also want to create a new connection thread, the database system will be rejected, if the program did not do too much error handling, There will be similar to the strong altar error message.
because the connection to the database is created and destroyed, the resources of the system are consumed. And to avoid having too many connection threads open at the same time, programming now generally uses the so-called database connection pooling technology.
However, database connection pooling technology does not prevent program errors from draining the connection resources.
This situation usually occurs when the program fails to release the database connection resource in a timely manner or otherwise causes the database connection resource to not be released, but the strong altar system estimates that this low-level programming error will not occur.
An easy way to check this error is to constantly monitor threads_connected changes as you refresh the strong altar page. If the max_connections is large enough and the threads_connected value is increasing to max_connections, then the program should be checked. Of course, if you use database connection pooling technology, the threads_connected grows to the maximum number of connection threads in the database connection pool, no longer grows.
from the case of a strong altar error, the larger possibility is that the database system is not properly configured. A few suggestions are made below. For reference
Let your engineers tune the maximum allowable number of connections for MySQL from the default of 100 to 32000. This will not be the old connection too much problem.
View Max_connections
go to MySQL, with the command:
Show Variables
to view the variable value of the maximum number of connections for a database:
max_connections
View threads_connected
go to MySQL, with the command:
Show Status
to view the value of the currently active connection thread variable:
threads_connected
Set Max_connections
The Setup method is to add the following last red line in the my.cnf file:
[Mysqld]
port=3306
#socket =mysql
skip-locking
set-variable = key_buffer=16k
set-variable = max_allowed_packet=1m
set-variable = thread_stack=64k
set-variable = table_cache=4
set-variable = sort_buffer=64k
set-variable = net_buffer_length=2k
set-variable = max_connections=32000
once the modifications are complete, restart MySQL. Of course, to make sure the settings are correct, you should look at max_connections.
Note:
1, although the 32000 written here. But the actual MySQL server allowed the maximum number of connections 16384;
2, in addition to Max_connections, the above other configuration should be based on your system needs to be configured, do not have to rigidly;
3, add the maximum allowable number of connections, the system consumption increase is not small.
4. If your MySQL is using My.ini as the configuration file, the settings are similar, but the format of the settings to be slightly flexible.

Method Two: MySQL maximum number of connections by default is 100 client login: Mysql-uusername-ppassword

Set the new maximum number of connections to 200:mysql> set GLOBAL max_connections=200

Displays the currently running query:mysql> show processlist

Show current status:mysql> show status

Exit Client:mysql> Exit

To view the maximum number of connections currently: Mysqladmin-uusername-ppassword variables

Method Three: In CentOS 4.4 below the MySQL 5.0.33 manually compiled version for example description:

Vi/usr/local/mysql/bin/mysqld_safe

Find Safe_mysqld edit it, find the two lines that mysqld started, and add the following parameters:

　　-O max_connections=1500

The specific point is the following position:

Special description in Scarlet Letter:

Then $NOHUP _niceness $ledir/$MYSQLD

$defaults--basedir= $MY _basedir_version

--datadir= $DATADIR $USER _option

--pid-file= $pid _file

--skip-external-locking

　　-O max_connections=1500

>> $err _log 2>&1 Else

Eval "$NOHUP _niceness $ledir/$MYSQLD

$defaults--basedir= $MY _basedir_version

--datadir= $DATADIR $USER _option

--pid-file= $pid _file

--skip-external-locking $args

　　-O max_connections=1500 >>

$err _log 2>&1 "

Save.

# Service Mysqld Restart

　　#/usr/local/mysql/bin/mysqladmin-uroot-p variables

Enter the password of the root database account to see

Max_connections 1500 that the new changes are in effect.

There is also a way to

Modify the original code:

To unlock the original MySQL code, go inside the SQL directory to modify mysqld.cc to find the following line:

{"Max_connections", Opt_max_connections,

"The number of simultaneous clients allowed.", (gptr*) &max_connections,

(gptr*) &max_connections, 0, Get_ulong, Required_arg, 100, 1, 16384, 0, 1,

0},

Change it to:

{"Max_connections", Opt_max_connections,

"The number of simultaneous clients allowed.", (gptr*) &max_connections,

(gptr*) &max_connections, 0, Get_ulong, Required_arg, 1500, 1, 16384, 0, 1,

0},

Save to exit, then./configure; Make;make install can achieve the same effect.