WL#7803: PERFORMANCE SCHEMA, NATIVE FUNCTIONS

Affects: Server-8.0   —   Status: Complete   —   Priority: Medium

This worklog defines native functions to format or retrieve data from the Performance Schema:
  • format_pico_time() - Format picosecond time
  • format_bytes() - Format a number of bytes
  • PS_current_thread_id() - Get the Performance Schema thread id for the current thread
  • PS_thread_id() - Get the Performance Schema thread id for another thread

These native functions will replace corresponding stored functions in the MySQL sys schema.

Functional Requirements

F-1: Semantics

Performance Schema native functions have the same semantics as other MySQL functions. For example:

SELECT PS_thread_id(48);
SELECT PS_current_thread_id();
SELECT format_pico_time(8640059343);
SELECT format_bytes(11805916207174);

F-2: Input and Output

format_pico_time(t)

INPUT: Any valid numeric SQL representation, such as an integer, real, function or hybrid. For example:

SELECT format_pico_time(86400000000000000000);
SELECT format_pico_time(36000000.495523);
SELECT format_pico_time((1000 * pow(10,12)) * 86400); 

OUTPUT: A string containing the decimal time rounded to 2 decimal places and a minimum of 3 significant digits, appended with the corresponding unit of time. Times under 1 nanosecond are represented as whole numbers and not rounded. For example:

100 ps
647.82 us
110.65 ms
200.00 d

See the units table in High Level Specification.

format_bytes(n)

INPUT: Any valid numeric SQL representation, such as an integer, real, function or hybrid. For example:

SELECT format_bytes(1008883892);
SELECT format_bytes(988386.5242);
SELECT format_bytes(pow(2,63) * 2 - 1); 

OUTPUT: A string with the number of bytes rounded to 2 decimal places and a minimum of 3 significant digits, appended with the corresponding unit of bytes. Numbers less than 1024 bytes are represented as whole numbers and are not rounded. For example:

101 Bytes
994.00 KiB
635.84 GiB

See the units table in High Level Specification.

PS_current_thread_id()

INPUT: None.

OUTPUT: The Performance Schema thread ID assigned to the current connection. The thread ID corresponds to the THREAD_ID column in the Performance Schema tables.

If the Performance Schema thread instrumentation is disabled, an error is raised. This function never returns NULL.

PS_thread_id(connection_id)

INPUT: A connection ID.

OUTPUT: The Performance Schema thread ID assigned to the given connection ID. The thread ID corresponds to the THREAD_ID column in the Performance Schema tables.

If the connection ID does not have a Performance Schema thread ID, then NULL is returned. This can happen for server threads that are not instrumented.

Function format_pico_time(t)

Formats a time t in picoseconds into a format like '####.## UUU', rounded to 2 decimal places and a minimum of 3 significant digits, where 'UUU' is the unit of time. Times under 1 nanosecond are represented as whole numbers and are not rounded:

Up To Units
10^3 - 1 ps picoseconds
10^6 - 1 ns nanoseconds
10^9 - 1 us microseconds
10^12 - 1 ms milliseconds
60*10^12 - 1 s seconds
3.6*10^15 - 1 min minutes
8.64*10^16 - 1 d days

For example:

mysql> SELECT format_pico_time(990360000056284599) AS 'Uptime';
+----------+
| Uptime   |
+----------+
| 11.46 d  |
+----------+
1 row in set (0.00 sec)

Function format_bytes(n)

Formats a number n in bytes into a format like '####.## UUUUU', rounded to 2 decimal places and 3 significant digits, where 'UUUUU' is the units of bytes. Numbers less than 1024 bytes are represented as whole numbers and are not rounded:

Up to Units
1023 Bytes
1024^2 - 1 KiB
1024^3 - 1 MiB
1024^4 - 1 GiB
1024^5 - 1 TiB
1024^6 - 1 PiB
1024^7 - 1 EiB

For example:

mysql> SELECT format_bytes(10485556999999) AS 'Total Bytes';
+-------------+
| Total Bytes |
+-------------+
| 9.54 TiB    |
+-------------+
1 row in set (0.00 sec)

Function PS_current_thread_id()

Returns the Performance Schema thread ID for the current connection. For example:

mysql> SELECT PS_current_thread_id() as 'Current Thread';
+----------------+
| Current Thread |
+----------------+
|             42 |
+----------------+
1 row in set (0.00 sec)

If the Performance Schema is disabled, then NULL is returned:

mysql> SELECT PS_current_thread_id() as 'Current Thread';
+----------------+
| Current Thread |
+----------------+
|           NULL |
+----------------+
1 row in set (0.00 sec)

Function PS_thread_id(id)

Returns the Performance Schema thread ID for the given connection ID, id. If a thread ID does not exist for the connection ID, then NULL is returned.

For example:

mysql> SELECT PS_thread_id(237) as 'Another Thread';
+----------------+
| Another Thread |
+----------------+
|           1138 |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT PS_thread_id(8675309) as 'Invalid ID';
+----------------+
|     Invalid ID |
+----------------+
|           NULL |
+----------------+
1 row in set (0.00 sec)