Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to view process connections in mysql". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Now let the editor take you to learn "how to view process connections in mysql"!

Command: show processlist

If it is a root account, you can see the current connections of all users. If it is another ordinary account, you can only see the connection occupied by yourself.

Show processlist; only lists the first 100 items. If you want to list them all, please use show full processlist.

MySQL > show processlist

Command: show status

Command: show status like'%% the following variable%'

Aborted_clients the number of connections that have been abandoned because the customer did not close the connection correctly.

The number of times Aborted_connects has attempted a connection to the MySQL server that has failed.

The number of times Connections attempted to connect to the MySQL server.

Created_tmp_tables the number of implicit temporary tables that have been created when the statement is executed.

The number of latency insertion processor threads being used by Delayed_insert_threads.

The number of rows written by Delayed_writes in INSERT DELAYED.

The number of rows written by Delayed_errors with INSERT DELAYED for which some errors (possibly duplicate key values) have occurred.

The number of times Flush_commands executed the FLUSH command.

The number of times Handler_delete requests to delete rows from a table.

The number of times Handler_read_first requests to read the first row in the table.

The Handler_read_key request number is based on the key reading line.

The number of times Handler_read_next requests to read a row based on a key.

The number of times Handler_read_rnd requests to read a row based on a fixed location.

The number of times Handler_update requests to update a row in the table.

The number of times Handler_write requests to insert a row into the table.

The number of blocks used by Key_blocks_used for keyword caching.

The number of times Key_read_requests requests to read a key value from the cache.

The number of times Key_reads physically reads a key value from disk.

Key_write_requests requests the number of times a keyword block is written to the cache.

The number of times Key_writes physically writes a key block to disk.

The maximum number of connections used simultaneously by Max_used_connections.

Not_flushed_key_blocks keyblocks that have been changed in the key cache but have not been emptied to disk.

The number of rows that Not_flushed_delayed_rows is waiting to write in the INSERT DELAY queue.

The number of Open_tables open tables.

The number of files opened by Open_files.

Number of Open_streams open streams (mainly used for logging)

The number of tables that Opened_tables has opened.

The number of queries sent by Questions to the server.

The number of queries that Slow_queries takes longer than long_query_time time.

The number of connections currently open by Threads_connected.

The number of threads that Threads_running is not sleeping.

How many seconds did the Uptime server work?

My.ini configure virtual memory

Innodb_buffer_pool_size=576M-> 128m InnoDB engine buffer

Query_cache_size=100M-> 32 query cache

Tmp_table_size=102M-> 32m temporary table size

Key_buffer_size=16m-> 8m

Set up max_connections

Command: show variables like'% max_connections%'

(this method is available at debian+mysql Ver 12.22 Distrib 4.0.22, for pc-Linux (i386)

In the experiment)

The setting 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

(the syntax is different in mysql4.0, the DELL machine in the courtyard

Max_connecionts=2000

Just write it this way.

)

After the modification, restart MySQL. Of course, to make sure the settings are correct, you should take a look at max_connections.

Note:

1. Although 32000 is written here. But the maximum number of connections allowed by the actual MySQL server is 16384

2. In addition to max_connections, the other configurations mentioned above should be configured according to the needs of your system.

3. The maximum number of allowable connections is added, which does not increase the consumption of the system.

4. If your mysql uses my.ini as a configuration file, the setting is similar, but the format should be slightly modified.

You can view the max_connections variable with mysqld-- help.

Or mysql-uuser-p

Post mysql > show variables

You will also see max_connections.

Here is how to modify Mr. Zhang's redhat9:

First mysql-uw01f-p

Mysql > show variables

See that the max_connections is 100

Mysql > exit

Vi / etc/my.cnf

[mysqld]

Set-variable=max_connections=250 # add this content

: wq

/ etc/init.d/mysqld restart

All right, all right.

The one below is copied. I can't use it.

The maximum number of connections in mysql is 100 by default, which is not enough for database applications with many concurrent connections. When the connection request is greater than the default number of connections, there will be an error that you cannot connect to the database, so we need to make it larger appropriately. There are two ways to modify the maximum number of connections, one is to modify safe_mysqld, the other is to directly modify the original code and recompile. Let's introduce these two methods respectively:

1. Modify safe_mysqld

Find safe_mysqld and edit it, find the two lines where mysqld starts, and add parameters at the end:

-O max_connections=1000

For example: (the content preceded by-- is the original content, while + is modified)

-safe_mysqld.orig Mon Sep 25 09:34:01 2000

+ + safe_mysqld Sun Sep 24 16:56:46 2000

@ @-109, 10 + 109, 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

Then close mysql and restart it, using the

/ path where mysqladmin is located / mysqladmin-uroot-p variables

After entering the password of the root database account, you can see

| | max_connections | 1000 | |

That is, the new changes have come into effect.

two。 Modify the original code

Unlock the original code of MySQL, enter the sql directory inside and modify mysqld.cc to find the following line:

{"max_connections", (long*) & max_connections,1000,1,16384,0,1}

Change it to:

{"max_connections", (long*) & max_connections,1000,1,16384,0,1}

Save and exit, and then. / configure; make;make install can get the same effect.

At this point, I believe you have a deeper understanding of "how to view process connections in mysql". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.