Description:
in sys schema x$memory_global_by_current_bytes view is not same as memory_global_by_current_bytes.
Create View: CREATE ALGORITHM=MERGE DEFINER=`mysql.sys`@`localhost` SQL SECURITY INVOKER VIEW `x$memory_global_by_current_bytes` (`event_name`,`current_count`,`current_alloc`,`current_avg_alloc`,`high_count`,`high_alloc`,`high_avg_alloc`) AS select `performance_schema`.`memory_summary_global_by_event_name`.`EVENT_NAME` AS `event_name`,`performance_schema`.`memory_summary_global_by_event_name`.`CURRENT_COUNT_USED` AS `current_count`,`performance_schema`.`memory_summary_global_by_event_name`.`CURRENT_NUMBER_OF_BYTES_USED` AS `current_alloc`,ifnull((`performance_schema`.`memory_summary_global_by_event_name`.`CURRENT_NUMBER_OF_BYTES_USED` / nullif(`performance_schema`.`memory_summary_global_by_event_name`.`CURRENT_COUNT_USED`,0)),0) AS `current_avg_alloc`,`performance_schema`.`memory_summary_global_by_event_name`.`HIGH_COUNT_USED` AS `high_count`,`performance_schema`.`memory_summary_global_by_event_name`.`HIGH_NUMBER_OF_BYTES_USED` AS `high_alloc`,ifnull((`performance_schema`.`memory_summary_global_by_event_name`.`HIGH_NUMBER_OF_BYTES_USED` / nullif(`performance_schema`.`memory_summary_global_by_event_name`.`HIGH_COUNT_USED`,0)),0) AS `high_avg_alloc` from `performance_schema`.`memory_summary_global_by_event_name` where (`performance_schema`.`memory_summary_global_by_event_name`.`CURRENT_NUMBER_OF_BYTES_USED` > 0) order by `performance_schema`.`memory_summary_global_by_event_name`.`CURRENT_NUMBER_OF_BYTES_USED` desc
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
The problem here is if we query the memory_global_by_current_bytes and x$memory_global_by_current_bytes Views they give different results for memory usage which may be confusing for users.
ref: https://dev.mysql.com/doc/refman/8.0/en/sys-memory-global-by-current-bytes.html
Example:
> SELECT SUBSTRING_INDEX(event_name,'/',2) AS code_area, SUM(current_alloc/1024/1024) AS current_allocMb FROM sys.memory_global_by_current_bytes GROUP BY SUBSTRING_INDEX(event_name,'/',2) ORDER BY SUM(current_alloc) DESC;
+---------------------------+--------------------------+
| code_area | current_allocMb |
+---------------------------+--------------------------+
| memory/sql | 0.009774971008300782 |
| memory/performance_schema | 0.0075833797454833984 |
| memory/innodb | 0.006267375946044922 |
| memory/mysys | 0.0009424877166748047 |
| memory/myisam | 0.00066375732421875 |
| memory/mysqld_openssl | 0.00021210670471191406 |
| memory/csv | 0.00008392333984375 |
| memory/blackhole | 0.00008392333984375 |
| memory/vio | 0.0000152587890625 |
| memory/mysqlx | 0.0000029659271240234374 |
| memory/temptable | 0.0000019073486328125 |
+---------------------------+--------------------------+
11 rows in set (0.00 sec)
> SELECT SUBSTRING_INDEX(event_name,'/',2) AS code_area, SUM(current_alloc/1024/1024) AS current_allocMb FROM sys.x$memory_global_by_current_bytes GROUP BY SUBSTRING_INDEX(event_name,'/',2) ORDER BY SUM(current_alloc) DESC;
+---------------------------+-----------------+
| code_area | current_allocMb |
+---------------------------+-----------------+
| memory/sql | 892.37761780 |
| memory/innodb | 604.44603543 |
| memory/performance_schema | 276.61473088 |
| memory/mysys | 8.63545609 |
| memory/temptable | 2.00000000 |
| memory/mysqld_openssl | 0.21719551 |
| memory/mysqlx | 0.00303650 |
| memory/myisam | 0.00066376 |
| memory/csv | 0.00008392 |
| memory/blackhole | 0.00008392 |
| memory/vio | 0.00001526 |
+---------------------------+-----------------+
11 rows in set (0.01 sec)
How to repeat:
Example:
> SELECT SUBSTRING_INDEX(event_name,'/',2) AS code_area, SUM(current_alloc/1024/1024) AS current_allocMb FROM sys.memory_global_by_current_bytes GROUP BY SUBSTRING_INDEX(event_name,'/',2) ORDER BY SUM(current_alloc) DESC;
+---------------------------+--------------------------+
| code_area | current_allocMb |
+---------------------------+--------------------------+
| memory/sql | 0.009774971008300782 |
| memory/performance_schema | 0.0075833797454833984 |
| memory/innodb | 0.006267375946044922 |
| memory/mysys | 0.0009424877166748047 |
| memory/myisam | 0.00066375732421875 |
| memory/mysqld_openssl | 0.00021210670471191406 |
| memory/csv | 0.00008392333984375 |
| memory/blackhole | 0.00008392333984375 |
| memory/vio | 0.0000152587890625 |
| memory/mysqlx | 0.0000029659271240234374 |
| memory/temptable | 0.0000019073486328125 |
+---------------------------+--------------------------+
11 rows in set (0.00 sec)
> SELECT SUBSTRING_INDEX(event_name,'/',2) AS code_area, SUM(current_alloc/1024/1024) AS current_allocMb FROM sys.x$memory_global_by_current_bytes GROUP BY SUBSTRING_INDEX(event_name,'/',2) ORDER BY SUM(current_alloc) DESC;
+---------------------------+-----------------+
| code_area | current_allocMb |
+---------------------------+-----------------+
| memory/sql | 892.37761780 |
| memory/innodb | 604.44603543 |
| memory/performance_schema | 276.61473088 |
| memory/mysys | 8.63545609 |
| memory/temptable | 2.00000000 |
| memory/mysqld_openssl | 0.21719551 |
| memory/mysqlx | 0.00303650 |
| memory/myisam | 0.00066376 |
| memory/csv | 0.00008392 |
| memory/blackhole | 0.00008392 |
| memory/vio | 0.00001526 |
+---------------------------+-----------------+
11 rows in set (0.01 sec)
