THREAD_STATISTICS is always empty

[Swany]

Reproducible on both 5.1 and 5.5.

mysql> select @@userstat;
+------------+
| @@userstat |
+------------+
| 1 |
+------------+
1 row in set (0.00 sec)

mysql> show thread_statistics;
Empty set (0.00 sec)

mysql> select * from information_schema.thread_statistics;
Empty set (0.01 sec)

[Stewart Smith (stewart)]

should be a rather simple test case at least.

[Stewart Smith (stewart)]

mysql> select * from information_schema.thread_statistics;
Empty set (0.00 sec)

mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from thread_statistics;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'thread_statistics' at line 1
mysql> select * from `thread_statistics`;
Empty set (0.00 sec)

mysql>

[Laurynas Biveinis (laurynas-biveinis)]

We have a report that 5.1 fails in the same way.

[Alexey Kopytov (akopytov)]

There are 2 different issues:

1. THREAD_STATISTICS is a reserved word in 5.5. This is bug #997036.
2. THREAD_STATISTICS is always empty in both 5.1 and 5.5.

This report was originally about issue #1, so technically it's a duplicate of bug #997036 or the other way around. But since there's already some confusion on what this bug is about, let's keep it as a report about issue #2, and bug #997036 as a report about issue #1.

Changing summary and report accordingly.

[Alexey Kopytov (akopytov)]

And the reason for issue #2, i.e. thread_statistics being an empty table, is that to enable thread statistics collection, setting userstat to ON is insufficient, one also has to set the thread_statistics variable to ON (5.5 also requires quoting until bug #997036 is fixed):

mysql> select @@userstat;
+------------+
| @@userstat |
+------------+
| 1 |
+------------+
1 row in set (0.00 sec)

mysql> select * from information_schema.thread_statistics;
Empty set (0.00 sec)

mysql> set global `thread_statistics`=on;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from information_schema.thread_statistics\G
*************************** 1. row ***************************
             THREAD_ID: 1
     TOTAL_CONNECTIONS: 1
CONCURRENT_CONNECTIONS: 0
        CONNECTED_TIME: 2
             BUSY_TIME: 0
              CPU_TIME: 0
        BYTES_RECEIVED: 55
            BYTES_SENT: 0
  BINLOG_BYTES_WRITTEN: 0
          ROWS_FETCHED: 1
          ROWS_UPDATED: 0
       TABLE_ROWS_READ: 0
       SELECT_COMMANDS: 1
       UPDATE_COMMANDS: 0
        OTHER_COMMANDS: 0
   COMMIT_TRANSACTIONS: 0
 ROLLBACK_TRANSACTIONS: 0
    DENIED_CONNECTIONS: 0
      LOST_CONNECTIONS: 0
         ACCESS_DENIED: 0
         EMPTY_QUERIES: 0
1 row in set (0.00 sec)

[Alexey Kopytov (akopytov)]

I see no reasons for having a separate system variable to control thread_statistics. I think the fix for this bug would be to keep the variable for compatibility, but make thread stats enabled when userstat is ON.

[Laurynas Biveinis (laurynas-biveinis)]

One argument for having a separate system variable for thread_statistics is that its memory usage grows rather fast when enabled, presumably much faster than for the rest of usertat: https://www.percona.com/blog/2017/07/11/thread_statistics-and-high-memory-usage/

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1850