Search in book...
Toggle Font Controls
Create new playlist

Name your new playlist

Playlist description (optional)
Sign In

Email address

Password

Forgot Password?

or

Continue with Facebook

Continue with Google
Sign Up

Full Name

Email address

Confirm Email Address

Password

or

Continue with Facebook

Continue with Google

Monitoring connections

The SHOW PROCESSLIST statement returns information about the active connections. The PROCESSLIST table in the information_schema database contains additional complete information; we will discuss this. Also, the threads table in the performance_schema database includes the same information returned by SHOW PROCESSLIST, plus some extra columns. Normally, SHOW PROCESSLIST returns sufficient information and is less verbose. The PROCESSLIST table has the advantage that its contents can be used in a stored program.

The following example shows the contents of the PROCESSLIST table and the output of SHOW PROCESSLIST:

MariaDB [(none)]> SELECT * FROM information_schema.PROCESSLIST G

*************************** 1. row ***************************

           ID: 5

         USER: root

         HOST: localhost

           DB: NULL

      COMMAND: Query

         TIME: 0

        STATE: Filling schema table

         INFO: SELECT * FROM information_schema.PROCESSLIST

      TIME_MS: 0.652

        STAGE: 0

    MAX_STAGE: 0

     PROGRESS: 0.000

  MEMORY_USED: 82920

EXAMINED_ROWS: 0

     QUERY_ID: 11

1 row in set (0.00 sec)



MariaDB [(none)]> SHOW PROCESSLIST G

*************************** 1. row ***************************

      Id: 5

    User: root

    Host: localhost

      db: NULL

 Command: Query

    Time: 0

   State: closing tables

    Info: SHOW PROCESSLIST

Progress: 0.000

1 row in set (0.00 sec)

The following table shows the descriptions of the columns of the PROCESSLIST table:

PROCESSLIST table column	SHOW PROCESSLIST statement column	Description
`ID`	`Id`	This shows the connection ID.
`USER`	`User`	This shows the username as it appears after `SELECT USER()`. The `event_scheduler` variable is displayed for the process that executes events.
`HOST`	`Host`	This shows the hostname as it appears after `SELECT USER()`.
`DB`	`db`	This shows the default database (that changes after the USE statement).
`COMMAND`	`Command`	This shows the type of the issued command (see the next table for the possible values).
`TIME`	`Time`	This shows the seconds elapsed since the process switched to the current state.
`STATE`	`State`	This shows the state of the process (see the table in the States of the process section for the possible values).
`INFO`	`Info`	This shows the statement that is being executed, if any. Unless `SHOW FULL PROCESSLIST` is executed (with the `FULL` option), only the first 100 characters are displayed.
`TIME_MS`		This is similar to `TIME`, but is expressed in milliseconds.
`STAGE, MAX_STAGE`		If the statements support the progress reporting, the job can consist of multiple stages. These columns provide the number of the current stage and final stage.
`PROGRESS`	`Progress`	If the statement supports the progress reporting, this is the percentage of the completed job (as `FLOAT`).
`MEMORY_USED`		This shows the quantity of memory used by this process. This feature is added in MariaDB 10.0.
`EXAMINED_ROWS`		This shows the number of rows read by the process and is added in MariaDB 10.0.
`QUERY_ID`		This shows the ID of the current statement, if any, being executed. This feature is added in MariaDB 10.0.

Querying the threads table causes no locks. However, note that activating the performance_schema database causes an overhead, which affects most server activities. This is the reason why performance_schema is disabled by default in MariaDB 10.0. It stores information about all threads, including internal threads, and not only the connections with the clients. The threads table contains all the columns from SHOW PROCESSLIST, written in uppercase with the PROCESSLIST_ prefix. It also contains the following extra columns:

THREAD_ID: This is the ID of the thread. It is different from the value of PROCESSLIST_ID, which is the value that is shown by SHOW PROCESSLIST.
NAME: This indicates the thread types. There are many possible values that reflect the internal MariaDB structures. For example, the value for a thread that is associated to a client connection is thread/sql/one_connection.
TYPE: This is BACKGROUND for internal threads and FOREGROUND for threads that are visible via SHOW PROCESSLIST.
PARENT_THREAD_ID: This is the ID of the thread that created it.
ROLE: This is always NULL. These are currently ignored in the performance_schema database.
INSTRUMENTED: This indicates whether the thread activities are tracked in the performance_schema database.

The THREAD_ID column can be used to join the threads table with other tables in the performance_schema database. Consider the following example:

SELECT *
  FROM performance_schema.events_statements_current s
  LEFT JOIN performance_schema.threads t
  ON s.THREAD_ID = t.THREAD_ID;

The following table describes the values of the COMMAND column:

