Description:
Sometimes MySQL instance status infomation such as Slow_queries changed in a incredible way in short time. But we did not find any entries in slow log file.
How to repeat:
1, turn on pfs
2, set performance_schema_hosts_size=1 in configuration file
3, create user tu1@'%' identified by '123456';
4, connect root and tu1 with difference host to mysql
./bin/mysql -uroot -S mysql.sock
./bin/mysql -utu1 -p123456 --port=3307 --host=127.0.0.1
5, now mysql looks like this
==================================
mysql> select * from accounts;
+------+-----------+---------------------+-------------------+
| USER | HOST | CURRENT_CONNECTIONS | TOTAL_CONNECTIONS |
+------+-----------+---------------------+-------------------+
| NULL | NULL | 87 | 90 |
| root | localhost | 1 | 1 |
| tu1 | localhost | 1 | 1 |
+------+-----------+---------------------+-------------------+
3 rows in set (0.00 sec)
mysql> select * from hosts;
+------+---------------------+-------------------+
| HOST | CURRENT_CONNECTIONS | TOTAL_CONNECTIONS |
+------+---------------------+-------------------+
| NULL | 87 | 90 |
+------+---------------------+-------------------+
1 row in set (0.00 sec)
mysql> show global status like 'Performance_schema_hosts_lost';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| Performance_schema_hosts_lost | 2 |
+-------------------------------+-------+
1 row in set (0.01 sec)
==================================
6, create some slow entry by tu1 connection
set long_query_time=0.1;
select sleep(1);select sleep(1);select sleep(1);
===========
now mysql looks like this
mysql> show global status like 'Slow_queries';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries | 3 |
+---------------+-------+
1 row in set (0.02 sec)
===========
7, truncate accounts by root user
truncate table accounts;
===========
now mysqld looks like this
mysql> show global status like 'Slow_queries';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries | 6 |
+---------------+-------+
1 row in set (0.01 sec)
Suggested fix:
diff --git a/storage/perfschema/pfs_account.cc b/storage/perfschema/pfs_account.cc
index e8d36f8..f11bed8 100644
--- a/storage/perfschema/pfs_account.cc
+++ b/storage/perfschema/pfs_account.cc
@@ -541,7 +541,7 @@ void PFS_account::aggregate_status(PFS_user *safe_user, PFS_host *safe
- GLOBAL_STATUS
*/
safe_user->m_status_stats.aggregate(& m_status_stats);
- m_status_stats.aggregate_to(& global_status_var);
+ //m_status_stats.aggregate_to(& global_status_var);
m_status_stats.reset();
return;
}
@@ -561,7 +561,7 @@ void PFS_account::aggregate_status(PFS_user *safe_user, PFS_host *safe
Aggregate STATUS_BY_ACCOUNT to:
- GLOBAL_STATUS
*/
- m_status_stats.aggregate_to(& global_status_var);
+ //m_status_stats.aggregate_to(& global_status_var);
m_status_stats.reset();
return;
}
(
Description: Sometimes MySQL instance status infomation such as Slow_queries changed in a incredible way in short time. But we did not find any entries in slow log file. How to repeat: 1, turn on pfs 2, set performance_schema_hosts_size=1 in configuration file 3, create user tu1@'%' identified by '123456'; 4, connect root and tu1 with difference host to mysql ./bin/mysql -uroot -S mysql.sock ./bin/mysql -utu1 -p123456 --port=3307 --host=127.0.0.1 5, now mysql looks like this ================================== mysql> select * from accounts; +------+-----------+---------------------+-------------------+ | USER | HOST | CURRENT_CONNECTIONS | TOTAL_CONNECTIONS | +------+-----------+---------------------+-------------------+ | NULL | NULL | 87 | 90 | | root | localhost | 1 | 1 | | tu1 | localhost | 1 | 1 | +------+-----------+---------------------+-------------------+ 3 rows in set (0.00 sec) mysql> select * from hosts; +------+---------------------+-------------------+ | HOST | CURRENT_CONNECTIONS | TOTAL_CONNECTIONS | +------+---------------------+-------------------+ | NULL | 87 | 90 | +------+---------------------+-------------------+ 1 row in set (0.00 sec) mysql> show global status like 'Performance_schema_hosts_lost'; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | Performance_schema_hosts_lost | 2 | +-------------------------------+-------+ 1 row in set (0.01 sec) ================================== 6, create some slow entry by tu1 connection set long_query_time=0.1; select sleep(1);select sleep(1);select sleep(1); =========== now mysql looks like this mysql> show global status like 'Slow_queries'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Slow_queries | 3 | +---------------+-------+ 1 row in set (0.02 sec) =========== 7, truncate accounts by root user truncate table accounts; =========== now mysqld looks like this mysql> show global status like 'Slow_queries'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Slow_queries | 6 | +---------------+-------+ 1 row in set (0.01 sec) Suggested fix: diff --git a/storage/perfschema/pfs_account.cc b/storage/perfschema/pfs_account.cc index e8d36f8..f11bed8 100644 --- a/storage/perfschema/pfs_account.cc +++ b/storage/perfschema/pfs_account.cc @@ -541,7 +541,7 @@ void PFS_account::aggregate_status(PFS_user *safe_user, PFS_host *safe - GLOBAL_STATUS */ safe_user->m_status_stats.aggregate(& m_status_stats); - m_status_stats.aggregate_to(& global_status_var); + //m_status_stats.aggregate_to(& global_status_var); m_status_stats.reset(); return; } @@ -561,7 +561,7 @@ void PFS_account::aggregate_status(PFS_user *safe_user, PFS_host *safe Aggregate STATUS_BY_ACCOUNT to: - GLOBAL_STATUS */ - m_status_stats.aggregate_to(& global_status_var); + //m_status_stats.aggregate_to(& global_status_var); m_status_stats.reset(); return; } (