5

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.

2
  • There doesn't currently seem to be a way to flush ALL global status variables without a restart. Which ones are you specifically interested in? Commented Sep 1, 2011 at 19:58
  • currently would like to log variables like QCACHE, QUERIES, THREADS, ABORTED, READS along with COM_DELETE, COM_INSERT, COM_SELECT, COM_UPDATE, MAX_USED_CONNECTIONS, etc...
    – Dan S
    Commented Sep 1, 2011 at 21:12

2 Answers 2

2

Try from command-line (not mysql prompt):

$ mysqladmin flush-status
2
  • looks like mysqladmin flush-status does not do what I expected it to. Back with more, but there was a very old feature request for this: bugs.mysql.com/bug.php?id=22875 Commented Sep 1, 2011 at 18:29
  • thanks for trying --- yeah saw that when googling around but figured it was either enabled by now or there were better ways of doing the same thing
    – Dan S
    Commented Sep 1, 2011 at 19:28
1

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 !!!

1
  • Will try, thanks. Some of the load testing overwhelms the db server and I might not be able to keep the connection but I suppose in that case I'd have the same problem with the global variables as well.
    – Dan S
    Commented Sep 1, 2011 at 21:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.