That sounds very unusual, but not surprising. All it takes to run SHOW GLOBAL STATUS; is the USAGE privilege, which is just general connectivity as stated in the last sentence of the first paragraph of the MySQL Documentation on SHOW STATUS:
This statement does not require any privilege. It requires only the ability to connect to the server.
Given these facts, there can only be two possible causes:
POSSIBLE CAUSE #1
In MySQL 5.1 and MySQL 5.5, values for global status are accessible via the INFORMATION-SCHEMA database
Here is the table layout:
mysql> show create table information_schema.global_status\G
*************************** 1. row ***************************
Table: GLOBAL_STATUS
Create Table: CREATE TEMPORARY TABLE `GLOBAL_STATUS` (
`VARIABLE_NAME` varchar(64) NOT NULL DEFAULT '',
`VARIABLE_VALUE` varchar(1024) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
Notice it is a MEMORY table. Like MyISAM, all INSERTs, UPDATEs, and DELETEs into a MEMORY table require a full table lock (See Locking Granularity for MEMORY Storage Engine). There has to be some intermittent lock to populate values into this table. Since this table is local to the DB Connection, only the DB Connection is affected.
POSSIBLE CAUSE #2
User authentication may be blocked for a time due to host caching and DNS. Note paragraph 2 the MySQL Documentation on DNS Lookup Optimization and the Host Cache:
For each new client connection, the server uses the client IP address to check whether the client host name is in the host cache. If not, the server attempts to resolve the host name. First, it resolves the IP address to a host name and resolves that host name back to an IP address. Then it compares the result to the original IP address to ensure that they are the same. The server stores information about the result of this operation in the host cache.
If you do not have skip-host-cache and skip-name-resolve configured, please add these to my.cnf:
[mysqld]
skip-host-cache
skip-name-resolve
and restart mysql ASAP.
EPILOGUE
Cause #2 is a bit of a stretch since authentication should not trigger an entry into the slow query log.
Cause #1 is a lot more likely because if the global status is being loaded into a table, it is an SQL query with discernible passage of time.Thus, if the query's running time (in seconds) exceeds long_query_time, it will get recorded into the slow query log.
There is most definitely a difference between SHOW STATUS; and SHOW GLOBAL STATUS;
SHOW GLOBAL STATUS; will give you status variables that have updated since mysqld started for all sessions that are connected or have ever been connected.SHOW STATUS; will give you status variables that have updated within your session. The command can also be expressed as SHOW SESSION STATUS; (As the MySQL Documentation says, it displays the status values for the current connection).
To physically show the difference, the information_schema database has them separated as
INFORMATION_SCHEMA.GLOBAL_STATUSINFORMATION_SCHEMA.SESSION_STATUS
These information_schema tables have been around since MySQL 5.1.12.
Why the difference?
To thoroughly demonstrate the difference, let me run an INNER JOIN of these tables to show which values are different. Here is the query:
SELECT a.variable_name,a.variable_value,b.variable_value
FROM information_schema.global_status A INNER join information_schema.session_status B
USING (variable_name) WHERE A.variable_value <> B.variable_value;
Please note the output:
mysql> SELECT A.variable_name,A.variable_value,B.variable_value
-> FROM information_schema.global_status A INNER join information_schema.session_status B
-> USING (variable_name) WHERE A.variable_value <> B.variable_value;
+---------------------------+----------------+----------------+
| variable_name | variable_value | variable_value |
+---------------------------+----------------+----------------+
| BYTES_RECEIVED | 123641576598 | 7757 |
| BYTES_SENT | 149888451047 | 300001 |
| COM_ADMIN_COMMANDS | 121915 | 0 |
| COM_ALTER_TABLE | 111 | 0 |
| COM_BEGIN | 1 | 0 |
| COM_CALL_PROCEDURE | 530 | 0 |
| COM_CHANGE_DB | 1623 | 1 |
| COM_COMMIT | 19220 | 0 |
| COM_CREATE_FUNCTION | 4 | 0 |
| COM_CREATE_PROCEDURE | 126 | 0 |
| COM_CREATE_TABLE | 354 | 0 |
| COM_DEALLOC_SQL | 924 | 0 |
| COM_DELETE | 64668 | 0 |
| COM_DELETE_MULTI | 19 | 0 |
| COM_DROP_FUNCTION | 4 | 0 |
| COM_DROP_PROCEDURE | 148 | 0 |
| COM_DROP_TABLE | 238 | 0 |
| COM_EXECUTE_SQL | 945 | 0 |
| COM_INSERT | 1182379 | 0 |
| COM_INSERT_SELECT | 40673 | 0 |
| COM_KILL | 68 | 0 |
| COM_LOAD | 22386 | 0 |
| COM_LOCK_TABLES | 2 | 0 |
| COM_OPTIMIZE | 2 | 0 |
| COM_PREPARE_SQL | 948 | 0 |
| COM_REPAIR | 8 | 0 |
| COM_REPLACE | 34737 | 0 |
| COM_ROLLBACK | 13 | 0 |
| COM_SELECT | 1107225018 | 65 |
| COM_SET_OPTION | 602159 | 0 |
| COM_SHOW_BINLOGS | 8 | 0 |
| COM_SHOW_CHARSETS | 12 | 0 |
| COM_SHOW_COLLATIONS | 100 | 0 |
| COM_SHOW_CREATE_DB | 6 | 0 |
| COM_SHOW_CREATE_FUNC | 2453 | 0 |
| COM_SHOW_CREATE_PROC | 5684 | 0 |
| COM_SHOW_CREATE_TABLE | 1313 | 0 |
| COM_SHOW_DATABASES | 275 | 0 |
| COM_SHOW_EVENTS | 1 | 0 |
| COM_SHOW_FIELDS | 13666 | 1 |
| COM_SHOW_FUNCTION_STATUS | 362 | 0 |
| COM_SHOW_KEYS | 494 | 0 |
| COM_SHOW_PLUGINS | 2 | 0 |
| COM_SHOW_PROCEDURE_STATUS | 361 | 0 |
| COM_SHOW_PROCESSLIST | 488943 | 15 |
| COM_SHOW_SLAVE_STATUS | 4 | 0 |
| COM_SHOW_STATUS | 12315 | 10 |
| COM_SHOW_STORAGE_ENGINES | 30 | 0 |
| COM_SHOW_TABLE_STATUS | 320 | 0 |
| COM_SHOW_TABLES | 584 | 0 |
| COM_SHOW_TRIGGERS | 2 | 0 |
| COM_SHOW_VARIABLES | 190 | 1 |
| COM_STMT_CLOSE | 924 | 0 |
| COM_STMT_EXECUTE | 945 | 0 |
| COM_STMT_PREPARE | 948 | 0 |
| COM_TRUNCATE | 522 | 0 |
| COM_UNLOCK_TABLES | 2 | 0 |
| COM_UPDATE | 496041 | 0 |
| COM_UPDATE_MULTI | 625 | 0 |
| CREATED_TMP_DISK_TABLES | 16772 | 40 |
| CREATED_TMP_TABLES | 34336 | 63 |
| HANDLER_COMMIT | 1109540769 | 0 |
| HANDLER_DELETE | 12775993 | 0 |
| HANDLER_EXTERNAL_LOCK | 2228108102 | 24 |
| HANDLER_PREPARE | 2155764 | 0 |
| HANDLER_READ_FIRST | 23586 | 9 |
| HANDLER_READ_KEY | 18285349400 | 0 |
| HANDLER_READ_LAST | 13000 | 0 |
| HANDLER_READ_NEXT | 72142303428 | 0 |
| HANDLER_READ_PREV | 3000146 | 0 |
| HANDLER_READ_RND | 1261418742 | 156 |
| HANDLER_READ_RND_NEXT | 12320861765 | 7845 |
| HANDLER_ROLLBACK | 269376 | 0 |
| HANDLER_UPDATE | 2596924399 | 0 |
| HANDLER_WRITE | 8200421074 | 8241 |
| LAST_QUERY_COST | 0.000000 | 21.399123 |
| LAST_QUERY_PARTIAL_PLANS | 0 | 3 |
| OPENED_TABLE_DEFINITIONS | 2482 | 0 |
| OPENED_TABLES | 3619 | 0 |
| QUESTIONS | 1110214247 | 97 |
| SELECT_FULL_JOIN | 615 | 9 |
| SELECT_RANGE | 243635 | 0 |
| SELECT_SCAN | 47851 | 53 |
| SLOW_QUERIES | 29290 | 50 |
| SORT_MERGE_PASSES | 6 | 0 |
| SORT_RANGE | 179956 | 0 |
| SORT_ROWS | 321609927 | 156 |
| SORT_SCAN | 1829 | 39 |
| TABLE_OPEN_CACHE_HITS | 1109365721 | 13 |
| TABLE_OPEN_CACHE_MISSES | 1669 | 0 |
+---------------------------+----------------+----------------+
90 rows in set (0.03 sec)
mysql>
Look at four variables:
| BYTES_RECEIVED | 123641576598 | 7757 |
| BYTES_SENT | 149888451047 | 300001 |
| COM_ADMIN_COMMANDS | 121915 | 0 |
| COM_SHOW_PROCESSLIST | 488955 | 15 |
What does this tell you?
- mysqld received 123,641,576,598 bytes (115.15GB) from all DB Connections since mysqld started
- The session I ran the query with received 7,757 bytes (a little over 7K) in my current session
- mysqld sent 149,888,451,047 bytes (139.59GB) from all DB Connections since mysqld started
- The session I ran the query with sent 300,001 bytes (a little under 297K) in my current session
- There have been 121,915 administrative commands that have run since mysqld started
- There have benn 0 administrative commands that have run in my current session
- The command
SHOW PROCESSLIST has been run 488,955 times since mysqld started
- The command
SHOW PROCESSLIST has been run 15 times in my current session
You can compare the other 86 varibales and interpret them the same way.
Give it a Try !!!
Best Answer