MySQL database maximum number of connections

Source: Internet
Author: User

After the MySQL database installation is complete, the default maximum number of connections is 100.

Command: Show Processlist;
If it is the root account, you can see the current connection for all users. If you are a normal account, you can only see the connection you are occupying.
Show Processlist; only the first 100 are listed, please use show full processlist if you want to list them all.
Mysql> show Processlist;

Command: Show status;
Command: Show status like '% under variable% ';
Aborted_clients the number of connections that have been discarded because the client did not properly close the connection already dead.
Aborted_connects the number of times the connection to the MySQL server has failed.
Connections the number of attempts to connect to the MySQL server.
Created_tmp_tables the number of suppressed temporary tables that have been created when the statement is executed.
Delayed_insert_threads the number of deferred plug-in processor threads being used.
Delayed_writes the number of rows written with the insert Delayed.
Delayed_errors the number of rows written with insert Delayed for some errors (possibly repeating key values).
Flush_commands the number of times the flush command was executed.
Handler_delete the number of times a row was requested to be deleted from a table.
The number of times the Handler_read_first request reads the first row in the table.
The Handler_read_key requests a number based on the key read line.
The number of times a handler_read_next request reads into a row based on one key.
The number of times a HANDLER_READ_RND request reads into a row based on a fixed position.
Handler_update the number of times a row in the table was requested to be updated.
The number of times the Handler_write request inserts a row into the table.
key_blocks_used the number of blocks used for the keyword cache.
Key_read_requests the number of times a key value was requested to be read from the cache.
Key_reads the number of times a key value has been physically read from disk.
Key_write_requests requests a keyword block to be written to the cache count.
Key_writes the number of times a key-value block is physically written to disk.
Max_used_connections the maximum number of connections that are used at the same time.
Not_flushed_key_blocks a key block that has been changed in the key cache but has not been emptied to disk.
Not_flushed_delayed_rows the number of rows waiting to be written in the Insert delay queue.
Open_tables the number of open tables.
Open_files the number of open files.
Open_streams Number of open streams (primarily for log records)
Opened_tables the number of tables that have been opened.
Questions the number of queries destined for the server.
Slow_queries the number of queries that will take more than long_query_time time.
threads_connected the number of connections currently open.
Threads_running the number of threads that are not sleeping.
Uptime How many seconds the server has worked.

My.ini Configuring virtual Memory
innodb_buffer_pool_size=576m->128m InnoDB engine buffers
query_cache_size=100m->32 Query Cache
tmp_table_size=102m->32m temporary table size
key_buffer_size=16m->8m

Set Max_connections

Method One:
Command: Show variables like '%max_connections% '
The Setup method is to add the following last red line in the My.cnf file:
If we want to view this server setting. #vi/etc/my.cnf
[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

#set-variable=max_user_connections=30 This is the number of single-user connections
#set-variable=max_connections=800 This is the global limit number of connections

Once the modifications are complete, restart MySQL. Of course, to make sure the settings are correct, you should look at max_connections.

Attention:
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.

The max_connections variable can be viewed with mysqld--help.

Method Two:
MySQL maximum number of connections by default is 100 client logon:
Mysql-uusername-ppassword
Mysql> Set GLOBAL max_connections=200
Mysql> Show Processlist
Mysql> Show Status
Mysql> exit
Mysqladmin-uusername-ppassword variables

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.