Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from serverfault.com Sep 1 '11 at 18:19

This question came from our site for system and network administrators.

    
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? –  Derek Downey Sep 1 '11 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 Sep 1 '11 at 21:12

2 Answers 2

Try from command-line (not mysql prompt):

$ mysqladmin flush-status
share|improve this answer
    
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 –  Derek Downey Sep 1 '11 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 Sep 1 '11 at 19:28

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

share|improve this answer
    
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 Sep 1 '11 at 21:52

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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