MySQL : Is there a command similar to FLUSH STATUS to reset GLOBAL STATUS?

Question

I'm trying to run some load tests on my MySQL server and check a bunch of relevant GLOBAL STATUS variables after each run. Is there an easy way to flush GLOBAL STATUS variables like the regular FLUSH STATUS command?

Restarting the server seems to be one way of accomplishing the same thing but I'd prefer something faster.

Answer 1

Try from command-line (not mysql prompt):

$ mysqladmin flush-status

Answer 2

I found something interesting. You may looking at the global variables the wrong way. Try using the session variables instead.

Here is an example: I ran the following commands in succession:

select sleep(1);
flush status;
select sleep(1);
show global status like 'com_select';
show session status like 'com_select';

He is what I got:

mysql> select sleep(1); flush status; select sleep(1); show global status like 'com_select'; show session status like 'com_select';
+----------+
| sleep(1) |
+----------+
|        0 |
+----------+
1 row in set (1.00 sec)

Query OK, 0 rows affected (0.00 sec)

+----------+
| sleep(1) |
+----------+
|        0 |
+----------+
1 row in set (1.01 sec)

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 25    |
+---------------+-------+
1 row in set (0.00 sec)

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 1     |
+---------------+-------+
1 row in set (0.00 sec)

mysql> select sleep(1); flush status; select sleep(1); show global status like 'com_select'; show session status like 'com_select';
+----------+
| sleep(1) |
+----------+
|        0 |
+----------+
1 row in set (1.00 sec)

Query OK, 0 rows affected (0.00 sec)

+----------+
| sleep(1) |
+----------+
|        0 |
+----------+
1 row in set (1.00 sec)

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 27    |
+---------------+-------+
1 row in set (0.00 sec)

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 1     |
+---------------+-------+
1 row in set (0.00 sec)

mysql> select sleep(1); flush status; select sleep(1); show global status like 'com_select'; show session status like 'com_select';
+----------+
| sleep(1) |
+----------+
|        0 |
+----------+
1 row in set (1.00 sec)

Query OK, 0 rows affected (0.00 sec)

+----------+
| sleep(1) |
+----------+
|        0 |
+----------+
1 row in set (1.01 sec)

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 29    |
+---------------+-------+
1 row in set (0.00 sec)

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 1     |
+---------------+-------+
1 row in set (0.00 sec)

mysql>

As shown, Com_select will reset in your session but not globally, which actually makes sense in terms of global variables. In light of this, you must tailor your testing as follows:

• Never disconnect
• Stay in the same session
• FLUSH STATUS
• Start a new test in the same session

Give it a Try !!!