I'm reviewing the slow log, and on one of my slaves the average time for SHOW GLOBAL STATUS is 914s.
Any idea how to determine the cause of this?
MySQLmysql-5performanceslow-log
I'm reviewing the slow log, and on one of my slaves the average time for SHOW GLOBAL STATUS is 914s.
Any idea how to determine the cause of this?
Best Answer
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 onSHOW STATUS
: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:
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:
If you do not have skip-host-cache and skip-name-resolve configured, please add these to
my.cnf
: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.