Close all connections:
# #把全部的MySQL连接kill掉
For I in $ (mysql-uroot-p123456-bse "Show processlist" | awk ' {print $} ');d o mysql-uroot-p123456-e "Kill $i";d on E
# #把admin用户的连接kill掉
For I in $ (mysql-uroot-p123456-se "Show processlist" | awk ' {if ($2== "admin") print '} ');d o mysql-uroot-p123456-e "Kill $i";d One Show Processlist Analysis:mysql> show processlist;+-–+ ————-+ —————— –+| Id | User | Host | db | Command | time| State | Info+-–+ ————-+ —————— –+|207|root |192.168.0.2:51621 |mytest | Sleep | 5 | | NULL|208|root |192.168.0.2:51622 |mytest | Sleep | 5 | | NULL|220|root |192.168.0.2:51676 |mytest | Query | 84 | Locked |Select Name,culture,value,type from book where Id=1describe the meaning and purpose of each column,ID Column: An identity that is useful when you want to kill a statement. User column: Displays the current user, if not root, this command displays only the SQL statements that are within the scope of your permission. Host column: Shows which IP port this statement was issued from. Users that can be used to track the problem statement. db column: Shows which database this process is currently connected to. Command column: Displays commands for the execution of the current connection, typically hibernation (sleep), query, connection (connect). Time Column: The duration of this state, in seconds. State column: Displays the status of the SQL statement using the current connection, the very important column, followed by a description of all States, note that State is only one of the states in the statement execution, an SQL statement that has been queried as an example, may need to go through copying to TMP table, Sorting result,sending data and other states can be completed. Info column: This SQL statement is displayed because the length is limited, so long SQL statements are incomplete, but an important basis for judging the problem statement. The most critical of this command is the State column, which is listed in the following categories :Checking TableChecking the data table (this is automatic). Closing TablesThe modified data in the table is being flushed to disk, and the tables that have been exhausted are being closed. This is a quick operation, and if not, you should confirm that the disk space is full or that the disk is under heavy load. Connect outreplication from the server is connecting to the primary server. Copying to TMP table on diskbecause the temporary result set is larger than tmp_table_size, the temporary table is being converted from memory storage to disk storage to save memory. Creating tmp tablecreating temporary tables to hold partial query results. deleting from Main TableThe server is performing the first part of a multi-table delete and has just deleted the first table. deleting from reference tablesThe server is performing the second part of a multi-table delete and is deleting records from other tables. Flushing Tablesexecuting FLUSH TABLES, waiting for other threads to close the data table. killedsends a KILL request to a thread, the thread checks the kill flag bit and discards the next kill request. MySQL checks the kill flag bit in each of the main loops, but in some cases the thread may die in a short period of time. If the line regulation regulation is locked by another thread, the kill request will take effect as soon as the lock is released. Lockedlocked by another query. Sending Datathe record for the SELECT query is being processed, and the results are being sent to the client. Sorting for groupsorting is being done for GROUP by. Sorting for orderthe order by is being sorted. Opening Tablesthe process should be quick, unless other factors interfere with it. For example, a data table cannot be opened by another thread until the row of the ALTER table or LOCK table statement is complete. Attempting to open a table. Removing duplicatesa query that is executing a SELECT DISTINCT method is being executed, but MySQL cannot optimize those duplicate records in the previous phase. Therefore, MySQL needs to remove the duplicate records again, and then send the results to the client. Reopen Tablea lock on a table is obtained, but it must be changed after the table structure has been modified. The lock has been released, the data table is closed, and the data table is being tried again. Repair by sortingrepair instructions are being sorted to create an index. Repair with KeycacheThe repair instructions are using the index cache to create a new index one by one. It will be slower than Repair by sorting. searching rows for updatethe qualifying records are being told to find out to prepare for the update. It must be completed before the UPDATE is about to modify the related records. Sleepingwaiting for the client to send a new request.System Lockis waiting to get an external system lock. If you are not currently running multiple mysqld servers requesting the same table at the same time, you can suppress the external system lock by increasing the –skip-external-locking parameter. Upgrading LockInsert DELAYED is trying to get a lock table to insert a new record. Updatingsearching for matching records, and modifying them. User Lockwaiting for Get_lock (). Waiting for Tablesthe thread is notified that the data table structure has been modified and the data table needs to be reopened to obtain a new structure. Then, to be able to reopen the data table, you must wait until all other threads close the table. This notification is generated in the following cases: FLUSH TABLES tbl_name, ALTER table, RENAME table, REPAIR table, ANALYZE table, or OPTIMIZE table. waiting for handler insertInsert DELAYED has processed all pending insertions and is waiting for a new request. most of the state corresponds to a fast operation, so long as one thread remains in the same state for several seconds, a problem may occur and need to be checked. There are other states that are not listed above, but most of them are only useful to see if there is an error in the server.
view MySQL number of connections and other commandscommand: 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. Www.2cto.comshow Processlist; only the first 100 are listed, please use show full processlist if you want to list them all.mysql> show processlist;Show Status Analysis: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 memorywww.2cto.cominnodb_buffer_pool_size=576m->128m InnoDB engine buffersquery_cache_size=100m->32 Query Cachetmp_table_size=102m->32m temporary table sizekey_buffer_size=16m->8m Set Max_connectionscommand: Show variables like '%max_connections% '(This method in Debian+mysql Ver 12.22 distrib 4.0.22, for Pc-linux (i386)in the experiment)The Setup method is to add the following last red line in the my.cnf file:--------------------------------------------------------------------------------www.2cto.com[Mysqld]port=3306#socket =mysqlskip-lockingset-variable = key_buffer=16kset-variable = max_allowed_packet=1mset-variable = thread_stack=64kset-variable = table_cache=4set-variable = sort_buffer=64kset-variable = net_buffer_length=2kset-variable = max_connections=32000(The syntax is different in the Dell machine mysql4.0 in the yardmax_connecionts=2000just write it straight, www.2cto.com.)--------------------------------------------------------------------------------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. www.2cto.comthe max_connections variable can be viewed with mysqld--help. or Mysql-uuser-p .after mysql>show variables;will also see max_connections.
MySQL connection operation
