MySQL views the number of connections, status

Command: Show processlist;

If it is a root account, you can see the current connection of all users. For other common accounts, you can only view the connections you are using.
Show processlist;Only list the first 100 entries. If you want to list all entries, useShow full processlist;

Mysql> show processlist;

 

Command: Show status;

Command: Show status like '% variable % ';

Aborted_clients indicates the number of dropped connections because the client fails to properly close the connection.
The number of connection times of the MySQL server that failed the aborted_connects attempt.
The number of times that connections attempted to connect to the MySQL server.
Created_tmp_tables: Number of implicit temporary tables created when the statement is executed.
The number of threads that delayed_insert_threads is using to insert a delayed processor.
The number of rows written by delayed_writes Using Insert delayed.
The number of rows in which delayed_errors writes data using insert delayed (which may duplicate key values.
The number of times flush_commands executes the flush command.
The number of rows that handler_delete requests to delete from a table.
The number of times the handler_read_first request reads the first row of the table.
The handler_read_key request number is based on the key-read row.
The number of times the handler_read_next request reads a row based on a key.
The number of times the handler_read_rnd request reads a row based on a fixed position.
The number of times handler_update requests to update a row in the table.
The number of times a handler_write request inserts a row into a table.
The number of key_blocks_used Blocks Used for keyword cache.
The number of times key_read_requests requests read a key value from the cache.
The number of times that key_reads reads a key value from the disk physically.
Number of times that key_write_requests requests write a key block to the cache.
The number of times that key_writes physically writes a key-Value block to a disk.
The maximum number of connections simultaneously used by max_used_connections.
Not_flushed_key_blocks has been changed in the key cache but has not been cleared to the disk.
The number of rows that not_flushed_delayed_rows is waiting to write in the insert delay queue.
The number of open tables in open_tables.
The number of open files in open_files.
Number of open streams in open_streams (mainly used for log recording)
The number of open tables in opened_tables.
The number of queries sent by questions to the server.
Slow_queries takes more than long_query_time.
The number of connections currently opened by threads_connected.
The number of threads whose threads_running is not sleeping.
How many seconds does the uptime server work.

 

 

 

Configure virtual memory in my. ini

 

 

 

Innodb_buffer_pool_size = 576 M-> 128 M InnoDB Engine Buffer

Query_cache_size = 100 m-> 32 query Cache
Tmp_table_size = 102 m-> 32 m temporary table size
Key_buffer_size = 16 m-> 8 m

 

Set max_connections

Command: Show variables like '% max_connections %'
(This method is used in Debian + MySQL ver 12.22 distrib 4.0.22, for PC-Linux (i386)
In the experiment)
The setting method is to add the last red line below in the my. CNF file:

 

--------------------------------------------------------------------------------

 

[Mysqld]
Port = 3306
# Socket = MySQL
Skip-locking
Set-variable = key_buffer = 16 K
Set-variable = max_allowed_packet = 1 m
Set-variable = thread_stack = 64 K
Set-variable = table_cache = 4
Set-variable = sort_buffer = 64 K
Set-variable = net_buffer_length = 2 K
Set-variable = max_connections = 32000
(The syntax of mysql4.0 on the Dell machine in the courtyard is different.
Max_connecionts = 2000
Just write it like this.

)

 

 

--------------------------------------------------------------------------------

 

After modification, restart MySQL. Of course, to ensure correct settings, check max_connections.

Note:
1. Although 32000 is written here. However, the actual MySQL server allows a maximum of 16384 connections;
2. In addition to max_connections, the above configurations should be configured according to your system's own needs;
3. added the maximum number of allowed connections, which does not increase the system consumption much.
4. If your MySQL instance uses my. ini as the configuration file, the settings are similar, but the settings must be slightly changed.

 

Use mysqld -- help to view the max_connections variable.
Or mysql-uuser-P
Later mysql> show variables;
Max_connections is also displayed.

The following describes how to modify redhat9 of instructor Zhang:

First, MySQL-uw01f-P
Mysql> show variables;
We can see that max_connections is 100
Mysql> exit;
VI/etc/My. CNF
[Mysqld]
Set-variable = max_connections = 250 # add these content
: WQ

/Etc/init. d/mysqld restart
Okay, all right.

 

The following is a copy. I cannot use it.
The maximum number of connections in MySQL is 100 by default. This value is far from enough for database applications with many concurrent connections. When the number of connection requests exceeds the default number of connections, the database cannot be connected, therefore, we need to increase the value as appropriate. There are two ways to modify the maximum number of connections, one is to modify safe_mysqld, and the other is to directly modify the original code and re-compile it. The following two methods are described respectively:

1. Modify safe_mysqld
Find safe_mysqld and edit it. Find the two lines started by mysqld and add the following parameters:

-O max_connections = 1000

For example: (the preceding content is --- the original content, and ++ is modified)
--- Safe_mysqld.orig mon Sep 25 09:34:01 2000
++ Safe_mysqld sun Sep 24 16:56:46 2000
@-Random, 10 + random, 10 @@
If test "$ #"-EQ 0
Then
Nohup $ ledir/mysqld -- basedir = $ my_basedir_version -- datadir = $ datadir/
--- Skip-locking> $ err_log 2> & 1
+ -- Skip-locking-O max_connections = 1000 >>$ err_log 2> & 1
Else
Nohup $ ledir/mysqld -- basedir = $ my_basedir_version -- datadir = $ datadir/
--- Skip-locking "$ @" >>$ err_log 2> & 1
+ -- Skip-locking "$ @"-O max_connections = 1000 >>$ err_log 2> & 1
Fi
If test! -F $ pid_file # This is removed if normal Shutdown
Then
Disable MySQL and restart it.
/Mysqladmin path/mysqladmin-uroot-P Variables
Enter the password of the root database account.
| Max_connections | 1000 |
That is, the new change has taken effect.

2. modify the original code

Unlock the original MySQL code and go to the SQL directory to modify mysqld. CC. Find the following line:

{"Max_connections", (long *) & max_connections, 1, 1 },

Change it:

{"Max_connections", (long *) & max_connections, 1, 1 },

Save the disk and exit./configure; Make; make install to achieve the same effect.