Value	Description
`Binlog Dump`	This means that the process sends the binary log contents to a slave
`Change user`	This means that the process changes the current user
`Close stmt`	This means that the process deallocates a prepared statement
`Connect`	This means that the process is a slave thread connected to a master
`Connect Out`	This means that the process is a slave process connected to a master
`Creating DB`	This means that the process creates a database
`Daemon`	This means that the process itself is an internal thread
`Debug`	This means that the process generates the debug information
`Delayed insert`	This means that the process executes an `INSERT DELAYED` statement
`Drop DB`	This means that the process erases a database
`Error`	This means that a fatal error has occurred in the process
`Execute`	This means that the process executes a prepared statement
`Fetch`	This means that the process fetches rows from the result of a prepared statement
`Field List`	This means that the process retrieves information about table columns
`Init DB`	This means that the process changes the default database
`Kill`	This means that the process terminates another process
`Long Data`	This means that the process retrieves a large amount of data from prepared statement results
`Ping`	This means that the process replies to a ping
`Prepare`	This means that the process makes a prepared statement
`Processlist`	This means that the process gathers information about the existing processes
`Query`	This means that the process executes a statement
`Quit`	This means that the process exits
`Refresh`	This means that the process flushes tables, hosts, or caches; or resets status variables or replication information
`Register Slave`	This means that the process registers a new slave
`Reset stmt`	This means that the process resets a prepared statement
`Set option`	This means that the process changes a connection option
`Shutdown`	This means that the process stops the server
`Sleep`	This means that the process waits for a new statement from the client
`Statistics`	This means that the process gathers information about the server's status
`Table dump`	This means that the process sends a whole table to a slave

States of the process

The STATE column indicates exactly what the state is doing. It has several possible values. Here, we only list the main values:

Value	Description
`After create`	This means that the process creates a table, possibly an internal temporary table.
`altering table`	This means that the process alters a table.
`Analyzing`	This means that the process calculates the indexes distributions of a table.
`checking permissions`	This means that the process checks whether the account has the necessary permissions to execute a statement.
`Checking table`	This means that the process executes a `CHECK TABLE` command.
`cleaning up`	This means that the process frees memory after a statement's execution.
`closing tables`	This means that the process flushes a table's data to a disk. If this operation takes a lot of time, the disk might be full or very busy. On some filesystems, it might also mean that a process is blocked by the journaling block device.
`committing alter table to storage engine`	This means that the server finishes an `ALTER TABLE` command and is committing the changes to a storage engine.
`converting HEAP to MyISAM`	This means that the process converts a `MEMORY` temporary table to a `MyISAM` on-disk temporary table (`HEAP` is the old name of `MEMORY`).
`copy to tmp table`	This means that the process executes an `ALTER TABLE` command, which requires a table copy.
`Copying to group table`	This means that the process orders the results of a query involving `GROUP BY` using a temporary table.
`Copying to tmp table`	This means that the process populates an in-memory temporary table.
`Copying to tmp table on disk`	This means that the process populates an internal on-disk temporary table.
`Creating sort index`	This means that the process creates an index for an internal temporary table.
`Creating table`	This means that the process creates a table, possibly a temporary table.
`Creating tmp table`	This means that the process creates an internal temporary table.
`Creating sort index`	This means that the process creates an index for an internal temporary table.
`deleting from main table`	This means that the process deletes rows from the first table in a `DELETE` statement.
`deleting from reference tables`	This means that the process deletes rows from a table (not the first one) in a `DELETE` statement.
`discard_or_import_tablespace`	This means that the process executes `ALTER TABLE … DISCARD/IMPORT TABLESPACE`.
`end`	This means that a statement has just finished its execution but the memory has not been cleaned up.
`executing`	This means that the process executes a statement.
`freeing items`	This means that the process cleans up a statement that involves the query cache.
`flushing tables`	This means that the process waits for other threads to finish their statements, before executing `FLUSH TABLES`.
`init`	This means that the process prepares to execute a statement.
`Killed`	This means that a `KILL` command (described in the Aborting connections section) has been executed against this process. It is waiting for a lock, or executing some actions before disappearing.
`logging slow query`	This means that the process logs a statement into the slow query log.
`login`	This means that a user has logged in.
`Opening table`	This means that the process opens a table. If this operation is not very fast, the table is probably locked.
`optimizing`	This means that the optimizer determines the statement execution plans.
`preparing`	This is the stage which precedes optimization; the statement is parser and names are resolved.
`preparing alter table`	This means that the process performs some preliminary operations before an `ALTER TABLE` command.
`Purging old relay logs`	This means that the process removes old relay logfiles.
`query end`	This means that the process cleans up the memory after a statement, which precedes freeing items.
`Removing duplicates`	This means that the process removes duplicate rows during a `SELECT DISTINCT` command.
`removing tmp table`	This means that the process drops an internal temporary table.
`Rolling back`	This means that the process executes `ROLLBACK`.
`Updating rows for update`	This means that the process executes an `UPDATE` command which modifies indexed columns. It searches for rows pointed by the index entries that will be modified.
`Sending data`	This means that the process sends a result set to the client.
`setup`	This means that the process executes an `ALTER TABLE` command, which is followed by preparing an alter table.
`Sorting for group`	This means that the process sorts rows, as requested by an `ORDER BY` clause.
`Sorting result`	This means that the process sorts the results of a `SELECT` query. No internal temporary table is used.
`Statistics`	This means that the process calculates index statistics.
`System lock`	This means that the process is on hold because another program (not MariaDB) locked a table file.
`update`	This means that the process prepares to update a table.
`Updating`	This means that the process is modifying rows.
`Waiting for commit lock`	This means that the process waits until a `COMMIT` command is finished.
`Waiting for global read lock`	This means that the process waits for a lock that involves all tables (like the one created by the `FLUSH TABLES … WITH READ LOCK` statement).
`Waiting for tables, Waiting for table flush`	This means that the process is similar to `Reopen tables`, but it has to wait because some threads are using the table.
`Waiting for * lock`	This means that the process waits for a metadata lock (possibly a metadata lock) to be released and `*` is replaced by the type of the locked object (for example, table level).
`Waiting on cond`	This means that the process waits for a condition lock.

