Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 37.9Mb
PDF (A4) - 37.9Mb
PDF (RPM) - 32.9Mb
HTML Download (TGZ) - 8.0Mb
HTML Download (Zip) - 8.1Mb
HTML Download (RPM) - 6.9Mb
Man Pages (TGZ) - 132.7Kb
Man Pages (Zip) - 189.0Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual

MySQL 8.0 Reference Manual  /  ...  /  Server Status Variables

5.1.9 Server Status Variables

The MySQL server maintains many status variables that provide information about its operation. You can view these variables and their values by using the SHOW [GLOBAL | SESSION] STATUS statement (see Section 13.7.6.35, “SHOW STATUS Syntax”). The optional GLOBAL keyword aggregates the values over all connections, and SESSION shows the values for the current connection.

mysql> SHOW GLOBAL STATUS;
+-----------------------------------+------------+
| Variable_name                     | Value      |
+-----------------------------------+------------+
| Aborted_clients                   | 0          |
| Aborted_connects                  | 0          |
| Bytes_received                    | 155372598  |
| Bytes_sent                        | 1176560426 |
...
| Connections                       | 30023      |
| Created_tmp_disk_tables           | 0          |
| Created_tmp_files                 | 3          |
| Created_tmp_tables                | 2          |
...
| Threads_created                   | 217        |
| Threads_running                   | 88         |
| Uptime                            | 1389872    |
+-----------------------------------+------------+

Several status variables provide statement counts. To determine the number of statements executed, use these relationships:

  SUM(Com_xxx)
= Questions + statements executed within stored programs
= Queries

Many status variables are reset to 0 by the FLUSH STATUS statement.

This section provides a description of each status variable. For a status variable summary, see Section 5.1.5, “Server Status Variable Reference”.

The status variables have the following meanings.


User Comments
  Posted by Demetrios Stavrinos on July 22, 2004
Reading the explanation for Handler read rnd next , I question it! I list some number from a test db that does almost all accesses by locating a record with a key (GE or xxx%) and then using next to access related record; yet the Handler read rnd next is relatively large.
Handler read key 42053
Handler read next 453703
Handler read rnd 696
Handler read rnd next 104378

  Posted by Heikki Hannikainen on April 28, 2006
On MySQL 5.0 the com_* variables of 'show status' are counted for the current connection only. The new undocumented command 'show global status' shows server-wide counters. (http://bugs.mysql.com/bug.php?id=19422)
  Posted by sheila yao on April 16, 2007
In version 4.0.17, the explanation to Handler_ is different:
Example:
Handler_delete:
The number of times a row was deleted from a table.
Handler_update
The number of requests to update a row in a table.
Handler_write
The number of requests to insert a row in a table.

Could this be a typo for Handler_delete? Because requests and rows updated/deleted/inserted are different concept.

Sheila

  Posted by Roel Van de Paar on September 21, 2009
There's a bug in 5.0.36 (and likely surrounding versions) for Handler_write: it increases by one every time a row from SHOW STATUS is displayed:

------
mysql> FLUSH STATUS;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GLOBAL STATUS; SHOW SESSION STATUS;
[...] 224 rows in set (0.01 sec)
[...] 224 rows in set (0.01 sec)

mysql> SHOW SESSION STATUS LIKE 'Handler_write';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Handler_write | 448 |
+---------------+-------+
1 row in set (0.00 sec)
------

Given the bug for Handler_write it is not possible to find out how many rows were inserted in this version, unless you use a workaround:

Rows_inserted= Handler_write - (number of times SHOW STATUS was run * number of rows SHOW STATUS displayed)

This could be programmed further using the Com_show_status variable:

--------
mysql> SHOW SESSION STATUS LIKE 'Com_show_status';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| Com_show_status | 8 |
+-----------------+-------+
1 row in set (0.00 sec)
--------

So, on a system/version that had 224 rows in SHOW STATUS (please check), the workaround formula would be:

Rows_inserted=Handler_write-(Com_show_status * 224)

Note though that this workaround does not work when using partial SHOW STATUS statements (using LIKE etc) from time to time.
Sign Up Login You must be logged in to post a comment.