MariaDB 10.5 and Memory Instrumentation in Performance Schema - Basic Checks

My MariaDB 10.5-related talk at upcoming MariaDB Server Fest is already announced, so I have no other option but to continue writing blog posts while preparing for it. 

In the previous one we enabled memory instrumentation and tried to run some basic queries again the performance_schema.memory_summary_global_by_event_name table. Now I'd like to use it for checking how much memory is allocated to the Performance Schema itself and when this allocation happens. For this I am starting 10.5 with performance_schema and memory instrumentation enabled:

openxs@ao756:~/dbs/maria10.5$ bin/mysqld_safe --no-defaults --port=3311 --socket=/tmp/mariadb105.sock --performance_schema --performance-schema-instrument='memory/%=ON' &
[4] 19144
openxs@ao756:~/dbs/maria10.5$ bin/mysql --socket=/tmp/mariadb105.sock -e"show global variables like 'performance_schema';;select version(), count(*) from performance_schema.setup_instruments where name like 'memory%' and enabled='yes'"
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+
+----------------+----------+
| version()      | count(*) |
+----------------+----------+
| 10.5.6-MariaDB |      270 |
+----------------+----------+

Let's start the initial memory usage for allocations related to the performance_schema:

openxs@ao756:~/dbs/maria10.5$ bin/mysql --socket=/tmp/mariadb105.sock -e"select sum(SUM_NUMBER_OF_BYTES_ALLOC) alloc, sum(SUM_NUMBER_OF_BYTES_FREE) free, sum(CURRENT_NUMBER_OF_BYTES_USED) used from performance_schema.memory_summary_global_by_event_name where event_name like 'memory/performance%'"
+----------+------+----------+
| alloc    | free | used     |
+----------+------+----------+
| 97903648 |    0 | 97903648 |
+----------+------+----------+
openxs@ao756:~/dbs/maria10.5$ bin/mysql --socket=/tmp/mariadb105.sock -e"show engine performance_schema status\G" | tail -4
*************************** 229. row ***************************
  Type: performance_schema
  Name: performance_schema.memory
Status: 97902160

So we know that now with mostly default settings 10.5 uses a bit more than 93M of memory We can also note a difference in the sum of memory allocated according to the memory_summary_global_by_event_name table and the total memory from show engine performance_schema status output. The difference is resent in MySQL 5.7 as well:

openxs@ao756:~/dbs/5.7$ bin/mysqld_safe --no-defaults --port=3310 --socket=/tmp/mysql57.sock --performance_schema=1 --performance-schema-instrument='memory/%=ON' &
[5] 20459
openxs@ao756:~/dbs/5.7$ bin/mysql -uroot --socket=/tmp/mysql57.sock -e"select sum(SUM_NUMBER_OF_BYTES_ALLOC) alloc, sum(SUM_NUMBER_OF_BYTES_FREE) free, sum(CURRENT_NUMBER_OF_BYTES_USED) used from performance_schema.memory_summary_global_by_event_name where event_name like 'memory/performance%'"
+-----------+------+-----------+
| alloc     | free | used      |
+-----------+------+-----------+
| 137641608 |    0 | 137641608 |
+-----------+------+-----------+
openxs@ao756:~/dbs/5.7$ bin/mysql -uroot --socket=/tmp/mysql57.sock -e"show engine performance_schema status\G" | tail -4
*************************** 229. row ***************************
  Type: performance_schema
  Name: performance_schema.memory
Status: 137640120
openxs@ao756:~/dbs/5.7$ bin/mysql -uroot --socket=/tmp/mysql57.sock -e"select 137641608 - 137640120"
+-----------------------+
| 137641608 - 137640120 |
+-----------------------+
|                  1488 |
+-----------------------+

The difference is the same, 1488 bytes, so MariaDB probably inherited the problem that I've reported as Bug #100624 - "Total memory allocation for P_S is different in different sources".

Let's also get top 5 memory users:

openxs@ao756:~/dbs/maria10.5$ bin/mysql --socket=/tmp/mariadb105.sock -e"select EVENT_NAME, SUM_NUMBER_OF_BYTES_ALLOC alloc, SUM_NUMBER_OF_BYTES_FREE free, CURRENT_NUMBER_OF_BYTES_USED used from performance_schema.memory_summary_global_by_event_name where event_name like 'memory/performance%' order by 2 desc limit 5"
+------------------------------------------------------------------------------+----------+------+----------+
| EVENT_NAME                                                                   | alloc    | free | used     |
+------------------------------------------------------------------------------+----------+------+----------+
| memory/performance_schema/events_statements_summary_by_thread_by_event_name  | 10457088 |    0 | 10457088 |
| memory/performance_schema/memory_summary_by_thread_by_event_name             |  5898240 |    0 |  5898240 |
| memory/performance_schema/events_statements_summary_by_account_by_event_name |  5228544 |    0 |  5228544 |
| memory/performance_schema/events_statements_summary_by_host_by_event_name    |  5228544 |    0 |  5228544 |
| memory/performance_schema/events_statements_summary_by_user_by_event_name    |  5228544 |    0 |  5228544 |
+------------------------------------------------------------------------------+----------+------+----------+

Now let's add some load, 4 threads in a simple sysbench test:

openxs@ao756:~/dbs/maria10.5$ sysbench --table-size=1000000 --threads=4 --time=10 --mysql-socket=/tmp/mariadb105.sock --mysql-user=openxs --mysql-db=sbtest /usr/share/sysbench/oltp_read_only.lua run
sysbench 1.1.0-faaff4f (using bundled LuaJIT 2.1.0-beta3)

Running the test with following options:
Number of threads: 4
...
openxs@ao756:~/dbs/maria10.5$ bin/mysql --socket=/tmp/mariadb105.sock -e"select sum(SUM_NUMBER_OF_BYTES_ALLOC) alloc, sum(SUM_NUMBER_OF_BYTES_FREE) free, sum(CURRENT_NUMBER_OF_BYTES_USED) used from performance_schema.memory_summary_global_by_event_name where event_name like 'memory/performance%'"
+-----------+------+-----------+
| alloc     | free | used      |
+-----------+------+-----------+
| 109110496 |    0 | 109110496 |
+-----------+------+-----------+
openxs@ao756:~/dbs/maria10.5$ bin/mysql --socket=/tmp/mariadb105.sock -e"show engine performance_schema status\G" | tail -4
*************************** 229. row ***************************
  Type: performance_schema
  Name: performance_schema.memory
Status: 109108816
openxs@ao756:~/dbs/maria10.5$ bin/mysql --socket=/tmp/mariadb105.sock -e"select 109110496 - 97903648, 109108816 - 97902160"
+----------------------+----------------------+
| 109110496 - 97903648 | 109108816 - 97902160 |
+----------------------+----------------------+
|             11206848 |             11206656 |
+----------------------+----------------------+

We can see that after the load total memory usage by the performance_schema increased, based on both sources! Moreover, both sources report different values and different increase, but it's still around 11M. If we try more threads:

openxs@ao756:~/dbs/maria10.5$ sysbench --table-size=1000000 --threads=34 --time=10 --mysql-socket=/tmp/mariadb105.sock --mysql-user=openxs --mysql-db=sbtest /usr/share/sysbench/oltp_read_only.lua run
...
openxs@ao756:~/dbs/maria10.5$ bin/mysql --socket=/tmp/mariadb105.sock -e"select sum(SUM_NUMBER_OF_BYTES_ALLOC) alloc, sum(SUM_NUMBER_OF_BYTES_FREE) free, sum(CURRENT_NUMBER_OF_BYTES_USED) used from performance_schema.memory_summary_global_by_event_name where event_name like 'memory/performance%'"
+-----------+------+-----------+
| alloc     | free | used      |
+-----------+------+-----------+
| 109258144 |    0 | 109258144 |
+-----------+------+-----------+
openxs@ao756:~/dbs/maria10.5$ bin/mysql --socket=/tmp/mariadb105.sock -e"show engine performance_schema status\G" | tail -4
*************************** 229. row ***************************
  Type: performance_schema
  Name: performance_schema.memory