Aborting connections

While examining the list of processes, you may notice that one of them is slow and is blocking other processes. Or perhaps, you may notice several sleeping processes. In this case, you may want to terminate them. The KILL statement can be used for this purpose. MariaDB supports more clauses for this command, if compared to the MySQL syntax. The syntax for KILL is as follows:

KILL
  [ HARD | SOFT ]
  [ CONNECTION | QUERY [ID] ]
  < id | USER user_name >

By default, KILL terminates a connection. The CONNECTION keyword just makes this clear for the one who reads the statement. If the QUERY keyword is specified, only the statement executed by a connection is killed, while the connection itself remains open.

The ID of the connection or statement to be killed must be specified. Even with the QUERY keyword, the thread ID must be specified; MariaDB expects a query ID only if the ID keyword is specified too. Remember that the connection ID becomes visible by querying the PROCESSLIST table, not with SHOW PROCESSLIST.

As an alternative, you can specify the USER clause followed by an account name or a username to destroy all the connections or queries that belong to the specified account or user. To kill all connections or statements that belong to our account, we can also specify CURRENT_USER().

Killing a query or connection with the SOFT option, which is the default option, can be slower but it is also safer. The HARD keyword is a faster but more brutal way to terminate operations, which should only be used if SOFT KILL takes too long. After a HARD KILL, we are likely to experience data corruption. This is how the command works:

The KILL command sets a flag for the target connection. If a connection is flagged by KILL, SHOW PROCESSLIST shows 'Killed' in the Info column.
Whenever it is safe to interrupt the current operation, the target connection checks whether the flag has been set. If it has, the connection or the statement terminates.
Sometimes, even if it is not safe, the connection checks whether a HARD KILL command was requested. If so, the connection or statement is terminated immediately, probably leaving a table in an inconsistent state.

But in the current versions, this difference only concerns the repairing of Aria or MyISAM tables, and the creation of an index on such tables. Remember that, in the first case, the data could be even more corrupted than before, and in the second case the new index will need to be rebuilt.

The following example shows how to kill a statement without closing the connection.

In a mysql command-line client, let's execute this statement:

MariaDB [(none)]> DO SLEEP(2000);

The preceding statement is not harmful for anyone, but it is good for our example because it makes the thread pause for 2000 seconds.

Next, let's open another client and execute the following statement:

MariaDB [(none)]> SHOW PROCESSLIST;
+----+-----------------+-----------+------+---------+-------+------------------------+------------------+----------+ 
| Id | User            | Host      | db   | Command | Time  | State   | Info             | Progress | 
+----+-----------------+-----------+------+---------+-------+------------------------+------------------+----------+ 
…
|  9 | root            | localhost | NULL | Query   |     8 | User sleep             | DO SLEEP(2000)   |    0.000 | 
+----+-----------------+-----------+------+---------+-------+------------------------+------------------+----------+ 
3 rows in set (0.00 sec)

Many rows are returned, but we only care about the one that shows the previous statement. The thread ID is 9. Let's use this information to terminate the statement:

MariaDB [(none)]> KILL QUERY 9;
Query OK, 0 rows affected (0.00 sec)

We can verify that the KILL statement worked by using SHOW PROCESSLIST.

An inactive connection is automatically closed by the server when the timeout specified with the wait_timeout server variable expires.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.

Table of Contents for Monitoring connections

Create new playlist

Sign In

Sign Up

Monitoring connections

States of the process

Aborting connections

Table of Contents for
Monitoring connections