Bug #64129 Accounts table in Performance_Schema does not show metadata
Submitted: 25 Jan 2012 13:51 Modified: 27 Jan 2012 5:06
Reporter: Mahesh Patil Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Performance Schema Severity:S2 (Serious)
Version:5.6.4-m7 OS:Windows
Assigned to: CPU Architecture:Any

[25 Jan 2012 13:51] Mahesh Patil
Description:
I want to get top 5 most active users using accounts table of PS.

-- top5 most connected user accounts right now
SELECT 
  `user`,
  `host`,
  `current_connections`,
  `total_connections` 
FROM
  `performance_schema`.`accounts` 
WHERE `host` IS NOT NULL 
  AND `user` IS NOT NULL 
ORDER BY `current_connections` DESC 
LIMIT 5 ;

/* This query returns nothing although there are queries being executed */
Also I have set performance_schema_accounts_size to 1024

SHOW VARIABLES LIKE 'performance_schema_accounts_size';
Returns : 1024

How to repeat:
Execute queries from multiple MySQL clients, should record entry in accounts table, but it does not

Suggested fix:
Accounts table should show values and record each entry from a client
[25 Jan 2012 15:58] Valeriy Kravchuk
Are you sure server is started with performance_schema option? See http://dev.mysql.com/doc/refman/5.6/en/performance-schema-startup-configuration.html.

I can not repeat this with current mysql-trunk on Mac OS X:

macbook-pro:trunk openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.5-m8-debug Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT 
    ->   `user`,
    ->   `host`,
    ->   `current_connections`,
    ->   `total_connections` 
    -> FROM
    ->   `performance_schema`.`accounts` 
    -> WHERE `host` IS NOT NULL 
    ->   AND `user` IS NOT NULL 
    -> ORDER BY `current_connections` DESC 
    -> LIMIT 5 ;
+------+-----------+---------------------+-------------------+
| user | host      | current_connections | total_connections |
+------+-----------+---------------------+-------------------+
| root | localhost |                   3 |                 3 |
+------+-----------+---------------------+-------------------+
1 row in set (0.02 sec)
[27 Jan 2012 4:44] Mahesh Patil
After adding performance_schema line under mysqld section,Started showing result.
Thanks for your suggestion.

I still think it should be enabled using
set @@global.performance_schema=1;
[27 Jan 2012 5:06] Valeriy Kravchuk
Our manual (http://dev.mysql.com/doc/refman/5.6/en/performance-schema-system-variables.html#sysvar_per...) clearly says this variable is NOT dynamic. So it must be set at startup.