Status: 109256272
openxs@ao756:~/dbs/maria10.5$ bin/mysql --socket=/tmp/mariadb105.sock -e"select 109258144 - 109110496"
+-----------------------+
| 109258144 - 109110496 |
+-----------------------+
|                147648 |
+-----------------------+

we see some additional, small increase in total memory allocated and used. Repeating the load with the same concurrency does not change memory allocated. Top 5 memory users now are a bit different:

openxs@ao756:~/dbs/maria10.5$ bin/mysql --socket=/tmp/mariadb105.sock -e"select EVENT_NAME, SUM_NUMBER_OF_BYTES_ALLOC alloc, SUM_NUMBER_OF_BYTES_FREE free, CURRENT_NUMBER_OF_BYTES_USED used from performance_schema.memory_summary_global_by_event_name where event_name like 'memory/performance%' order by 2 desc limit 5"
+------------------------------------------------------------------------------+----------+------+----------+
| EVENT_NAME                                                                   | alloc    | free | used     |
+------------------------------------------------------------------------------+----------+------+----------+
| memory/performance_schema/events_statements_summary_by_thread_by_event_name  | 10457088 |    0 | 10457088 |
| memory/performance_schema/table_handles                                      |  9502720 |    0 |  9502720 |
| memory/performance_schema/memory_summary_by_thread_by_event_name             |  5898240 |    0 |  5898240 |
| memory/performance_schema/events_statements_summary_by_account_by_event_name |  5228544 |    0 |  5228544 |
| memory/performance_schema/events_statements_summary_by_host_by_event_name    |  5228544 |    0 |  5228544 |
+------------------------------------------------------------------------------+----------+------+----------+

Note that memory/performance_schema/table_handles allocation had increased dynamically and the second row now. 

These dynamic allocations are expected based on fine MySQL manual:

"The Performance Schema dynamically allocates memory incrementally, scaling its memory use to actual server load, instead of allocating required memory during server startup. Once memory is allocated, it is not freed until the server is restarted."

Now back to the table we've used. We do not have any nice comments for the columns:

openxs@ao756:~/dbs/maria10.5$ bin/mysql --socket=/tmp/mariadb105.sock -e"show create table performance_schema.memory_summary_global_by_event_name\G"            *************************** 1. row ***************************
       Table: memory_summary_global_by_event_name
Create Table: CREATE TABLE `memory_summary_global_by_event_name` (
  `EVENT_NAME` varchar(128) NOT NULL,
  `COUNT_ALLOC` bigint(20) unsigned NOT NULL,
  `COUNT_FREE` bigint(20) unsigned NOT NULL,
  `SUM_NUMBER_OF_BYTES_ALLOC` bigint(20) unsigned NOT NULL,
  `SUM_NUMBER_OF_BYTES_FREE` bigint(20) unsigned NOT NULL,
  `LOW_COUNT_USED` bigint(20) NOT NULL,
  `CURRENT_COUNT_USED` bigint(20) NOT NULL,
  `HIGH_COUNT_USED` bigint(20) NOT NULL,
  `LOW_NUMBER_OF_BYTES_USED` bigint(20) NOT NULL,
  `CURRENT_NUMBER_OF_BYTES_USED` bigint(20) NOT NULL,
  `HIGH_NUMBER_OF_BYTES_USED` bigint(20) NOT NULL
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8

Current MariaDB KB article on the table does NOT add much value onm top of the above. So, we have to rely on MySQL manual for explanations of columns' semantics and basic usage principles.

I am trying to focus on features, but end up reporting bugs and problems...

To summarize:

• In MariaDB 10.5 (same as in MySQL 5.7+) memory for performance_schema is allocated not only at startup, but also dynamically at runtime and is never returned back to the system. So we have one more potential "memory leak" to care about.
• Total memory used is reported differently by SHOW ENGINE... and memory_summary_global_by_event_name table. This is a bug.
  
• By default MariaDB 10.5 seems to allocate less memory than MySQL 5.7 for Performance Schema in total. See my old report MDEV-20216 also.
  
• So far we mostly have to rely on fine MySQL 5.7 manual if the details on memory instrumentation are needed.